-- ============================================================================
-- CLINICA CORE SCHEMA - ERP Clinicas
-- Tablas principales del sistema clinico
-- ============================================================================
-- Fecha: 2026-01-13
-- Version: 1.0
-- Modulos: CL-002 (Pacientes), CL-003 (Citas), CL-004 (Consultas)
-- ============================================================================

-- Crear schema si no existe
CREATE SCHEMA IF NOT EXISTS clinica;

-- ============================================================================
-- ENUMS
-- ============================================================================

CREATE TYPE clinica.patient_status AS ENUM (
    'active',
    'inactive',
    'deceased'
);

CREATE TYPE clinica.gender AS ENUM (
    'male',
    'female',
    'other',
    'unknown'
);

CREATE TYPE clinica.blood_type AS ENUM (
    'A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-', 'unknown'
);

CREATE TYPE clinica.appointment_status AS ENUM (
    'scheduled',
    'confirmed',
    'in_progress',
    'completed',
    'cancelled',
    'no_show'
);

CREATE TYPE clinica.consultation_status AS ENUM (
    'in_progress',
    'completed',
    'cancelled'
);

CREATE TYPE clinica.prescription_status AS ENUM (
    'active',
    'completed',
    'cancelled'
);

-- ============================================================================
-- TABLAS: ESPECIALIDADES Y MEDICOS (CL-002)
-- ============================================================================

-- Catalogo de especialidades medicas
CREATE TABLE IF NOT EXISTS clinica.specialties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    requires_referral BOOLEAN DEFAULT false,
    consultation_duration_minutes INTEGER DEFAULT 30,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT uq_specialty_code UNIQUE (tenant_id, code)
);

COMMENT ON TABLE clinica.specialties IS 'Catalogo de especialidades medicas - CL-002';

-- Medicos/Doctores
CREATE TABLE IF NOT EXISTS clinica.doctors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    employee_id UUID, -- FK a hr.employees opcional
    -- Datos personales
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
    -- Datos profesionales
    professional_license VARCHAR(50) NOT NULL, -- Cedula profesional
    specialty_license VARCHAR(50), -- Cedula de especialidad
    specialty_id UUID NOT NULL,
    -- Configuracion
    consultation_duration_minutes INTEGER DEFAULT 30,
    max_appointments_per_day INTEGER DEFAULT 20,
    accepts_insurance BOOLEAN DEFAULT true,
    -- Control
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,
    CONSTRAINT fk_doctor_specialty FOREIGN KEY (specialty_id) REFERENCES clinica.specialties(id)
);

COMMENT ON TABLE clinica.doctors IS 'Registro de medicos y especialistas - CL-002';
COMMENT ON COLUMN clinica.doctors.professional_license IS 'Cedula profesional obligatoria';

-- ============================================================================
-- TABLAS: PACIENTES (CL-002)
-- ============================================================================

-- Pacientes
CREATE TABLE IF NOT EXISTS clinica.patients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    partner_id UUID, -- FK a core.partners opcional (para facturacion)
    -- Datos personales
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    gender clinica.gender DEFAULT 'unknown',
    -- Identificacion
    curp VARCHAR(18),
    rfc VARCHAR(13),
    -- Contacto
    email VARCHAR(255),
    phone VARCHAR(20),
    mobile VARCHAR(20),
    address JSONB, -- {street, city, state, zip, country}
    -- Datos medicos basicos
    blood_type clinica.blood_type DEFAULT 'unknown',
    allergies TEXT[],
    chronic_conditions TEXT[],
    emergency_contact_name VARCHAR(200),
    emergency_contact_phone VARCHAR(20),
    -- Seguro medico
    has_insurance BOOLEAN DEFAULT false,
    insurance_provider VARCHAR(100),
    insurance_policy_number VARCHAR(50),
    -- Control
    status clinica.patient_status DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

COMMENT ON TABLE clinica.patients IS 'Registro de pacientes - CL-002';
COMMENT ON COLUMN clinica.patients.allergies IS 'Lista de alergias conocidas';
COMMENT ON COLUMN clinica.patients.chronic_conditions IS 'Condiciones cronicas conocidas';

-- ============================================================================
-- TABLAS: CITAS (CL-003)
-- ============================================================================

-- Horarios disponibles por medico
CREATE TABLE IF NOT EXISTS clinica.appointment_slots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    doctor_id UUID NOT NULL,
    -- Horario
    day_of_week INTEGER NOT NULL CHECK (day_of_week BETWEEN 0 AND 6), -- 0=domingo
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    slot_duration_minutes INTEGER DEFAULT 30,
    -- Control
    active BOOLEAN DEFAULT true,
    valid_from DATE DEFAULT CURRENT_DATE,
    valid_until DATE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_slot_doctor FOREIGN KEY (doctor_id) REFERENCES clinica.doctors(id) ON DELETE CASCADE,
    CONSTRAINT chk_valid_time_range CHECK (end_time > start_time)
);

COMMENT ON TABLE clinica.appointment_slots IS 'Horarios disponibles de medicos - CL-003';

-- Citas medicas
CREATE TABLE IF NOT EXISTS clinica.appointments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    -- Relaciones
    patient_id UUID NOT NULL,
    doctor_id UUID NOT NULL,
    -- Programacion
    scheduled_date DATE NOT NULL,
    scheduled_time TIME NOT NULL,
    duration_minutes INTEGER DEFAULT 30,
    -- Estado
    status clinica.appointment_status DEFAULT 'scheduled',
    -- Detalles
    reason TEXT,
    notes TEXT,
    -- Confirmacion
    confirmed_at TIMESTAMPTZ,
    confirmed_by UUID,
    -- Cancelacion
    cancelled_at TIMESTAMPTZ,
    cancelled_by UUID,
    cancellation_reason TEXT,
    -- Cita pasada
    check_in_at TIMESTAMPTZ,
    check_out_at TIMESTAMPTZ,
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    CONSTRAINT fk_appointment_patient FOREIGN KEY (patient_id) REFERENCES clinica.patients(id),
    CONSTRAINT fk_appointment_doctor FOREIGN KEY (doctor_id) REFERENCES clinica.doctors(id)
);

COMMENT ON TABLE clinica.appointments IS 'Citas medicas programadas - CL-003';

-- ============================================================================
-- TABLAS: CONSULTAS Y EXPEDIENTE (CL-004)
-- ============================================================================

-- Consultas medicas
CREATE TABLE IF NOT EXISTS clinica.consultations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    -- Relaciones
    patient_id UUID NOT NULL,
    doctor_id UUID NOT NULL,
    appointment_id UUID, -- puede ser consulta sin cita
    -- Tiempo
    started_at TIMESTAMPTZ DEFAULT NOW(),
    ended_at TIMESTAMPTZ,
    -- Contenido clinico
    chief_complaint TEXT, -- Motivo de consulta
    present_illness TEXT, -- Padecimiento actual
    physical_examination TEXT, -- Exploracion fisica
    assessment TEXT, -- Valoracion/Impresion diagnostica
    plan TEXT, -- Plan de tratamiento
    -- Signos vitales (snapshot)
    vital_signs JSONB, -- {weight_kg, height_cm, temperature, blood_pressure, heart_rate, respiratory_rate, oxygen_saturation}
    -- Estado
    status clinica.consultation_status DEFAULT 'in_progress',
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_consultation_patient FOREIGN KEY (patient_id) REFERENCES clinica.patients(id),
    CONSTRAINT fk_consultation_doctor FOREIGN KEY (doctor_id) REFERENCES clinica.doctors(id),
    CONSTRAINT fk_consultation_appointment FOREIGN KEY (appointment_id) REFERENCES clinica.appointments(id)
);

COMMENT ON TABLE clinica.consultations IS 'Consultas medicas - CL-004';
COMMENT ON COLUMN clinica.consultations.chief_complaint IS 'Motivo de consulta principal';
COMMENT ON COLUMN clinica.consultations.vital_signs IS 'Signos vitales en formato JSON';

-- Diagnosticos (CIE-10)
CREATE TABLE IF NOT EXISTS clinica.diagnoses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    consultation_id UUID NOT NULL,
    -- Diagnostico
    icd10_code VARCHAR(10), -- Codigo CIE-10
    description TEXT NOT NULL,
    is_primary BOOLEAN DEFAULT false,
    diagnosis_type VARCHAR(20) DEFAULT 'definitive', -- 'presumptive', 'definitive', 'differential'
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_diagnosis_consultation FOREIGN KEY (consultation_id) REFERENCES clinica.consultations(id) ON DELETE CASCADE
);

COMMENT ON TABLE clinica.diagnoses IS 'Diagnosticos con codificacion CIE-10 - CL-004';

-- Recetas/Prescripciones
CREATE TABLE IF NOT EXISTS clinica.prescriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    consultation_id UUID NOT NULL,
    patient_id UUID NOT NULL,
    doctor_id UUID NOT NULL,
    -- Datos de la receta
    prescription_number VARCHAR(50),
    prescription_date DATE DEFAULT CURRENT_DATE,
    -- Estado
    status clinica.prescription_status DEFAULT 'active',
    -- Notas
    instructions TEXT,
    notes TEXT,
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_prescription_consultation FOREIGN KEY (consultation_id) REFERENCES clinica.consultations(id),
    CONSTRAINT fk_prescription_patient FOREIGN KEY (patient_id) REFERENCES clinica.patients(id),
    CONSTRAINT fk_prescription_doctor FOREIGN KEY (doctor_id) REFERENCES clinica.doctors(id)
);

COMMENT ON TABLE clinica.prescriptions IS 'Recetas medicas - CL-004';

-- Items de receta (medicamentos)
CREATE TABLE IF NOT EXISTS clinica.prescription_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    prescription_id UUID NOT NULL,
    -- Medicamento
    medication_name VARCHAR(200) NOT NULL,
    medication_code VARCHAR(50), -- codigo interno o externo
    -- Dosificacion
    dosage VARCHAR(100) NOT NULL, -- "500mg"
    frequency VARCHAR(100) NOT NULL, -- "cada 8 horas"
    duration VARCHAR(100), -- "7 dias"
    quantity INTEGER,
    -- Instrucciones
    instructions TEXT,
    -- Control
    sequence INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_prescription_item FOREIGN KEY (prescription_id) REFERENCES clinica.prescriptions(id) ON DELETE CASCADE
);

COMMENT ON TABLE clinica.prescription_items IS 'Medicamentos en receta - CL-004';

-- Signos vitales (historial)
CREATE TABLE IF NOT EXISTS clinica.vital_signs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    patient_id UUID NOT NULL,
    consultation_id UUID,
    recorded_by UUID,
    -- Mediciones
    weight_kg NUMERIC(5,2),
    height_cm NUMERIC(5,2),
    temperature_celsius NUMERIC(4,1),
    blood_pressure_systolic INTEGER,
    blood_pressure_diastolic INTEGER,
    heart_rate INTEGER, -- latidos por minuto
    respiratory_rate INTEGER, -- respiraciones por minuto
    oxygen_saturation INTEGER, -- porcentaje
    -- Extras
    glucose_mg_dl NUMERIC(5,1),
    notes TEXT,
    -- Control
    recorded_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT fk_vitals_patient FOREIGN KEY (patient_id) REFERENCES clinica.patients(id),
    CONSTRAINT fk_vitals_consultation FOREIGN KEY (consultation_id) REFERENCES clinica.consultations(id)
);

COMMENT ON TABLE clinica.vital_signs IS 'Historial de signos vitales - CL-004';

-- ============================================================================
-- INDICES
-- ============================================================================

-- Specialties
CREATE INDEX IF NOT EXISTS idx_specialties_tenant ON clinica.specialties(tenant_id);
CREATE INDEX IF NOT EXISTS idx_specialties_active ON clinica.specialties(tenant_id, active);

-- Doctors
CREATE INDEX IF NOT EXISTS idx_doctors_tenant ON clinica.doctors(tenant_id);
CREATE INDEX IF NOT EXISTS idx_doctors_specialty ON clinica.doctors(specialty_id);
CREATE INDEX IF NOT EXISTS idx_doctors_license ON clinica.doctors(professional_license);
CREATE INDEX IF NOT EXISTS idx_doctors_active ON clinica.doctors(tenant_id, active) WHERE deleted_at IS NULL;

-- Patients
CREATE INDEX IF NOT EXISTS idx_patients_tenant ON clinica.patients(tenant_id);
CREATE INDEX IF NOT EXISTS idx_patients_name ON clinica.patients(tenant_id, last_name, first_name);
CREATE INDEX IF NOT EXISTS idx_patients_curp ON clinica.patients(curp) WHERE curp IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_patients_phone ON clinica.patients(tenant_id, mobile);
CREATE INDEX IF NOT EXISTS idx_patients_status ON clinica.patients(tenant_id, status) WHERE deleted_at IS NULL;

-- Appointment Slots
CREATE INDEX IF NOT EXISTS idx_slots_tenant ON clinica.appointment_slots(tenant_id);
CREATE INDEX IF NOT EXISTS idx_slots_doctor ON clinica.appointment_slots(doctor_id);
CREATE INDEX IF NOT EXISTS idx_slots_day ON clinica.appointment_slots(tenant_id, day_of_week, active);

-- Appointments
CREATE INDEX IF NOT EXISTS idx_appointments_tenant ON clinica.appointments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_appointments_patient ON clinica.appointments(patient_id);
CREATE INDEX IF NOT EXISTS idx_appointments_doctor ON clinica.appointments(doctor_id);
CREATE INDEX IF NOT EXISTS idx_appointments_date ON clinica.appointments(tenant_id, scheduled_date);
CREATE INDEX IF NOT EXISTS idx_appointments_status ON clinica.appointments(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_appointments_doctor_date ON clinica.appointments(doctor_id, scheduled_date);

-- Consultations
CREATE INDEX IF NOT EXISTS idx_consultations_tenant ON clinica.consultations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_consultations_patient ON clinica.consultations(patient_id);
CREATE INDEX IF NOT EXISTS idx_consultations_doctor ON clinica.consultations(doctor_id);
CREATE INDEX IF NOT EXISTS idx_consultations_date ON clinica.consultations(tenant_id, started_at);

-- Diagnoses
CREATE INDEX IF NOT EXISTS idx_diagnoses_tenant ON clinica.diagnoses(tenant_id);
CREATE INDEX IF NOT EXISTS idx_diagnoses_consultation ON clinica.diagnoses(consultation_id);
CREATE INDEX IF NOT EXISTS idx_diagnoses_icd10 ON clinica.diagnoses(icd10_code) WHERE icd10_code IS NOT NULL;

-- Prescriptions
CREATE INDEX IF NOT EXISTS idx_prescriptions_tenant ON clinica.prescriptions(tenant_id);
CREATE INDEX IF NOT EXISTS idx_prescriptions_consultation ON clinica.prescriptions(consultation_id);
CREATE INDEX IF NOT EXISTS idx_prescriptions_patient ON clinica.prescriptions(patient_id);
CREATE INDEX IF NOT EXISTS idx_prescriptions_date ON clinica.prescriptions(tenant_id, prescription_date);

-- Prescription Items
CREATE INDEX IF NOT EXISTS idx_prescription_items_prescription ON clinica.prescription_items(prescription_id);

-- Vital Signs
CREATE INDEX IF NOT EXISTS idx_vitals_tenant ON clinica.vital_signs(tenant_id);
CREATE INDEX IF NOT EXISTS idx_vitals_patient ON clinica.vital_signs(patient_id);
CREATE INDEX IF NOT EXISTS idx_vitals_date ON clinica.vital_signs(patient_id, recorded_at DESC);

-- ============================================================================
-- ROW LEVEL SECURITY
-- ============================================================================

ALTER TABLE clinica.specialties ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.doctors ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.patients ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.appointment_slots ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.appointments ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.consultations ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.diagnoses ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.prescriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.prescription_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.vital_signs ENABLE ROW LEVEL SECURITY;

-- Politicas de aislamiento por tenant
CREATE POLICY tenant_isolation_specialties ON clinica.specialties
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_doctors ON clinica.doctors
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_patients ON clinica.patients
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_slots ON clinica.appointment_slots
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_appointments ON clinica.appointments
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_consultations ON clinica.consultations
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_diagnoses ON clinica.diagnoses
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_prescriptions ON clinica.prescriptions
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_prescription_items ON clinica.prescription_items
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

CREATE POLICY tenant_isolation_vitals ON clinica.vital_signs
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================================================
-- FIN CLINICA CORE SCHEMA
-- ============================================================================