-- ============================================================================
-- Finance Management Module - Schema Creation and Demo Data
-- ============================================================================
-- Description: Complete schema and realistic sample data for Finance module
-- Date: 2025-11-25
-- Author: TAF Development Team
-- ============================================================================

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

-- ============================================================================
-- PART 1: CREATE TABLES
-- ============================================================================

-- Chart of Accounts
-- ============================================================================
CREATE TABLE IF NOT EXISTS gl_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    account_type VARCHAR(50) NOT NULL CHECK (account_type IN ('asset', 'liability', 'equity', 'revenue', 'expense')),
    parent_id UUID REFERENCES gl_accounts(id) ON DELETE SET NULL,
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    branch_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    updated_by UUID
);

CREATE INDEX IF NOT EXISTS idx_gl_accounts_code ON gl_accounts(code);
CREATE INDEX IF NOT EXISTS idx_gl_accounts_type ON gl_accounts(account_type);
CREATE INDEX IF NOT EXISTS idx_gl_accounts_parent ON gl_accounts(parent_id);
CREATE INDEX IF NOT EXISTS idx_gl_accounts_org ON gl_accounts(org_id);

-- Journal Entries (Header)
-- ============================================================================
CREATE TABLE IF NOT EXISTS GL_Journal_Entries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    entry_date DATE NOT NULL,
    reference VARCHAR(100),
    description TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'posted', 'void')),
    total_debits DECIMAL(15, 2) DEFAULT 0,
    total_credits DECIMAL(15, 2) DEFAULT 0,
    is_balanced BOOLEAN DEFAULT FALSE,
    source_module VARCHAR(100),
    source_id UUID,
    posted_at TIMESTAMP,
    posted_by UUID,
    voided_at TIMESTAMP,
    voided_by UUID,
    void_reason TEXT,
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    branch_id UUID,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    updated_by UUID
);

CREATE INDEX IF NOT EXISTS idx_journal_entries_date ON GL_Journal_Entries(entry_date);
CREATE INDEX IF NOT EXISTS idx_journal_entries_status ON GL_Journal_Entries(status);
CREATE INDEX IF NOT EXISTS idx_journal_entries_source ON GL_Journal_Entries(source_module, source_id);
CREATE INDEX IF NOT EXISTS idx_journal_entries_org ON GL_Journal_Entries(org_id);

-- Journal Entry Lines (Transactions)
-- ============================================================================
CREATE TABLE IF NOT EXISTS GL_Journal_Lines (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    journal_entry_id UUID NOT NULL REFERENCES GL_Journal_Entries(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES gl_accounts(id),
    debit DECIMAL(15, 2) DEFAULT 0 CHECK (debit >= 0),
    credit DECIMAL(15, 2) DEFAULT 0 CHECK (credit >= 0),
    memo TEXT,
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_debit_or_credit CHECK ((debit > 0 AND credit = 0) OR (credit > 0 AND debit = 0))
);

CREATE INDEX IF NOT EXISTS idx_journal_lines_entry ON GL_Journal_Lines(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_journal_lines_account ON GL_Journal_Lines(account_id);
CREATE INDEX IF NOT EXISTS idx_journal_lines_debit ON GL_Journal_Lines(debit);
CREATE INDEX IF NOT EXISTS idx_journal_lines_credit ON GL_Journal_Lines(credit);

-- Legacy gl_transactions table (alias for compatibility)
-- ============================================================================
CREATE TABLE IF NOT EXISTS gl_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    journal_entry_id UUID NOT NULL REFERENCES GL_Journal_Entries(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES gl_accounts(id),
    type VARCHAR(10) CHECK (type IN ('debit', 'credit')),
    amount DECIMAL(15, 2) NOT NULL CHECK (amount >= 0),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    memo TEXT,
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid
);

CREATE INDEX IF NOT EXISTS idx_gl_transactions_entry ON gl_transactions(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_gl_transactions_account ON gl_transactions(account_id);
CREATE INDEX IF NOT EXISTS idx_gl_transactions_type ON gl_transactions(type);

-- Bank Accounts
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    account_name VARCHAR(255) NOT NULL,
    account_number VARCHAR(100) NOT NULL,
    bank_name VARCHAR(255) NOT NULL,
    branch_name VARCHAR(255),
    currency VARCHAR(10) DEFAULT 'KES',
    account_type VARCHAR(50) DEFAULT 'checking' CHECK (account_type IN ('checking', 'savings', 'credit_card', 'money_market')),
    gl_account_id UUID REFERENCES gl_accounts(id),
    current_balance DECIMAL(15, 2) DEFAULT 0,
    opening_balance DECIMAL(15, 2) DEFAULT 0,
    opening_balance_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    is_primary BOOLEAN DEFAULT FALSE,
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    branch_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    updated_by UUID
);

CREATE INDEX IF NOT EXISTS idx_bank_accounts_number ON bank_accounts(account_number);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_bank ON bank_accounts(bank_name);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_org ON bank_accounts(org_id);

-- Bank Transactions
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    bank_account_id UUID NOT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE,
    transaction_date DATE NOT NULL,
    description TEXT NOT NULL,
    reference VARCHAR(100),
    type VARCHAR(50) CHECK (type IN ('deposit', 'withdrawal', 'transfer', 'fee', 'interest', 'adjustment')),
    amount DECIMAL(15, 2) NOT NULL,
    balance_after DECIMAL(15, 2),
    reconciled BOOLEAN DEFAULT FALSE,
    reconciliation_id UUID,
    reconciled_at TIMESTAMP,
    reconciled_by UUID,
    journal_entry_id UUID REFERENCES GL_Journal_Entries(id),
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID
);

CREATE INDEX IF NOT EXISTS idx_bank_transactions_account ON bank_transactions(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_transactions_date ON bank_transactions(transaction_date);
CREATE INDEX IF NOT EXISTS idx_bank_transactions_type ON bank_transactions(type);
CREATE INDEX IF NOT EXISTS idx_bank_transactions_reconciled ON bank_transactions(reconciled);

-- Bank Reconciliations
-- ============================================================================
CREATE TABLE IF NOT EXISTS bank_reconciliations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    bank_account_id UUID NOT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE,
    reconciliation_date DATE NOT NULL,
    statement_date DATE NOT NULL,
    statement_balance DECIMAL(15, 2) NOT NULL,
    book_balance DECIMAL(15, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN ('draft', 'in_progress', 'reconciled', 'closed')),
    is_balanced BOOLEAN DEFAULT FALSE,
    variance DECIMAL(15, 2) DEFAULT 0,
    notes TEXT,
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    updated_by UUID,
    reconciled_by UUID,
    reconciled_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_bank_reconciliations_account ON bank_reconciliations(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_reconciliations_date ON bank_reconciliations(reconciliation_date);
CREATE INDEX IF NOT EXISTS idx_bank_reconciliations_status ON bank_reconciliations(status);

-- Fiscal Periods
-- ============================================================================
CREATE TABLE IF NOT EXISTS fiscal_periods (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    period_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    fiscal_year INTEGER NOT NULL,
    quarter INTEGER CHECK (quarter BETWEEN 1 AND 4),
    period_number INTEGER,
    status VARCHAR(50) DEFAULT 'open' CHECK (status IN ('open', 'closed', 'locked')),
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP,
    closed_by UUID
);

CREATE INDEX IF NOT EXISTS idx_fiscal_periods_year ON fiscal_periods(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);

-- Financial Budgets
-- ============================================================================
CREATE TABLE IF NOT EXISTS financial_budgets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    budget_name VARCHAR(255) NOT NULL,
    description TEXT,
    fiscal_period_id UUID REFERENCES fiscal_periods(id),
    budget_type VARCHAR(50) DEFAULT 'operating' CHECK (budget_type IN ('operating', 'capital', 'project', 'department')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_amount DECIMAL(15, 2) DEFAULT 0,
    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'approved', 'archived')),
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    branch_id UUID,
    department_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID,
    approved_by UUID,
    approved_at TIMESTAMP
);

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

-- Budget Line Items
-- ============================================================================
CREATE TABLE IF NOT EXISTS budget_line_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    budget_id UUID NOT NULL REFERENCES financial_budgets(id) ON DELETE CASCADE,
    gl_account_id UUID NOT NULL REFERENCES gl_accounts(id),
    line_description TEXT,
    budgeted_amount DECIMAL(15, 2) NOT NULL DEFAULT 0,
    actual_amount DECIMAL(15, 2) DEFAULT 0,
    variance DECIMAL(15, 2) DEFAULT 0,
    variance_percent DECIMAL(5, 2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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);

-- Financial Reports Configuration
-- ============================================================================
CREATE TABLE IF NOT EXISTS financial_reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    report_name VARCHAR(255) NOT NULL,
    report_type VARCHAR(100) NOT NULL CHECK (report_type IN ('trial_balance', 'income_statement', 'balance_sheet', 'cash_flow', 'general_ledger', 'aging')),
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    org_id UUID default '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by UUID
);

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

-- ============================================================================
-- PART 2: INSERT DEMO DATA
-- ============================================================================

-- 1. Insert Standard Chart of Accounts
-- ============================================================================

INSERT INTO gl_accounts (code, name, account_type, parent_id, is_active, description) VALUES
-- ASSETS (1000-1999)
('1000', 'Cash and Cash Equivalents', 'asset', NULL, TRUE, 'Total liquid assets'),
('1100', 'Petty Cash', 'asset', NULL, TRUE, 'Cash on hand for small expenses'),
('1200', 'Bank Account - Main', 'asset', NULL, TRUE, 'Primary operating account'),
('1210', 'Bank Account - Payroll', 'asset', NULL, TRUE, 'Dedicated payroll account'),
('1300', 'Accounts Receivable', 'asset', NULL, TRUE, 'Money owed by customers'),
('1400', 'Inventory', 'asset', NULL, TRUE, 'Stock and goods for sale'),
('1500', 'Prepaid Expenses', 'asset', NULL, TRUE, 'Expenses paid in advance'),
('1600', 'Fixed Assets', 'asset', NULL, TRUE, 'Long-term physical assets'),
('1610', 'Vehicles', 'asset', NULL, TRUE, 'Company vehicles and trucks'),
('1620', 'Equipment', 'asset', NULL, TRUE, 'Machinery and equipment'),
('1630', 'Furniture & Fixtures', 'asset', NULL, TRUE, 'Office furniture'),
('1640', 'Buildings', 'asset', NULL, TRUE, 'Real estate property'),
('1700', 'Accumulated Depreciation', 'asset', NULL, TRUE, 'Contra asset account for depreciation'),

-- LIABILITIES (2000-2999)
('2000', 'Accounts Payable', 'liability', NULL, TRUE, 'Money owed to suppliers'),
('2100', 'Short-term Loans', 'liability', NULL, TRUE, 'Loans due within 1 year'),
('2200', 'Accrued Expenses', 'liability', NULL, TRUE, 'Expenses incurred but not yet paid'),
('2210', 'Salaries Payable', 'liability', NULL, TRUE, 'Wages owed to employees'),
('2220', 'Taxes Payable', 'liability', NULL, TRUE, 'Tax obligations'),
('2300', 'Long-term Debt', 'liability', NULL, TRUE, 'Loans and debt due after 1 year'),
('2400', 'Deferred Revenue', 'liability', NULL, TRUE, 'Advance payments from customers'),

-- EQUITY (3000-3999)
('3000', 'Owner''s Equity', 'equity', NULL, TRUE, 'Owner''s investment in the business'),
('3100', 'Retained Earnings', 'equity', NULL, TRUE, 'Accumulated profits'),
('3200', 'Dividends', 'equity', NULL, TRUE, 'Distributions to owners'),

-- REVENUE (4000-4999)
('4000', 'Sales Revenue', 'revenue', NULL, TRUE, 'Income from product sales'),
('4100', 'Service Revenue', 'revenue', NULL, TRUE, 'Income from services'),
('4200', 'Interest Income', 'revenue', NULL, TRUE, 'Interest earned on investments'),
('4300', 'Other Income', 'revenue', NULL, TRUE, 'Miscellaneous revenue'),

-- EXPENSES (5000-9999)
('5000', 'Cost of Goods Sold', 'expense', NULL, TRUE, 'Direct costs of products sold'),
('5100', 'Salaries & Wages', 'expense', NULL, TRUE, 'Employee compensation'),
('5200', 'Rent Expense', 'expense', NULL, TRUE, 'Facility rental costs'),
('5300', 'Utilities', 'expense', NULL, TRUE, 'Electricity, water, internet'),
('5400', 'Insurance', 'expense', NULL, TRUE, 'Business insurance premiums'),
('5500', 'Depreciation Expense', 'expense', NULL, TRUE, 'Asset depreciation'),
('5600', 'Marketing & Advertising', 'expense', NULL, TRUE, 'Promotional costs'),
('5700', 'Office Supplies', 'expense', NULL, TRUE, 'General office materials'),
('5800', 'Professional Fees', 'expense', NULL, TRUE, 'Legal, accounting, consulting'),
('5900', 'Travel & Entertainment', 'expense', NULL, TRUE, 'Business travel expenses'),
('6000', 'Repairs & Maintenance', 'expense', NULL, TRUE, 'Equipment and facility repairs'),
('6100', 'Fuel & Vehicle Expenses', 'expense', NULL, TRUE, 'Transportation costs'),
('6200', 'Telephone & Communications', 'expense', NULL, TRUE, 'Phone and internet'),
('6300', 'Bank Fees', 'expense', NULL, TRUE, 'Banking service charges'),
('6400', 'Interest Expense', 'expense', NULL, TRUE, 'Interest on loans and debt'),
('6500', 'Taxes & Licenses', 'expense', NULL, TRUE, 'Business taxes and permits')
ON CONFLICT (code) DO NOTHING;

-- 2. Insert Fiscal Periods
-- ============================================================================

INSERT INTO fiscal_periods (period_name, start_date, end_date, fiscal_year, quarter, period_number, status) VALUES
('Q1 2025', '2025-01-01', '2025-03-31', 2025, 1, 1, 'closed'),
('Q2 2025', '2025-04-01', '2025-06-30', 2025, 2, 2, 'closed'),
('Q3 2025', '2025-07-01', '2025-09-30', 2025, 3, 3, 'closed'),
('Q4 2025', '2025-10-01', '2025-12-31', 2025, 4, 4, 'open'),
('January 2025', '2025-01-01', '2025-01-31', 2025, 1, 1, 'closed'),
('February 2025', '2025-02-01', '2025-02-28', 2025, 1, 2, 'closed'),
('March 2025', '2025-03-01', '2025-03-31', 2025, 1, 3, 'closed'),
('April 2025', '2025-04-01', '2025-04-30', 2025, 2, 4, 'closed'),
('May 2025', '2025-05-01', '2025-05-31', 2025, 2, 5, 'closed'),
('June 2025', '2025-06-01', '2025-06-30', 2025, 2, 6, 'closed'),
('July 2025', '2025-07-01', '2025-07-31', 2025, 3, 7, 'closed'),
('August 2025', '2025-08-01', '2025-08-31', 2025, 3, 8, 'closed'),
('September 2025', '2025-09-01', '2025-09-30', 2025, 3, 9, 'closed'),
('October 2025', '2025-10-01', '2025-10-31', 2025, 4, 10, 'closed'),
('November 2025', '2025-11-01', '2025-11-30', 2025, 4, 11, 'open'),
('December 2025', '2025-12-01', '2025-12-31', 2025, 4, 12, 'open')
ON CONFLICT DO NOTHING;

-- 3. Insert Bank Accounts
-- ============================================================================

INSERT INTO bank_accounts (account_name, account_number, bank_name, branch_name, currency, account_type, gl_account_id, current_balance, opening_balance, opening_balance_date, is_active, is_primary) VALUES
('Main Operating Account', '1234567890', 'Kenya Commercial Bank', 'Nairobi Branch', 'KES', 'checking', (SELECT id FROM gl_accounts WHERE code = '1200'), 2500000.00, 1000000.00, '2025-01-01', TRUE, TRUE),
('Payroll Account', '0987654321', 'Kenya Commercial Bank', 'Nairobi Branch', 'KES', 'checking', (SELECT id FROM gl_accounts WHERE code = '1210'), 500000.00, 200000.00, '2025-01-01', TRUE, FALSE),
('Savings Account', '5555666677', 'Equity Bank', 'Westlands Branch', 'KES', 'savings', (SELECT id FROM gl_accounts WHERE code = '1200'), 800000.00, 500000.00, '2025-01-01', TRUE, FALSE)
ON CONFLICT DO NOTHING;

-- 4. Insert Sample Journal Entries
-- ============================================================================

-- Journal Entry 1: Initial Capital Investment
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-01-01', 'JE-001', 'Initial capital investment by owner', 'posted', 1000000.00, 1000000.00, TRUE, '2025-01-01 09:00:00', '2025-01-01 09:00:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-001'), (SELECT id FROM gl_accounts WHERE code = '1200'), 1000000.00, 0, 'Cash deposit to bank account'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-001'), (SELECT id FROM gl_accounts WHERE code = '3000'), 0, 1000000.00, 'Owner capital contribution')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-001'), (SELECT id FROM gl_accounts WHERE code = '1200'), 'debit', 1000000.00, '2025-01-01 09:00:00', 'Cash deposit to bank account'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-001'), (SELECT id FROM gl_accounts WHERE code = '3000'), 'credit', 1000000.00, '2025-01-01 09:00:00', 'Owner capital contribution')
ON CONFLICT DO NOTHING;

-- Journal Entry 2: Purchase of equipment
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-01-15', 'JE-002', 'Purchase of office equipment', 'posted', 250000.00, 250000.00, TRUE, '2025-01-15 10:30:00', '2025-01-15 10:30:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-002'), (SELECT id FROM gl_accounts WHERE code = '1620'), 250000.00, 0, 'Computer and office equipment'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-002'), (SELECT id FROM gl_accounts WHERE code = '1200'), 0, 250000.00, 'Payment to vendor')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-002'), (SELECT id FROM gl_accounts WHERE code = '1620'), 'debit', 250000.00, '2025-01-15 10:30:00', 'Computer and office equipment'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-002'), (SELECT id FROM gl_accounts WHERE code = '1200'), 'credit', 250000.00, '2025-01-15 10:30:00', 'Payment to vendor')
ON CONFLICT DO NOTHING;

-- Journal Entry 3: First month sales revenue
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-01-31', 'JE-003', 'January sales revenue', 'posted', 450000.00, 450000.00, TRUE, '2025-01-31 16:00:00', '2025-01-31 16:00:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-003'), (SELECT id FROM gl_accounts WHERE code = '1200'), 300000.00, 0, 'Cash sales'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-003'), (SELECT id FROM gl_accounts WHERE code = '1300'), 150000.00, 0, 'Credit sales'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-003'), (SELECT id FROM gl_accounts WHERE code = '4000'), 0, 450000.00, 'Total sales for January')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-003'), (SELECT id FROM gl_accounts WHERE code = '1200'), 'debit', 300000.00, '2025-01-31 16:00:00', 'Cash sales'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-003'), (SELECT id FROM gl_accounts WHERE code = '1300'), 'debit', 150000.00, '2025-01-31 16:00:00', 'Credit sales'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-003'), (SELECT id FROM gl_accounts WHERE code = '4000'), 'credit', 450000.00, '2025-01-31 16:00:00', 'Total sales for January')
ON CONFLICT DO NOTHING;

-- Journal Entry 4: Salary payment
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-01-31', 'JE-004', 'January salary payment', 'posted', 180000.00, 180000.00, TRUE, '2025-01-31 14:00:00', '2025-01-31 14:00:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-004'), (SELECT id FROM gl_accounts WHERE code = '5100'), 180000.00, 0, 'Staff salaries'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-004'), (SELECT id FROM gl_accounts WHERE code = '1210'), 0, 180000.00, 'Payment from payroll account')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-004'), (SELECT id FROM gl_accounts WHERE code = '5100'), 'debit', 180000.00, '2025-01-31 14:00:00', 'Staff salaries'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-004'), (SELECT id FROM gl_accounts WHERE code = '1210'), 'credit', 180000.00, '2025-01-31 14:00:00', 'Payment from payroll account')
ON CONFLICT DO NOTHING;

-- Journal Entry 5: Rent payment
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-02-01', 'JE-005', 'February rent payment', 'posted', 75000.00, 75000.00, TRUE, '2025-02-01 09:00:00', '2025-02-01 09:00:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-005'), (SELECT id FROM gl_accounts WHERE code = '5200'), 75000.00, 0, 'Office rent'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-005'), (SELECT id FROM gl_accounts WHERE code = '1200'), 0, 75000.00, 'Rent payment')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-005'), (SELECT id FROM gl_accounts WHERE code = '5200'), 'debit', 75000.00, '2025-02-01 09:00:00', 'Office rent'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-005'), (SELECT id FROM gl_accounts WHERE code = '1200'), 'credit', 75000.00, '2025-02-01 09:00:00', 'Rent payment')
ON CONFLICT DO NOTHING;

-- Journal Entry 6: Utilities
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-02-15', 'JE-006', 'Utilities - electricity and water', 'posted', 18500.00, 18500.00, TRUE, '2025-02-15 11:00:00', '2025-02-15 11:00:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-006'), (SELECT id FROM gl_accounts WHERE code = '5300'), 18500.00, 0, 'Electricity and water bills'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-006'), (SELECT id FROM gl_accounts WHERE code = '1200'), 0, 18500.00, 'Payment')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-006'), (SELECT id FROM gl_accounts WHERE code = '5300'), 'debit', 18500.00, '2025-02-15 11:00:00', 'Electricity and water bills'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-006'), (SELECT id FROM gl_accounts WHERE code = '1200'), 'credit', 18500.00, '2025-02-15 11:00:00', 'Payment')
ON CONFLICT DO NOTHING;

-- Journal Entry 7: Marketing expense
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-03-10', 'JE-007', 'Marketing campaign - social media ads', 'posted', 35000.00, 35000.00, TRUE, '2025-03-10 13:00:00', '2025-03-10 13:00:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-007'), (SELECT id FROM gl_accounts WHERE code = '5600'), 35000.00, 0, 'Facebook and Instagram ads'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-007'), (SELECT id FROM gl_accounts WHERE code = '1200'), 0, 35000.00, 'Payment to Meta')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-007'), (SELECT id FROM gl_accounts WHERE code = '5600'), 'debit', 35000.00, '2025-03-10 13:00:00', 'Facebook and Instagram ads'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-007'), (SELECT id FROM gl_accounts WHERE code = '1200'), 'credit', 35000.00, '2025-03-10 13:00:00', 'Payment to Meta')
ON CONFLICT DO NOTHING;

-- Journal Entry 8: Depreciation
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, posted_at, timestamp) VALUES
('2025-03-31', 'JE-008', 'Q1 depreciation expense', 'posted', 12500.00, 12500.00, TRUE, '2025-03-31 17:00:00', '2025-03-31 17:00:00')
ON CONFLICT DO NOTHING;

INSERT INTO GL_Journal_Lines (journal_entry_id, account_id, debit, credit, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-008'), (SELECT id FROM gl_accounts WHERE code = '5500'), 12500.00, 0, 'Quarterly depreciation on equipment'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-008'), (SELECT id FROM gl_accounts WHERE code = '1700'), 0, 12500.00, 'Accumulated depreciation')
ON CONFLICT DO NOTHING;

INSERT INTO gl_transactions (journal_entry_id, account_id, type, amount, timestamp, memo) VALUES
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-008'), (SELECT id FROM gl_accounts WHERE code = '5500'), 'debit', 12500.00, '2025-03-31 17:00:00', 'Quarterly depreciation on equipment'),
((SELECT id FROM GL_Journal_Entries WHERE reference = 'JE-008'), (SELECT id FROM gl_accounts WHERE code = '1700'), 'credit', 12500.00, '2025-03-31 17:00:00', 'Accumulated depreciation')
ON CONFLICT DO NOTHING;

-- Journal Entry 9: Draft entry (not yet posted)
INSERT INTO GL_Journal_Entries (entry_date, reference, description, status, total_debits, total_credits, is_balanced, timestamp) VALUES
('2025-11-25', 'JE-009', 'November sales revenue (draft)', 'draft', 0.00, 0.00, FALSE, '2025-11-25 10:00:00')
ON CONFLICT DO NOTHING;

-- 5. Insert Sample Budgets
-- ============================================================================

INSERT INTO financial_budgets (budget_name, description, fiscal_period_id, budget_type, start_date, end_date, total_amount, status) VALUES
('2025 Operating Budget', 'Annual operating budget for fiscal year 2025', (SELECT id FROM fiscal_periods WHERE period_name = 'Q1 2025' LIMIT 1), 'operating', '2025-01-01', '2025-12-31', 12000000.00, 'approved'),
('Q4 2025 Marketing Budget', 'Marketing initiatives for final quarter', (SELECT id FROM fiscal_periods WHERE period_name = 'Q4 2025' LIMIT 1), 'operating', '2025-10-01', '2025-12-31', 500000.00, 'active')
ON CONFLICT DO NOTHING;

-- Insert budget line items for Operating Budget
INSERT INTO budget_line_items (budget_id, gl_account_id, line_description, budgeted_amount) VALUES
((SELECT id FROM financial_budgets WHERE budget_name = '2025 Operating Budget'), (SELECT id FROM gl_accounts WHERE code = '5100'), 'Annual salaries and wages', 2160000.00),
((SELECT id FROM financial_budgets WHERE budget_name = '2025 Operating Budget'), (SELECT id FROM gl_accounts WHERE code = '5200'), 'Annual rent expense', 900000.00),
((SELECT id FROM financial_budgets WHERE budget_name = '2025 Operating Budget'), (SELECT id FROM gl_accounts WHERE code = '5300'), 'Utilities for the year', 240000.00),
((SELECT id FROM financial_budgets WHERE budget_name = '2025 Operating Budget'), (SELECT id FROM gl_accounts WHERE code = '5600'), 'Marketing and advertising', 600000.00),
((SELECT id FROM financial_budgets WHERE budget_name = '2025 Operating Budget'), (SELECT id FROM gl_accounts WHERE code = '5700'), 'Office supplies', 150000.00)
ON CONFLICT DO NOTHING;

-- Insert budget line items for Marketing Budget
INSERT INTO budget_line_items (budget_id, gl_account_id, line_description, budgeted_amount) VALUES
((SELECT id FROM financial_budgets WHERE budget_name = 'Q4 2025 Marketing Budget'), (SELECT id FROM gl_accounts WHERE code = '5600'), 'Digital advertising campaigns', 300000.00),
((SELECT id FROM financial_budgets WHERE budget_name = 'Q4 2025 Marketing Budget'), (SELECT id FROM gl_accounts WHERE code = '5900'), 'Trade shows and events', 150000.00),
((SELECT id FROM financial_budgets WHERE budget_name = 'Q4 2025 Marketing Budget'), (SELECT id FROM gl_accounts WHERE code = '5700'), 'Promotional materials', 50000.00)
ON CONFLICT DO NOTHING;

-- 6. Insert Sample Bank Reconciliation
-- ============================================================================

INSERT INTO bank_reconciliations (bank_account_id, reconciliation_date, statement_date, statement_balance, book_balance, status, is_balanced, variance) VALUES
((SELECT id FROM bank_accounts WHERE account_number = '1234567890'), '2025-01-31', '2025-01-31', 1245000.00, 1245000.00, 'reconciled', TRUE, 0.00)
ON CONFLICT DO NOTHING;

-- 7. Insert Financial Reports Configuration
-- ============================================================================

INSERT INTO financial_reports (report_name, report_type, description, is_active) VALUES
('Monthly Trial Balance', 'trial_balance', 'Standard trial balance report showing all account balances', TRUE),
('Income Statement - Monthly', 'income_statement', 'Profit and loss statement for the month', TRUE),
('Balance Sheet - End of Month', 'balance_sheet', 'Statement of financial position at month end', TRUE),
('Cash Flow Statement', 'cash_flow', 'Statement of cash flows showing operating, investing, and financing activities', TRUE),
('General Ledger Detail', 'general_ledger', 'Detailed transaction listing for all accounts', TRUE)
ON CONFLICT DO NOTHING;

-- Complete
SELECT 'Finance Management Schema and Demo Data Installed Successfully' AS status,
       (SELECT COUNT(*) FROM gl_accounts) AS total_accounts,
       (SELECT COUNT(*) FROM GL_Journal_Entries) AS total_journal_entries,
       (SELECT COUNT(*) FROM bank_accounts) AS total_bank_accounts;
