-- ============================================================================
--  Multi-tenant migration for the Collection Dashboard.
--
--  Adds `report_id` to every collection table so the same Collection Dashboard
--  format can be used by multiple Vertical / Client / Process combinations
--  without data crossing between tenants.
--
--  Also adds a `handler` column to `reports` so the Reports browser can route
--  any future report to /admin/collection.php just by setting
--  reports.handler = 'collection_dashboard' (no more code-string matching).
--
--  Existing rows are backfilled with the current PY-Medical SNF report id
--  so nothing breaks.
--
--  Apply once.  Safe to re-run — guards everywhere.
-- ============================================================================
USE `medmecs_mis`;

SET FOREIGN_KEY_CHECKS = 0;

/* ------------------------------------------------------------------------
   Helper procedure to add a column only if missing
   ------------------------------------------------------------------------ */
DROP PROCEDURE IF EXISTS `_add_col`;
DELIMITER //
CREATE PROCEDURE `_add_col`(IN t VARCHAR(64), IN c VARCHAR(64), IN ddl TEXT) BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = DATABASE() AND table_name = t AND column_name = c) THEN
        SET @sql := CONCAT('ALTER TABLE `', t, '` ADD COLUMN ', ddl);
        PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
    END IF;
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS `_add_idx`;
DELIMITER //
CREATE PROCEDURE `_add_idx`(IN t VARCHAR(64), IN i VARCHAR(64), IN cols TEXT) BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.statistics
                    WHERE table_schema = DATABASE() AND table_name = t AND index_name = i) THEN
        SET @sql := CONCAT('ALTER TABLE `', t, '` ADD INDEX `', i, '` (', cols, ')');
        PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
    END IF;
END //
DELIMITER ;

/* ------------------------------------------------------------------------
   1.  reports.handler  — viewer routing column
   ------------------------------------------------------------------------ */
CALL _add_col('reports', 'handler', "`handler` VARCHAR(64) NULL COMMENT 'Viewer routing key. NULL = generic report-view.php. collection_dashboard = admin/collection.php' AFTER `report_type`");
UPDATE reports SET handler = 'collection_dashboard' WHERE code = 'COLL-DASH' AND handler IS NULL;
CALL _add_idx('reports', 'idx_reports_handler', '`handler`');

/* ------------------------------------------------------------------------
   2.  Capture the existing PY-Medical SNF report id for backfill
   ------------------------------------------------------------------------ */
SET @rpt := (SELECT id FROM reports WHERE code = 'COLL-DASH' LIMIT 1);

/* ------------------------------------------------------------------------
   3.  collection_raw / collection_raw_stage
   ------------------------------------------------------------------------ */
CALL _add_col('collection_raw',       'report_id', "`report_id` BIGINT UNSIGNED NULL AFTER `id`");
CALL _add_col('collection_raw_stage', 'report_id', "`report_id` BIGINT UNSIGNED NULL AFTER `id`");

UPDATE collection_raw       SET report_id = @rpt WHERE report_id IS NULL;
UPDATE collection_raw_stage SET report_id = @rpt WHERE report_id IS NULL;

/* Add the new leading-column covering indexes */
CALL _add_idx('collection_raw', 'idx_col_report_facility_charge',    '`report_id`,`facility`,`charge_amount`');
CALL _add_idx('collection_raw', 'idx_col_report_provider_charge',    '`report_id`,`provider_name`,`charge_amount`');
CALL _add_idx('collection_raw', 'idx_col_report_cpt_charge',         '`report_id`,`charge_code`,`charge_amount`');
CALL _add_idx('collection_raw', 'idx_col_report_carrier_charge',     '`report_id`,`carrier1_name`,`charge_amount`');
CALL _add_idx('collection_raw', 'idx_col_report_ym_charge',          '`report_id`,`dos_year`,`dos_month`,`charge_amount`');
CALL _add_idx('collection_raw', 'idx_col_report_uniqpt',             '`report_id`,`facility`,`provider_name`,`dos_year`,`dos_month`,`chart_number`');
CALL _add_idx('collection_raw', 'idx_col_report_dos',                '`report_id`,`dos`');
CALL _add_idx('collection_raw', 'idx_col_report_visit',              '`report_id`,`visit_id`');
CALL _add_idx('collection_raw', 'idx_col_report_chart',              '`report_id`,`chart_number`');

/* ------------------------------------------------------------------------
   4.  facility_mappings / facility_mappings_stage
   ------------------------------------------------------------------------ */
CALL _add_col('facility_mappings',       'report_id', "`report_id` BIGINT UNSIGNED NULL AFTER `id`");
CALL _add_col('facility_mappings_stage', 'report_id', "`report_id` BIGINT UNSIGNED NULL AFTER `id`");

UPDATE facility_mappings       SET report_id = @rpt WHERE report_id IS NULL;
UPDATE facility_mappings_stage SET report_id = @rpt WHERE report_id IS NULL;

CALL _add_idx('facility_mappings', 'idx_fac_map_report_visit', '`report_id`,`visit_id`');

/* ------------------------------------------------------------------------
   5.  fee_schedules / fee_schedules_stage
   ------------------------------------------------------------------------ */
CALL _add_col('fee_schedules',       'report_id', "`report_id` BIGINT UNSIGNED NULL AFTER `id`");
CALL _add_col('fee_schedules_stage', 'report_id', "`report_id` BIGINT UNSIGNED NULL AFTER `id`");

UPDATE fee_schedules       SET report_id = @rpt WHERE report_id IS NULL;
UPDATE fee_schedules_stage SET report_id = @rpt WHERE report_id IS NULL;

CALL _add_idx('fee_schedules', 'idx_fee_report_year_code', '`report_id`,`year`,`charge_code`');

/* ------------------------------------------------------------------------
   6.  collection_uploads
   ------------------------------------------------------------------------ */
CALL _add_col('collection_uploads', 'report_id', "`report_id` BIGINT UNSIGNED NULL AFTER `id`");
UPDATE collection_uploads SET report_id = @rpt WHERE report_id IS NULL;
CALL _add_idx('collection_uploads', 'idx_col_uploads_report_kind', '`report_id`,`kind`,`status`');

/* ------------------------------------------------------------------------
   7.  Cleanup
   ------------------------------------------------------------------------ */
DROP PROCEDURE _add_col;
DROP PROCEDURE _add_idx;

SET FOREIGN_KEY_CHECKS = 1;

/* ------------------------------------------------------------------------
   8.  Sanity report
   ------------------------------------------------------------------------ */
SELECT 'Multi-tenant migration complete.' AS status;

SELECT 'reports.handler'                AS column_name, COUNT(*) AS rows_with_value FROM reports WHERE handler IS NOT NULL
UNION ALL SELECT 'collection_raw.report_id',       COUNT(*) FROM collection_raw       WHERE report_id IS NOT NULL
UNION ALL SELECT 'facility_mappings.report_id',    COUNT(*) FROM facility_mappings    WHERE report_id IS NOT NULL
UNION ALL SELECT 'fee_schedules.report_id',        COUNT(*) FROM fee_schedules        WHERE report_id IS NOT NULL
UNION ALL SELECT 'collection_uploads.report_id',   COUNT(*) FROM collection_uploads   WHERE report_id IS NOT NULL;
