-- Migration: GL Posting Rules and Sample Data
-- Date: 2025-11-11
-- Description: Add sample GL posting rules for common business transactions

-- Insert sample GL posting rules for POS sales
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.id, revenue_account.id, 'total_amount',
    'POS Sale #{source_id}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '1100' LIMIT 1) cash_account,
    (SELECT 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;

-- Insert posting rule for invoice payments
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event,
    debit_account_id, credit_account_id, amount_field,
    description_template, is_active
)
SELECT 
    'Invoice Payment Received', 'invoices', 'payment_received',
    cash_account.id, ar_account.id, 'payment_amount',
    'Payment for Invoice #{source_id}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '1100' LIMIT 1) cash_account,
    (SELECT id FROM gl_accounts WHERE code = '1200' LIMIT 1) ar_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '1100')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '1200')
ON CONFLICT DO NOTHING;

-- Insert posting rule for creating AR invoices
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event,
    debit_account_id, credit_account_id, amount_field,
    description_template, is_active
)
SELECT 
    'Invoice Created', 'invoices', 'invoice_created',
    ar_account.id, revenue_account.id, 'total_amount',
    'Invoice #{source_id} - {customer_name}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '1200' LIMIT 1) ar_account,
    (SELECT id FROM gl_accounts WHERE code = '4000' LIMIT 1) revenue_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '1200')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '4000')
ON CONFLICT DO NOTHING;

-- Insert posting rule for vendor bill creation
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event,
    debit_account_id, credit_account_id, amount_field,
    description_template, is_active
)
SELECT 
    'Vendor Bill Created', 'ap', 'bill_created',
    expense_account.id, ap_account.id, 'amount',
    'Bill #{source_id} - {vendor_name}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '5000' LIMIT 1) expense_account,
    (SELECT id FROM gl_accounts WHERE code = '2100' LIMIT 1) ap_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '5000')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '2100')
ON CONFLICT DO NOTHING;

-- Insert posting rule for vendor bill payments
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event,
    debit_account_id, credit_account_id, amount_field,
    description_template, is_active
)
SELECT 
    'Vendor Bill Payment', 'ap', 'payment_made',
    ap_account.id, cash_account.id, 'payment_amount',
    'Payment for Bill #{source_id}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '2100' LIMIT 1) ap_account,
    (SELECT id FROM gl_accounts WHERE code = '1100' LIMIT 1) cash_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '2100')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '1100')
ON CONFLICT DO NOTHING;

-- Insert posting rule for case expenses
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event,
    debit_account_id, credit_account_id, amount_field,
    description_template, is_active
)
SELECT 
    'Case Expense', 'cases', 'expense_recorded',
    legal_expense_account.id, ap_account.id, 'amount',
    'Case #{source_id} Expense - {description}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '5100' LIMIT 1) legal_expense_account,
    (SELECT id FROM gl_accounts WHERE code = '2100' LIMIT 1) ap_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '5100')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '2100')
ON CONFLICT DO NOTHING;

-- Insert posting rule for inventory purchases
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event,
    debit_account_id, credit_account_id, amount_field,
    description_template, is_active
)
SELECT 
    'Inventory Purchase', 'inventory', 'goods_received',
    inventory_account.id, ap_account.id, 'total_cost',
    'Inventory Purchase - PO #{source_id}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '1300' LIMIT 1) inventory_account,
    (SELECT id FROM gl_accounts WHERE code = '2100' LIMIT 1) ap_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '1300')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '2100')
ON CONFLICT DO NOTHING;

-- Insert posting rule for cost of goods sold
INSERT INTO gl_posting_rules (
    rule_name, source_module, trigger_event,
    debit_account_id, credit_account_id, amount_field,
    description_template, is_active
)
SELECT 
    'Cost of Goods Sold', 'pos', 'sale_with_inventory',
    cogs_account.id, inventory_account.id, 'cost_amount',
    'COGS for Sale #{source_id}', true
FROM 
    (SELECT id FROM gl_accounts WHERE code = '5200' LIMIT 1) cogs_account,
    (SELECT id FROM gl_accounts WHERE code = '1300' LIMIT 1) inventory_account
WHERE EXISTS (SELECT 1 FROM gl_accounts WHERE code = '5200')
  AND EXISTS (SELECT 1 FROM gl_accounts WHERE code = '1300')
ON CONFLICT DO NOTHING;