-- Migration: GL Audit Trail System
-- Date: 2025-11-11
-- Description: Create comprehensive audit trail system for all GL operations

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

-- 2. 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 INTEGER NOT NULL,
    credit_account_id INTEGER 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(id),
    
    CONSTRAINT fk_posting_rule_credit_account 
    FOREIGN KEY (credit_account_id) REFERENCES gl_accounts(id)
);

-- 3. GL Validation Errors tracking
CREATE TABLE IF NOT EXISTS gl_validation_errors (
    error_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    journal_entry_id INTEGER NOT NULL,
    error_type VARCHAR(50) NOT NULL,
    error_message TEXT NOT NULL,
    field_name VARCHAR(100) NULL,
    severity VARCHAR(20) DEFAULT 'ERROR', -- ERROR, WARNING, INFO
    resolved BOOLEAN DEFAULT FALSE,
    resolved_by UUID NULL,
    resolved_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_validation_error_entry 
    FOREIGN KEY (journal_entry_id) REFERENCES GL_Journal_Entries(id) ON DELETE CASCADE,
    
    CONSTRAINT fk_validation_error_resolved_by 
    FOREIGN KEY (resolved_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- 4. Performance indexes
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_ap_transactions_gl_posted ON ap_transactions(gl_posted, org_id);
CREATE INDEX IF NOT EXISTS idx_ar_transactions_gl_posted ON ar_transactions(gl_posted, org_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_user_timestamp ON gl_audit_trail(user_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_trail_timestamp ON gl_audit_trail(timestamp);
CREATE INDEX IF NOT EXISTS idx_gl_validation_errors_entry ON gl_validation_errors(journal_entry_id, resolved);
CREATE INDEX IF NOT EXISTS idx_gl_posting_rules_module_event ON gl_posting_rules(source_module, trigger_event, is_active);