-- Phase 02: Fix purchase returns in party statements + enrich ledger notes
-- Safe to run more than once.

-- 1) Backfill posted purchase returns into unified party_ledger if missing
INSERT INTO party_ledger (
    party_id,
    doc_type,
    doc_id,
    doc_no,
    doc_date,
    debit,
    credit,
    notes,
    created_by
)
SELECT
    COALESCE(pr.party_id, pr.supplier_id) AS party_id,
    'PURCHASE_RET' AS doc_type,
    pr.id AS doc_id,
    pr.ret_no AS doc_no,
    pr.ret_date AS doc_date,
    0 AS debit,
    COALESCE(pr.total, 0) AS credit,
    NULLIF(TRIM(COALESCE(pr.notes, '')), '') AS notes,
    COALESCE(pr.posted_by, pr.created_by, 1) AS created_by
FROM purchase_returns pr
WHERE UPPER(COALESCE(pr.status, '')) = 'POSTED'
  AND COALESCE(pr.party_id, pr.supplier_id) IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM party_ledger pl
      WHERE pl.doc_type = 'PURCHASE_RET'
        AND pl.doc_id = pr.id
  );

-- 2) Fill blank ledger notes from source document notes when possible
UPDATE party_ledger pl
LEFT JOIN sales_invoices si
       ON pl.doc_type = 'SALES_INV' AND si.id = pl.doc_id
LEFT JOIN customer_receipts cr
       ON pl.doc_type = 'RCPT' AND cr.id = pl.doc_id
LEFT JOIN cash_payments cp
       ON pl.doc_type = 'PAY' AND cp.id = pl.doc_id
LEFT JOIN purchase_invoices pi
       ON pl.doc_type = 'PURCHASE_INV' AND pi.id = pl.doc_id
LEFT JOIN purchase_returns pr
       ON pl.doc_type = 'PURCHASE_RET' AND pr.id = pl.doc_id
SET pl.notes = COALESCE(
    NULLIF(TRIM(pl.notes), ''),
    NULLIF(TRIM(si.notes), ''),
    NULLIF(TRIM(cr.notes), ''),
    NULLIF(TRIM(cp.notes), ''),
    NULLIF(TRIM(pi.notes), ''),
    NULLIF(TRIM(pr.notes), ''),
    pl.notes
)
WHERE COALESCE(TRIM(pl.notes), '') = ''
  AND pl.doc_type IN ('SALES_INV', 'RCPT', 'PAY', 'PURCHASE_INV', 'PURCHASE_RET');