-- ============================================================================
-- Finance Management Module - Database Enhancements
-- ============================================================================
-- Description: Add comprehensive accounting tables and views
-- Date: 2025-11-21
-- Author: TAF Development Team
-- ============================================================================

-- 1. Fiscal Periods Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS fiscal_periods (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    period_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    fiscal_year INTEGER NOT NULL,
    quarter INTEGER NULL CHECK (quarter BETWEEN 1 AND 4),
    period_number INTEGER NULL,
    status VARCHAR(20) DEFAULT 'open' CHECK (status IN ('open', 'closed', 'locked')),
    closed_at TIMESTAMPTZ NULL,
    closed_by UUID NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_fiscal_period_closed_by
    FOREIGN KEY (closed_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT unique_fiscal_period
    UNIQUE (org_id, period_name, fiscal_year)
);

CREATE INDEX IF NOT EXISTS idx_fiscal_periods_org_year ON fiscal_periods(org_id, fiscal_year);
CREATE INDEX IF NOT EXISTS idx_fiscal_periods_dates ON fiscal_periods(start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_fiscal_periods_status ON fiscal_periods(status);

-- 2. Bank Accounts Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_accounts (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    account_name VARCHAR(200) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    bank_name VARCHAR(200) NOT NULL,
    branch_name VARCHAR(200) NULL,
    swift_code VARCHAR(20) NULL,
    iban VARCHAR(50) NULL,
    currency VARCHAR(10) DEFAULT 'KES',
    account_type VARCHAR(50) DEFAULT 'checking' CHECK (account_type IN ('checking', 'savings', 'money_market', 'credit_line')),
    gl_account_id INTEGER NULL,
    current_balance DECIMAL(15,2) DEFAULT 0.00,
    opening_balance DECIMAL(15,2) DEFAULT 0.00,
    opening_balance_date DATE NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_primary BOOLEAN DEFAULT FALSE,
    notes TEXT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_bank_account_gl_account
    FOREIGN KEY (gl_account_id) REFERENCES gl_accounts(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_bank_accounts_org ON bank_accounts(org_id);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_active ON bank_accounts(is_active);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_gl ON bank_accounts(gl_account_id);

-- 3. Bank Reconciliations Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_reconciliations (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    bank_account_id INTEGER NOT NULL,
    reconciliation_date DATE NOT NULL,
    statement_date DATE NOT NULL,
    statement_balance DECIMAL(15,2) NOT NULL,
    book_balance DECIMAL(15,2) NOT NULL,
    adjusted_balance DECIMAL(15,2) NULL,
    total_deposits_in_transit DECIMAL(15,2) DEFAULT 0.00,
    total_outstanding_checks DECIMAL(15,2) DEFAULT 0.00,
    total_bank_charges DECIMAL(15,2) DEFAULT 0.00,
    total_bank_credits DECIMAL(15,2) DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'in_progress', 'reconciled', 'approved')),
    is_balanced BOOLEAN DEFAULT FALSE,
    variance DECIMAL(15,2) DEFAULT 0.00,
    notes TEXT NULL,
    reconciled_by UUID NULL,
    reconciled_at TIMESTAMPTZ NULL,
    approved_by UUID NULL,
    approved_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_bank_recon_account
    FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_bank_recon_reconciled_by
    FOREIGN KEY (reconciled_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT fk_bank_recon_approved_by
    FOREIGN KEY (approved_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_bank_recon_account ON bank_reconciliations(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_recon_date ON bank_reconciliations(reconciliation_date);
CREATE INDEX IF NOT EXISTS idx_bank_recon_status ON bank_reconciliations(status);

-- 4. Bank Reconciliation Items Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_reconciliation_items (
    id SERIAL PRIMARY KEY,
    reconciliation_id INTEGER NOT NULL,
    transaction_type VARCHAR(50) NOT NULL CHECK (transaction_type IN ('deposit_in_transit', 'outstanding_check', 'bank_charge', 'bank_credit', 'adjustment')),
    transaction_date DATE NOT NULL,
    description TEXT NOT NULL,
    reference_number VARCHAR(100) NULL,
    amount DECIMAL(15,2) NOT NULL,
    gl_transaction_id INTEGER NULL,
    is_cleared BOOLEAN DEFAULT FALSE,
    cleared_date DATE NULL,
    notes TEXT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_bank_recon_item
    FOREIGN KEY (reconciliation_id) REFERENCES bank_reconciliations(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_bank_recon_item_gl
    FOREIGN KEY (gl_transaction_id) REFERENCES gl_transactions(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_bank_recon_items_recon ON bank_reconciliation_items(reconciliation_id);
CREATE INDEX IF NOT EXISTS idx_bank_recon_items_type ON bank_reconciliation_items(transaction_type);
CREATE INDEX IF NOT EXISTS idx_bank_recon_items_cleared ON bank_reconciliation_items(is_cleared);

-- 5. Financial Budgets Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS financial_budgets (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    budget_name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    fiscal_period_id INTEGER NULL,
    budget_type VARCHAR(50) DEFAULT 'operating' CHECK (budget_type IN ('operating', 'capital', 'cash', 'project')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_amount DECIMAL(15,2) DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'submitted', 'approved', 'active', 'closed')),
    approved_by UUID NULL,
    approved_at TIMESTAMPTZ NULL,
    created_by UUID NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_budget_fiscal_period
    FOREIGN KEY (fiscal_period_id) REFERENCES fiscal_periods(id) ON DELETE SET NULL,
    
    CONSTRAINT fk_budget_approved_by
    FOREIGN KEY (approved_by) REFERENCES users(user_id) ON DELETE SET NULL,
    
    CONSTRAINT fk_budget_created_by
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_budgets_org ON financial_budgets(org_id);
CREATE INDEX IF NOT EXISTS idx_budgets_period ON financial_budgets(fiscal_period_id);
CREATE INDEX IF NOT EXISTS idx_budgets_status ON financial_budgets(status);
CREATE INDEX IF NOT EXISTS idx_budgets_dates ON financial_budgets(start_date, end_date);

-- 6. Budget Line Items Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS budget_line_items (
    id SERIAL PRIMARY KEY,
    budget_id INTEGER NOT NULL,
    gl_account_id INTEGER NOT NULL,
    line_description VARCHAR(255) NULL,
    budgeted_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    actual_amount DECIMAL(15,2) DEFAULT 0.00,
    variance DECIMAL(15,2) DEFAULT 0.00,
    variance_percent DECIMAL(5,2) DEFAULT 0.00,
    notes TEXT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_budget_line_budget
    FOREIGN KEY (budget_id) REFERENCES financial_budgets(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_budget_line_account
    FOREIGN KEY (gl_account_id) REFERENCES gl_accounts(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_budget_lines_budget ON budget_line_items(budget_id);
CREATE INDEX IF NOT EXISTS idx_budget_lines_account ON budget_line_items(gl_account_id);

-- 7. Financial Reports Configuration Table
-- ============================================================================
CREATE TABLE IF NOT EXISTS financial_reports (
    id SERIAL PRIMARY KEY,
    org_id UUID NULL,
    tenant_id INTEGER NULL,
    branch_id INTEGER NULL,
    report_name VARCHAR(200) NOT NULL,
    report_type VARCHAR(50) NOT NULL CHECK (report_type IN ('income_statement', 'balance_sheet', 'cash_flow', 'trial_balance', 'general_ledger', 'custom')),
    description TEXT NULL,
    config_json JSONB NULL,
    last_generated_at TIMESTAMPTZ NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_by UUID NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_financial_report_created_by
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_financial_reports_org ON financial_reports(org_id);
CREATE INDEX IF NOT EXISTS idx_financial_reports_type ON financial_reports(report_type);

-- 8. Create Trial Balance View
-- ============================================================================
CREATE OR REPLACE VIEW trial_balance AS
SELECT 
    a.id AS account_id,
    a.code AS account_code,
    a.name AS account_name,
    a.account_type,
    COALESCE(SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END), 0) AS total_debits,
    COALESCE(SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END), 0) AS total_credits,
    COALESCE(
        SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END) - 
        SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END),
        0
    ) AS balance,
    CASE 
        WHEN a.account_type IN ('asset', 'expense') THEN 
            COALESCE(SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END), 0) - 
            COALESCE(SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END), 0)
        ELSE 
            COALESCE(SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END), 0) - 
            COALESCE(SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END), 0)
    END AS normal_balance,
    a.org_id,
    a.tenant_id,
    a.branch_id
FROM 
    gl_accounts a
LEFT JOIN 
    gl_transactions t ON a.id = t.account_id
WHERE 
    a.is_active = TRUE
GROUP BY 
    a.id, a.code, a.name, a.account_type, a.org_id, a.tenant_id, a.branch_id
ORDER BY 
    a.code;

-- 9. Create Account Balance View
-- ============================================================================
CREATE OR REPLACE VIEW account_balances AS
SELECT 
    a.id AS account_id,
    a.code AS account_code,
    a.name AS account_name,
    a.account_type,
    a.parent_id,
    COALESCE(SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END), 0) AS debit_total,
    COALESCE(SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END), 0) AS credit_total,
    CASE 
        WHEN a.account_type IN ('asset', 'expense') THEN 
            COALESCE(SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END), 0) - 
            COALESCE(SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END), 0)
        WHEN a.account_type IN ('liability', 'equity', 'revenue') THEN 
            COALESCE(SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END), 0) - 
            COALESCE(SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END), 0)
        ELSE 0
    END AS current_balance,
    COUNT(t.id) AS transaction_count,
    MAX(t.timestamp) AS last_transaction_date,
    a.org_id,
    a.tenant_id,
    a.branch_id,
    a.is_active
FROM 
    gl_accounts a
LEFT JOIN 
    gl_transactions t ON a.id = t.account_id
GROUP BY 
    a.id, a.code, a.name, a.account_type, a.parent_id, a.org_id, a.tenant_id, a.branch_id, a.is_active
ORDER BY 
    a.code;

-- 10. Performance Indexes
-- ============================================================================
CREATE INDEX IF NOT EXISTS idx_gl_accounts_type_active ON gl_accounts(account_type, is_active);
CREATE INDEX IF NOT EXISTS idx_gl_accounts_code ON gl_accounts(code);
CREATE INDEX IF NOT EXISTS idx_gl_accounts_parent ON gl_accounts(parent_id);
CREATE INDEX IF NOT EXISTS idx_gl_transactions_account_date ON gl_transactions(account_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_gl_transactions_journal ON gl_transactions(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_gl_journal_entries_status_date ON GL_Journal_Entries(status, timestamp);

-- 11. Add missing columns to GL_Journal_Entries if needed
-- ============================================================================
DO $$ 
BEGIN
    -- Add org_id if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'gl_journal_entries' 
        AND column_name = 'org_id'
    ) THEN
        ALTER TABLE GL_Journal_Entries ADD COLUMN org_id UUID NULL;
    END IF;

    -- Add description if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'gl_journal_entries' 
        AND column_name = 'description'
    ) THEN
        ALTER TABLE GL_Journal_Entries ADD COLUMN description TEXT NULL;
    END IF;
END $$;

-- 12. Add missing columns to gl_accounts if needed
-- ============================================================================
DO $$ 
BEGIN
    -- Add org_id if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'gl_accounts' 
        AND column_name = 'org_id'
    ) THEN
        ALTER TABLE gl_accounts ADD COLUMN org_id UUID NULL;
    END IF;

    -- Add description if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'gl_accounts' 
        AND column_name = 'description'
    ) THEN
        ALTER TABLE gl_accounts ADD COLUMN description TEXT NULL;
    END IF;

    -- Add account_type if it doesn't exist (type might be the column name)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'gl_accounts' 
        AND column_name = 'account_type'
    ) THEN
        -- Check if 'type' column exists instead
        IF EXISTS (
            SELECT 1 FROM information_schema.columns 
            WHERE table_name = 'gl_accounts' 
            AND column_name = 'type'
        ) THEN
            ALTER TABLE gl_accounts RENAME COLUMN type TO account_type;
        ELSE
            ALTER TABLE gl_accounts ADD COLUMN account_type VARCHAR(50) NULL;
        END IF;
    END IF;
END $$;

-- 13. Add missing columns to gl_transactions if needed
-- ============================================================================
DO $$ 
BEGIN
    -- Add org_id if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name = 'gl_transactions' 
        AND column_name = 'org_id'
    ) THEN
        ALTER TABLE gl_transactions ADD COLUMN org_id UUID NULL;
    END IF;
END $$;

-- Complete
SELECT 'Finance Management Enhancements Migration Completed' AS status;
