-- Phase 03: fix unified party ledger signs for purchases and purchase returns
-- Safe for demo/test databases. Rebuilds party_ledger entries for posted purchase invoices and posted purchase returns.

START TRANSACTION;

-- 1) Remove old unified ledger rows for purchase invoices / purchase returns
DELETE FROM party_ledger WHERE doc_type IN ('PURCHASE_INV', 'PURCHASE_RET');

-- 2) Rebuild purchase invoices: they increase what we owe the supplier => credit
INSERT INTO party_ledger (party_id, doc_type, doc_id, doc_no, doc_date, debit, credit, notes, created_by)
SELECT
  COALESCE(pi.party_id, pi.supplier_id) AS party_id,
  'PURCHASE_INV' AS doc_type,
  pi.id AS doc_id,
  pi.inv_no AS doc_no,
  pi.inv_date AS doc_date,
  0.0 AS debit,
  COALESCE(pi.total,0) AS credit,
  COALESCE(NULLIF(pi.notes,''), 'فاتورة شراء') AS notes,
  COALESCE(pi.posted_by, pi.created_by, 1) AS created_by
FROM purchase_invoices pi
WHERE UPPER(COALESCE(pi.status,'')) = 'POSTED'
  AND COALESCE(pi.party_id, pi.supplier_id) IS NOT NULL
  AND COALESCE(pi.party_id, pi.supplier_id) > 0;

-- 3) Rebuild purchase returns: they reduce what we owe the supplier => debit
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,
  COALESCE(pr.total,0) AS debit,
  0.0 AS credit,
  COALESCE(NULLIF(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 COALESCE(pr.party_id, pr.supplier_id) > 0;

COMMIT;