-- AI360 Platform - Central Backend Database Schema
-- PostgreSQL

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============ USERS ============
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    avatar_url VARCHAR(500),

    -- Role & Permissions
    role VARCHAR(50) DEFAULT 'client_user' NOT NULL,
    -- Values: super_admin, admin, support, client_owner, client_admin, client_user

    -- Client association (null for admin users)
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,

    -- Status
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    is_verified BOOLEAN DEFAULT FALSE NOT NULL,
    email_verified BOOLEAN DEFAULT FALSE NOT NULL,

    -- OAuth
    oauth_provider VARCHAR(50),
    oauth_id VARCHAR(255),

    -- Security
    failed_login_attempts INTEGER DEFAULT 0,
    last_login_at TIMESTAMP,
    last_login_ip VARCHAR(50),

    -- 2FA
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    two_factor_secret VARCHAR(255),

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- ============ CLIENTS ============
CREATE TABLE IF NOT EXISTS clients (
    id SERIAL PRIMARY KEY,

    -- Company Info
    company_name VARCHAR(255) NOT NULL,
    company_email VARCHAR(255) UNIQUE NOT NULL,
    company_phone VARCHAR(50),
    company_website VARCHAR(255),
    company_logo_url VARCHAR(500),

    -- Address
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100) DEFAULT 'India',
    postal_code VARCHAR(20),

    -- Business Info
    industry VARCHAR(100),
    company_size VARCHAR(50),
    gstin VARCHAR(20),
    pan VARCHAR(20),

    -- Status
    status VARCHAR(50) DEFAULT 'trial' NOT NULL,
    is_verified BOOLEAN DEFAULT FALSE,

    -- Billing
    billing_email VARCHAR(255),
    billing_address TEXT,
    stripe_customer_id VARCHAR(100),
    razorpay_customer_id VARCHAR(100),
    preferred_payment_method VARCHAR(50) DEFAULT 'razorpay',

    -- Settings
    settings JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',

    -- Internal
    internal_notes TEXT,
    referred_by VARCHAR(100),
    referral_code VARCHAR(50) UNIQUE,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- ============ SUBSCRIPTIONS ============
CREATE TABLE IF NOT EXISTS subscriptions (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,

    -- Service & Plan
    service_type VARCHAR(50) NOT NULL,
    plan_tier VARCHAR(50) DEFAULT 'starter' NOT NULL,
    plan_name VARCHAR(100) NOT NULL,

    -- Pricing
    price_monthly DECIMAL(10,2) NOT NULL,
    price_yearly DECIMAL(10,2),
    billing_cycle VARCHAR(20) DEFAULT 'monthly',
    currency VARCHAR(3) DEFAULT 'INR',

    -- Dates
    start_date DATE NOT NULL DEFAULT CURRENT_DATE,
    end_date DATE,
    trial_end_date DATE,
    next_billing_date DATE,
    cancelled_at DATE,

    -- Status
    status VARCHAR(50) DEFAULT 'trial' NOT NULL,
    auto_renew BOOLEAN DEFAULT TRUE,

    -- Usage
    usage_limits JSONB DEFAULT '{}',
    current_usage JSONB DEFAULT '{}',
    features JSONB DEFAULT '[]',

    -- Payment
    stripe_subscription_id VARCHAR(100),
    razorpay_subscription_id VARCHAR(100),

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- ============ API KEYS ============
CREATE TABLE IF NOT EXISTS api_keys (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE NOT NULL,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,

    -- Key Info
    name VARCHAR(255) NOT NULL,
    key_prefix VARCHAR(10) NOT NULL,
    key_hash VARCHAR(255) UNIQUE NOT NULL,

    -- Permissions
    permissions JSONB DEFAULT '["*"]',
    allowed_services JSONB DEFAULT '[]',
    allowed_ips JSONB DEFAULT '[]',

    -- Rate Limiting
    rate_limit_per_minute INTEGER DEFAULT 60,
    rate_limit_per_hour INTEGER DEFAULT 1000,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    is_test_key BOOLEAN DEFAULT FALSE,

    -- Dates
    expires_at TIMESTAMP,
    last_used_at TIMESTAMP,
    last_used_ip VARCHAR(50),

    -- Usage
    total_requests INTEGER DEFAULT 0,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- ============ INVOICES ============
CREATE TABLE IF NOT EXISTS invoices (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,

    -- Invoice Info
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    invoice_date DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,

    -- Billing Period
    billing_period_start DATE NOT NULL,
    billing_period_end DATE NOT NULL,

    -- Amounts
    subtotal DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    discount_percentage DECIMAL(5,2) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 18,
    tax_amount DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    amount_due DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'INR',

    -- Status
    status VARCHAR(50) DEFAULT 'pending' NOT NULL,

    -- Line Items
    line_items JSONB DEFAULT '[]',

    -- Payment
    payment_method VARCHAR(50),
    payment_reference VARCHAR(255),
    stripe_invoice_id VARCHAR(100),
    razorpay_invoice_id VARCHAR(100),
    paid_at TIMESTAMP,

    -- PDF
    pdf_url VARCHAR(500),

    -- Notes
    notes TEXT,
    internal_notes TEXT,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- ============ USAGE LOGS ============
CREATE TABLE IF NOT EXISTS usage_logs (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    subscription_id INTEGER REFERENCES subscriptions(id) ON DELETE SET NULL,

    -- Service
    service_type VARCHAR(50) NOT NULL,
    resource_type VARCHAR(100) NOT NULL,

    -- Usage
    quantity INTEGER DEFAULT 1 NOT NULL,
    unit VARCHAR(50),

    -- Cost
    unit_cost DECIMAL(10,4) DEFAULT 0,
    total_cost DECIMAL(10,2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'INR',

    -- Metadata
    metadata JSONB DEFAULT '{}',

    -- Request Info
    request_id VARCHAR(100),
    api_key_id INTEGER REFERENCES api_keys(id) ON DELETE SET NULL,
    ip_address VARCHAR(50),

    -- Timestamp
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    -- Billing
    is_billed INTEGER DEFAULT 0,
    invoice_id INTEGER REFERENCES invoices(id) ON DELETE SET NULL,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- ============ AUDIT LOGS ============
CREATE TABLE IF NOT EXISTS audit_logs (
    id SERIAL PRIMARY KEY,

    -- Who
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
    user_email VARCHAR(255),

    -- What
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(100),
    resource_id VARCHAR(100),

    -- Details
    description TEXT,
    old_values JSONB,
    new_values JSONB,
    metadata JSONB DEFAULT '{}',

    -- Request Info
    ip_address VARCHAR(50),
    user_agent VARCHAR(500),
    request_id VARCHAR(100),

    -- Status
    status VARCHAR(50) DEFAULT 'success',

    -- Timestamp
    performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- ============ INDEXES ============

-- Users
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_client_id ON users(client_id);
CREATE INDEX idx_users_role ON users(role);

-- Clients
CREATE INDEX idx_clients_email ON clients(company_email);
CREATE INDEX idx_clients_status ON clients(status);

-- Subscriptions
CREATE INDEX idx_subscriptions_client_id ON subscriptions(client_id);
CREATE INDEX idx_subscriptions_service_type ON subscriptions(service_type);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);

-- API Keys
CREATE INDEX idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX idx_api_keys_client_id ON api_keys(client_id);
CREATE INDEX idx_api_keys_key_hash ON api_keys(key_hash);

-- Invoices
CREATE INDEX idx_invoices_client_id ON invoices(client_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_invoice_number ON invoices(invoice_number);

-- Usage Logs
CREATE INDEX idx_usage_logs_client_id ON usage_logs(client_id);
CREATE INDEX idx_usage_logs_service_type ON usage_logs(service_type);
CREATE INDEX idx_usage_logs_recorded_at ON usage_logs(recorded_at);

-- Audit Logs
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_performed_at ON audit_logs(performed_at);

-- ============ TRIGGERS ============

-- Update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_clients_updated_at BEFORE UPDATE ON clients
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_subscriptions_updated_at BEFORE UPDATE ON subscriptions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_api_keys_updated_at BEFORE UPDATE ON api_keys
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_invoices_updated_at BEFORE UPDATE ON invoices
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============ SERVICE CONFIGS ============
CREATE TABLE IF NOT EXISTS service_configs (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,

    -- Service Type
    service_type VARCHAR(50) NOT NULL,

    -- Status
    is_enabled BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,

    -- AI Provider Configuration
    ai_provider VARCHAR(50) DEFAULT 'openai',
    ai_model VARCHAR(100) DEFAULT 'gpt-4',
    ai_api_key TEXT,

    -- Service Settings (JSON)
    settings JSONB DEFAULT '{}',

    -- Usage Limits
    daily_limit INTEGER,
    monthly_limit INTEGER,
    rate_limit_per_minute INTEGER DEFAULT 60,

    -- Webhook Configuration
    webhook_url VARCHAR(500),
    webhook_secret VARCHAR(255),
    webhook_events JSONB DEFAULT '[]',

    -- Custom Branding
    branding JSONB DEFAULT '{}',

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    UNIQUE(client_id, service_type)
);

-- ============ SERVICE CREDENTIALS ============
CREATE TABLE IF NOT EXISTS service_credentials (
    id SERIAL PRIMARY KEY,
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE NOT NULL,
    service_type VARCHAR(50) NOT NULL,

    -- Credential Details
    provider VARCHAR(50) NOT NULL,
    credential_type VARCHAR(50) NOT NULL,
    credentials JSONB NOT NULL,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    last_verified_at TIMESTAMP,

    -- Metadata
    name VARCHAR(100),
    description TEXT,
    expires_at TIMESTAMP,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

-- Service Configs Indexes
CREATE INDEX IF NOT EXISTS idx_service_configs_client_id ON service_configs(client_id);
CREATE INDEX IF NOT EXISTS idx_service_configs_service_type ON service_configs(service_type);

-- Service Credentials Indexes
CREATE INDEX IF NOT EXISTS idx_service_credentials_client_id ON service_credentials(client_id);
CREATE INDEX IF NOT EXISTS idx_service_credentials_provider ON service_credentials(provider);

-- Service Configs Trigger
CREATE TRIGGER update_service_configs_updated_at BEFORE UPDATE ON service_configs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_service_credentials_updated_at BEFORE UPDATE ON service_credentials
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============ SEED DATA ============

-- Create super admin user (password: Admin@123)
INSERT INTO users (email, password_hash, name, role, is_active, is_verified, email_verified)
VALUES (
    'admin@ai360.com',
    '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/X4.SUgI1mOXB6K0Ky',
    'Super Admin',
    'super_admin',
    true,
    true,
    true
) ON CONFLICT (email) DO NOTHING;

-- Create demo client company
INSERT INTO clients (company_name, company_email, industry, status, is_verified)
VALUES (
    'Demo Company',
    'demo@company.com',
    'Technology',
    'active',
    true
) ON CONFLICT (company_email) DO NOTHING;

-- Create demo user linked to demo company
INSERT INTO users (email, password_hash, name, role, client_id, is_active, is_verified, email_verified)
SELECT
    'demo@company.com',
    '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/X4.SUgI1mOXB6K0Ky',
    'Demo User',
    'client_owner',
    c.id,
    true,
    true,
    true
FROM clients c WHERE c.company_email = 'demo@company.com'
ON CONFLICT (email) DO NOTHING;

-- Create demo subscription for chatbot
INSERT INTO subscriptions (client_id, service_type, plan_tier, plan_name, price_monthly, status, trial_end_date, usage_limits)
SELECT
    c.id,
    'chatbot',
    'professional',
    'Professional Plan',
    4999.00,
    'trial',
    CURRENT_DATE + INTERVAL '14 days',
    '{"messages": 5000, "api_calls": 10000}'::jsonb
FROM clients c WHERE c.company_email = 'demo@company.com'
ON CONFLICT DO NOTHING;