-- Payroll runs gain an optional `item` (description, e.g. "Monthly salaries")
-- and a `method` (how staff were paid: Cash / Bank transfer / Mobile Money).
-- Both flow into reports: `item` fills the Expenses view's items column (was
-- always NULL for payroll), and `method` replaces the hardcoded 'Bank transfer'
-- there AND routes the ledger credit to the right cash account.
--
-- Idempotent — safe to re-run.

ALTER TABLE payroll_runs ADD COLUMN IF NOT EXISTS item   VARCHAR(120) NULL AFTER period;
ALTER TABLE payroll_runs ADD COLUMN IF NOT EXISTS method VARCHAR(40)  NULL AFTER item;

-- Rebuild v_expenses_all so payroll exposes its item + method (rest unchanged).
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, item AS items,
         net AS amount, 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,
         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;