-- ============================================================ -- hqsMEDIKAL — Historia Clínica Electrónica (HCE) -- Migración: 041_clinical_records.sql -- Soporte especializado en Neurología / Neurofisiología -- ============================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ── 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; SET FOREIGN_KEY_CHECKS = 1;