-- ============================================================ -- hqsMEDIKAL — Agenda Médica Multidisciplinaria -- Migración: 040_appointments.sql -- ============================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ── Recursos médicos (salas, consultorios, equipos) ─────── CREATE TABLE IF NOT EXISTS medical_resources ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL COMMENT 'Sala EEG 1, Consultorio 3, Cabina EMT...', resource_type ENUM('consultation_room','eeg_room','emg_room', 'emt_room','therapy_room','operating_room', 'telemedicine','other') NOT NULL DEFAULT 'consultation_room', capacity TINYINT NOT NULL DEFAULT 1, location VARCHAR(100) DEFAULT NULL COMMENT 'Piso, ala, edificio', color VARCHAR(7) DEFAULT '#3B82F6' COMMENT 'Color en calendario', is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT NOT NULL DEFAULT 0, notes VARCHAR(300) DEFAULT NULL, PRIMARY KEY (id), INDEX idx_company (company_id), INDEX idx_type (resource_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Médicos / especialistas del centro ─────────────────── -- Extiende la tabla users con perfil médico CREATE TABLE IF NOT EXISTS medical_staff ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED DEFAULT NULL COMMENT 'Vinculado a users si tiene acceso', -- Datos profesionales first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, specialty VARCHAR(100) NOT NULL COMMENT 'Neurología, Neurofisiología, Psicología...', sub_specialty VARCHAR(100) DEFAULT NULL, license_number VARCHAR(50) DEFAULT NULL COMMENT 'Número colegio médico', license_expiry DATE DEFAULT NULL, -- Contacto y firma email VARCHAR(150) DEFAULT NULL, phone VARCHAR(20) DEFAULT NULL, digital_signature TEXT DEFAULT NULL COMMENT 'Firma base64 para informes', -- Honorarios consultation_fee DECIMAL(10,2) DEFAULT NULL, -- Estado is_active TINYINT(1) NOT NULL DEFAULT 1, color VARCHAR(7) DEFAULT '#1B3A6B', photo VARCHAR(255) DEFAULT NULL, notes TEXT DEFAULT NULL, PRIMARY KEY (id), INDEX idx_company (company_id), INDEX idx_user (user_id), INDEX idx_specialty (specialty) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Disponibilidad de médicos y recursos ───────────────── CREATE TABLE IF NOT EXISTS medical_schedules ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, -- A quién aplica: médico, recurso, o ambos staff_id INT UNSIGNED DEFAULT NULL, resource_id INT UNSIGNED DEFAULT NULL, -- Horario recurrente day_of_week TINYINT NOT NULL COMMENT '0=Dom 1=Lun ... 6=Sab', start_time TIME NOT NULL, end_time TIME NOT NULL, slot_duration_min SMALLINT NOT NULL DEFAULT 30 COMMENT 'Duración del slot en min', -- Vigencia valid_from DATE NOT NULL DEFAULT (CURRENT_DATE), valid_to DATE DEFAULT NULL, -- Excepción / bloqueo is_blocked TINYINT(1) NOT NULL DEFAULT 0, block_reason VARCHAR(200) DEFAULT NULL, PRIMARY KEY (id), INDEX idx_company (company_id), INDEX idx_staff (staff_id), INDEX idx_resource (resource_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Citas / Turnos ──────────────────────────────────────── CREATE TABLE IF NOT EXISTS appointments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, appointment_number VARCHAR(20) NOT NULL COMMENT 'APT-YYYY-NNNNN', -- Paciente (puede ser nuevo en el momento del agendamiento) patient_id INT UNSIGNED DEFAULT NULL, patient_name_snap VARCHAR(180) DEFAULT NULL COMMENT 'Snapshot nombre', patient_phone_snap VARCHAR(20) DEFAULT NULL, -- Médico y sala staff_id INT UNSIGNED NOT NULL, resource_id INT UNSIGNED DEFAULT NULL, -- Fecha y hora scheduled_at DATETIME NOT NULL, duration_min SMALLINT NOT NULL DEFAULT 30, -- Tipo de cita appointment_type ENUM('first_visit','follow_up','diagnostic_study', 'therapy_session','telemedicine','procedure', 'surgery_evaluation') NOT NULL DEFAULT 'first_visit', -- Motivo principal reason VARCHAR(300) NOT NULL, -- Estado status ENUM('scheduled','confirmed','checked_in', 'in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled', -- Tiempos reales arrived_at DATETIME DEFAULT NULL, started_at DATETIME DEFAULT NULL, ended_at DATETIME DEFAULT NULL, -- Vinculación admission_id INT UNSIGNED DEFAULT NULL, -- Origen del agendamiento booked_by ENUM('staff','patient_portal','phone','whatsapp','walk_in') NOT NULL DEFAULT 'staff', confirmation_token VARCHAR(64) DEFAULT NULL COMMENT 'Token para confirmación paciente', confirmed_at DATETIME DEFAULT NULL, cancellation_reason VARCHAR(200) 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_apt_number (company_id, appointment_number), INDEX idx_company (company_id), INDEX idx_patient (patient_id), INDEX idx_staff (staff_id), INDEX idx_resource (resource_id), INDEX idx_date (scheduled_at), INDEX idx_status (status), CONSTRAINT fk_apt_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Bloqueos de agenda específicos (días feriados, vacaciones) ─ CREATE TABLE IF NOT EXISTS schedule_blocks ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, staff_id INT UNSIGNED DEFAULT NULL, resource_id INT UNSIGNED DEFAULT NULL, block_date DATE NOT NULL, start_time TIME DEFAULT '00:00:00', end_time TIME DEFAULT '23:59:59', reason VARCHAR(200) NOT NULL, is_full_day TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), INDEX idx_company (company_id), INDEX idx_date (block_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;