erp-construccion-database-v2/schemas/12-analytics-kpis-ddl.sql

-- ============================================================================
-- 12-analytics-kpis-ddl.sql
-- Schema: reports (extension)
-- ERP Construccion - KPIs Configurables (GAP-001)
-- ============================================================================
-- Descripcion: Configuracion dinamica de KPIs incluyendo:
--   - Definicion de KPIs con formulas configurables
--   - Valores calculados historicos
--   - Umbrales y semaforizacion
-- ============================================================================
-- Autor: Claude-Especialista-BD
-- Fecha: 2026-02-04
-- Version: 1.0.0
-- Tarea: TASK-2026-02-03-ANALISIS-MODELADO-INTEGRAL / GAP-001
-- ============================================================================

-- Usar schema reports existente
-- CREATE SCHEMA IF NOT EXISTS reports;

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

-- ----------------------------------------------------------------------------
-- 1. Configuracion de KPIs
-- Permite definir KPIs dinamicos con formulas configurables
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reports.kpis_config (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Identificacion
    code VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,

    -- Clasificacion
    category VARCHAR(50) NOT NULL, -- financial, progress, quality, hse, hr, inventory, operational
    module VARCHAR(50) NOT NULL,   -- MAI-006, MAE-014, etc.

    -- Formula de calculo
    formula TEXT NOT NULL,         -- SQL o expresion matematica
    formula_type VARCHAR(20) NOT NULL DEFAULT 'sql', -- sql, expression, function
    query_function VARCHAR(255),   -- Nombre de funcion PL/pgSQL si aplica

    -- Parametros de la formula
    parameters_schema JSONB DEFAULT '{}',

    -- Unidad y formato
    unit VARCHAR(20),              -- %, $, hrs, dias, etc.
    decimal_places INTEGER DEFAULT 2,
    format_pattern VARCHAR(50),    -- Patron de formato para display

    -- Umbrales de semaforizacion
    target_value DECIMAL(18,4),
    threshold_green DECIMAL(18,4), -- Valor >= este es verde
    threshold_yellow DECIMAL(18,4), -- Valor >= este es amarillo, < es rojo
    invert_colors BOOLEAN DEFAULT FALSE, -- TRUE si menor es mejor

    -- Frecuencia de calculo
    calculation_frequency VARCHAR(20) DEFAULT 'daily', -- realtime, hourly, daily, weekly, monthly

    -- Visualizacion
    display_order INTEGER DEFAULT 0,
    icon VARCHAR(50),
    color VARCHAR(20),

    -- Estado
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_system BOOLEAN NOT NULL DEFAULT FALSE, -- TRUE = no editable por usuario

    -- Metadatos
    metadata JSONB,

    -- Auditoria
    created_by UUID,
    updated_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,

    CONSTRAINT uq_kpis_config_tenant_code UNIQUE (tenant_id, code)
);

-- ----------------------------------------------------------------------------
-- 2. Valores Calculados de KPIs
-- Almacena los valores calculados periodicamente
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reports.kpis_values (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- KPI
    kpi_id UUID NOT NULL REFERENCES reports.kpis_config(id) ON DELETE CASCADE,

    -- Periodo
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    period_type VARCHAR(20) NOT NULL DEFAULT 'daily', -- daily, weekly, monthly, quarterly, yearly

    -- Contexto opcional
    project_id UUID,           -- Fraccionamiento/Obra especifica
    department_id UUID,        -- Departamento especifico

    -- Valor calculado
    value DECIMAL(18,4) NOT NULL,
    previous_value DECIMAL(18,4),

    -- Comparacion con objetivo
    target_value DECIMAL(18,4),
    variance_value DECIMAL(18,4),     -- value - target_value
    variance_percentage DECIMAL(8,2), -- ((value - target) / target) * 100

    -- Semaforizacion calculada
    status VARCHAR(10), -- green, yellow, red
    is_on_target BOOLEAN,

    -- Tendencia
    trend_direction VARCHAR(10), -- up, down, stable
    change_percentage DECIMAL(8,2),

    -- Desglose
    breakdown JSONB, -- Datos adicionales de calculo

    -- Calculo
    calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    calculation_duration_ms INTEGER,
    calculation_error TEXT,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

-- KPIs Config
CREATE INDEX IF NOT EXISTS idx_kpis_config_tenant ON reports.kpis_config(tenant_id);
CREATE INDEX IF NOT EXISTS idx_kpis_config_tenant_category ON reports.kpis_config(tenant_id, category);
CREATE INDEX IF NOT EXISTS idx_kpis_config_tenant_module ON reports.kpis_config(tenant_id, module);
CREATE INDEX IF NOT EXISTS idx_kpis_config_active ON reports.kpis_config(tenant_id, is_active) WHERE is_active = TRUE;

-- KPIs Values
CREATE INDEX IF NOT EXISTS idx_kpis_values_tenant ON reports.kpis_values(tenant_id);
CREATE INDEX IF NOT EXISTS idx_kpis_values_kpi ON reports.kpis_values(kpi_id);
CREATE INDEX IF NOT EXISTS idx_kpis_values_period ON reports.kpis_values(tenant_id, period_start, period_end);
CREATE INDEX IF NOT EXISTS idx_kpis_values_kpi_period ON reports.kpis_values(kpi_id, period_start DESC);
CREATE INDEX IF NOT EXISTS idx_kpis_values_project ON reports.kpis_values(tenant_id, project_id) WHERE project_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_kpis_values_calculated ON reports.kpis_values(calculated_at DESC);

-- ============================================================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================================================

ALTER TABLE reports.kpis_config ENABLE ROW LEVEL SECURITY;
ALTER TABLE reports.kpis_values ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- TRIGGERS DE AUDITORIA
-- ============================================================================

CREATE OR REPLACE FUNCTION reports.set_kpis_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_kpis_config_updated_at ON reports.kpis_config;
CREATE TRIGGER trg_kpis_config_updated_at
    BEFORE UPDATE ON reports.kpis_config
    FOR EACH ROW EXECUTE FUNCTION reports.set_kpis_updated_at();

-- ============================================================================
-- FUNCIONES AUXILIARES
-- ============================================================================

-- Funcion para calcular un KPI especifico
CREATE OR REPLACE FUNCTION reports.calculate_kpi(
    p_kpi_id UUID,
    p_tenant_id UUID,
    p_period_start DATE,
    p_period_end DATE,
    p_project_id UUID DEFAULT NULL
)
RETURNS DECIMAL(18,4) AS $$
DECLARE
    v_kpi RECORD;
    v_result DECIMAL(18,4);
BEGIN
    -- Obtener configuracion del KPI
    SELECT * INTO v_kpi
    FROM reports.kpis_config
    WHERE id = p_kpi_id AND tenant_id = p_tenant_id AND is_active = TRUE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'KPI not found or inactive: %', p_kpi_id;
    END IF;

    -- Si es una funcion, ejecutarla
    IF v_kpi.formula_type = 'function' AND v_kpi.query_function IS NOT NULL THEN
        EXECUTE format('SELECT %s($1, $2, $3, $4)', v_kpi.query_function)
        INTO v_result
        USING p_tenant_id, p_period_start, p_period_end, p_project_id;
    ELSE
        -- Ejecutar formula SQL directamente (con cuidado de seguridad)
        -- En produccion esto debe ser mas restrictivo
        EXECUTE v_kpi.formula
        INTO v_result
        USING p_tenant_id, p_period_start, p_period_end, p_project_id;
    END IF;

    RETURN v_result;
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Error calculating KPI %: %', p_kpi_id, SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Funcion para determinar color de semaforo
CREATE OR REPLACE FUNCTION reports.get_kpi_status(
    p_value DECIMAL(18,4),
    p_threshold_green DECIMAL(18,4),
    p_threshold_yellow DECIMAL(18,4),
    p_invert_colors BOOLEAN DEFAULT FALSE
)
RETURNS VARCHAR(10) AS $$
BEGIN
    IF p_threshold_green IS NULL OR p_threshold_yellow IS NULL THEN
        RETURN NULL;
    END IF;

    IF p_invert_colors THEN
        -- Menor es mejor
        IF p_value <= p_threshold_green THEN
            RETURN 'green';
        ELSIF p_value <= p_threshold_yellow THEN
            RETURN 'yellow';
        ELSE
            RETURN 'red';
        END IF;
    ELSE
        -- Mayor es mejor
        IF p_value >= p_threshold_green THEN
            RETURN 'green';
        ELSIF p_value >= p_threshold_yellow THEN
            RETURN 'yellow';
        ELSE
            RETURN 'red';
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- ============================================================================
-- COMENTARIOS DE DOCUMENTACION
-- ============================================================================

COMMENT ON TABLE reports.kpis_config IS 'Configuracion de KPIs dinamicos con formulas (GAP-001)';
COMMENT ON COLUMN reports.kpis_config.formula IS 'Formula SQL o expresion para calcular el KPI';
COMMENT ON COLUMN reports.kpis_config.threshold_green IS 'Umbral para status verde';
COMMENT ON COLUMN reports.kpis_config.threshold_yellow IS 'Umbral para status amarillo';
COMMENT ON COLUMN reports.kpis_config.invert_colors IS 'TRUE si valores menores son mejores';

COMMENT ON TABLE reports.kpis_values IS 'Valores calculados historicos de KPIs (GAP-001)';
COMMENT ON COLUMN reports.kpis_values.variance_value IS 'Diferencia absoluta: value - target';
COMMENT ON COLUMN reports.kpis_values.variance_percentage IS 'Diferencia porcentual respecto al target';

-- ============================================================================
-- FIN DEL SCRIPT
-- ============================================================================