-- Free-text "note" fields for capturing extra description on credit transactions:
--   * sales.note          — terms when goods go out on credit (give on credit)
--   * expenses.note        — terms when items are bought on credit (purchase on credit)
--   * feed_purchases.note  — terms when feed is bought on credit
-- v_expenses_all is rebuilt to carry `note` (from expenses / feed_purchases; NULL
-- for payroll rows) so the note shows on the unified Expenses list too.
-- Safe/idempotent: each column add is guarded, the view is CREATE OR REPLACE.

-- ---- sales ------------------------------------------------------------------
SET @has := (SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name = 'sales' AND column_name = 'note');
SET @sql := IF(@has = 0, 'ALTER TABLE sales ADD COLUMN note VARCHAR(255) NULL AFTER customer_address', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ---- expenses ---------------------------------------------------------------
SET @has2 := (SELECT COUNT(*) FROM information_schema.columns
              WHERE table_schema = DATABASE() AND table_name = 'expenses' AND column_name = 'note');
SET @sql2 := IF(@has2 = 0, 'ALTER TABLE expenses ADD COLUMN note VARCHAR(255) NULL AFTER items', 'SELECT 1');
PREPARE s2 FROM @sql2; EXECUTE s2; DEALLOCATE PREPARE s2;

-- ---- feed_purchases ---------------------------------------------------------
SET @has3 := (SELECT COUNT(*) FROM information_schema.columns
              WHERE table_schema = DATABASE() AND table_name = 'feed_purchases' AND column_name = 'note');
SET @sql3 := IF(@has3 = 0, 'ALTER TABLE feed_purchases ADD COLUMN note VARCHAR(255) NULL AFTER supplier', 'SELECT 1');
PREPARE s3 FROM @sql3; EXECUTE s3; DEALLOCATE PREPARE s3;

-- ---- v_expenses_all (now carries note) --------------------------------------
CREATE OR REPLACE VIEW v_expenses_all AS
  SELECT id, farm_id, ref_no, category, payee, items, note, 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,
         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,
         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;