-- Keelance SaaS Multi-Tenant Database Schema - MySQL Compatible (Correct Order)

-- Step 1: Core tables (no foreign keys)
CREATE TABLE companies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    subdomain VARCHAR(100) UNIQUE NOT NULL,
    plan_id INT,
    subscription_status ENUM('trial', 'active', 'past_due', 'canceled') DEFAULT 'trial',
    subscription_expires_at DATETIME,
    stripe_customer_id VARCHAR(255),
    razorpay_customer_id VARCHAR(255),
    billing_cycle ENUM('monthly', 'yearly') DEFAULT 'monthly',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subdomain (subdomain),
    INDEX idx_stripe_customer (stripe_customer_id)
);

CREATE TABLE subscription_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    price_monthly DECIMAL(10,2) NOT NULL,
    price_yearly DECIMAL(10,2) NOT NULL,
    features JSON NOT NULL,
    limits JSON NOT NULL,
    stripe_price_id_monthly VARCHAR(255),
    stripe_price_id_yearly VARCHAR(255),
    razorpay_plan_id_monthly VARCHAR(255),
    razorpay_plan_id_yearly VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    company VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    notes TEXT,
    display_name_option ENUM('client_name', 'company_name', 'custom') DEFAULT 'client_name',
    custom_display_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_clients (company_id),
    INDEX idx_client_email (email)
);

-- Step 2: Tables with foreign keys to core tables
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin', 'member') DEFAULT 'member',
    is_active BOOLEAN DEFAULT TRUE,
    email_verified_at DATETIME,
    last_login_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_company_user (company_id, email),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE user_sessions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    expires_at DATETIME NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_session_token (session_token),
    INDEX idx_user_sessions (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE payment_methods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    type ENUM('card', 'bank_account') NOT NULL,
    stripe_payment_method_id VARCHAR(255),
    razorpay_payment_method_id VARCHAR(255),
    is_default BOOLEAN DEFAULT FALSE,
    last_four VARCHAR(4),
    brand VARCHAR(50),
    expires_month INT,
    expires_year INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_payment_methods (company_id, is_default),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- Step 3: Tables with foreign keys to multiple tables
CREATE TABLE subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    plan_id INT NOT NULL,
    status ENUM('trial', 'active', 'past_due', 'canceled', 'incomplete') DEFAULT 'trial',
    billing_cycle ENUM('monthly', 'yearly') DEFAULT 'monthly',
    current_period_start DATETIME NOT NULL,
    current_period_end DATETIME NOT NULL,
    trial_ends_at DATETIME,
    canceled_at DATETIME,
    payment_method_id INT,
    stripe_subscription_id VARCHAR(255),
    razorpay_subscription_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_subscription (company_id, status),
    INDEX idx_stripe_subscription (stripe_subscription_id),
    INDEX idx_razorpay_subscription (razorpay_subscription_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
    FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
);

CREATE TABLE usage_tracking (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    metric_value INT NOT NULL DEFAULT 1,
    period_type ENUM('daily', 'monthly', 'yearly') DEFAULT 'monthly',
    period_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_company_metric_period (company_id, metric_name, period_type, period_date),
    INDEX idx_company_usage (company_id, period_date),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE billing_invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('draft', 'sent', 'paid', 'void', 'uncollectible') DEFAULT 'draft',
    due_date DATE,
    paid_at DATETIME,
    stripe_invoice_id VARCHAR(255),
    razorpay_invoice_id VARCHAR(255),
    billing_period_start DATE,
    billing_period_end DATE,
    line_items JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_invoices (company_id, status),
    INDEX idx_invoice_number (invoice_number),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- Step 4: Tables with foreign keys to clients and companies
CREATE TABLE proposals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    client_id INT NOT NULL,
    template_id INT,
    title VARCHAR(255) NOT NULL,
    status ENUM('draft', 'sent', 'accepted', 'rejected', 'expired') DEFAULT 'draft',
    modules_json JSON,
    variables_json JSON,
    pricing_json JSON,
    currency VARCHAR(3) DEFAULT 'USD',
    pdf_path VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_proposals (company_id, status),
    INDEX idx_proposal_client (client_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    client_id INT NOT NULL,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('draft', 'sent', 'paid', 'overdue', 'void') DEFAULT 'draft',
    currency VARCHAR(3) DEFAULT 'USD',
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
    tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    notes TEXT,
    terms TEXT,
    pdf_path VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_invoices (company_id, status),
    INDEX idx_invoice_number (invoice_number),
    INDEX idx_invoice_client (client_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE contracts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    client_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    status ENUM('draft', 'sent', 'signed', 'expired', 'terminated') DEFAULT 'draft',
    modules_json JSON,
    variables_json JSON,
    pdf_path VARCHAR(500),
    signed_at DATETIME,
    signature_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_company_contracts (company_id, status),
    INDEX idx_contract_client (client_id),
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

-- Step 5: Insert default data
INSERT INTO subscription_plans (name, slug, price_monthly, price_yearly, features, limits, sort_order) VALUES
('Starter', 'starter', 29.00, 290.00, 
 '["50 Proposals/month", "25 Clients", "3 Team Members", "Basic Templates", "Email Support"]',
 '{"proposals_per_month": 50, "clients": 25, "users": 3, "templates": 10, "storage_mb": 1000}',
 10),
('Professional', 'professional', 79.00, 790.00,
 '["500 Proposals/month", "200 Clients", "10 Team Members", "Premium Templates", "Priority Support", "Custom Branding"]',
 '{"proposals_per_month": 500, "clients": 200, "users": 10, "templates": 50, "storage_mb": 5000}',
 20),
('Enterprise', 'enterprise', 199.00, 1990.00,
 '["Unlimited Proposals", "Unlimited Clients", "25 Team Members", "All Templates", "Dedicated Support", "White Label", "API Access"]',
 '{"proposals_per_month": -1, "clients": -1, "users": 25, "templates": -1, "storage_mb": -1}',
 30);

-- Create default company
INSERT INTO companies (name, subdomain, plan_id, subscription_status, subscription_expires_at) 
VALUES ('Default Company', 'default', 1, 'active', DATE_ADD(NOW(), INTERVAL 30 DAY));

-- Create default admin user
INSERT INTO users (company_id, name, email, password_hash, role) 
VALUES (1, 'Admin User', 'admin@keelance.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin');

-- Create default subscription
INSERT INTO subscriptions (company_id, plan_id, status, billing_cycle, current_period_start, current_period_end) 
VALUES (1, 1, 'active', 'monthly', NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY));
