whatsapp/migrations/20260704_lis_08_vistas.sql

-- =============================================================
-- LIS 08 — Vistas del sistema
-- Todas usan CREATE OR REPLACE para poder re-ejecutar sin error.
-- =============================================================

-- -----------------------------------------------------------
-- v_muestras_hoy
-- Muestras del día en el turnero (estado en tiempo real).
-- Usada por: dashboard, widget lugar.php
-- -----------------------------------------------------------
CREATE OR REPLACE VIEW v_muestras_hoy AS
SELECT
    tm.id,
    tm.solicitud_id,
    tm.tipo_muestra,
    COALESCE(lt.nombre, tm.tipo_muestra)  AS tipo_muestra_label,
    lt.color_hex                          AS tipo_muestra_color,
    tm.estado,
    tm.motivo_rechazo,
    tm.recibida_at,
    ts.turno_id,
    tt.codigo                             AS turno_codigo,
    ts.lugar_id,
    tl.nombre                             AS lugar_nombre,
    ts.paciente_id,
    lp.nombre_completo                    AS paciente_nombre,
    lp.numero_documento                   AS paciente_documento,
    DATE(tt.creado_at)                    AS fecha
FROM      turnero_muestras    tm
JOIN      turnero_solicitudes ts  ON ts.id  = tm.solicitud_id
JOIN      turnero_turnos      tt  ON tt.id  = ts.turno_id
JOIN      turnero_lugares     tl  ON tl.id  = ts.lugar_id
LEFT JOIN lab_pacientes       lp  ON lp.id  = ts.paciente_id
LEFT JOIN lab_tipos_muestra   lt  ON lt.codigo = tm.tipo_muestra;

-- -----------------------------------------------------------
-- v_muestras_pendientes_hoy
-- Solo las que faltan recibir hoy.
-- Usada por: contador en dashboard, alerta visual
-- -----------------------------------------------------------
CREATE OR REPLACE VIEW v_muestras_pendientes_hoy AS
SELECT *
FROM v_muestras_hoy
WHERE estado = 'pendiente'
  AND fecha  = CURDATE();

-- -----------------------------------------------------------
-- v_recepcion_completa
-- Histórico Firebird con todas las FK resueltas.
-- Usada por: módulo de consulta histórica (solo lectura)
-- -----------------------------------------------------------
CREATE OR REPLACE VIEW v_recepcion_completa AS
SELECT
    r.id,
    r.fecha_recepcion,
    r.hora_inicio,
    r.prefijo,
    r.num_factura,
    CONCAT(COALESCE(r.prefijo,''), '-',
           LPAD(COALESCE(r.num_factura, 0), 6, '0'))   AS factura,
    r.valor_total,
    r.valor_desc,
    (r.valor_total - r.valor_desc)                     AS valor_neto,
    r.paciente_id,
    r.cod_paciente_legacy,
    COALESCE(lp.nombre_completo, r.cod_paciente_legacy) AS paciente_nombre,
    lp.numero_documento                                  AS paciente_documento,
    lp.tipo_documento                                    AS paciente_tipo_doc,
    r.medico_id,
    r.cod_medico_legacy,
    CONCAT(COALESCE(m.nombres,''), ' ', COALESCE(m.apellidos,'')) AS medico_nombre,
    m.cod_especialidad                                   AS medico_especialidad,
    r.nit_empresa,
    COALESCE(e.nombre, r.nit_empresa)                    AS empresa_nombre,
    r.subgrupo,
    r.diag_ppal,
    r.tipo_usuario,
    r.autorizacion,
    r.usuario
FROM      lab_recepciones r
LEFT JOIN lab_pacientes   lp ON lp.id  = r.paciente_id
LEFT JOIN medicos         m  ON m.id   = r.medico_id
LEFT JOIN lab_empresas    e  ON e.nit  = r.nit_empresa;

-- -----------------------------------------------------------
-- v_relacion_completa
-- Exámenes por recepción con nombre resuelto.
-- -----------------------------------------------------------
CREATE OR REPLACE VIEW v_relacion_completa AS
SELECT
    lr.recepcion_id,
    r.fecha_recepcion,
    r.prefijo,
    r.num_factura,
    lr.cod_examen_legacy,
    lr.exam_tipo_id,
    et.nombre        AS examen_nombre,
    et.categoria     AS examen_categoria,
    et.tipo_muestra  AS tipo_muestra,
    lr.precio,
    lr.reportado,
    lr.fecha_reportado,
    lr.reportado_por,
    lr.validado,
    lr.usuario_valida,
    lr.fecha_valida
FROM      lab_relaciones   lr
JOIN      lab_recepciones  r   ON r.id  = lr.recepcion_id
LEFT JOIN exam_tipos       et  ON et.id = lr.exam_tipo_id;

-- -----------------------------------------------------------
-- v_examen_precio
-- Precio efectivo de cada examen en cada tarifa.
-- Resuelve tarifas derivadas por porcentaje.
-- Usada por: motor de precios en nueva recepción
-- -----------------------------------------------------------
CREATE OR REPLACE VIEW v_examen_precio AS
SELECT
    et.id                                       AS exam_tipo_id,
    et.codigo,
    et.codigo_legacy,
    et.nombre                                   AS examen_nombre,
    et.categoria,
    et.tipo_muestra,
    ti.id                                       AS tarifa_id,
    ti.nombre                                   AS tarifa_nombre,
    ti.porcentaje,
    ti.tarifa_origen,
    lt.valor                                    AS valor_almacenado,
    CASE
        WHEN ti.porcentaje > 0 AND ti.tarifa_origen IS NOT NULL
             AND lt_base.valor IS NOT NULL
        THEN ROUND(lt_base.valor * (1 + ti.porcentaje / 100), 0)
        ELSE lt.valor
    END                                         AS valor_efectivo,
    lt.recargo_urg,
    lt.recargo_fes,
    lt.recargo_esp
FROM      exam_tipos     et
JOIN      lab_tarifas    lt       ON lt.exam_tipo_id = et.id
JOIN      lab_tarifas_id ti       ON ti.id           = lt.tarifa_id
LEFT JOIN lab_tarifas    lt_base  ON lt_base.exam_tipo_id = et.id
                                 AND lt_base.tarifa_id    = ti.tarifa_origen;

-- -----------------------------------------------------------
-- v_paciente_resumen
-- Vista unificada: pacientes del nuevo sistema + migrados.
-- -----------------------------------------------------------
CREATE OR REPLACE VIEW v_paciente_resumen AS
SELECT
    id,
    nombre_completo,
    tipo_documento,
    numero_documento,
    telefono,
    email,
    fecha_nacimiento,
    genero,
    ciudad,
    eps,
    es_historico,
    codigo_legacy,
    created_at
FROM lab_pacientes
WHERE is_active = 1;

-- -----------------------------------------------------------
-- v_turno_muestras_estado
-- Estado agregado de muestras por turno (para cola y dashboard).
-- -----------------------------------------------------------
CREATE OR REPLACE VIEW v_turno_muestras_estado AS
SELECT
    ts.turno_id,
    COUNT(*)                                              AS total_muestras,
    SUM(tm.estado = 'pendiente')                          AS pendientes,
    SUM(tm.estado = 'recibida')                           AS recibidas,
    SUM(tm.estado = 'rechazada')                          AS rechazadas,
    CASE
        WHEN SUM(tm.estado = 'pendiente') = 0 THEN 'completo'
        WHEN SUM(tm.estado = 'recibida')  = 0 AND SUM(tm.estado = 'rechazada') = 0
        THEN 'sin_recibir'
        ELSE 'parcial'
    END                                                   AS estado_global
FROM      turnero_muestras    tm
JOIN      turnero_solicitudes ts ON ts.id = tm.solicitud_id
GROUP BY  ts.turno_id;