-- ============================================================================
-- Expense Management Migration
-- Created: 2025-11-26
-- Description: Creates expenses table and expense types configuration table
--              with UUID v7 primary keys and configurable GL account mapping
-- ============================================================================

BEGIN;

-- ============================================================================
-- 1. Create Expense Types Configuration Table
-- ============================================================================

CREATE TABLE IF NOT EXISTS gl_expense_types (
    expense_type_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    org_id UUID NOT NULL,
    
    -- Type definition
    type_code VARCHAR(50) NOT NULL,
    type_name VARCHAR(100) NOT NULL,
    description TEXT,
    
    -- GL Account mapping
    expense_account_id UUID NULL,
    vat_account_id UUID NULL,
    payable_account_id UUID NULL,
    
    -- Configuration
    is_active BOOLEAN DEFAULT true,
    display_order INTEGER DEFAULT 100,
    
    -- Audit fields
    created_by UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_expense_types_org FOREIGN KEY (org_id) REFERENCES orgs(org_id) ON DELETE CASCADE,
    CONSTRAINT fk_expense_types_expense_account FOREIGN KEY (expense_account_id) REFERENCES gl_accounts(id) ON DELETE RESTRICT,
    CONSTRAINT fk_expense_types_vat_account FOREIGN KEY (vat_account_id) REFERENCES gl_accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_types_payable_account FOREIGN KEY (payable_account_id) REFERENCES gl_accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_expense_types_created_by FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL,
    CONSTRAINT uk_expense_types_org_code UNIQUE (org_id, type_code)
);

-- ============================================================================
-- 2. Create Expenses Table with UUID v7 Primary Key
-- ============================================================================

CREATE TABLE IF NOT EXISTS gl_expenses (
    expense_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    org_id UUID NOT NULL,
    
    -- Expense details
    expense_date DATE NOT NULL DEFAULT CURRENT_DATE,
    invoice_number VARCHAR(100),
    tin VARCHAR(50),
    supplier_name VARCHAR(255) NOT NULL,
    expense_type_id UUID NOT NULL,
    memo TEXT,
    
    -- Financial details
    amount NUMERIC(15, 2) NOT NULL CHECK (amount > 0),
    vat NUMERIC(15, 2) DEFAULT 0 CHECK (vat >= 0),
    
    -- Status
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'posted', 'void')),
    
    -- Journal entry reference (set by controller)
    journal_entry_id UUID,
    
    -- Audit fields
    created_by UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_expenses_org FOREIGN KEY (org_id) REFERENCES orgs(org_id) ON DELETE CASCADE,
    CONSTRAINT fk_expenses_type FOREIGN KEY (expense_type_id) REFERENCES gl_expense_types(expense_type_id) ON DELETE RESTRICT,
    CONSTRAINT fk_expenses_journal FOREIGN KEY (journal_entry_id) REFERENCES GL_Journal_Entries(id) ON DELETE SET NULL,
    CONSTRAINT fk_expenses_created_by FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- ============================================================================
-- 3. Create Indexes for Better Performance
-- ============================================================================

CREATE INDEX idx_gl_expenses_org ON gl_expenses(org_id);
CREATE INDEX idx_gl_expenses_date ON gl_expenses(expense_date);
CREATE INDEX idx_gl_expenses_type ON gl_expenses(expense_type_id);
CREATE INDEX idx_gl_expenses_supplier ON gl_expenses(supplier_name);
CREATE INDEX idx_gl_expenses_invoice ON gl_expenses(invoice_number);
CREATE INDEX idx_gl_expenses_journal ON gl_expenses(journal_entry_id);
CREATE INDEX idx_gl_expenses_status ON gl_expenses(status);

CREATE INDEX idx_gl_expense_types_org ON gl_expense_types(org_id);
CREATE INDEX idx_gl_expense_types_active ON gl_expense_types(is_active) WHERE is_active = true;
CREATE INDEX idx_gl_expense_types_code ON gl_expense_types(type_code);

-- Full-text search index
CREATE INDEX idx_gl_expenses_search ON gl_expenses USING gin(
    to_tsvector('english', 
        coalesce(supplier_name, '') || ' ' || 
        coalesce(memo, '') || ' ' || 
        coalesce(invoice_number, '')
    )
);

-- ============================================================================
-- 4. Create Triggers for Updated Timestamp
-- ============================================================================

CREATE OR REPLACE FUNCTION update_gl_expenses_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER gl_expenses_updated_at_trigger
    BEFORE UPDATE ON gl_expenses
    FOR EACH ROW
    EXECUTE FUNCTION update_gl_expenses_updated_at();

CREATE TRIGGER gl_expense_types_updated_at_trigger
    BEFORE UPDATE ON gl_expense_types
    FOR EACH ROW
    EXECUTE FUNCTION update_gl_expenses_updated_at();

-- ============================================================================
-- 5. Insert Default Expense Types (for existing organizations)
-- ============================================================================

-- This will create default expense types for each organization
-- You'll need to update the account IDs after the migration based on your GL account setup

INSERT INTO gl_expense_types (org_id, type_code, type_name, description, expense_account_id, display_order) 
SELECT 
    o.org_id,
    type_code,
    type_name,
    description,
    (SELECT id FROM gl_accounts 
     WHERE org_id = o.org_id 
     AND account_type = 'expense' 
     AND name ILIKE '%' || CASE type_code
         WHEN 'utilities' THEN 'utilities'
         WHEN 'rent' THEN 'rent'
         WHEN 'salaries' THEN 'salaries'
         WHEN 'supplies' THEN 'supplies'
         WHEN 'maintenance' THEN 'maintenance'
         WHEN 'travel' THEN 'travel'
         WHEN 'marketing' THEN 'marketing'
         WHEN 'insurance' THEN 'insurance'
         WHEN 'professional_fees' THEN 'professional'
         WHEN 'telecommunications' THEN 'telecom'
         ELSE 'expense'
     END || '%'
     LIMIT 1) as expense_account_id,
    display_order
FROM orgs o
CROSS JOIN (
    VALUES 
        ('utilities', 'Utilities', 'Electricity, water, gas, internet', 10),
        ('rent', 'Rent', 'Office and facility rent', 20),
        ('salaries', 'Salaries & Wages', 'Employee compensation', 30),
        ('supplies', 'Office Supplies', 'Stationery, materials, consumables', 40),
        ('maintenance', 'Maintenance & Repairs', 'Equipment and facility maintenance', 50),
        ('travel', 'Travel & Transportation', 'Business travel, fuel, vehicle costs', 60),
        ('marketing', 'Marketing & Advertising', 'Promotional activities and advertising', 70),
        ('insurance', 'Insurance', 'Business insurance premiums', 80),
        ('professional_fees', 'Professional Fees', 'Legal, accounting, consulting fees', 90),
        ('telecommunications', 'Telecommunications', 'Phone, mobile, internet services', 100),
        ('other', 'Other Expenses', 'Miscellaneous expenses', 999)
) AS types(type_code, type_name, description, display_order)
WHERE EXISTS (
    SELECT 1 FROM gl_accounts 
    WHERE org_id = o.org_id 
    AND account_type = 'expense'
)
ON CONFLICT (org_id, type_code) DO NOTHING;

-- ============================================================================
-- 6. Create Views for Reporting
-- ============================================================================

-- Expense summary by type and month
CREATE OR REPLACE VIEW gl_expense_summary_by_type AS
SELECT 
    e.org_id,
    et.type_name,
    et.type_code,
    DATE_TRUNC('month', e.expense_date) as month,
    COUNT(*) as expense_count,
    SUM(e.amount) as total_amount,
    SUM(e.vat) as total_vat,
    SUM(e.amount + e.vat) as total_with_vat,
    AVG(e.amount) as avg_amount
FROM gl_expenses e
JOIN gl_expense_types et ON e.expense_type_id = et.expense_type_id
WHERE e.status != 'void'
GROUP BY e.org_id, et.type_name, et.type_code, DATE_TRUNC('month', e.expense_date);

-- Expenses by supplier
CREATE OR REPLACE VIEW gl_expenses_by_supplier AS
SELECT 
    e.org_id,
    e.supplier_name,
    e.tin,
    COUNT(*) as expense_count,
    SUM(e.amount + e.vat) as total_spent,
    MAX(e.expense_date) as last_expense_date,
    MIN(e.expense_date) as first_expense_date
FROM gl_expenses e
WHERE e.status != 'void'
GROUP BY e.org_id, e.supplier_name, e.tin;

-- ============================================================================
-- 7. Add Comments
-- ============================================================================

COMMENT ON TABLE gl_expenses IS 'Stores business expense records with UUID v7 primary keys';
COMMENT ON TABLE gl_expense_types IS 'Configuration table for expense types and their GL account mappings';

COMMENT ON COLUMN gl_expenses.expense_id IS 'UUID v7 primary key for expense records';
COMMENT ON COLUMN gl_expenses.journal_entry_id IS 'Reference to auto-created journal entry (set by controller)';
COMMENT ON COLUMN gl_expenses.status IS 'Expense status: draft (not posted), posted (journal created), void (cancelled)';

COMMENT ON COLUMN gl_expense_types.expense_type_id IS 'UUID primary key for expense type configuration';
COMMENT ON COLUMN gl_expense_types.expense_account_id IS 'GL account to debit for expenses of this type';
COMMENT ON COLUMN gl_expense_types.vat_account_id IS 'GL account to debit for VAT on expenses of this type';
COMMENT ON COLUMN gl_expense_types.payable_account_id IS 'GL account to credit (accounts payable) for expenses of this type';

COMMIT;

-- ============================================================================
-- End of Migration
-- ============================================================================
