-- ============================================================================
-- TABLAS CLÍNICAS - ERP Clínicas
-- ============================================================================
-- Módulos: CL-001 (Pacientes), CL-002 (Citas), CL-003 (Expediente)
-- Versión: 1.0.0
-- Fecha: 2025-12-09
-- ============================================================================
-- PREREQUISITOS:
--   1. ERP-Core instalado (auth.tenants, auth.users, core.partners)
--   2. Schema clinica creado
-- ============================================================================

-- ============================================================================
-- TYPES (ENUMs)
-- ============================================================================

DO $$ BEGIN
    CREATE TYPE clinica.appointment_status AS ENUM (
        'scheduled', 'confirmed', 'in_progress', 'completed', 'cancelled', 'no_show'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE clinica.patient_gender AS ENUM (
        'male', 'female', 'other', 'prefer_not_to_say'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE clinica.blood_type AS ENUM (
        'A+', 'A-', 'B+', 'B-', 'AB+', 'AB-', 'O+', 'O-', 'unknown'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
    CREATE TYPE clinica.consultation_status AS ENUM (
        'draft', 'in_progress', 'completed', 'cancelled'
    );
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ============================================================================
-- CATÁLOGOS BASE
-- ============================================================================

-- Tabla: specialties (Especialidades médicas)
CREATE TABLE IF NOT EXISTS clinica.specialties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    consultation_duration INTEGER DEFAULT 30, -- minutos
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_specialties_code UNIQUE (tenant_id, code)
);

-- Tabla: doctors (Médicos - extiende hr.employees)
CREATE TABLE IF NOT EXISTS clinica.doctors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    employee_id UUID,  -- FK a hr.employees (ERP Core)
    user_id UUID REFERENCES auth.users(id),
    specialty_id UUID NOT NULL REFERENCES clinica.specialties(id),
    license_number VARCHAR(50) NOT NULL,  -- Cédula profesional
    license_expiry DATE,
    secondary_specialties UUID[],  -- Array de specialty_ids
    consultation_fee DECIMAL(12,2),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),
    CONSTRAINT uq_doctors_license UNIQUE (tenant_id, license_number)
);

-- ============================================================================
-- PACIENTES (CL-001)
-- ============================================================================

-- Tabla: patients (Pacientes - extiende core.partners)
CREATE TABLE IF NOT EXISTS clinica.patients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    partner_id UUID REFERENCES core.partners(id),  -- Vinculo a partner

    -- Identificación
    patient_number VARCHAR(30) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),

    -- Datos personales
    birth_date DATE,
    gender clinica.patient_gender,
    curp VARCHAR(18),

    -- Contacto
    email VARCHAR(255),
    phone VARCHAR(20),
    mobile VARCHAR(20),

    -- Dirección
    street VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    zip_code VARCHAR(10),
    country VARCHAR(100) DEFAULT 'México',

    -- Datos médicos básicos
    blood_type clinica.blood_type DEFAULT 'unknown',
    allergies TEXT[],
    chronic_conditions TEXT[],

    -- Seguro médico
    has_insurance BOOLEAN DEFAULT FALSE,
    insurance_provider VARCHAR(100),
    insurance_policy VARCHAR(50),

    -- Control
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    last_visit_date DATE,

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES auth.users(id),

    CONSTRAINT uq_patients_number UNIQUE (tenant_id, patient_number)
);

-- Tabla: patient_contacts (Contactos de emergencia)
CREATE TABLE IF NOT EXISTS clinica.patient_contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    patient_id UUID NOT NULL REFERENCES clinica.patients(id) ON DELETE CASCADE,
    contact_name VARCHAR(200) NOT NULL,
    relationship VARCHAR(50),  -- Parentesco
    phone VARCHAR(20),
    mobile VARCHAR(20),
    email VARCHAR(255),
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id)
);

-- Tabla: patient_insurance (Información de seguros)
CREATE TABLE IF NOT EXISTS clinica.patient_insurance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    patient_id UUID NOT NULL REFERENCES clinica.patients(id) ON DELETE CASCADE,
    insurance_provider VARCHAR(100) NOT NULL,
    policy_number VARCHAR(50) NOT NULL,
    group_number VARCHAR(50),
    holder_name VARCHAR(200),
    holder_relationship VARCHAR(50),
    coverage_type VARCHAR(50),
    valid_from DATE,
    valid_until DATE,
    is_primary BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id)
);

-- ============================================================================
-- CITAS (CL-002)
-- ============================================================================

-- Tabla: appointment_slots (Horarios disponibles)
CREATE TABLE IF NOT EXISTS clinica.appointment_slots (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    doctor_id UUID NOT NULL REFERENCES clinica.doctors(id),
    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 INTEGER DEFAULT 30,  -- minutos
    max_appointments INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),
    CONSTRAINT chk_slot_times CHECK (end_time > start_time)
);

-- Tabla: appointments (Citas médicas)
CREATE TABLE IF NOT EXISTS clinica.appointments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Referencias
    patient_id UUID NOT NULL REFERENCES clinica.patients(id),
    doctor_id UUID NOT NULL REFERENCES clinica.doctors(id),
    specialty_id UUID REFERENCES clinica.specialties(id),

    -- Programación
    appointment_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    duration INTEGER DEFAULT 30,  -- minutos

    -- Estado
    status clinica.appointment_status NOT NULL DEFAULT 'scheduled',

    -- Detalles
    reason TEXT,  -- Motivo de consulta
    notes TEXT,
    is_first_visit BOOLEAN DEFAULT FALSE,
    is_follow_up BOOLEAN DEFAULT FALSE,
    follow_up_to UUID REFERENCES clinica.appointments(id),

    -- Recordatorios
    reminder_sent BOOLEAN DEFAULT FALSE,
    reminder_sent_at TIMESTAMPTZ,

    -- Confirmación
    confirmed_at TIMESTAMPTZ,
    confirmed_by UUID REFERENCES auth.users(id),

    -- Cancelación
    cancelled_at TIMESTAMPTZ,
    cancelled_by UUID REFERENCES auth.users(id),
    cancellation_reason TEXT,

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),

    CONSTRAINT chk_appointment_times CHECK (end_time > start_time)
);

-- ============================================================================
-- EXPEDIENTE CLÍNICO (CL-003)
-- ============================================================================

-- Tabla: medical_records (Expediente clínico electrónico)
-- NOTA: Datos sensibles según NOM-024-SSA3-2012
CREATE TABLE IF NOT EXISTS clinica.medical_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    patient_id UUID NOT NULL REFERENCES clinica.patients(id),

    -- Número de expediente
    record_number VARCHAR(30) NOT NULL,

    -- Antecedentes
    family_history TEXT,
    personal_history TEXT,
    surgical_history TEXT,

    -- Hábitos
    smoking_status VARCHAR(50),
    alcohol_status VARCHAR(50),
    exercise_status VARCHAR(50),
    diet_notes TEXT,

    -- Gineco-obstétricos (si aplica)
    obstetric_history JSONB,

    -- Notas generales
    notes TEXT,

    -- Control de acceso
    is_confidential BOOLEAN DEFAULT TRUE,
    access_restricted BOOLEAN DEFAULT FALSE,

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),

    CONSTRAINT uq_medical_records_number UNIQUE (tenant_id, record_number),
    CONSTRAINT uq_medical_records_patient UNIQUE (patient_id)
);

-- Tabla: consultations (Consultas realizadas)
CREATE TABLE IF NOT EXISTS clinica.consultations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Referencias
    medical_record_id UUID NOT NULL REFERENCES clinica.medical_records(id),
    appointment_id UUID REFERENCES clinica.appointments(id),
    doctor_id UUID NOT NULL REFERENCES clinica.doctors(id),

    -- Fecha/hora
    consultation_date DATE NOT NULL,
    start_time TIMESTAMPTZ,
    end_time TIMESTAMPTZ,

    -- Estado
    status clinica.consultation_status DEFAULT 'draft',

    -- Motivo de consulta
    chief_complaint TEXT NOT NULL,  -- Motivo principal
    present_illness TEXT,  -- Padecimiento actual

    -- Exploración física
    physical_exam JSONB,  -- Estructurado por sistemas

    -- Plan
    treatment_plan TEXT,
    follow_up_instructions TEXT,
    next_appointment_days INTEGER,

    -- Notas
    notes TEXT,
    private_notes TEXT,  -- Solo visible para el médico

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id)
);

-- Tabla: vital_signs (Signos vitales)
CREATE TABLE IF NOT EXISTS clinica.vital_signs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    consultation_id UUID NOT NULL REFERENCES clinica.consultations(id) ON DELETE CASCADE,

    -- Signos vitales
    weight_kg DECIMAL(5,2),
    height_cm DECIMAL(5,2),
    bmi DECIMAL(4,2) GENERATED ALWAYS AS (
        CASE WHEN height_cm > 0 THEN weight_kg / ((height_cm/100) * (height_cm/100)) END
    ) STORED,
    temperature_c DECIMAL(4,2),
    blood_pressure_systolic INTEGER,
    blood_pressure_diastolic INTEGER,
    heart_rate INTEGER,  -- latidos por minuto
    respiratory_rate INTEGER,  -- respiraciones por minuto
    oxygen_saturation INTEGER,  -- porcentaje

    -- Fecha/hora de medición
    measured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    measured_by UUID REFERENCES auth.users(id),

    notes TEXT,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

-- Tabla: diagnoses (Diagnósticos - CIE-10)
CREATE TABLE IF NOT EXISTS clinica.diagnoses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    consultation_id UUID NOT NULL REFERENCES clinica.consultations(id) ON DELETE CASCADE,

    -- Código CIE-10
    icd10_code VARCHAR(10) NOT NULL,
    icd10_description VARCHAR(255),

    -- Tipo
    diagnosis_type VARCHAR(20) NOT NULL DEFAULT 'primary',  -- primary, secondary, differential

    -- Detalles
    notes TEXT,
    is_chronic BOOLEAN DEFAULT FALSE,
    onset_date DATE,

    -- Orden
    sequence INTEGER DEFAULT 1,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

-- Tabla: prescriptions (Recetas médicas)
CREATE TABLE IF NOT EXISTS clinica.prescriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    consultation_id UUID NOT NULL REFERENCES clinica.consultations(id),

    -- Número de receta
    prescription_number VARCHAR(30) NOT NULL,
    prescription_date DATE NOT NULL DEFAULT CURRENT_DATE,

    -- Médico
    doctor_id UUID NOT NULL REFERENCES clinica.doctors(id),

    -- Instrucciones generales
    general_instructions TEXT,

    -- Vigencia
    valid_until DATE,

    -- Estado
    is_printed BOOLEAN DEFAULT FALSE,
    printed_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ,
    updated_by UUID REFERENCES auth.users(id),

    CONSTRAINT uq_prescriptions_number UNIQUE (tenant_id, prescription_number)
);

-- Tabla: prescription_items (Líneas de receta)
CREATE TABLE IF NOT EXISTS clinica.prescription_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
    prescription_id UUID NOT NULL REFERENCES clinica.prescriptions(id) ON DELETE CASCADE,

    -- Medicamento
    product_id UUID,  -- FK a inventory.products (ERP Core)
    medication_name VARCHAR(255) NOT NULL,
    presentation VARCHAR(100),  -- Tabletas, jarabe, etc.

    -- Dosificación
    dosage VARCHAR(100) NOT NULL,  -- "1 tableta"
    frequency VARCHAR(100) NOT NULL,  -- "cada 8 horas"
    duration VARCHAR(100),  -- "por 7 días"
    quantity INTEGER,  -- Cantidad a surtir

    -- Instrucciones
    instructions TEXT,

    -- Orden
    sequence INTEGER DEFAULT 1,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

-- ============================================================================
-- ÍNDICES
-- ============================================================================

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

-- 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_user ON clinica.doctors(user_id);

-- Patients
CREATE INDEX IF NOT EXISTS idx_patients_tenant ON clinica.patients(tenant_id);
CREATE INDEX IF NOT EXISTS idx_patients_partner ON clinica.patients(partner_id);
CREATE INDEX IF NOT EXISTS idx_patients_name ON clinica.patients(last_name, first_name);
CREATE INDEX IF NOT EXISTS idx_patients_curp ON clinica.patients(curp);

-- Patient contacts
CREATE INDEX IF NOT EXISTS idx_patient_contacts_tenant ON clinica.patient_contacts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_patient_contacts_patient ON clinica.patient_contacts(patient_id);

-- Patient insurance
CREATE INDEX IF NOT EXISTS idx_patient_insurance_tenant ON clinica.patient_insurance(tenant_id);
CREATE INDEX IF NOT EXISTS idx_patient_insurance_patient ON clinica.patient_insurance(patient_id);

-- Appointment slots
CREATE INDEX IF NOT EXISTS idx_appointment_slots_tenant ON clinica.appointment_slots(tenant_id);
CREATE INDEX IF NOT EXISTS idx_appointment_slots_doctor ON clinica.appointment_slots(doctor_id);

-- 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(appointment_date);
CREATE INDEX IF NOT EXISTS idx_appointments_status ON clinica.appointments(status);

-- Medical records
CREATE INDEX IF NOT EXISTS idx_medical_records_tenant ON clinica.medical_records(tenant_id);
CREATE INDEX IF NOT EXISTS idx_medical_records_patient ON clinica.medical_records(patient_id);

-- Consultations
CREATE INDEX IF NOT EXISTS idx_consultations_tenant ON clinica.consultations(tenant_id);
CREATE INDEX IF NOT EXISTS idx_consultations_record ON clinica.consultations(medical_record_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(consultation_date);

-- Vital signs
CREATE INDEX IF NOT EXISTS idx_vital_signs_tenant ON clinica.vital_signs(tenant_id);
CREATE INDEX IF NOT EXISTS idx_vital_signs_consultation ON clinica.vital_signs(consultation_id);

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

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

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

-- ============================================================================
-- 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.patient_contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.patient_insurance ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.appointment_slots ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.appointments ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.medical_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.consultations ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.vital_signs 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;

-- Políticas de aislamiento por tenant
DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_specialties ON clinica.specialties;
    CREATE POLICY tenant_isolation_specialties ON clinica.specialties
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_doctors ON clinica.doctors;
    CREATE POLICY tenant_isolation_doctors ON clinica.doctors
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_patients ON clinica.patients;
    CREATE POLICY tenant_isolation_patients ON clinica.patients
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_patient_contacts ON clinica.patient_contacts;
    CREATE POLICY tenant_isolation_patient_contacts ON clinica.patient_contacts
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_patient_insurance ON clinica.patient_insurance;
    CREATE POLICY tenant_isolation_patient_insurance ON clinica.patient_insurance
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_appointment_slots ON clinica.appointment_slots;
    CREATE POLICY tenant_isolation_appointment_slots ON clinica.appointment_slots
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_appointments ON clinica.appointments;
    CREATE POLICY tenant_isolation_appointments ON clinica.appointments
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_medical_records ON clinica.medical_records;
    CREATE POLICY tenant_isolation_medical_records ON clinica.medical_records
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_consultations ON clinica.consultations;
    CREATE POLICY tenant_isolation_consultations ON clinica.consultations
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_vital_signs ON clinica.vital_signs;
    CREATE POLICY tenant_isolation_vital_signs ON clinica.vital_signs
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_diagnoses ON clinica.diagnoses;
    CREATE POLICY tenant_isolation_diagnoses ON clinica.diagnoses
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_prescriptions ON clinica.prescriptions;
    CREATE POLICY tenant_isolation_prescriptions ON clinica.prescriptions
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

DO $$ BEGIN
    DROP POLICY IF EXISTS tenant_isolation_prescription_items ON clinica.prescription_items;
    CREATE POLICY tenant_isolation_prescription_items ON clinica.prescription_items
        FOR ALL USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);
EXCEPTION WHEN undefined_object THEN NULL; END $$;

-- ============================================================================
-- COMENTARIOS
-- ============================================================================

COMMENT ON TABLE clinica.specialties IS 'Catálogo de especialidades médicas';
COMMENT ON TABLE clinica.doctors IS 'Médicos y especialistas - extiende hr.employees';
COMMENT ON TABLE clinica.patients IS 'Registro de pacientes - extiende core.partners';
COMMENT ON TABLE clinica.patient_contacts IS 'Contactos de emergencia del paciente';
COMMENT ON TABLE clinica.patient_insurance IS 'Información de seguros médicos';
COMMENT ON TABLE clinica.appointment_slots IS 'Horarios disponibles por médico';
COMMENT ON TABLE clinica.appointments IS 'Citas médicas programadas';
COMMENT ON TABLE clinica.medical_records IS 'Expediente clínico electrónico (NOM-024-SSA3)';
COMMENT ON TABLE clinica.consultations IS 'Consultas médicas realizadas';
COMMENT ON TABLE clinica.vital_signs IS 'Signos vitales del paciente';
COMMENT ON TABLE clinica.diagnoses IS 'Diagnósticos según CIE-10';
COMMENT ON TABLE clinica.prescriptions IS 'Recetas médicas';
COMMENT ON TABLE clinica.prescription_items IS 'Medicamentos en receta';

-- ============================================================================
-- FIN TABLAS CLÍNICAS
-- Total: 13 tablas, 4 ENUMs
-- ============================================================================