-- ============================================================
-- hqsMEDIKAL — Módulo Pacientes y Admisión
-- Migración: 039_patients.sql
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── Pacientes ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS patients (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    -- Número de historia clínica (HCL-YYYY-NNNNN)
    record_number       VARCHAR(20)     NOT NULL,
    -- Datos personales
    first_name          VARCHAR(80)     NOT NULL,
    last_name           VARCHAR(80)     NOT NULL,
    birth_date          DATE            NOT NULL,
    gender              ENUM('M','F','other') NOT NULL,
    -- Identificación (RUC/CI en EC, Cédula/Pasaporte en DO)
    id_type             ENUM('cedula','passport','rnc','other') NOT NULL DEFAULT 'cedula',
    id_number           VARCHAR(30)     NOT NULL,
    nationality         CHAR(2)         DEFAULT NULL COMMENT 'ISO 3166-1 alfa-2',
    -- Contacto
    phone               VARCHAR(20)     DEFAULT NULL,
    phone_alt           VARCHAR(20)     DEFAULT NULL,
    email               VARCHAR(150)    DEFAULT NULL,
    address             TEXT            DEFAULT NULL,
    city                VARCHAR(100)    DEFAULT NULL,
    province_state      VARCHAR(100)    DEFAULT NULL,
    -- Datos clínicos base
    blood_type          ENUM('A+','A-','B+','B-','AB+','AB-','O+','O-','unknown')
                        NOT NULL DEFAULT 'unknown',
    known_allergies     TEXT            DEFAULT NULL,
    -- Médico tratante principal y referidor
    primary_physician_id INT UNSIGNED   DEFAULT NULL,
    referring_source    VARCHAR(150)    DEFAULT NULL,
    -- Estado
    status              ENUM('active','inactive','deceased') NOT NULL DEFAULT 'active',
    notes               TEXT            DEFAULT NULL,
    photo               VARCHAR(255)    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_record    (company_id, record_number),
    UNIQUE KEY uq_id_number (company_id, id_type, id_number),
    INDEX idx_company       (company_id),
    INDEX idx_name          (last_name, first_name),
    INDEX idx_status        (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Registro único del paciente (maestro). Una persona = un paciente.';

-- ── Seguros / ARS del paciente ────────────────────────────
CREATE TABLE IF NOT EXISTS patient_insurance (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    patient_id          INT UNSIGNED    NOT NULL,
    -- Tipo de seguro
    insurance_type      ENUM('ars','private','iess','self','other') NOT NULL DEFAULT 'ars',
    insurance_name      VARCHAR(100)    NOT NULL COMMENT 'Nombre de la ARS/aseguradora',
    insurance_code      VARCHAR(20)     DEFAULT NULL COMMENT 'Código SISALRIL / IESS',
    -- Datos de afiliación
    affiliate_number    VARCHAR(50)     NOT NULL,
    plan_name           VARCHAR(100)    DEFAULT NULL,
    plan_type           VARCHAR(50)     DEFAULT NULL COMMENT 'Individual, Familiar, etc.',
    copay_amount        DECIMAL(10,2)   NOT NULL DEFAULT 0,
    copay_percentage    DECIMAL(5,2)    NOT NULL DEFAULT 0,
    -- Vigencia
    valid_from          DATE            DEFAULT NULL,
    valid_to            DATE            DEFAULT NULL,
    is_primary          TINYINT(1)      NOT NULL DEFAULT 1,
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    notes               VARCHAR(300)    DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_patient   (patient_id),
    INDEX idx_active    (is_active),
    CONSTRAINT fk_pi_patient FOREIGN KEY (patient_id)
        REFERENCES patients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contactos de emergencia ──────────────────────────────
CREATE TABLE IF NOT EXISTS patient_contacts (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    patient_id          INT UNSIGNED    NOT NULL,
    name                VARCHAR(180)    NOT NULL,
    relationship        VARCHAR(60)     NOT NULL COMMENT 'Madre, Padre, Cónyuge, Tutor...',
    phone               VARCHAR(20)     NOT NULL,
    phone_alt           VARCHAR(20)     DEFAULT NULL,
    email               VARCHAR(150)    DEFAULT NULL,
    is_legal_guardian   TINYINT(1)      NOT NULL DEFAULT 0,
    sort_order          TINYINT         NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    INDEX idx_patient   (patient_id),
    CONSTRAINT fk_pc_patient FOREIGN KEY (patient_id)
        REFERENCES patients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Episodios de atención (Admisiones) ────────────────────
CREATE TABLE IF NOT EXISTS admissions (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    patient_id          INT UNSIGNED    NOT NULL,
    admission_number    VARCHAR(20)     NOT NULL COMMENT 'ADM-YYYY-NNNNN',
    -- Tipo de atención
    admission_type      ENUM('consultation','diagnostic','therapy',
                             'emergency','surgery','telemedicine')
                        NOT NULL DEFAULT 'consultation',
    -- Fechas
    admitted_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    discharged_at       DATETIME        DEFAULT NULL,
    -- Personal
    attending_physician_id INT UNSIGNED DEFAULT NULL,
    triage_nurse_id     INT UNSIGNED    DEFAULT NULL,
    -- Motivo de consulta
    chief_complaint     TEXT            NOT NULL,
    -- Seguro activo en este episodio
    patient_insurance_id INT UNSIGNED   DEFAULT NULL,
    -- Estado
    status              ENUM('open','in_progress','pending_results',
                             'discharged','cancelled')
                        NOT NULL DEFAULT 'open',
    -- Facturación vinculada
    invoice_id          INT UNSIGNED    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_admission_number (company_id, admission_number),
    INDEX idx_company   (company_id),
    INDEX idx_patient   (patient_id),
    INDEX idx_status    (status),
    INDEX idx_date      (admitted_at),
    CONSTRAINT fk_adm_patient FOREIGN KEY (patient_id)
        REFERENCES patients(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Episodio de atención. Un paciente puede tener múltiples episodios.';

SET FOREIGN_KEY_CHECKS = 1;