-- Phase 04.1: safe party statement chronological ordering
-- Idempotent version: adds event_at only if missing, backfills it, and adds the composite index if missing.

ALTER TABLE `party_ledger`
  ADD COLUMN IF NOT EXISTS `event_at` DATETIME NULL AFTER `doc_date`;

ALTER TABLE `party_ledger`
  ADD INDEX IF NOT EXISTS `idx_pl_party_event` (`party_id`, `doc_date`, `event_at`, `id`);

-- Sales invoices
UPDATE `party_ledger` pl
JOIN `sales_invoices` si
  ON pl.`doc_type` = 'SALES_INV' AND pl.`doc_id` = si.`id`
SET pl.`event_at` = COALESCE(pl.`event_at`, si.`created_at`, CONCAT(pl.`doc_date`, ' 00:00:00'))
WHERE pl.`event_at` IS NULL;

-- Customer receipts
UPDATE `party_ledger` pl
JOIN `customer_receipts` cr
  ON pl.`doc_type` = 'RCPT' AND pl.`doc_id` = cr.`id`
SET pl.`event_at` = COALESCE(pl.`event_at`, cr.`created_at`, CONCAT(pl.`doc_date`, ' 00:00:00'))
WHERE pl.`event_at` IS NULL;

-- Cash payments
UPDATE `party_ledger` pl
JOIN `cash_payments` cp
  ON pl.`doc_type` = 'PAY' AND pl.`doc_id` = cp.`id`
SET pl.`event_at` = COALESCE(pl.`event_at`, cp.`created_at`, CONCAT(pl.`doc_date`, ' 00:00:00'))
WHERE pl.`event_at` IS NULL;

-- Purchase invoices
UPDATE `party_ledger` pl
JOIN `purchase_invoices` pi
  ON pl.`doc_type` = 'PURCHASE_INV' AND pl.`doc_id` = pi.`id`
SET pl.`event_at` = COALESCE(pl.`event_at`, pi.`created_at`, CONCAT(pl.`doc_date`, ' 00:00:00'))
WHERE pl.`event_at` IS NULL;

-- Purchase returns
UPDATE `party_ledger` pl
JOIN `purchase_returns` pr
  ON pl.`doc_type` = 'PURCHASE_RET' AND pl.`doc_id` = pr.`id`
SET pl.`event_at` = COALESCE(pl.`event_at`, pr.`created_at`, CONCAT(pl.`doc_date`, ' 00:00:00'))
WHERE pl.`event_at` IS NULL;

-- Final fallback
UPDATE `party_ledger`
SET `event_at` = COALESCE(`event_at`, `created_at`, CONCAT(`doc_date`, ' 00:00:00'))
WHERE `event_at` IS NULL;