-- Migration: Double Entry Validation Functions and Triggers
-- Date: 2025-11-11
-- Description: Create functions and triggers for double-entry validation and audit logging

-- 1. Double Entry Validation Functions
CREATE OR REPLACE FUNCTION validate_journal_entry_balance(entry_id INTEGER)
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),
        updatedat = NOW()
    WHERE id = entry_id;
    
    -- Return true if balanced (within 1 cent tolerance)
    RETURN balance_diff < 0.01;
END;
$$ LANGUAGE plpgsql;

-- 2. Audit Trail Logging Function
CREATE OR REPLACE FUNCTION log_gl_changes()
RETURNS TRIGGER AS $$
DECLARE
    current_user_id UUID;
BEGIN
    -- Try to get current user from session or use system user
    current_user_id := COALESCE(
        current_setting('app.current_user_id', true)::UUID,
        '00000000-0000-0000-0000-000000000000'::UUID
    );

    -- 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.id::TEXT::UUID, 'INSERT', row_to_json(NEW),
                COALESCE(NEW.created_by, current_user_id)
            );
            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.id::TEXT::UUID, 'UPDATE', row_to_json(OLD), row_to_json(NEW),
                COALESCE(NEW.created_by, current_user_id)
            );
            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.id::TEXT::UUID, 'DELETE', row_to_json(OLD),
                current_user_id
            );
            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::TEXT::UUID, 'INSERT', row_to_json(NEW),
                COALESCE(NEW.created_by, current_user_id)
            );
            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::TEXT::UUID, 'UPDATE', row_to_json(OLD), row_to_json(NEW),
                COALESCE(NEW.updated_by, NEW.created_by, current_user_id)
            );
            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::TEXT::UUID, 'DELETE', row_to_json(OLD),
                current_user_id
            );
            RETURN OLD;
        END IF;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 3. 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;

-- 4. 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;

-- 5. 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();