-- StartupLX Database Schema
-- Run this on your PostgreSQL instance

-- 1. Projects Table
CREATE TABLE IF NOT EXISTS projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    client_id INTEGER,
    budget INTEGER DEFAULT 0,
    actual_cost INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'prospect',
    start_date DATE,
    completion_date DATE,
    delivery_date DATE,
    assigned_to INTEGER,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 2. Leads Table
CREATE TABLE IF NOT EXISTS leads (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    company VARCHAR(255),
    source VARCHAR(100),
    status VARCHAR(50) DEFAULT 'new',
    budget INTEGER,
    industry VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 3. Maintenance Clients Table
CREATE TABLE IF NOT EXISTS maintenance_clients (
    id SERIAL PRIMARY KEY,
    client_id INTEGER,
    service_type VARCHAR(50),
    monthly_fee INTEGER DEFAULT 100,
    status VARCHAR(50) DEFAULT 'active',
    start_date DATE DEFAULT CURRENT_DATE,
    renewal_date DATE,
    next_bill_date DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 4. Stripe Transactions
CREATE TABLE IF NOT EXISTS stripe_transactions (
    id SERIAL PRIMARY KEY,
    stripe_id VARCHAR(255),
    amount INTEGER,
    currency VARCHAR(3) DEFAULT 'USD',
    status VARCHAR(50),
    customer_email VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 5. Invoices
CREATE TABLE IF NOT EXISTS invoices (
    id SERIAL PRIMARY KEY,
    project_id INTEGER,
    invoice_number VARCHAR(50) UNIQUE,
    amount INTEGER,
    status VARCHAR(50) DEFAULT 'draft',
    due_date DATE,
    paid_date DATE,
    stripe_invoice_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 6. Time Tracking
CREATE TABLE IF NOT EXISTS time_tracking (
    id SERIAL PRIMARY KEY,
    project_id INTEGER,
    user_id INTEGER,
    hours_billed INTEGER,
    hours_available INTEGER,
    date DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for Performance
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
CREATE INDEX IF NOT EXISTS idx_stripe_created ON stripe_transactions(created_at);
CREATE INDEX IF NOT EXISTS idx_maintenance_renewal ON maintenance_clients(renewal_date);