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