erp-clinicas-database-v2/schemas/08-clinica-ext-fase8-schema-ddl.sql

-- ============================================================================
-- CLINICA EXTENSIONS - FASE 8 ERP-Core
-- ERP Clínicas (Base Genérica)
-- ============================================================================
-- Fecha: 2026-01-04
-- Versión: 1.0
-- ============================================================================

-- El schema clinica ya existe (03-clinical-tables.sql)

-- ============================================================================
-- TABLAS
-- ============================================================================

-- Personal de clínica (adaptación de collaborators)
CREATE TABLE IF NOT EXISTS clinica.personal_clinica (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    employee_id UUID NOT NULL,
    consultorio_id UUID,
    -- Datos del rol
    rol VARCHAR(50) NOT NULL, -- 'medico', 'enfermera', 'recepcion', 'auxiliar', 'laboratorio', 'farmacia'
    vigencia_desde DATE,
    vigencia_hasta DATE,
    es_titular BOOLEAN DEFAULT false,
    horario JSONB, -- {"lunes": {"inicio": "09:00", "fin": "18:00"}, ...}
    -- Control
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE clinica.personal_clinica IS 'Personal asignado a consultorios - FASE 8';
COMMENT ON COLUMN clinica.personal_clinica.rol IS 'Rol del personal en el consultorio';
COMMENT ON COLUMN clinica.personal_clinica.horario IS 'Horario de trabajo en formato JSON';

-- Calificaciones/Ratings
CREATE TABLE IF NOT EXISTS clinica.ratings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    consultation_id UUID,
    patient_id UUID,
    doctor_id UUID,
    -- Calificación
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    feedback TEXT,
    -- Aspectos específicos
    puntualidad INTEGER CHECK (puntualidad BETWEEN 1 AND 5),
    atencion INTEGER CHECK (atencion BETWEEN 1 AND 5),
    instalaciones INTEGER CHECK (instalaciones BETWEEN 1 AND 5),
    -- Metadata
    rated_at TIMESTAMPTZ DEFAULT NOW(),
    is_anonymous BOOLEAN DEFAULT false,
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE clinica.ratings IS 'Calificaciones de consultas - FASE 8';
COMMENT ON COLUMN clinica.ratings.rating IS 'Calificación general de 1 a 5';

-- ============================================================================
-- FKs OPCIONALES
-- ============================================================================

DO $$
BEGIN
    -- FK personal_clinica → hr.work_locations
    IF EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = 'hr' AND table_name = 'work_locations') THEN
        IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
                       WHERE constraint_name = 'fk_personal_consultorio') THEN
            ALTER TABLE clinica.personal_clinica
            ADD CONSTRAINT fk_personal_consultorio
            FOREIGN KEY (consultorio_id) REFERENCES hr.work_locations(id) ON DELETE SET NULL;
        END IF;
    END IF;

    -- FK ratings → clinica.consultations
    IF EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = 'clinica' AND table_name = 'consultations') THEN
        IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
                       WHERE constraint_name = 'fk_rating_consultation') THEN
            ALTER TABLE clinica.ratings
            ADD CONSTRAINT fk_rating_consultation
            FOREIGN KEY (consultation_id) REFERENCES clinica.consultations(id) ON DELETE SET NULL;
        END IF;
    END IF;

    -- FK ratings → clinica.patients
    IF EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = 'clinica' AND table_name = 'patients') THEN
        IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
                       WHERE constraint_name = 'fk_rating_patient') THEN
            ALTER TABLE clinica.ratings
            ADD CONSTRAINT fk_rating_patient
            FOREIGN KEY (patient_id) REFERENCES clinica.patients(id) ON DELETE SET NULL;
        END IF;
    END IF;

    -- FK ratings → clinica.doctors
    IF EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = 'clinica' AND table_name = 'doctors') THEN
        IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints
                       WHERE constraint_name = 'fk_rating_doctor') THEN
            ALTER TABLE clinica.ratings
            ADD CONSTRAINT fk_rating_doctor
            FOREIGN KEY (doctor_id) REFERENCES clinica.doctors(id) ON DELETE SET NULL;
        END IF;
    END IF;
END $$;

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

CREATE INDEX IF NOT EXISTS idx_personal_clinica_tenant
    ON clinica.personal_clinica(tenant_id);
CREATE INDEX IF NOT EXISTS idx_personal_clinica_employee
    ON clinica.personal_clinica(employee_id);
CREATE INDEX IF NOT EXISTS idx_personal_clinica_consultorio
    ON clinica.personal_clinica(consultorio_id);
CREATE INDEX IF NOT EXISTS idx_personal_clinica_rol
    ON clinica.personal_clinica(tenant_id, rol);

CREATE INDEX IF NOT EXISTS idx_ratings_tenant
    ON clinica.ratings(tenant_id);
CREATE INDEX IF NOT EXISTS idx_ratings_consultation
    ON clinica.ratings(consultation_id);
CREATE INDEX IF NOT EXISTS idx_ratings_doctor
    ON clinica.ratings(doctor_id);
CREATE INDEX IF NOT EXISTS idx_ratings_patient
    ON clinica.ratings(patient_id);

-- ============================================================================
-- RLS
-- ============================================================================

ALTER TABLE clinica.personal_clinica ENABLE ROW LEVEL SECURITY;
ALTER TABLE clinica.ratings ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tenant_isolation_personal_clinica ON clinica.personal_clinica;
CREATE POLICY tenant_isolation_personal_clinica ON clinica.personal_clinica
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_ratings ON clinica.ratings;
CREATE POLICY tenant_isolation_ratings ON clinica.ratings
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================================================
-- FIN CLINICA EXTENSIONS
-- ============================================================================