-- Deletes all sales (and everything that references those sales) for TEST_BRANCH (branch_id = 13).
-- Review the PREVIEW counts below before running the DELETE section. Run this whole file in
-- one session (phpMyAdmin "SQL" tab or mysql CLI) so the transaction stays open between steps.
--
-- Scope: sold_items rows for branch 13, plus every table that references those sales:
--   sale_payments            (per-sale payment splits, keyed by transaction_id + branch_id)
--   mra_eis_outbox            (EIS 'sale' submissions for this branch)
--   mra_eis_compliance_events (audit trail tied to those outbox rows)
--   mra_eis_sales_queue       (EIS sales queue entries for this branch)
--   mra_eis_sales_queue_lines (line items of those queue entries)
--   credit_accounts           (credit sales opened at this branch)
--   credit_sale_items         (line items of those credit sales, or linked by sale_id)
--   credit_payments           (payments recorded against those credit sales)
--
-- NOT touched (aggregate/rollup data, not per-sale records - would just look stale for this
-- branch/period, nothing to "clean up" per se): sales_reports, sales_targets, weekly_reports.

SET @branch_id = 13;

-- ============================================================
-- PREVIEW - run this section first and eyeball the counts
-- ============================================================
SELECT 'sold_items'                AS table_name, COUNT(*) AS row_count FROM sold_items                WHERE branch_id = @branch_id
UNION ALL
SELECT 'sale_payments',                    COUNT(*) FROM sale_payments               WHERE branch_id = @branch_id
UNION ALL
SELECT 'mra_eis_outbox (sale)',            COUNT(*) FROM mra_eis_outbox              WHERE entity_type = 'sale' AND branch_id = @branch_id
UNION ALL
SELECT 'mra_eis_compliance_events',        COUNT(*) FROM mra_eis_compliance_events   WHERE outbox_id IN (SELECT id FROM mra_eis_outbox WHERE entity_type = 'sale' AND branch_id = @branch_id)
UNION ALL
SELECT 'mra_eis_sales_queue',              COUNT(*) FROM mra_eis_sales_queue         WHERE branch_id = @branch_id
UNION ALL
SELECT 'mra_eis_sales_queue_lines',        COUNT(*) FROM mra_eis_sales_queue_lines   WHERE queue_id IN (SELECT id FROM mra_eis_sales_queue WHERE branch_id = @branch_id)
UNION ALL
SELECT 'credit_accounts',                  COUNT(*) FROM credit_accounts             WHERE branch_id = @branch_id
UNION ALL
SELECT 'credit_sale_items',                COUNT(*) FROM credit_sale_items           WHERE credit_id IN (SELECT credit_id FROM credit_accounts WHERE branch_id = @branch_id)
   OR sale_id IN (SELECT sale_id FROM sold_items WHERE branch_id = @branch_id)
UNION ALL
SELECT 'credit_payments',                  COUNT(*) FROM credit_payments             WHERE credit_id IN (SELECT credit_id FROM credit_accounts WHERE branch_id = @branch_id);

-- ============================================================
-- DELETE - only run this section after checking the preview above
-- ============================================================
START TRANSACTION;

DELETE FROM credit_payments
WHERE credit_id IN (SELECT credit_id FROM credit_accounts WHERE branch_id = @branch_id);

DELETE FROM credit_sale_items
WHERE credit_id IN (SELECT credit_id FROM credit_accounts WHERE branch_id = @branch_id)
   OR sale_id IN (SELECT sale_id FROM sold_items WHERE branch_id = @branch_id);

DELETE FROM credit_accounts
WHERE branch_id = @branch_id;

DELETE FROM mra_eis_sales_queue_lines
WHERE queue_id IN (SELECT id FROM mra_eis_sales_queue WHERE branch_id = @branch_id);

DELETE FROM mra_eis_sales_queue
WHERE branch_id = @branch_id;

DELETE FROM mra_eis_compliance_events
WHERE outbox_id IN (SELECT id FROM mra_eis_outbox WHERE entity_type = 'sale' AND branch_id = @branch_id);

DELETE FROM mra_eis_outbox
WHERE entity_type = 'sale' AND branch_id = @branch_id;

DELETE FROM sale_payments
WHERE branch_id = @branch_id;

DELETE FROM sold_items
WHERE branch_id = @branch_id;

-- Review the row counts MySQL reports for each DELETE above.
-- If everything looks right:
COMMIT;
-- If something looks wrong, run ROLLBACK; instead of COMMIT; above.
