clinica-veterinaria/database/schemas/02-veterinaria-farmacia-ddl.sql

-- ============================================================================
-- VETERINARIA SCHEMA - Farmacia (VET-006)
-- Sistema de gestion de farmacia veterinaria
-- ============================================================================
-- Fecha: 2026-01-07
-- Version: 1.0
-- Basado en: VET-006-farmacia.md
-- ============================================================================

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

DO $$ BEGIN
    CREATE TYPE veterinaria.categoria_medicamento AS ENUM (
        'antibiotico',
        'antiparasitario',
        'analgesico',
        'antiinflamatorio',
        'vacuna',
        'vitamina',
        'dermatologico',
        'oftalmico',
        'cardiaco',
        'digestivo',
        'otro'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE veterinaria.tipo_movimiento_farmacia AS ENUM (
        'entrada',
        'salida',
        'ajuste_positivo',
        'ajuste_negativo',
        'devolucion',
        'merma'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE veterinaria.fraccion_controlada AS ENUM (
        'no_controlado',
        'fraccion_i',
        'fraccion_ii',
        'fraccion_iii',
        'fraccion_iv'
    );
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

-- ============================================================================
-- TABLAS PRINCIPALES
-- ============================================================================

-- Catalogo de medicamentos
CREATE TABLE IF NOT EXISTS veterinaria.medicamentos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    -- Identificacion
    codigo VARCHAR(50),
    nombre VARCHAR(150) NOT NULL,
    nombre_comercial VARCHAR(150),
    principio_activo VARCHAR(200),
    -- Clasificacion
    categoria veterinaria.categoria_medicamento DEFAULT 'otro',
    -- Presentacion
    presentacion VARCHAR(100), -- 'tabletas', 'inyectable', 'suspension', etc.
    concentracion VARCHAR(50), -- '500mg', '100mg/ml'
    contenido VARCHAR(50), -- '30 tabletas', '100ml'
    -- Fabricante
    laboratorio VARCHAR(100),
    -- Control
    requiere_receta BOOLEAN DEFAULT false,
    controlado BOOLEAN DEFAULT false,
    fraccion_controlada veterinaria.fraccion_controlada DEFAULT 'no_controlado',
    -- Stock
    stock_minimo INTEGER DEFAULT 10,
    stock_actual INTEGER DEFAULT 0,
    -- Precios
    precio_compra NUMERIC(10,2),
    precio_venta NUMERIC(10,2),
    -- Especies aplicables (NULL = todas)
    especies_aplicables UUID[], -- Array de especie_id
    -- Estado
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE veterinaria.medicamentos IS 'Catalogo de medicamentos veterinarios';

-- Lotes de medicamentos (control de caducidad)
CREATE TABLE IF NOT EXISTS veterinaria.medicamentos_lotes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    medicamento_id UUID NOT NULL REFERENCES veterinaria.medicamentos(id) ON DELETE CASCADE,
    -- Lote
    numero_lote VARCHAR(50) NOT NULL,
    fecha_caducidad DATE NOT NULL,
    -- Cantidades
    cantidad_inicial INTEGER NOT NULL,
    cantidad_actual INTEGER NOT NULL,
    -- Compra
    precio_compra NUMERIC(10,2),
    factura_compra VARCHAR(50),
    proveedor VARCHAR(100),
    fecha_recepcion DATE DEFAULT CURRENT_DATE,
    -- Estado
    bloqueado BOOLEAN DEFAULT false, -- Bloquear si esta vencido
    motivo_bloqueo TEXT,
    -- Control
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE veterinaria.medicamentos_lotes IS 'Lotes de medicamentos con control de caducidad';

-- Dispensaciones de medicamentos
CREATE TABLE IF NOT EXISTS veterinaria.dispensaciones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    -- Referencias
    medicamento_id UUID NOT NULL REFERENCES veterinaria.medicamentos(id),
    lote_id UUID NOT NULL REFERENCES veterinaria.medicamentos_lotes(id),
    mascota_id UUID REFERENCES veterinaria.mascotas(id),
    veterinario_id UUID, -- Referencia a clinica.doctors
    receta_id UUID, -- Referencia a clinica.prescriptions si existe
    consultation_id UUID, -- Referencia a clinica.consultations
    -- Dispensacion
    cantidad INTEGER NOT NULL,
    fecha_dispensacion TIMESTAMPTZ DEFAULT NOW(),
    -- Instrucciones
    dosis VARCHAR(100), -- '1 tableta cada 8 horas'
    duracion_tratamiento VARCHAR(50), -- '7 dias'
    instrucciones TEXT,
    -- Control
    dispensado_por UUID, -- employee_id
    notas TEXT,
    -- Auditoria
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE veterinaria.dispensaciones IS 'Registro de dispensacion de medicamentos';

-- Movimientos de inventario (kardex)
CREATE TABLE IF NOT EXISTS veterinaria.movimientos_farmacia (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    medicamento_id UUID NOT NULL REFERENCES veterinaria.medicamentos(id),
    lote_id UUID REFERENCES veterinaria.medicamentos_lotes(id),
    -- Movimiento
    tipo veterinaria.tipo_movimiento_farmacia NOT NULL,
    cantidad INTEGER NOT NULL,
    stock_anterior INTEGER NOT NULL,
    stock_posterior INTEGER NOT NULL,
    -- Referencia
    referencia_tipo VARCHAR(50), -- 'dispensacion', 'compra', 'ajuste'
    referencia_id UUID,
    -- Detalles
    motivo TEXT,
    documento VARCHAR(100), -- Factura, nota, etc.
    -- Auditoria
    usuario_id UUID,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE veterinaria.movimientos_farmacia IS 'Kardex de movimientos de inventario de farmacia';

-- Bitacora de medicamentos controlados
CREATE TABLE IF NOT EXISTS veterinaria.bitacora_controlados (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    medicamento_id UUID NOT NULL REFERENCES veterinaria.medicamentos(id),
    lote_id UUID REFERENCES veterinaria.medicamentos_lotes(id),
    dispensacion_id UUID REFERENCES veterinaria.dispensaciones(id),
    -- Movimiento
    tipo_movimiento veterinaria.tipo_movimiento_farmacia NOT NULL,
    cantidad INTEGER NOT NULL,
    -- Paciente
    mascota_id UUID REFERENCES veterinaria.mascotas(id),
    propietario_nombre VARCHAR(200), -- Snapshot del nombre
    -- Prescripcion
    receta_id UUID,
    veterinario_id UUID,
    veterinario_cedula VARCHAR(50),
    -- Justificacion
    justificacion TEXT NOT NULL,
    diagnostico TEXT,
    -- Auditoria
    fecha_registro TIMESTAMPTZ DEFAULT NOW(),
    registrado_por UUID NOT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE veterinaria.bitacora_controlados IS 'Bitacora de movimientos de medicamentos controlados (requerido por COFEPRIS)';

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

CREATE INDEX IF NOT EXISTS idx_medicamentos_tenant ON veterinaria.medicamentos(tenant_id);
CREATE INDEX IF NOT EXISTS idx_medicamentos_codigo ON veterinaria.medicamentos(tenant_id, codigo);
CREATE INDEX IF NOT EXISTS idx_medicamentos_nombre ON veterinaria.medicamentos(tenant_id, nombre);
CREATE INDEX IF NOT EXISTS idx_medicamentos_categoria ON veterinaria.medicamentos(tenant_id, categoria);
CREATE INDEX IF NOT EXISTS idx_medicamentos_controlado ON veterinaria.medicamentos(tenant_id, controlado) WHERE controlado = true;
CREATE INDEX IF NOT EXISTS idx_medicamentos_stock_bajo ON veterinaria.medicamentos(tenant_id)
    WHERE stock_actual <= stock_minimo;

CREATE INDEX IF NOT EXISTS idx_lotes_tenant ON veterinaria.medicamentos_lotes(tenant_id);
CREATE INDEX IF NOT EXISTS idx_lotes_medicamento ON veterinaria.medicamentos_lotes(medicamento_id);
CREATE INDEX IF NOT EXISTS idx_lotes_caducidad ON veterinaria.medicamentos_lotes(fecha_caducidad);
CREATE INDEX IF NOT EXISTS idx_lotes_numero ON veterinaria.medicamentos_lotes(tenant_id, numero_lote);
CREATE INDEX IF NOT EXISTS idx_lotes_proximos_caducar ON veterinaria.medicamentos_lotes(tenant_id, fecha_caducidad)
    WHERE cantidad_actual > 0 AND bloqueado = false;

CREATE INDEX IF NOT EXISTS idx_dispensaciones_tenant ON veterinaria.dispensaciones(tenant_id);
CREATE INDEX IF NOT EXISTS idx_dispensaciones_medicamento ON veterinaria.dispensaciones(medicamento_id);
CREATE INDEX IF NOT EXISTS idx_dispensaciones_mascota ON veterinaria.dispensaciones(mascota_id);
CREATE INDEX IF NOT EXISTS idx_dispensaciones_fecha ON veterinaria.dispensaciones(fecha_dispensacion);
CREATE INDEX IF NOT EXISTS idx_dispensaciones_veterinario ON veterinaria.dispensaciones(veterinario_id);

CREATE INDEX IF NOT EXISTS idx_movimientos_tenant ON veterinaria.movimientos_farmacia(tenant_id);
CREATE INDEX IF NOT EXISTS idx_movimientos_medicamento ON veterinaria.movimientos_farmacia(medicamento_id);
CREATE INDEX IF NOT EXISTS idx_movimientos_fecha ON veterinaria.movimientos_farmacia(created_at);

CREATE INDEX IF NOT EXISTS idx_bitacora_tenant ON veterinaria.bitacora_controlados(tenant_id);
CREATE INDEX IF NOT EXISTS idx_bitacora_medicamento ON veterinaria.bitacora_controlados(medicamento_id);
CREATE INDEX IF NOT EXISTS idx_bitacora_fecha ON veterinaria.bitacora_controlados(fecha_registro);

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

ALTER TABLE veterinaria.medicamentos ENABLE ROW LEVEL SECURITY;
ALTER TABLE veterinaria.medicamentos_lotes ENABLE ROW LEVEL SECURITY;
ALTER TABLE veterinaria.dispensaciones ENABLE ROW LEVEL SECURITY;
ALTER TABLE veterinaria.movimientos_farmacia ENABLE ROW LEVEL SECURITY;
ALTER TABLE veterinaria.bitacora_controlados ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS tenant_isolation_medicamentos ON veterinaria.medicamentos;
CREATE POLICY tenant_isolation_medicamentos ON veterinaria.medicamentos
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_lotes ON veterinaria.medicamentos_lotes;
CREATE POLICY tenant_isolation_lotes ON veterinaria.medicamentos_lotes
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_dispensaciones ON veterinaria.dispensaciones;
CREATE POLICY tenant_isolation_dispensaciones ON veterinaria.dispensaciones
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_movimientos ON veterinaria.movimientos_farmacia;
CREATE POLICY tenant_isolation_movimientos ON veterinaria.movimientos_farmacia
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

DROP POLICY IF EXISTS tenant_isolation_bitacora ON veterinaria.bitacora_controlados;
CREATE POLICY tenant_isolation_bitacora ON veterinaria.bitacora_controlados
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ============================================================================
-- TRIGGERS
-- ============================================================================

-- Trigger para actualizar stock_actual en medicamentos
CREATE OR REPLACE FUNCTION veterinaria.actualizar_stock_medicamento()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- Actualizar stock del medicamento
        UPDATE veterinaria.medicamentos
        SET stock_actual = stock_actual + NEW.cantidad_inicial,
            updated_at = NOW()
        WHERE id = NEW.medicamento_id;
    ELSIF TG_OP = 'UPDATE' THEN
        -- Si cambia cantidad_actual
        UPDATE veterinaria.medicamentos
        SET stock_actual = (
            SELECT COALESCE(SUM(cantidad_actual), 0)
            FROM veterinaria.medicamentos_lotes
            WHERE medicamento_id = NEW.medicamento_id
            AND bloqueado = false
        ),
        updated_at = NOW()
        WHERE id = NEW.medicamento_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_actualizar_stock ON veterinaria.medicamentos_lotes;
CREATE TRIGGER trg_actualizar_stock
    AFTER INSERT OR UPDATE OF cantidad_actual ON veterinaria.medicamentos_lotes
    FOR EACH ROW
    EXECUTE FUNCTION veterinaria.actualizar_stock_medicamento();

COMMENT ON FUNCTION veterinaria.actualizar_stock_medicamento() IS 'Actualiza stock_actual en medicamentos cuando cambian los lotes';

-- Trigger para registrar movimiento en dispensacion
CREATE OR REPLACE FUNCTION veterinaria.registrar_movimiento_dispensacion()
RETURNS TRIGGER AS $$
DECLARE
    v_stock_anterior INTEGER;
BEGIN
    -- Obtener stock anterior
    SELECT stock_actual INTO v_stock_anterior
    FROM veterinaria.medicamentos
    WHERE id = NEW.medicamento_id;

    -- Descontar del lote
    UPDATE veterinaria.medicamentos_lotes
    SET cantidad_actual = cantidad_actual - NEW.cantidad,
        updated_at = NOW()
    WHERE id = NEW.lote_id;

    -- Registrar movimiento
    INSERT INTO veterinaria.movimientos_farmacia (
        tenant_id, medicamento_id, lote_id,
        tipo, cantidad, stock_anterior, stock_posterior,
        referencia_tipo, referencia_id, usuario_id
    ) VALUES (
        NEW.tenant_id, NEW.medicamento_id, NEW.lote_id,
        'salida', NEW.cantidad, v_stock_anterior, v_stock_anterior - NEW.cantidad,
        'dispensacion', NEW.id, NEW.dispensado_por
    );

    -- Si es controlado, registrar en bitacora
    IF EXISTS (
        SELECT 1 FROM veterinaria.medicamentos
        WHERE id = NEW.medicamento_id AND controlado = true
    ) THEN
        INSERT INTO veterinaria.bitacora_controlados (
            tenant_id, medicamento_id, lote_id, dispensacion_id,
            tipo_movimiento, cantidad, mascota_id,
            propietario_nombre, receta_id, veterinario_id,
            justificacion, registrado_por
        )
        SELECT
            NEW.tenant_id, NEW.medicamento_id, NEW.lote_id, NEW.id,
            'salida', NEW.cantidad, NEW.mascota_id,
            CONCAT(p.nombre, ' ', COALESCE(p.apellidos, '')),
            NEW.receta_id, NEW.veterinario_id,
            COALESCE(NEW.notas, 'Dispensacion de medicamento'),
            NEW.dispensado_por
        FROM veterinaria.mascotas m
        JOIN veterinaria.propietarios p ON m.propietario_id = p.id
        WHERE m.id = NEW.mascota_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_registrar_dispensacion ON veterinaria.dispensaciones;
CREATE TRIGGER trg_registrar_dispensacion
    AFTER INSERT ON veterinaria.dispensaciones
    FOR EACH ROW
    EXECUTE FUNCTION veterinaria.registrar_movimiento_dispensacion();

COMMENT ON FUNCTION veterinaria.registrar_movimiento_dispensacion() IS 'Registra movimiento y bitacora al dispensar medicamentos';

-- ============================================================================
-- FUNCIONES DE CONSULTA
-- ============================================================================

-- Funcion para obtener lotes proximos a caducar
CREATE OR REPLACE FUNCTION veterinaria.get_lotes_proximos_caducar(
    p_tenant_id UUID,
    p_dias INTEGER DEFAULT 30
)
RETURNS TABLE (
    lote_id UUID,
    medicamento_id UUID,
    medicamento_nombre VARCHAR,
    numero_lote VARCHAR,
    fecha_caducidad DATE,
    dias_restantes INTEGER,
    cantidad_actual INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        l.id AS lote_id,
        m.id AS medicamento_id,
        m.nombre AS medicamento_nombre,
        l.numero_lote,
        l.fecha_caducidad,
        (l.fecha_caducidad - CURRENT_DATE)::INTEGER AS dias_restantes,
        l.cantidad_actual
    FROM veterinaria.medicamentos_lotes l
    JOIN veterinaria.medicamentos m ON l.medicamento_id = m.id
    WHERE l.tenant_id = p_tenant_id
      AND l.cantidad_actual > 0
      AND l.bloqueado = false
      AND l.fecha_caducidad <= CURRENT_DATE + p_dias
    ORDER BY l.fecha_caducidad ASC;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION veterinaria.get_lotes_proximos_caducar(UUID, INTEGER) IS 'Obtiene lotes que caducaran en los proximos N dias';

-- Funcion para obtener medicamentos con stock bajo
CREATE OR REPLACE FUNCTION veterinaria.get_medicamentos_stock_bajo(p_tenant_id UUID)
RETURNS TABLE (
    medicamento_id UUID,
    codigo VARCHAR,
    nombre VARCHAR,
    stock_actual INTEGER,
    stock_minimo INTEGER,
    diferencia INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        m.id AS medicamento_id,
        m.codigo,
        m.nombre,
        m.stock_actual,
        m.stock_minimo,
        (m.stock_minimo - m.stock_actual) AS diferencia
    FROM veterinaria.medicamentos m
    WHERE m.tenant_id = p_tenant_id
      AND m.active = true
      AND m.stock_actual <= m.stock_minimo
    ORDER BY diferencia DESC;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION veterinaria.get_medicamentos_stock_bajo(UUID) IS 'Obtiene medicamentos con stock igual o menor al minimo';

-- Funcion para seleccionar lote FEFO (First Expired, First Out)
CREATE OR REPLACE FUNCTION veterinaria.seleccionar_lote_fefo(
    p_medicamento_id UUID,
    p_cantidad INTEGER
)
RETURNS UUID AS $$
DECLARE
    v_lote_id UUID;
BEGIN
    SELECT id INTO v_lote_id
    FROM veterinaria.medicamentos_lotes
    WHERE medicamento_id = p_medicamento_id
      AND cantidad_actual >= p_cantidad
      AND bloqueado = false
      AND fecha_caducidad > CURRENT_DATE
    ORDER BY fecha_caducidad ASC
    LIMIT 1;

    IF v_lote_id IS NULL THEN
        RAISE EXCEPTION 'No hay lotes disponibles con stock suficiente para el medicamento %', p_medicamento_id;
    END IF;

    RETURN v_lote_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION veterinaria.seleccionar_lote_fefo(UUID, INTEGER) IS 'Selecciona el lote con fecha de caducidad mas proxima (FEFO)';

-- ============================================================================
-- FIN VETERINARIA FARMACIA SCHEMA
-- ============================================================================