-- ============================================================================
--  MEDMECS — Collection Dashboard schema
--  Adds:  facility_mappings, collection_raw (+ staging),
--         collection_uploads, collection_supporting_uploads
--
--  Design notes:
--   - Atomic replace (old stays until new commits): use _stage tables; on
--     successful ingest, transactionally swap rows.
--   - Year/month derived from DOS, stored as INT for fast slicing.
--   - Facility name JOINs from facility_mappings on visit_id at ingest time
--     AND re-runs whenever facility_mappings is replaced.
--   - Expected Payment derived from each CPT's average primary-insurance payment.
--   - All amounts stored as DECIMAL(15,2) for accuracy.
--
--  Apply:  mysql -u root -p medmecs_mis < collection-dashboard.sql
-- ============================================================================

USE `medmecs_mis`;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- 1.  Facility mappings  (visit_id -> facility name + practice)
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `facility_mappings`;
CREATE TABLE `facility_mappings` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_id`  BIGINT UNSIGNED NOT NULL,     -- Multi-tenant: which Collection Dashboard owns this row
    `visit_id`   VARCHAR(64)  NOT NULL,        -- "Visit Number" from supporting CSV
    `facility`   VARCHAR(255) NULL,            -- "Facility"     from supporting CSV
    `entry_date` DATE         NULL,            -- "Entry Date"   from supporting CSV
    `upload_id`  BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_fac_map_visit`           (`visit_id`),
    KEY `idx_fac_map_upload`          (`upload_id`),
    KEY `idx_fac_map_report_visit`    (`report_id`,`visit_id`),
    KEY `idx_fac_map_report_facility` (`report_id`,`facility`),  /* covering: COUNT(DISTINCT facility) per report (was 134s) */
    KEY `idx_fac_map_report_id`       (`report_id`,`id`)         /* per-client newest-first browse at any scale */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- staging variant (same columns minus PK auto-fill; used during ingest)
DROP TABLE IF EXISTS `facility_mappings_stage`;
CREATE TABLE `facility_mappings_stage` LIKE `facility_mappings`;

-- ----------------------------------------------------------------------------
-- 2.  (Fee schedule removed 2026-05-12.  Expected Payment is now derived at
--     ingest from each CPT's average primary-insurance payment per unit, so
--     no fee-schedule table is needed.)
-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------
-- 3.  Collection raw  (43 columns from "Collection Raw.csv" + derived)
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `collection_raw`;
CREATE TABLE `collection_raw` (
    `id`                       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_id`                BIGINT UNSIGNED NOT NULL,    -- Multi-tenant
    `visit_id`                 VARCHAR(64)  NOT NULL,
    `chart_number`             VARCHAR(64)  NULL,
    `provider_name`            VARCHAR(255) NULL,
    `patient_last_name`        VARCHAR(120) NULL,
    `patient_first_name`       VARCHAR(120) NULL,
    `financial_class`          VARCHAR(16)  NULL,
    `carrier1_code`            VARCHAR(32)  NULL,
    `carrier1_name`            VARCHAR(255) NULL,
    `carrier2_code`            VARCHAR(32)  NULL,
    `carrier2_name`            VARCHAR(255) NULL,
    `carrier3_code`            VARCHAR(32)  NULL,
    `carrier3_name`            VARCHAR(255) NULL,
    `dos`                      DATE         NULL,
    `dos_year`                 SMALLINT UNSIGNED NULL,
    `dos_month`                TINYINT  UNSIGNED NULL,
    `charge_code_cat`          VARCHAR(32)  NULL,
    `charge_code`              VARCHAR(32)  NULL,
    `charge_code_desc`         VARCHAR(255) NULL,
    `units`                    DECIMAL(10,2) NULL,
    `charge_amount`            DECIMAL(15,2) NOT NULL DEFAULT 0,
    `amount_allowed`           DECIMAL(15,2) NULL,
    `primary_ins_paid`         DECIMAL(15,2) NOT NULL DEFAULT 0,
    `primary_ins_payment_date` DATE         NULL,
    `secondary_ins_paid`       DECIMAL(15,2) NOT NULL DEFAULT 0,
    `secondary_ins_payment_date` DATE       NULL,
    `tertiary_ins_paid`        DECIMAL(15,2) NOT NULL DEFAULT 0,
    `tertiary_ins_payment_date` DATE        NULL,
    `patient_payment_amount`   DECIMAL(15,2) NOT NULL DEFAULT 0,
    `patient_payment_date`     DATE         NULL,
    `wo_amount`                DECIMAL(15,2) NOT NULL DEFAULT 0,
    `wo_date`                  DATE         NULL,
    `patient_balance`          DECIMAL(15,2) NOT NULL DEFAULT 0,
    `insurance_balance`        DECIMAL(15,2) NOT NULL DEFAULT 0,
    `diagnosis_code1`          VARCHAR(32)  NULL,
    `diagnosis_code1_desc`     VARCHAR(255) NULL,
    `diagnosis_code2`          VARCHAR(32)  NULL,
    `diagnosis_code2_desc`     VARCHAR(255) NULL,
    `diagnosis_code3`          VARCHAR(32)  NULL,
    `diagnosis_code3_desc`     VARCHAR(255) NULL,
    `diagnosis_code4`          VARCHAR(32)  NULL,
    `diagnosis_code4_desc`     VARCHAR(255) NULL,
    `patient_zip`              VARCHAR(16)  NULL,
    `referring_provider`       VARCHAR(255) NULL,
    `appointment_date`         DATE         NULL,
    `appointment_types`        VARCHAR(120) NULL,
    -- DERIVED (populated at ingest, re-populated when mapping changes)
    `facility`                 VARCHAR(255) NULL,
    `entry_date`               DATE          NULL,   -- "Entry Date" mapped from facility_mappings (by visit_id)
    `expected_payment`         DECIMAL(15,2) NULL,   -- per-CPT avg primary paid/unit × units
    `outstanding`              DECIMAL(15,2) NULL,   -- DEPRECATED: dashboards now read insurance_balance + patient_balance directly. Column kept for safe rollback.
    `days_to_first_payment`    SMALLINT     NULL,
    `upload_id`                BIGINT UNSIGNED NULL,
    `created_at`               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_col_visit`              (`visit_id`),
    KEY `idx_col_dos`                (`dos`),
    KEY `idx_col_dos_ym`             (`dos_year`,`dos_month`),
    KEY `idx_col_facility`           (`facility`),
    KEY `idx_col_provider`           (`provider_name`),
    KEY `idx_col_charge_code`        (`charge_code`),
    KEY `idx_col_chart`              (`chart_number`),
    KEY `idx_col_upload`             (`upload_id`),
    /* Covering indexes for the dashboard's GROUP BY ... ORDER BY SUM(charge_amount) queries */
    KEY `idx_col_facility_charge`    (`facility`,`charge_amount`),
    KEY `idx_col_provider_charge`    (`provider_name`,`charge_amount`),
    KEY `idx_col_cpt_charge`         (`charge_code`,`charge_amount`),
    KEY `idx_col_carrier_charge`     (`carrier1_name`,`charge_amount`),
    KEY `idx_col_ym_charge`          (`dos_year`,`dos_month`,`charge_amount`),
    KEY `idx_col_aging`              (`insurance_balance`,`dos`),
    /* For COUNT(DISTINCT chart_number) PER facility/provider (two-pass strategy) */
    KEY `idx_col_facility_chart`     (`facility`,`chart_number`),
    KEY `idx_col_provider_chart`     (`provider_name`,`chart_number`),
    KEY `idx_col_uniqpt`             (`facility`,`provider_name`,`dos_year`,`dos_month`,`chart_number`),
    /* Multi-tenant covering indexes (every dashboard query starts with report_id) */
    KEY `idx_col_report_facility_charge` (`report_id`,`facility`,`charge_amount`),
    KEY `idx_col_report_provider_charge` (`report_id`,`provider_name`,`charge_amount`),
    KEY `idx_col_report_cpt_charge`      (`report_id`,`charge_code`,`charge_amount`),
    KEY `idx_col_report_carrier_charge`  (`report_id`,`carrier1_name`,`charge_amount`),
    KEY `idx_col_report_ym_charge`       (`report_id`,`dos_year`,`dos_month`,`charge_amount`),
    KEY `idx_col_report_uniqpt`          (`report_id`,`facility`,`provider_name`,`dos_year`,`dos_month`,`chart_number`),
    KEY `idx_col_report_dos`             (`report_id`,`dos`),
    KEY `idx_col_report_visit`           (`report_id`,`visit_id`),
    KEY `idx_col_report_chart`           (`report_id`,`chart_number`),   /* fast COUNT(DISTINCT chart_number) per report */
    KEY `idx_col_report_outstanding`     (`report_id`,`outstanding`,`dos`)  /* aging / outstanding queries */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `collection_raw_stage`;
CREATE TABLE `collection_raw_stage` LIKE `collection_raw`;

-- ----------------------------------------------------------------------------
-- 4.  Upload metadata (separate from generic report_uploads since this is a
--     dedicated, opinionated pipeline)
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `collection_uploads`;
CREATE TABLE `collection_uploads` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_id`     BIGINT UNSIGNED NULL,            -- Multi-tenant (NULL only on legacy rows)
    `kind`          ENUM('collection','facility_mapping','fee_schedule') NOT NULL,
    `file_name`     VARCHAR(255) NOT NULL,
    `file_path`     VARCHAR(500) NULL,
    `file_size`     BIGINT UNSIGNED NULL,
    `file_hash`     CHAR(64) NULL,
    `year`          SMALLINT UNSIGNED NULL COMMENT 'For fee_schedule uploads',
    `total_rows`    INT UNSIGNED NULL DEFAULT 0,
    `valid_rows`    INT UNSIGNED NULL DEFAULT 0,
    `error_rows`    INT UNSIGNED NULL DEFAULT 0,
    `status`        ENUM('processing','completed','failed','staged') NOT NULL DEFAULT 'processing',
    `uploaded_by`   BIGINT UNSIGNED NULL,
    `note`          VARCHAR(500) NULL,
    `meta_json`     TEXT NULL,                 -- cached derived KPI counts (e.g. unique visits/facilities) computed once at ingest
    `started_at`    TIMESTAMP NULL DEFAULT NULL,
    `completed_at`  TIMESTAMP NULL DEFAULT NULL,
    `created_at`    TIMESTAMP NULL DEFAULT NULL,
    `updated_at`    TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_col_uploads_kind_status` (`kind`,`status`),
    KEY `idx_col_uploads_created`     (`created_at`),
    KEY `idx_col_uploads_report_kind` (`report_id`,`kind`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------------------------
-- 4.  Collection stats cache — pre-computed dashboard aggregates per upload.
--     Populated automatically by CollectionEngine::computeAndCacheStats() at
--     the end of every successful ingest. Lets the dashboard load in <100ms
--     instead of re-aggregating 200k+ rows on every page hit.
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `collection_stats`;
CREATE TABLE `collection_stats` (
    `upload_id`    BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    `snapshot`     LONGTEXT NOT NULL,                              -- JSON of all aggregates
    `computed_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_cs_upload` FOREIGN KEY (`upload_id`)
        REFERENCES `collection_uploads`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------------
-- 5. Seed the live customer entity: Billing › PY-Medical SNF › Monthly
--    Collection Summary › Collection Dashboard.  Idempotent.
-- ----------------------------------------------------------------------------
INSERT INTO `verticals` (`name`,`code`,`description`,`status`,`created_at`,`updated_at`) VALUES
  ('Billing','BILLING','Revenue cycle billing operations','active',NOW(),NOW())
ON DUPLICATE KEY UPDATE `updated_at`=NOW();

INSERT INTO `clients` (`vertical_id`,`name`,`code`,`spoc_name`,`spoc_email`,`timezone`,`status`,`created_at`,`updated_at`)
  SELECT v.id,'PY-Medical SNF','PYMED-SNF','Operations SPOC','spoc@pymedical.example','America/New_York','active',NOW(),NOW()
  FROM `verticals` v WHERE v.code='BILLING'
ON DUPLICATE KEY UPDATE `updated_at`=NOW();

INSERT INTO `client_branding` (`client_id`,`primary_color`,`login_slug`,`login_message`,`show_historical_to_client`,`allow_client_upload`,`allow_raw_data`,`updated_at`)
  SELECT c.id,'#0e7490','py-medical-snf','Welcome to your PY-Medical SNF reporting portal',1,0,0,NOW()
  FROM `clients` c WHERE c.code='PYMED-SNF'
ON DUPLICATE KEY UPDATE `updated_at`=NOW();

INSERT INTO `processes` (`client_id`,`name`,`code`,`description`,`status`,`created_at`,`updated_at`)
  SELECT c.id,'Monthly Collection Summary','COLLECTION','Detailed collection analytics — facility/provider/CPT/carrier rollups + aging + velocity','active',NOW(),NOW()
  FROM `clients` c WHERE c.code='PYMED-SNF'
ON DUPLICATE KEY UPDATE `updated_at`=NOW();

INSERT INTO `reports`
  (`process_id`,`name`,`code`,`description`,`report_type`,`handler`,`upload_strategy`,`period_column`,`unique_key_columns`,`status`,`created_at`,`updated_at`)
SELECT p.id,'Collection Dashboard','COLL-DASH',
  'Detailed Collection Dashboard — atomic-swap ingest, visit_id facility mapping, primary-insurance-average expected payment, 11 analytical tabs.',
  'standard','collection_dashboard','replace_all','DOS',
  JSON_ARRAY('Visit ID','DOS','Charge Code'),
  'active',NOW(),NOW()
FROM `processes` p
JOIN `clients` c ON c.id = p.client_id
WHERE p.code='COLLECTION' AND c.code='PYMED-SNF'
ON DUPLICATE KEY UPDATE handler='collection_dashboard', `updated_at`=NOW();

-- ----------------------------------------------------------------------------
-- 6. Convenience views
-- ----------------------------------------------------------------------------
CREATE OR REPLACE VIEW `v_collection_summary_ym` AS
SELECT  dos_year,
        dos_month,
        COUNT(*)                                              AS claims,
        COUNT(DISTINCT chart_number)                          AS unique_patients,
        SUM(charge_amount)                                    AS total_charge,
        SUM(amount_allowed)                                   AS total_allowed,
        SUM(primary_ins_paid)                                 AS total_pri_paid,
        SUM(secondary_ins_paid)                               AS total_sec_paid,
        SUM(tertiary_ins_paid)                                AS total_ter_paid,
        SUM(primary_ins_paid + secondary_ins_paid + tertiary_ins_paid) AS total_ins_paid,
        SUM(patient_payment_amount)                           AS total_pat_paid,
        SUM(insurance_balance)                                AS total_ins_balance,
        SUM(patient_balance)                                  AS total_pat_balance,
        SUM(wo_amount)                                        AS total_writeoff,
        SUM(expected_payment)                                 AS total_expected
FROM collection_raw
WHERE dos_year IS NOT NULL
GROUP BY dos_year, dos_month;
