-- Migration: Double Entry System Database Enhancement
-- Date: 2025-11-11
-- Description: Add double-entry validation, audit trails, and GL posting automation

-- 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 INTEGER 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 
DROP CONSTRAINT IF EXISTS fk_journal_entry_posted_by;

ALTER TABLE GL_Journal_Entries 
ADD CONSTRAINT 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 
DROP CONSTRAINT IF EXISTS fk_journal_entry_reversal;

ALTER TABLE GL_Journal_Entries 
ADD CONSTRAINT fk_journal_entry_reversal 
FOREIGN KEY (reversal_entry_id) REFERENCES GL_Journal_Entries(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;

-- Add foreign key constraints for gl_transactions
ALTER TABLE gl_transactions 
DROP CONSTRAINT IF EXISTS fk_gl_transaction_created_by;

ALTER TABLE gl_transactions 
ADD CONSTRAINT fk_gl_transaction_created_by 
FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL;

ALTER TABLE gl_transactions 
DROP CONSTRAINT IF EXISTS fk_gl_transaction_updated_by;

ALTER TABLE gl_transactions 
ADD CONSTRAINT fk_gl_transaction_updated_by 
FOREIGN KEY (updated_by) REFERENCES users(user_id) ON DELETE SET NULL;

-- 3. Enhanced AP/AR Transactions with GL integration
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS vendor_id UUID NULL;
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS due_date DATE NULL;
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS payment_terms VARCHAR(50) NULL;
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS payment_method VARCHAR(50) NULL;
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS payment_date DATE NULL;
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS payment_amount DECIMAL(15,2) NULL;
ALTER TABLE ap_transactions ADD COLUMN IF NOT EXISTS check_number VARCHAR(50) NULL;
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 INTEGER NULL;

ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS customer_id UUID NULL;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS due_date DATE NULL;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS payment_terms VARCHAR(50) NULL;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS payment_method VARCHAR(50) NULL;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS payment_date DATE NULL;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS payment_amount DECIMAL(15,2) NULL;
ALTER TABLE ar_transactions ADD COLUMN IF NOT EXISTS reference_number VARCHAR(50) 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 INTEGER NULL;

-- Add foreign keys for GL posting references
ALTER TABLE ap_transactions 
DROP CONSTRAINT IF EXISTS fk_ap_transaction_gl_entry;

ALTER TABLE ap_transactions 
ADD CONSTRAINT fk_ap_transaction_gl_entry 
FOREIGN KEY (gl_journal_entry_id) REFERENCES GL_Journal_Entries(id) ON DELETE SET NULL;

ALTER TABLE ar_transactions 
DROP CONSTRAINT IF EXISTS fk_ar_transaction_gl_entry;

ALTER TABLE ar_transactions 
ADD CONSTRAINT fk_ar_transaction_gl_entry 
FOREIGN KEY (gl_journal_entry_id) REFERENCES GL_Journal_Entries(id) ON DELETE SET NULL;