-- ============================================================================
--  Remove Blue Central demo data from a running database.
--
--  Safe to run multiple times. Only deletes rows tied to the demo entity
--  (client code 'BCH', report code 'BCH-REV-SUMMARY'). Leaves every other
--  client / report / user / setting untouched.
--
--  The application *code* for the demo dashboard (admin/report-view.php,
--  includes/report-engine.php, report_columns/report_outputs schema) is
--  fully retained — so any new report created via Reports → Report builder
--  will render in the same designed format.
--
--  Apply:  mysql -u root -p medmecs_mis < clean-demo-data.sql
-- ============================================================================
USE `medmecs_mis`;

SET @r := (SELECT id FROM reports WHERE code = 'BCH-REV-SUMMARY' LIMIT 1);
SET @p := (SELECT id FROM processes WHERE code = 'REV-MONTHLY'    LIMIT 1);
SET @c := (SELECT id FROM clients   WHERE code = 'BCH'            LIMIT 1);

-- Children of the demo report (cascade-safe order)
DELETE FROM `generated_outputs`   WHERE report_id = @r;
DELETE FROM `report_data`         WHERE report_id = @r;
DELETE FROM `report_data_archive` WHERE report_id = @r;
DELETE FROM `upload_errors`       WHERE upload_id IN (SELECT id FROM report_uploads WHERE report_id = @r);
DELETE FROM `report_uploads`      WHERE report_id = @r;
DELETE FROM `report_outputs`      WHERE report_id = @r;
DELETE FROM `report_columns`      WHERE report_id = @r;
DELETE FROM `report_access`       WHERE report_id = @r;

-- The report itself
DELETE FROM `reports`             WHERE id = @r;

-- The demo process (Monthly Revenue Summary) — only if still tied to BCH
DELETE FROM `processes`           WHERE id = @p AND client_id = @c;

-- The demo client + its branding
DELETE FROM `client_branding`     WHERE client_id = @c;
DELETE FROM `clients`             WHERE id = @c;

-- Drop any client-portal users linked only to the demo client
DELETE FROM `user_roles`          WHERE user_id IN (SELECT id FROM users WHERE user_type = 'client' AND client_id IS NULL);
DELETE FROM `users`               WHERE user_type = 'client' AND client_id = @c;

-- Free storage of any demo upload artifacts in storage/uploads/<id> remains —
-- safe to delete manually after this script.

SELECT 'Demo data cleanup complete.' AS message,
       (SELECT COUNT(*) FROM reports)  AS reports_remaining,
       (SELECT COUNT(*) FROM clients)  AS clients_remaining,
       (SELECT COUNT(*) FROM processes) AS processes_remaining;
