-- One-time orphan scan/scrub for commission-related tables.
-- Review SELECT results before running DELETE statements in production.

-- 1) Orphan rows in user_commissions_ledger (missing user or rule)
SELECT l.id, l.user_id, l.rule_id
FROM user_commissions_ledger l
LEFT JOIN users u ON u.id = l.user_id
LEFT JOIN commission_rules r ON r.id = l.rule_id
WHERE u.id IS NULL OR r.id IS NULL;

-- Optional cleanup:
-- DELETE l
-- FROM user_commissions_ledger l
-- LEFT JOIN users u ON u.id = l.user_id
-- LEFT JOIN commission_rules r ON r.id = l.rule_id
-- WHERE u.id IS NULL OR r.id IS NULL;

-- 2) Orphan rows in deal_commissions (if FK tables/columns exist)
SELECT dc.id, dc.deal_id, dc.salesperson_user_id
FROM deal_commissions dc
LEFT JOIN users u ON u.id = dc.salesperson_user_id
LEFT JOIN deals d ON d.id = dc.deal_id
WHERE (dc.salesperson_user_id IS NOT NULL AND u.id IS NULL)
   OR (dc.deal_id IS NOT NULL AND d.id IS NULL);

-- Optional cleanup:
-- DELETE dc
-- FROM deal_commissions dc
-- LEFT JOIN users u ON u.id = dc.salesperson_user_id
-- LEFT JOIN deals d ON d.id = dc.deal_id
-- WHERE (dc.salesperson_user_id IS NOT NULL AND u.id IS NULL)
--    OR (dc.deal_id IS NOT NULL AND d.id IS NULL);
