-- StartupLX MySQL schema for shared hosting (phpMyAdmin import)

CREATE TABLE IF NOT EXISTS leads (
    id VARCHAR(64) NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    company VARCHAR(255) DEFAULT NULL,
    business_type VARCHAR(100) DEFAULT 'other',
    service_needed VARCHAR(100) DEFAULT 'web-development',
    notes TEXT,
    source VARCHAR(100) DEFAULT 'website',
    status VARCHAR(50) DEFAULT 'new',
    budget INT DEFAULT NULL,
    estimated_value INT DEFAULT 0,
    pipedrive_id VARCHAR(64) DEFAULT NULL,
    stripe_customer_id VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_leads_status (status),
    INDEX idx_leads_email (email),
    INDEX idx_leads_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS projects (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    client_id VARCHAR(64) DEFAULT NULL,
    lead_id VARCHAR(64) DEFAULT NULL,
    budget INT DEFAULT 0,
    actual_cost INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'prospect',
    start_date DATE DEFAULT NULL,
    completion_date DATE DEFAULT NULL,
    delivery_date DATE DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_projects_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS maintenance_clients (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id VARCHAR(64) DEFAULT NULL,
    lead_id VARCHAR(64) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    service_type VARCHAR(50) DEFAULT 'sla',
    monthly_fee INT DEFAULT 100,
    status VARCHAR(50) DEFAULT 'active',
    stripe_customer_id VARCHAR(255) DEFAULT NULL,
    stripe_subscription_id VARCHAR(255) DEFAULT NULL,
    start_date DATE DEFAULT (CURRENT_DATE),
    renewal_date DATE DEFAULT NULL,
    next_bill_date DATE DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_maintenance_renewal (renewal_date),
    INDEX idx_maintenance_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stripe_transactions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    stripe_id VARCHAR(255) DEFAULT NULL,
    amount INT DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'USD',
    status VARCHAR(50) DEFAULT NULL,
    customer_email VARCHAR(255) DEFAULT NULL,
    description TEXT,
    package_key VARCHAR(100) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_stripe_id (stripe_id),
    INDEX idx_stripe_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED DEFAULT NULL,
    lead_id VARCHAR(64) DEFAULT NULL,
    invoice_number VARCHAR(50) DEFAULT NULL,
    amount INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'draft',
    due_date DATE DEFAULT NULL,
    paid_date DATE DEFAULT NULL,
    stripe_invoice_id VARCHAR(255) DEFAULT NULL,
    package_key VARCHAR(100) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_invoice_number (invoice_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS time_tracking (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED DEFAULT NULL,
    hours_billed INT DEFAULT 0,
    hours_available INT DEFAULT 40,
    work_date DATE DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;