-- Double Entry System Database Enhancements
-- Phase 1: Core double-entry validation and audit trails
-- Date: 2025-11-11

-- 1. Enhanced Journal Entries with workflow tracking and validation
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'draft';
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS posted_at TIMESTAMPTZ NULL;
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS posted_by UUID NULL;
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS reversal_entry_id UUID NULL;
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS source_module VARCHAR(50) NULL;
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS source_id VARCHAR(100) NULL;
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS total_debits DECIMAL(15,2) DEFAULT 0.00;
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS total_credits DECIMAL(15,2) DEFAULT 0.00;
ALTER TABLE GL_Journal_Entries ADD COLUMN IF NOT EXISTS is_balanced BOOLEAN DEFAULT FALSE;

-- Add foreign key constraint for posted_by
ALTER TABLE GL_Journal_Entries 
ADD CONSTRAINT IF NOT EXISTS fk_journal_entry_posted_by 
FOREIGN KEY (posted_by) REFERENCES users(user_id) ON DELETE SET NULL;

-- Add self-referencing constraint for reversals
ALTER TABLE GL_Journal_Entries 
ADD CONSTRAINT IF NOT EXISTS fk_journal_entry_reversal 
FOREIGN KEY (reversal_entry_id) REFERENCES GL_Journal_Entries(entry_id) ON DELETE SET NULL;

-- 2. Enhanced GL Transactions with better tracking
ALTER TABLE gl_transactions ADD COLUMN IF NOT EXISTS created_by UUID NULL;
ALTER TABLE gl_transactions ADD COLUMN IF NOT EXISTS updated_by UUID NULL;
ALTER TABLE gl_transactions ADD COLUMN IF NOT EXISTS line_description TEXT NULL;
ALTER TABLE gl_transactions ADD COLUMN IF NOT EXISTS source_document VARCHAR(100) NULL;
ALTER TABLE gl_transactions ADD COLUMN IF NOT EXISTS is_system_generated BOOLEAN DEFAULT FALSE;

-- 3. Enhanced AP/AR Transactions
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS gl_posted BOOLEAN DEFAULT FALSE;
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS gl_journal_entry_id UUID NULL;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS gl_posted BOOLEAN DEFAULT FALSE;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS gl_journal_entry_id UUID NULL;

-- 4. Comprehensive Audit Trail System
CREATE TABLE IF NOT EXISTS gl_audit_trail (
    audit_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    table_name VARCHAR(100) NOT NULL,
    record_id UUID NOT NULL,
    operation_type VARCHAR(20) NOT NULL, -- INSERT, UPDATE, DELETE, POST, REVERSE
    field_name VARCHAR(100) NULL,
    old_value TEXT NULL,
    new_value TEXT NULL,
    complete_old_record JSONB NULL,
    complete_new_record JSONB NULL,
    user_id UUID NOT NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    ip_address INET NULL,
    user_agent TEXT NULL,
    session_id VARCHAR(100) NULL,
    reason TEXT NULL,
    source_application VARCHAR(50) DEFAULT 'TAF_WEB',
    
    CONSTRAINT fk_audit_trail_user 
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- 5. GL Posting Rules for automation
CREATE TABLE IF NOT EXISTS gl_posting_rules (
    rule_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    org_id UUID NULL,
    rule_name VARCHAR(100) NOT NULL,
    source_module VARCHAR(50) NOT NULL,
    trigger_event VARCHAR(100) NOT NULL,
    debit_account_id UUID NOT NULL,
    credit_account_id UUID NOT NULL,
    amount_field VARCHAR(100) NOT NULL,
    description_template TEXT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_posting_rule_debit_account 
    FOREIGN KEY (debit_account_id) REFERENCES gl_accounts(account_id),
    
    CONSTRAINT fk_posting_rule_credit_account 
    FOREIGN KEY (credit_account_id) REFERENCES gl_accounts(account_id)
);

-- 6. Indexes for Performance
CREATE INDEX IF NOT EXISTS idx_gl_transactions_account_timestamp ON gl_transactions(account_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_gl_journal_status_date ON GL_Journal_Entries(status, timestamp);
CREATE INDEX IF NOT EXISTS idx_gl_journal_source ON GL_Journal_Entries(source_module, source_id);
CREATE INDEX IF NOT EXISTS idx_audit_trail_table_record ON gl_audit_trail(table_name, record_id);
CREATE INDEX IF NOT EXISTS idx_audit_trail_timestamp ON gl_audit_trail(timestamp);

-- 7. Double Entry Validation Functions
CREATE OR REPLACE FUNCTION validate_journal_entry_balance(entry_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    debit_total DECIMAL(15,2);
    credit_total DECIMAL(15,2);
    balance_diff DECIMAL(15,2);
BEGIN
    -- Calculate total debits and credits for the journal entry
    SELECT 
        COALESCE(SUM(CASE WHEN type = 'debit' THEN amount ELSE 0 END), 0),
        COALESCE(SUM(CASE WHEN type = 'credit' THEN amount ELSE 0 END), 0)
    INTO debit_total, credit_total
    FROM gl_transactions 
    WHERE journal_entry_id = entry_id;
    
    -- Calculate difference (should be 0 for valid double entry)
    balance_diff := ABS(debit_total - credit_total);
    
    -- Update journal entry totals
    UPDATE GL_Journal_Entries 
    SET 
        total_debits = debit_total,
        total_credits = credit_total,
        is_balanced = (balance_diff < 0.01)
    WHERE GL_Journal_Entries.entry_id = validate_journal_entry_balance.entry_id;
    
    -- Return true if balanced (within 1 cent tolerance)
    RETURN balance_diff < 0.01;
END;
$$ LANGUAGE plpgsql;

-- 8. Audit Trail Logging Function
CREATE OR REPLACE FUNCTION log_gl_changes()
RETURNS TRIGGER AS $$
BEGIN
    -- Log GL Journal Entry changes
    IF TG_TABLE_NAME = 'GL_Journal_Entries' THEN
        IF TG_OP = 'INSERT' THEN
            INSERT INTO gl_audit_trail (
                table_name, record_id, operation_type, complete_new_record, 
                user_id
            ) VALUES (
                'GL_Journal_Entries', NEW.entry_id, 'INSERT', row_to_json(NEW),
                COALESCE(NEW.created_by, '00000000-0000-0000-0000-000000000000'::UUID)
            );
            RETURN NEW;
        ELSIF TG_OP = 'UPDATE' THEN
            INSERT INTO gl_audit_trail (
                table_name, record_id, operation_type, complete_old_record, complete_new_record,
                user_id
            ) VALUES (
                'GL_Journal_Entries', NEW.entry_id, 'UPDATE', row_to_json(OLD), row_to_json(NEW),
                COALESCE(NEW.updated_by, NEW.created_by, '00000000-0000-0000-0000-000000000000'::UUID)
            );
            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN
            INSERT INTO gl_audit_trail (
                table_name, record_id, operation_type, complete_old_record,
                user_id
            ) VALUES (
                'GL_Journal_Entries', OLD.entry_id, 'DELETE', row_to_json(OLD),
                '00000000-0000-0000-0000-000000000000'::UUID
            );
            RETURN OLD;
        END IF;
    END IF;
    
    -- Log GL Transaction changes
    IF TG_TABLE_NAME = 'gl_transactions' THEN
        IF TG_OP = 'INSERT' THEN
            INSERT INTO gl_audit_trail (
                table_name, record_id, operation_type, complete_new_record,
                user_id
            ) VALUES (
                'gl_transactions', NEW.transaction_id, 'INSERT', row_to_json(NEW),
                COALESCE(NEW.created_by, '00000000-0000-0000-0000-000000000000'::UUID)
            );
            RETURN NEW;
        ELSIF TG_OP = 'UPDATE' THEN
            INSERT INTO gl_audit_trail (
                table_name, record_id, operation_type, complete_old_record, complete_new_record,
                user_id
            ) VALUES (
                'gl_transactions', NEW.transaction_id, 'UPDATE', row_to_json(OLD), row_to_json(NEW),
                COALESCE(NEW.updated_by, NEW.created_by, '00000000-0000-0000-0000-000000000000'::UUID)
            );
            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN
            INSERT INTO gl_audit_trail (
                table_name, record_id, operation_type, complete_old_record,
                user_id
            ) VALUES (
                'gl_transactions', OLD.transaction_id, 'DELETE', row_to_json(OLD),
                '00000000-0000-0000-0000-000000000000'::UUID
            );
            RETURN OLD;
        END IF;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 9. Trigger to validate journal entry balance on transaction changes
CREATE OR REPLACE FUNCTION validate_on_transaction_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Validate the journal entry balance when transactions are modified
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        PERFORM validate_journal_entry_balance(NEW.journal_entry_id);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        PERFORM validate_journal_entry_balance(OLD.journal_entry_id);
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 10. Function to prevent posting unbalanced entries
CREATE OR REPLACE FUNCTION prevent_unbalanced_posting()
RETURNS TRIGGER AS $$
BEGIN
    -- Only allow posting if journal entry is balanced
    IF NEW.status = 'posted' AND OLD.status != 'posted' THEN
        IF NOT COALESCE(NEW.is_balanced, FALSE) THEN
            RAISE EXCEPTION 'Cannot post unbalanced journal entry. Debits (%) must equal Credits (%)', 
                NEW.total_debits, NEW.total_credits;
        END IF;
        
        -- Set posted timestamp
        NEW.posted_at = NOW();
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 11. Create triggers for audit logging and validation
DROP TRIGGER IF EXISTS audit_gl_journal_entries ON GL_Journal_Entries;
CREATE TRIGGER audit_gl_journal_entries
    AFTER INSERT OR UPDATE OR DELETE ON GL_Journal_Entries
    FOR EACH ROW EXECUTE FUNCTION log_gl_changes();

DROP TRIGGER IF EXISTS audit_gl_transactions ON gl_transactions;
CREATE TRIGGER audit_gl_transactions
    AFTER INSERT OR UPDATE OR DELETE ON gl_transactions
    FOR EACH ROW EXECUTE FUNCTION log_gl_changes();

DROP TRIGGER IF EXISTS validate_journal_balance ON gl_transactions;
CREATE TRIGGER validate_journal_balance
    AFTER INSERT OR UPDATE OR DELETE ON gl_transactions
    FOR EACH ROW EXECUTE FUNCTION validate_on_transaction_change();

DROP TRIGGER IF EXISTS prevent_unbalanced_posting_trigger ON GL_Journal_Entries;
CREATE TRIGGER prevent_unbalanced_posting_trigger
    BEFORE UPDATE ON GL_Journal_Entries
    FOR EACH ROW EXECUTE FUNCTION prevent_unbalanced_posting();

-- 12. Sample GL posting rules for common transactions
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event, 
    debit_account_id, credit_account_id, amount_field, 
    description_template, is_active
) 
SELECT 
    'POS Cash Sales', 'pos', 'sale_completed',
    cash_account.account_id, revenue_account.account_id, 'total_amount',
    'POS Sale #{source_id}', true
FROM 
    (SELECT account_id FROM gl_accounts WHERE code = '1100' LIMIT 1) cash_account,
    (SELECT account_id FROM gl_accounts WHERE code = '4000' LIMIT 1) revenue_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '1100')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '4000')
ON CONFLICT DO NOTHING;

COMMIT;