-- Partial payments + reversal for expenses and feed-purchase (credit) settlements.
--   * expenses: amount_paid + a 'partial' status; a payment can be less than the
--     total, and each expense_payments row can be reversed.
--   * feed_purchases: amount_paid + a feed_purchase_payments table (mirrors
--     expense_payments) so a credit purchase can be settled in parts, each
--     reversible. Legacy full-settlements are backfilled as one payment row.
--   * v_expenses_all: exposes amount_paid and reports 'partial'.
-- Safe/idempotent: column adds are guarded; backfills and the CREATE/CREATE OR
-- REPLACE are re-runnable.

-- ---- expenses ---------------------------------------------------------------
SET @has := (SELECT COUNT(*) FROM information_schema.columns
             WHERE table_schema = DATABASE() AND table_name = 'expenses' AND column_name = 'amount_paid');
SET @sql := IF(@has = 0, 'ALTER TABLE expenses ADD COLUMN amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER amount', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

ALTER TABLE expenses MODIFY status ENUM('paid','pending','partial') NOT NULL DEFAULT 'paid';

-- a paid expense is fully paid; then reconcile any that already have payment rows
UPDATE expenses SET amount_paid = amount WHERE status = 'paid';
UPDATE expenses e
   SET amount_paid = (SELECT COALESCE(SUM(amount),0) FROM expense_payments p WHERE p.expense_id = e.id)
 WHERE EXISTS (SELECT 1 FROM expense_payments p WHERE p.expense_id = e.id);

-- ---- feed_purchases + settlements ------------------------------------------
SET @has2 := (SELECT COUNT(*) FROM information_schema.columns
              WHERE table_schema = DATABASE() AND table_name = 'feed_purchases' AND column_name = 'amount_paid');
SET @sql2 := IF(@has2 = 0, 'ALTER TABLE feed_purchases ADD COLUMN amount_paid DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER line_cost', 'SELECT 1');
PREPARE s2 FROM @sql2; EXECUTE s2; DEALLOCATE PREPARE s2;

CREATE TABLE IF NOT EXISTS feed_purchase_payments (
  id               INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id          INT UNSIGNED  NOT NULL,
  feed_purchase_id INT UNSIGNED  NOT NULL,
  amount           DECIMAL(14,2) NOT NULL DEFAULT 0,
  method           VARCHAR(40)   NOT NULL DEFAULT 'Cash',
  paid_on          DATE          NOT NULL,
  note             VARCHAR(255)  NULL,
  created_at       TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_fpp_farm (farm_id),
  KEY idx_fpp_purchase (feed_purchase_id),
  CONSTRAINT fk_fpp_farm     FOREIGN KEY (farm_id)          REFERENCES farms(id)          ON DELETE CASCADE,
  CONSTRAINT fk_fpp_purchase FOREIGN KEY (feed_purchase_id) REFERENCES feed_purchases(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- legacy fully-settled credit purchases: mark fully paid and record one payment
-- row (so they appear in the history and can be reversed). Its reversal undoes
-- the legacy 'feed_payment' ledger entry. Only backfill ones not already done.
UPDATE feed_purchases SET amount_paid = line_cost WHERE payment_status = 'credit' AND settled_on IS NOT NULL AND amount_paid = 0;
INSERT INTO feed_purchase_payments (farm_id, feed_purchase_id, amount, method, paid_on)
  SELECT fp.farm_id, fp.id, fp.line_cost, 'Cash', COALESCE(fp.settled_on, fp.purchased_on)
  FROM feed_purchases fp
  WHERE fp.payment_status = 'credit' AND fp.settled_on IS NOT NULL
    AND NOT EXISTS (SELECT 1 FROM feed_purchase_payments x WHERE x.feed_purchase_id = fp.id);

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