-- ============================================================ -- 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;