-- ============================================================================
--  Performance upgrade: add covering indexes to collection_raw.
--
--  Apply once on databases that pre-date the index additions. Safe to run
--  multiple times — uses CREATE INDEX IF NOT EXISTS semantics via stored
--  procedure (MySQL doesn't natively support IF NOT EXISTS on ALTER).
--
--  Expected speed-up: ~22x on the Collection Dashboard Overview tab
--  (from ~33s of aggregate queries to ~1.5s).
--
--  Apply:  mysql -u root -p medmecs_mis < upgrade-collection-indexes.sql
-- ============================================================================
USE `medmecs_mis`;

DROP PROCEDURE IF EXISTS `_add_index_if_missing`;
DELIMITER //
CREATE PROCEDURE `_add_index_if_missing`(
    IN tbl_name VARCHAR(64),
    IN idx_name VARCHAR(64),
    IN cols     VARCHAR(255)
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.statistics
         WHERE table_schema = DATABASE()
           AND table_name   = tbl_name
           AND index_name   = idx_name
    ) THEN
        SET @sql := CONCAT('ALTER TABLE `', tbl_name, '` ADD INDEX `', idx_name, '` (', cols, ')');
        PREPARE st FROM @sql;
        EXECUTE st;
        DEALLOCATE PREPARE st;
        SELECT CONCAT('Added index: ', idx_name) AS message;
    ELSE
        SELECT CONCAT('Skipped (exists): ', idx_name) AS message;
    END IF;
END//
DELIMITER ;

CALL _add_index_if_missing('collection_raw', 'idx_col_facility_charge',  '`facility`,`charge_amount`');
CALL _add_index_if_missing('collection_raw', 'idx_col_provider_charge',  '`provider_name`,`charge_amount`');
CALL _add_index_if_missing('collection_raw', 'idx_col_cpt_charge',       '`charge_code`,`charge_amount`');
CALL _add_index_if_missing('collection_raw', 'idx_col_carrier_charge',   '`carrier1_name`,`charge_amount`');
CALL _add_index_if_missing('collection_raw', 'idx_col_ym_charge',        '`dos_year`,`dos_month`,`charge_amount`');
CALL _add_index_if_missing('collection_raw', 'idx_col_aging',            '`insurance_balance`,`dos`');
CALL _add_index_if_missing('collection_raw', 'idx_col_facility_chart',   '`facility`,`chart_number`');
CALL _add_index_if_missing('collection_raw', 'idx_col_provider_chart',   '`provider_name`,`chart_number`');
CALL _add_index_if_missing('collection_raw', 'idx_col_uniqpt',           '`facility`,`provider_name`,`dos_year`,`dos_month`,`chart_number`');

DROP PROCEDURE _add_index_if_missing;

SELECT '✓ collection_raw indexes upgraded.' AS status,
       COUNT(*) AS total_indexes
FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'collection_raw';
