-- ===========================================================================
-- Migration: settle feed bought on credit
-- Date: 2026-07-27
--
-- Adds feed_purchases.settled_on so a credit feed purchase can be paid off
-- later (clearing its Accounts Payable in the ledger), and rebuilds
-- v_expenses_all so feed rows expose their real id (for the "Mark paid" action)
-- and read as paid once settled. schema.sql already carries both for fresh
-- installs. Idempotent — safe to re-run.
-- ===========================================================================

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

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 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,
         line_cost AS amount,
         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' AND settled_on IS NULL THEN 'pending' ELSE 'paid' END AS status,
         purchased_on AS spent_on, 'feed' AS source
    FROM feed_purchases;