-- ===========================================================================
-- Migration: link billing invoices to the farm's Expenses module
-- Date: 2026-07-28
--
-- A farm paying its PFMIS subscription / SMS is, from THAT farm's books, an
-- expense. When a billing invoice is fully paid we now create a matching paid
-- row in the farm's `expenses` table (which auto-posts to the ledger, so it
-- shows in Expenses AND the Trial Balance / Balance Sheet). This column links
-- the invoice to the expense it produced and guards against double-creating it.
-- schema.sql carries this for fresh installs. Idempotent; safe to re-run.
--
-- No FK on purpose: if a farm later deletes that expense from its books, the id
-- is left dangling (harmless) rather than nulled — so the sync won't resurrect a
-- charge the farm deliberately removed.
-- ===========================================================================

SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'billing_invoices' AND COLUMN_NAME = 'expense_id');
SET @s = IF(@c = 0, 'ALTER TABLE billing_invoices ADD COLUMN expense_id INT UNSIGNED NULL AFTER paid_on', 'DO 0');
PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;