-- ============================================================================
--  Switch Expected Payment + Outstanding to primary-insurance-average logic.
--
--  NEW DEFINITIONS:
--    expected_payment[row] = (avg primary_ins_paid per unit for that CPT) × units
--                            where the average is taken over rows in which
--                            primary insurance actually paid (primary_ins_paid > 0).
--    outstanding[row]      = expected_payment - (primary_ins_paid
--                                              + secondary_ins_paid
--                                              + tertiary_ins_paid)
--
--  The fee_schedules table is no longer used for expected payment.
--
--  Apply once on an existing DB.  Idempotent.
-- ============================================================================
USE `medmecs_mis`;

/* 1. Add the `outstanding` column to live + stage tables if missing -------- */
DROP PROCEDURE IF EXISTS `_add_outstanding`;
DELIMITER //
CREATE PROCEDURE `_add_outstanding`(IN t VARCHAR(64)) BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                    WHERE table_schema = DATABASE() AND table_name = t AND column_name = 'outstanding') THEN
        SET @sql := CONCAT('ALTER TABLE `', t, '` ADD COLUMN `outstanding` DECIMAL(15,2) NULL AFTER `expected_payment`');
        PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;
    END IF;
END //
DELIMITER ;
CALL _add_outstanding('collection_raw');
CALL _add_outstanding('collection_raw_stage');
DROP PROCEDURE _add_outstanding;

/* 2. Expected payment = per-CPT average primary paid per unit × units --------
      Average is weighted: SUM(primary_ins_paid) / SUM(units) across rows where
      primary actually paid, computed independently per report_id + charge_code. */
UPDATE collection_raw cr
JOIN (
    SELECT report_id, charge_code,
           SUM(primary_ins_paid)
             / NULLIF(SUM(CASE WHEN primary_ins_paid > 0 THEN COALESCE(units,1) ELSE 0 END), 0) AS rate
    FROM collection_raw
    WHERE primary_ins_paid > 0
      AND charge_code IS NOT NULL AND charge_code <> ''
    GROUP BY report_id, charge_code
) r ON r.report_id = cr.report_id AND r.charge_code = cr.charge_code
SET cr.expected_payment = ROUND(r.rate * COALESCE(cr.units,1), 2);

/* CPTs with no primary-payment history get expected = 0 (can't infer a rate) */
UPDATE collection_raw SET expected_payment = 0 WHERE expected_payment IS NULL;

/* 3. Outstanding = expected payment - total paid (primary + secondary + tertiary) */
UPDATE collection_raw
SET outstanding = ROUND(COALESCE(expected_payment,0)
                        - (primary_ins_paid + secondary_ins_paid + tertiary_ins_paid), 2);

/* 4. Index to keep aging / outstanding queries fast --------------------------- */
DROP PROCEDURE IF EXISTS `_add_idx_out`;
DELIMITER //
CREATE PROCEDURE `_add_idx_out`() BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.statistics
                    WHERE table_schema = DATABASE() AND table_name = 'collection_raw'
                      AND index_name = 'idx_col_report_outstanding') THEN
        ALTER TABLE collection_raw ADD INDEX `idx_col_report_outstanding` (`report_id`,`outstanding`,`dos`);
    END IF;
END //
DELIMITER ;
CALL _add_idx_out();
DROP PROCEDURE _add_idx_out;

/* 5. Report ------------------------------------------------------------------- */
SELECT
  COUNT(*)                                         AS total_rows,
  SUM(CASE WHEN expected_payment > 0 THEN 1 ELSE 0 END) AS rows_with_expected,
  CONCAT('$', FORMAT(SUM(expected_payment),2))     AS total_expected,
  CONCAT('$', FORMAT(SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid),2)) AS total_paid,
  CONCAT('$', FORMAT(SUM(outstanding),2))          AS total_outstanding
FROM collection_raw;
