-- ============================================================================
-- Finance Management Module - Demo Data
-- ============================================================================
-- Description: Realistic sample data for Finance Management module
-- Date: 2025-11-21
-- Author: TAF Development Team
-- ============================================================================

-- 1. Insert Standard Chart of Accounts (if not exists)
-- ============================================================================

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 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-21', 'JE-009', 'November sales revenue (draft)', 'draft', 0.00, 0.00, FALSE, '2025-11-21 10:00:00');

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

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

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

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

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

-- Complete
SELECT 'Finance Management Demo Data Inserted Successfully' AS status;
