-- ---------------------------------------------------------------------------
-- Migration: feed purchases can now be paid (cash), given on credit (an
-- unpaid liability), or donated (received free of charge) — not just cash,
-- which is all v_expenses_all previously assumed for every feed_purchases
-- row. Also adds an optional invoice/receipt number per purchase.
-- ---------------------------------------------------------------------------
-- schema.sql already carries this for FRESH installs. Run this ONCE against
-- an EXISTING database (paste into phpMyAdmin's SQL tab, or
-- `mysql -u <user> -p <db> < sql/migration-2026-07-feed-purchase-payment-status.sql`).
-- Every ALTER is guarded so this is safely re-runnable from any partial state
-- (see migration-2026-07-managed-lists-farm-scoped.sql for why that matters —
-- MySQL DDL auto-commits per statement and can't be rolled back as a group).
-- ---------------------------------------------------------------------------

SET @check = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'feed_purchases' AND COLUMN_NAME = 'payment_status');
SET @ddl = IF(@check = 0,
  'ALTER TABLE feed_purchases ADD COLUMN payment_status ENUM(''paid'',''credit'',''donated'') NOT NULL DEFAULT ''paid'' AFTER line_cost',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @check = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'feed_purchases' AND COLUMN_NAME = 'invoice_no');
SET @ddl = IF(@check = 0,
  'ALTER TABLE feed_purchases ADD COLUMN invoice_no VARCHAR(60) NULL AFTER payment_status',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- CREATE OR REPLACE is idempotent on its own — safe to always re-run.
-- 'credit' maps to 'pending' so an unpaid feed delivery now shows up under the
-- Dashboard/Expenses page's existing "Unpaid" tracking, the same way a manual
-- expense marked 'pending' already does. 'donated' has nothing owed, so it's
-- 'paid' (with a real amount of 0 — see FeedController::purchase()). The
-- entered invoice/receipt number is used as the expense's ref when present,
-- falling back to the synthetic FP-{id} exactly as before when it's blank.
CREATE OR REPLACE VIEW v_expenses_all AS
  SELECT id, farm_id, ref_no, category, payee, items, amount, 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, NULL AS items,
         net AS amount, 'Bank transfer' AS method, 'paid' AS status,
         run_on AS spent_on, 'payroll' AS source
    FROM payroll_runs
  UNION ALL
  SELECT NULL 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,
         line_cost AS amount,
         CASE payment_status WHEN 'credit' THEN 'Credit' WHEN 'donated' THEN 'Donated' ELSE 'Cash' END AS method,
         CASE payment_status WHEN 'credit' THEN 'pending' ELSE 'paid' END AS status,
         purchased_on AS spent_on, 'feed' AS source
    FROM feed_purchases;