-- ============================================================
-- hqsMEDIKAL — Estudios Diagnósticos Neurofisiológicos
-- Migración: 042_diagnostic_studies.sql
-- El módulo diferenciador crítico del sistema
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── Catálogo de tipos de estudio ─────────────────────────
CREATE TABLE IF NOT EXISTS diagnostic_study_types (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    code                VARCHAR(30)     NOT NULL COMMENT 'EEG-STD, EMG-VCN, PESS, VEEG-8H...',
    name                VARCHAR(200)    NOT NULL,
    category            ENUM('eeg','emg','evoked_potentials','emt',
                             'mnio','imaging','other')
                        NOT NULL DEFAULT 'other',
    -- Descripción clínica
    full_name           VARCHAR(300)    DEFAULT NULL,
    duration_min        SMALLINT        DEFAULT NULL COMMENT 'Duración estimada del estudio',
    preparation_instructions TEXT       DEFAULT NULL COMMENT 'Instrucciones al paciente',
    -- Recurso necesario
    required_resource_type VARCHAR(50)  DEFAULT NULL COMMENT 'eeg_room, emg_room, emt_room',
    -- Facturación
    base_price          DECIMAL(10,2)   NOT NULL DEFAULT 0,
    ars_code            VARCHAR(50)     DEFAULT NULL COMMENT 'Código en el tarifario ARS/SISALRIL',
    requires_ars_auth   TINYINT(1)      NOT NULL DEFAULT 0
        COMMENT 'Si requiere autorización previa de la ARS',
    -- Estado
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    sort_order          SMALLINT        NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_company_code (company_id, code),
    INDEX idx_company   (company_id),
    INDEX idx_category  (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tipos de estudio estándar (se insertan al configurar la empresa)
-- Se insertan con company_id = 0 como plantilla global
INSERT IGNORE INTO diagnostic_study_types
    (company_id,code,name,category,duration_min,requires_ars_auth,is_active,sort_order)
VALUES
(0,'EEG-STD',   'EEG Estándar (20 min)',                 'eeg',   30, 0, 1, 10),
(0,'EEG-2H',    'EEG Ambulatorio 2 horas',               'eeg',  135, 1, 1, 11),
(0,'EEG-4H',    'EEG Ambulatorio 4 horas',               'eeg',  255, 1, 1, 12),
(0,'EEG-8H',    'EEG Ambulatorio 8 horas',               'eeg',  495, 1, 1, 13),
(0,'VEEG',      'Video-EEG (monitoreo prolongado)',       'eeg',  480, 1, 1, 14),
(0,'EMG-VCN',   'EMG con Velocidad de Conducción Nerviosa','emg',  60, 0, 1, 20),
(0,'EMG-SIMPLE','EMG Simple',                            'emg',   45, 0, 1, 21),
(0,'PESS',      'Potenciales Evocados Somatosensoriales', 'evoked_potentials', 60, 1, 1, 30),
(0,'PEV',       'Potenciales Evocados Visuales',         'evoked_potentials', 45, 1, 1, 31),
(0,'PEATC',     'Potenciales Auditivos de Tronco Cerebral','evoked_potentials',45, 1, 1, 32),
(0,'PECOG',     'Potenciales Cognitivos P300',           'evoked_potentials', 60, 1, 1, 33),
(0,'EMT-EVAL',  'Evaluación con Estimulación Magnética Transcraneal','emt', 60, 1, 1, 40),
(0,'MNIO',      'Monitorización Neurofisiológica Intraoperatoria','mnio', 240, 1, 1, 50);

-- ── Equipos diagnósticos del centro ──────────────────────
CREATE TABLE IF NOT EXISTS diagnostic_equipment (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    resource_id         INT UNSIGNED    DEFAULT NULL COMMENT 'Sala donde está el equipo',
    brand               VARCHAR(100)    NOT NULL COMMENT 'Nihon Kohden, Natus, Micromed, Cadwell...',
    model               VARCHAR(100)    NOT NULL,
    serial_number       VARCHAR(100)    DEFAULT NULL,
    asset_tag           VARCHAR(50)     DEFAULT NULL,
    channels            SMALLINT        DEFAULT NULL COMMENT 'Canales EEG',
    software_version    VARCHAR(50)     DEFAULT NULL,
    -- Mantenimiento
    last_calibration    DATE            DEFAULT NULL,
    next_calibration    DATE            DEFAULT NULL,
    last_maintenance    DATE            DEFAULT NULL,
    -- Compatible con tipos de estudio
    compatible_studies  VARCHAR(500)    DEFAULT NULL COMMENT 'Códigos separados por coma',
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    notes               TEXT            DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_company   (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Órdenes de estudio diagnóstico ───────────────────────
CREATE TABLE IF NOT EXISTS diagnostic_orders (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    order_number        VARCHAR(20)     NOT NULL COMMENT 'DX-YYYY-NNNNN',
    -- Origen
    patient_id          INT UNSIGNED    NOT NULL,
    record_id           INT UNSIGNED    DEFAULT NULL COMMENT 'Vinculado a HCE',
    admission_id        INT UNSIGNED    DEFAULT NULL,
    -- Estudio solicitado
    study_type_id       INT UNSIGNED    NOT NULL,
    -- Médico solicitante y ejecutor
    ordering_physician_id INT UNSIGNED  NOT NULL,
    performing_technician_id INT UNSIGNED DEFAULT NULL,
    reporting_physician_id INT UNSIGNED  DEFAULT NULL,
    -- Justificación clínica
    clinical_indication TEXT            NOT NULL,
    priority            ENUM('routine','urgent','emergency')
                        NOT NULL DEFAULT 'routine',
    -- ARS / Autorización previa
    patient_insurance_id INT UNSIGNED   DEFAULT NULL,
    ars_auth_required   TINYINT(1)      NOT NULL DEFAULT 0,
    ars_auth_number     VARCHAR(100)    DEFAULT NULL,
    ars_auth_status     ENUM('not_required','pending','approved','rejected','expired')
                        NOT NULL DEFAULT 'not_required',
    ars_auth_requested_at DATETIME      DEFAULT NULL,
    ars_auth_received_at  DATETIME      DEFAULT NULL,
    ars_auth_expires_at   DATETIME      DEFAULT NULL,
    ars_rejection_reason  VARCHAR(300)  DEFAULT NULL,
    -- Programación
    scheduled_at        DATETIME        DEFAULT NULL,
    resource_id         INT UNSIGNED    DEFAULT NULL,
    -- Estado del flujo
    status              ENUM('ordered','pending_auth','authorized',
                             'scheduled','in_progress','report_pending',
                             'completed','delivered','cancelled')
                        NOT NULL DEFAULT 'ordered',
    -- Tiempos clave
    started_at          DATETIME        DEFAULT NULL,
    completed_at        DATETIME        DEFAULT NULL,
    delivered_at        DATETIME        DEFAULT NULL,
    -- Facturación
    invoice_id          INT UNSIGNED    DEFAULT NULL,
    -- Instrucciones especiales para el paciente
    patient_instructions TEXT           DEFAULT NULL,
    notes               TEXT            DEFAULT NULL,
    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_order_number (company_id, order_number),
    INDEX idx_company   (company_id),
    INDEX idx_patient   (patient_id),
    INDEX idx_record    (record_id),
    INDEX idx_study     (study_type_id),
    INDEX idx_status    (status),
    INDEX idx_scheduled (scheduled_at),
    CONSTRAINT fk_do_patient FOREIGN KEY (patient_id)
        REFERENCES patients(id) ON DELETE RESTRICT,
    CONSTRAINT fk_do_study FOREIGN KEY (study_type_id)
        REFERENCES diagnostic_study_types(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Orden de estudio diagnóstico. Flujo: Médico → Orden → Técnico → Informe → Entrega.';

-- ── Resultados / Informes de estudios ────────────────────
CREATE TABLE IF NOT EXISTS diagnostic_results (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    order_id            INT UNSIGNED    NOT NULL,
    -- Informe
    findings_text       TEXT            NOT NULL COMMENT 'Descripción de hallazgos',
    conclusion          TEXT            NOT NULL COMMENT 'Conclusión / diagnóstico neurofisiológico',
    recommendation      TEXT            DEFAULT NULL,
    -- Interpretación cuantitativa (para escalas neurológicas en estudios)
    quantitative_data   JSON            DEFAULT NULL
        COMMENT '{"alpha_dominant_freq":"9Hz","background":"normal",...}',
    -- Archivo adjunto (PDF del informe con firma del médico)
    report_file         VARCHAR(255)    DEFAULT NULL COMMENT 'Path al PDF del informe',
    report_file_size    INT             DEFAULT NULL,
    -- Comparativo con estudio previo
    previous_order_id   INT UNSIGNED    DEFAULT NULL COMMENT 'Para EEGs comparativos',
    comparison_notes    TEXT            DEFAULT NULL,
    -- Firmado por
    technician_id       INT UNSIGNED    DEFAULT NULL,
    reporting_physician_id INT UNSIGNED NOT NULL,
    physician_name_snap VARCHAR(180)    DEFAULT NULL,
    is_signed           TINYINT(1)      NOT NULL DEFAULT 0,
    signed_at           DATETIME        DEFAULT NULL,
    digital_signature   TEXT            DEFAULT NULL,
    -- Entrega
    delivered_to        VARCHAR(180)    DEFAULT NULL COMMENT 'Nombre de quien recibió',
    delivered_at        DATETIME        DEFAULT NULL,
    delivery_method     ENUM('printed','email','portal','in_person')
                        DEFAULT 'in_person',
    -- Revisión / Corrección
    revision_number     TINYINT         NOT NULL DEFAULT 1,
    is_amendment        TINYINT(1)      NOT NULL DEFAULT 0,
    amendment_reason    VARCHAR(300)    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_order     (order_id),
    INDEX idx_physician (reporting_physician_id),
    CONSTRAINT fk_dr_order FOREIGN KEY (order_id)
        REFERENCES diagnostic_orders(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Terapias neurológicas ────────────────────────────────
CREATE TABLE IF NOT EXISTS therapy_types (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    code                VARCHAR(30)     NOT NULL,
    name                VARCHAR(200)    NOT NULL,
    category            ENUM('neurological_rehab','cognitive_therapy',
                             'emt','botox','language_therapy',
                             'occupational_therapy','other')
                        NOT NULL DEFAULT 'neurological_rehab',
    default_sessions    TINYINT         DEFAULT NULL,
    session_duration_min SMALLINT       NOT NULL DEFAULT 60,
    base_price_per_session DECIMAL(10,2) NOT NULL DEFAULT 0,
    base_price_per_package DECIMAL(10,2) DEFAULT NULL,
    requires_prescription TINYINT(1)    NOT NULL DEFAULT 1,
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_company_code (company_id, code),
    INDEX idx_company   (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS therapy_plans (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    patient_id          INT UNSIGNED    NOT NULL,
    record_id           INT UNSIGNED    DEFAULT NULL,
    therapy_type_id     INT UNSIGNED    NOT NULL,
    plan_number         VARCHAR(20)     NOT NULL,
    -- Prescripción
    prescribing_physician_id INT UNSIGNED NOT NULL,
    therapist_id        INT UNSIGNED    DEFAULT NULL,
    sessions_prescribed TINYINT         NOT NULL DEFAULT 10,
    sessions_completed  TINYINT         NOT NULL DEFAULT 0,
    sessions_cancelled  TINYINT         NOT NULL DEFAULT 0,
    frequency_per_week  TINYINT         NOT NULL DEFAULT 3,
    goals               TEXT            DEFAULT NULL,
    -- Fechas
    start_date          DATE            NOT NULL,
    estimated_end_date  DATE            DEFAULT NULL,
    actual_end_date     DATE            DEFAULT NULL,
    -- Facturación
    billing_type        ENUM('per_session','per_package') NOT NULL DEFAULT 'per_session',
    package_invoice_id  INT UNSIGNED    DEFAULT NULL,
    -- Estado
    status              ENUM('active','completed','suspended','cancelled')
                        NOT NULL DEFAULT 'active',
    suspension_reason   VARCHAR(200)    DEFAULT NULL,
    notes               TEXT            DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_plan_number (company_id, plan_number),
    INDEX idx_company   (company_id),
    INDEX idx_patient   (patient_id),
    INDEX idx_status    (status),
    CONSTRAINT fk_tp_patient FOREIGN KEY (patient_id)
        REFERENCES patients(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS therapy_sessions (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    plan_id             INT UNSIGNED    NOT NULL,
    session_number      TINYINT         NOT NULL,
    scheduled_at        DATETIME        NOT NULL,
    started_at          DATETIME        DEFAULT NULL,
    ended_at            DATETIME        DEFAULT NULL,
    -- Asistencia
    attendance          ENUM('attended','absent','cancelled','rescheduled')
                        NOT NULL DEFAULT 'attended',
    absence_reason      VARCHAR(200)    DEFAULT NULL,
    -- Evolución clínica
    progress_note       TEXT            DEFAULT NULL,
    pain_scale          TINYINT         DEFAULT NULL COMMENT '0-10',
    functional_status   VARCHAR(200)    DEFAULT NULL,
    -- Datos específicos EMT
    emt_frequency_hz    DECIMAL(5,2)    DEFAULT NULL,
    emt_intensity_pct   DECIMAL(5,2)    DEFAULT NULL,
    emt_pulses          SMALLINT        DEFAULT NULL,
    emt_coil_position   VARCHAR(100)    DEFAULT NULL,
    -- Datos específicos Toxina Botulínica
    botox_units_applied DECIMAL(6,2)    DEFAULT NULL,
    botox_injection_sites TEXT          DEFAULT NULL,
    botox_lot_number    VARCHAR(50)     DEFAULT NULL,
    botox_next_dose_date DATE           DEFAULT NULL,
    -- Facturación
    invoice_id          INT UNSIGNED    DEFAULT NULL,
    therapist_id        INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_plan      (plan_id),
    INDEX idx_scheduled (scheduled_at),
    CONSTRAINT fk_ts_plan FOREIGN KEY (plan_id)
        REFERENCES therapy_plans(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
