-- ============================================================================
-- NBO tenant — Migration 003: link compliance_filings → expenses
-- ============================================================================
-- Tracks which expense entry was auto-created when a statutory filing was
-- marked Paid. Used for:
--   * Idempotency (don't double-create if save_compliance_filing runs twice)
--   * Cleanup (if filing's paid status is rolled back, delete the linked expense)
-- ============================================================================

SET @x := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_SCHEMA=DATABASE()
             AND TABLE_NAME='compliance_filings'
             AND COLUMN_NAME='expense_id');
SET @s := IF(@x=0,
    'ALTER TABLE compliance_filings ADD COLUMN expense_id INT NULL DEFAULT NULL AFTER receipt_path, ADD INDEX idx_cf_expense (expense_id)',
    'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;