-- Let a cash expense record WHICH asset/tender account its cash was drawn from,
-- now that a farm can have several (Cash on Hand, Bank, Mobile Money, plus custom
-- cash floats). Without this the ledger could only route by payment method to the
-- single role-tagged account, so two different tills were indistinguishable.
--   * expenses.cash_account_id         — the account a directly-PAID expense drew from
--   * expense_payments.cash_account_id — the account a later settlement drew from
--   * v_expenses_all carries cash_account_id (NULL for payroll/feed rows)
-- Plain nullable columns (no FK): the controller validates farm-scope + that the
-- account is a cash account (an FK can't do the farm check), and the ledger
-- captures the account at post time so later chart edits can't corrupt history.
-- Safe/idempotent: guarded column adds + CREATE OR REPLACE view.

-- ---- expenses ---------------------------------------------------------------
SET @has := (SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name = 'expenses' AND column_name = 'cash_account_id');
SET @sql := IF(@has = 0, 'ALTER TABLE expenses ADD COLUMN cash_account_id INT UNSIGNED NULL AFTER method', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- expense_payments -------------------------------------------------------
SET @has2 := (SELECT COUNT(*) FROM information_schema.columns
              WHERE table_schema = DATABASE() AND table_name = 'expense_payments' AND column_name = 'cash_account_id');
SET @sql2 := IF(@has2 = 0, 'ALTER TABLE expense_payments ADD COLUMN cash_account_id INT UNSIGNED NULL AFTER method', 'SELECT 1');
PREPARE s2 FROM @sql2; EXECUTE s2; DEALLOCATE PREPARE s2;

-- ---- v_expenses_all (now carries cash_account_id) ---------------------------
CREATE OR REPLACE VIEW v_expenses_all AS
  SELECT id, farm_id, ref_no, category, payee, items, note, cash_account_id, amount, amount_paid, method, status, spent_on, 'manual' AS source
    FROM expenses
  UNION ALL
  SELECT NULL AS id, farm_id, ref_no, 'Payroll' AS category,
         CONCAT('Staff wages - ', period) AS payee, item AS items, NULL AS note, NULL AS cash_account_id,
         net AS amount, net AS amount_paid, COALESCE(NULLIF(method, ''), 'Bank transfer') AS method, 'paid' AS status,
         run_on AS spent_on, 'payroll' AS source
    FROM payroll_runs
  UNION ALL
  SELECT id AS id, farm_id,
         COALESCE(NULLIF(invoice_no, ''), CONCAT('FP-', id)) AS ref_no,
         'Feed' AS category,
         IF(supplier IS NOT NULL AND supplier <> '', supplier, CONCAT(feed_type, ' purchase')) AS payee,
         CONCAT(qty_kg, 'kg ', feed_type) AS items, note, NULL AS cash_account_id,
         line_cost AS amount,
         CASE WHEN payment_status IN ('paid','donated') THEN line_cost ELSE amount_paid END AS amount_paid,
         CASE WHEN payment_status = 'donated' THEN 'Donated'
              WHEN payment_status = 'credit' AND settled_on IS NULL THEN 'Credit'
              ELSE 'Cash' END AS method,
         CASE WHEN payment_status <> 'credit' THEN 'paid'
              WHEN amount_paid >= line_cost THEN 'paid'
              WHEN amount_paid > 0 THEN 'partial'
              ELSE 'pending' END AS status,
         purchased_on AS spent_on, 'feed' AS source
    FROM feed_purchases;