-- ============================================================
-- hqsMEDIKAL — Historia Clínica Electrónica (HCE)
-- Migración: 041_clinical_records.sql
-- Soporte especializado en Neurología / Neurofisiología
-- ============================================================

-- ── Expediente clínico (encabezado por episodio) ──────────
CREATE TABLE IF NOT EXISTS clinical_records (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    patient_id          INT UNSIGNED    NOT NULL,
    admission_id        INT UNSIGNED    DEFAULT NULL,
    -- Especialidad que genera este registro
    specialty           VARCHAR(100)    NOT NULL DEFAULT 'Neurología',
    -- Estado del expediente
    status              ENUM('open','completed','archived') NOT NULL DEFAULT 'open',
    -- Médico responsable
    physician_id        INT UNSIGNED    DEFAULT NULL,
    opened_at           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at           DATETIME        DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_company   (company_id),
    INDEX idx_patient   (patient_id),
    INDEX idx_admission (admission_id),
    CONSTRAINT fk_cr_patient FOREIGN KEY (patient_id)
        REFERENCES patients(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Notas clínicas (SOAP) ────────────────────────────────
CREATE TABLE IF NOT EXISTS clinical_notes (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    record_id           INT UNSIGNED    NOT NULL,
    note_type           ENUM('soap','evolution','discharge','referral',
                             'nursing','procedure','telemedicine')
                        NOT NULL DEFAULT 'soap',
    -- Formato SOAP
    subjective          TEXT            DEFAULT NULL COMMENT 'Motivo consulta, síntomas paciente',
    objective           TEXT            DEFAULT NULL COMMENT 'Examen físico, signos vitales',
    assessment          TEXT            DEFAULT NULL COMMENT 'Diagnóstico / impresión',
    plan                TEXT            DEFAULT NULL COMMENT 'Tratamiento, indicaciones, seguimiento',
    -- Para notas no-SOAP (evolución libre)
    free_text           TEXT            DEFAULT NULL,
    -- Signos vitales
    blood_pressure_sys  SMALLINT        DEFAULT NULL,
    blood_pressure_dia  SMALLINT        DEFAULT NULL,
    heart_rate          SMALLINT        DEFAULT NULL,
    respiratory_rate    SMALLINT        DEFAULT NULL,
    temperature         DECIMAL(4,1)    DEFAULT NULL,
    oxygen_saturation   DECIMAL(5,2)    DEFAULT NULL,
    weight_kg           DECIMAL(6,2)    DEFAULT NULL,
    height_cm           DECIMAL(5,1)    DEFAULT NULL,
    -- Auditoría
    author_id           INT UNSIGNED    NOT NULL,
    author_name_snap    VARCHAR(180)    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_signed           TINYINT(1)      NOT NULL DEFAULT 0,
    signed_at           DATETIME        DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_record    (record_id),
    INDEX idx_type      (note_type),
    CONSTRAINT fk_cn_record FOREIGN KEY (record_id)
        REFERENCES clinical_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Escalas clínicas neurológicas ────────────────────────
CREATE TABLE IF NOT EXISTS clinical_scales (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    record_id           INT UNSIGNED    NOT NULL,
    scale_type          ENUM(
        'MMSE',        -- Mini-Mental State Examination (cognitivo)
        'MoCA',        -- Montreal Cognitive Assessment
        'NIH_Stroke',  -- NIH Stroke Scale
        'EDSS',        -- Expanded Disability Status Scale (Esclerosis Múltiple)
        'UPDRS',       -- Unified Parkinson Disease Rating Scale
        'Rankin',      -- Escala de Rankin modificada
        'Glasgow',     -- Glasgow Coma Scale
        'NIHSS',       -- NIH Stroke Scale (alias)
        'Barthel',     -- Índice de Barthel (funcionalidad)
        'Berg',        -- Escala de Berg (equilibrio)
        'FAC',         -- Functional Ambulation Categories
        'other'
    ) NOT NULL,
    -- Resultado
    total_score         DECIMAL(6,2)    DEFAULT NULL,
    max_score           DECIMAL(6,2)    DEFAULT NULL,
    interpretation      VARCHAR(200)    DEFAULT NULL COMMENT 'Leve / Moderado / Severo...',
    -- Detalle sub-ítems (JSON flexible)
    sub_scores          JSON            DEFAULT NULL
        COMMENT '{"orientation":10,"memory":3,"attention":5,...}',
    -- Contexto
    notes               TEXT            DEFAULT NULL,
    evaluated_by        INT UNSIGNED    DEFAULT NULL,
    evaluated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_record    (record_id),
    INDEX idx_type      (scale_type),
    INDEX idx_date      (evaluated_at),
    CONSTRAINT fk_cs_record FOREIGN KEY (record_id)
        REFERENCES clinical_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Diagnósticos CIE-10 ──────────────────────────────────
CREATE TABLE IF NOT EXISTS clinical_diagnoses (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    record_id           INT UNSIGNED    NOT NULL,
    cie10_code          VARCHAR(10)     NOT NULL COMMENT 'Ej: G40.0 (epilepsia), G20 (Parkinson)',
    cie10_description   VARCHAR(250)    NOT NULL,
    diagnosis_type      ENUM('principal','secondary','rule_out','chronic')
                        NOT NULL DEFAULT 'principal',
    status              ENUM('active','resolved','chronic','controlled')
                        NOT NULL DEFAULT 'active',
    onset_date          DATE            DEFAULT NULL,
    resolved_date       DATE            DEFAULT NULL,
    confirmed_by        INT UNSIGNED    DEFAULT NULL,
    notes               VARCHAR(300)    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_record    (record_id),
    INDEX idx_cie10     (cie10_code),
    CONSTRAINT fk_cd_record FOREIGN KEY (record_id)
        REFERENCES clinical_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Medicamentos prescritos ──────────────────────────────
CREATE TABLE IF NOT EXISTS clinical_medications (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    record_id           INT UNSIGNED    NOT NULL,
    drug_name           VARCHAR(200)    NOT NULL,
    generic_name        VARCHAR(200)    DEFAULT NULL,
    dose                VARCHAR(50)     NOT NULL COMMENT 'Ej: 500mg, 10mg',
    route               ENUM('oral','iv','im','sc','topical','inhaled','rectal','other')
                        NOT NULL DEFAULT 'oral',
    frequency           VARCHAR(100)    NOT NULL COMMENT 'Cada 8h, 2 veces/día, en crisis',
    duration            VARCHAR(100)    DEFAULT NULL COMMENT 'Por 7 días, crónico, según evolución',
    indication          VARCHAR(200)    DEFAULT NULL COMMENT 'Para qué diagnóstico',
    special_instructions TEXT           DEFAULT NULL,
    -- Control anticonvulsivantes (neurología)
    is_anticonvulsant   TINYINT(1)      NOT NULL DEFAULT 0,
    seizure_free_since  DATE            DEFAULT NULL,
    -- Estado
    status              ENUM('active','discontinued','completed','on_hold')
                        NOT NULL DEFAULT 'active',
    start_date          DATE            NOT NULL DEFAULT (CURRENT_DATE),
    end_date            DATE            DEFAULT NULL,
    discontinued_reason VARCHAR(200)    DEFAULT NULL,
    prescribed_by       INT UNSIGNED    NOT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_record    (record_id),
    INDEX idx_status    (status),
    CONSTRAINT fk_cm_record FOREIGN KEY (record_id)
        REFERENCES clinical_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Historial de crisis epilépticas ──────────────────────
-- (campo diferenciador para neurología)
CREATE TABLE IF NOT EXISTS seizure_events (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    record_id           INT UNSIGNED    NOT NULL,
    event_date          DATETIME        NOT NULL,
    seizure_type        ENUM('focal','generalized_tonic_clonic','absence',
                             'myoclonic','atonic','unknown','other')
                        NOT NULL DEFAULT 'unknown',
    duration_seconds    SMALLINT        DEFAULT NULL,
    triggers            VARCHAR(300)    DEFAULT NULL COMMENT 'Fiebre, privación sueño, estrés...',
    postictal_state     VARCHAR(200)    DEFAULT NULL,
    -- Medicación en ese momento
    medication_at_event VARCHAR(200)    DEFAULT NULL,
    -- ¿Requirió hospitalización / urgencias?
    required_er         TINYINT(1)      NOT NULL DEFAULT 0,
    -- Observaciones
    witnessed_by        VARCHAR(100)    DEFAULT NULL,
    notes               TEXT            DEFAULT NULL,
    recorded_by         INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_record    (record_id),
    INDEX idx_date      (event_date),
    CONSTRAINT fk_se_record FOREIGN KEY (record_id)
        REFERENCES clinical_records(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Alergias confirmadas ──────────────────────────────────
CREATE TABLE IF NOT EXISTS patient_allergies (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    patient_id          INT UNSIGNED    NOT NULL,
    allergen            VARCHAR(200)    NOT NULL COMMENT 'Penicilina, AINEs, látex...',
    allergy_type        ENUM('drug','food','environmental','other')
                        NOT NULL DEFAULT 'drug',
    severity            ENUM('mild','moderate','severe','anaphylaxis')
                        NOT NULL DEFAULT 'moderate',
    reaction_description VARCHAR(300)  DEFAULT NULL,
    verified_at         DATE            DEFAULT NULL,
    verified_by         INT UNSIGNED    DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_patient   (patient_id),
    CONSTRAINT fk_pa_patient FOREIGN KEY (patient_id)
        REFERENCES patients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Consentimientos informados ───────────────────────────
CREATE TABLE IF NOT EXISTS informed_consents (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    patient_id          INT UNSIGNED    NOT NULL,
    admission_id        INT UNSIGNED    DEFAULT NULL,
    -- Tipo
    consent_type        VARCHAR(100)    NOT NULL COMMENT 'Tratamiento general, EEG, EMT, Cirugía...',
    -- Texto del consentimiento
    consent_text        TEXT            NOT NULL,
    -- Firma
    signed_by_patient   TINYINT(1)      NOT NULL DEFAULT 0,
    signed_by_guardian  TINYINT(1)      NOT NULL DEFAULT 0,
    guardian_name       VARCHAR(180)    DEFAULT NULL,
    guardian_relationship VARCHAR(60)   DEFAULT NULL,
    signature_data      TEXT            DEFAULT NULL COMMENT 'Base64 firma digital',
    signed_at           DATETIME        DEFAULT NULL,
    -- Archivo PDF generado
    pdf_path            VARCHAR(255)    DEFAULT NULL,
    -- Auditoría
    created_by          INT UNSIGNED    NOT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company   (company_id),
    INDEX idx_patient   (patient_id),
    INDEX idx_admission (admission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

