392 lines
14 KiB
PL/PgSQL
392 lines
14 KiB
PL/PgSQL
-- ============================================================================
|
|
-- MIGRACIÓN: Sistema de Ranking de Partners (Clientes/Proveedores)
|
|
-- Fecha: 2025-12-12
|
|
-- Descripción: Crea tablas y funciones para clasificación ABC de partners
|
|
-- Impacto: Verticales que usan módulo de partners/ventas/compras
|
|
-- Rollback: DROP TABLE y DROP FUNCTION incluidos al final
|
|
-- ============================================================================
|
|
|
|
-- ============================================================================
|
|
-- 1. TABLA DE RANKINGS POR PERÍODO
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS core.partner_rankings (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
partner_id UUID NOT NULL REFERENCES core.partners(id) ON DELETE CASCADE,
|
|
company_id UUID REFERENCES auth.companies(id) ON DELETE SET NULL,
|
|
|
|
-- Período de análisis
|
|
period_start DATE NOT NULL,
|
|
period_end DATE NOT NULL,
|
|
|
|
-- Métricas de Cliente
|
|
total_sales DECIMAL(16,2) DEFAULT 0,
|
|
sales_order_count INTEGER DEFAULT 0,
|
|
avg_order_value DECIMAL(16,2) DEFAULT 0,
|
|
|
|
-- Métricas de Proveedor
|
|
total_purchases DECIMAL(16,2) DEFAULT 0,
|
|
purchase_order_count INTEGER DEFAULT 0,
|
|
avg_purchase_value DECIMAL(16,2) DEFAULT 0,
|
|
|
|
-- Métricas de Pago
|
|
avg_payment_days INTEGER,
|
|
on_time_payment_rate DECIMAL(5,2), -- Porcentaje 0-100
|
|
|
|
-- Rankings (posición relativa dentro del período)
|
|
sales_rank INTEGER,
|
|
purchase_rank INTEGER,
|
|
|
|
-- Clasificación ABC
|
|
customer_abc CHAR(1) CHECK (customer_abc IN ('A', 'B', 'C', NULL)),
|
|
supplier_abc CHAR(1) CHECK (supplier_abc IN ('A', 'B', 'C', NULL)),
|
|
|
|
-- Scores calculados (0-100)
|
|
customer_score DECIMAL(5,2) CHECK (customer_score IS NULL OR customer_score BETWEEN 0 AND 100),
|
|
supplier_score DECIMAL(5,2) CHECK (supplier_score IS NULL OR supplier_score BETWEEN 0 AND 100),
|
|
overall_score DECIMAL(5,2) CHECK (overall_score IS NULL OR overall_score BETWEEN 0 AND 100),
|
|
|
|
-- Tendencia vs período anterior
|
|
sales_trend DECIMAL(5,2), -- % cambio
|
|
purchase_trend DECIMAL(5,2),
|
|
|
|
-- Metadatos
|
|
calculated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
UNIQUE(tenant_id, partner_id, company_id, period_start, period_end),
|
|
CHECK (period_end >= period_start)
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- 2. CAMPOS DESNORMALIZADOS EN PARTNERS (para consultas rápidas)
|
|
-- ============================================================================
|
|
|
|
DO $$
|
|
BEGIN
|
|
-- Agregar columnas si no existen
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'core' AND table_name = 'partners'
|
|
AND column_name = 'customer_rank') THEN
|
|
ALTER TABLE core.partners ADD COLUMN customer_rank INTEGER;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'core' AND table_name = 'partners'
|
|
AND column_name = 'supplier_rank') THEN
|
|
ALTER TABLE core.partners ADD COLUMN supplier_rank INTEGER;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'core' AND table_name = 'partners'
|
|
AND column_name = 'customer_abc') THEN
|
|
ALTER TABLE core.partners ADD COLUMN customer_abc CHAR(1);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'core' AND table_name = 'partners'
|
|
AND column_name = 'supplier_abc') THEN
|
|
ALTER TABLE core.partners ADD COLUMN supplier_abc CHAR(1);
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'core' AND table_name = 'partners'
|
|
AND column_name = 'last_ranking_date') THEN
|
|
ALTER TABLE core.partners ADD COLUMN last_ranking_date DATE;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'core' AND table_name = 'partners'
|
|
AND column_name = 'total_sales_ytd') THEN
|
|
ALTER TABLE core.partners ADD COLUMN total_sales_ytd DECIMAL(16,2) DEFAULT 0;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'core' AND table_name = 'partners'
|
|
AND column_name = 'total_purchases_ytd') THEN
|
|
ALTER TABLE core.partners ADD COLUMN total_purchases_ytd DECIMAL(16,2) DEFAULT 0;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ============================================================================
|
|
-- 3. ÍNDICES
|
|
-- ============================================================================
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_partner_rankings_tenant_period
|
|
ON core.partner_rankings(tenant_id, period_start, period_end);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_partner_rankings_partner
|
|
ON core.partner_rankings(partner_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_partner_rankings_abc
|
|
ON core.partner_rankings(tenant_id, customer_abc)
|
|
WHERE customer_abc IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_partners_customer_rank
|
|
ON core.partners(tenant_id, customer_rank)
|
|
WHERE customer_rank IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_partners_supplier_rank
|
|
ON core.partners(tenant_id, supplier_rank)
|
|
WHERE supplier_rank IS NOT NULL;
|
|
|
|
-- ============================================================================
|
|
-- 4. RLS (Row Level Security)
|
|
-- ============================================================================
|
|
|
|
ALTER TABLE core.partner_rankings ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS partner_rankings_tenant_isolation ON core.partner_rankings;
|
|
CREATE POLICY partner_rankings_tenant_isolation ON core.partner_rankings
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- 5. FUNCIÓN: Calcular rankings de partners
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION core.calculate_partner_rankings(
|
|
p_tenant_id UUID,
|
|
p_company_id UUID DEFAULT NULL,
|
|
p_period_start DATE DEFAULT (CURRENT_DATE - INTERVAL '1 year')::date,
|
|
p_period_end DATE DEFAULT CURRENT_DATE
|
|
)
|
|
RETURNS TABLE (
|
|
partners_processed INTEGER,
|
|
customers_ranked INTEGER,
|
|
suppliers_ranked INTEGER
|
|
) AS $$
|
|
DECLARE
|
|
v_partners_processed INTEGER := 0;
|
|
v_customers_ranked INTEGER := 0;
|
|
v_suppliers_ranked INTEGER := 0;
|
|
BEGIN
|
|
-- 1. Calcular métricas de ventas por partner
|
|
INSERT INTO core.partner_rankings (
|
|
tenant_id, partner_id, company_id, period_start, period_end,
|
|
total_sales, sales_order_count, avg_order_value
|
|
)
|
|
SELECT
|
|
p_tenant_id,
|
|
so.partner_id,
|
|
COALESCE(p_company_id, so.company_id),
|
|
p_period_start,
|
|
p_period_end,
|
|
COALESCE(SUM(so.amount_total), 0),
|
|
COUNT(*),
|
|
COALESCE(AVG(so.amount_total), 0)
|
|
FROM sales.sales_orders so
|
|
WHERE so.tenant_id = p_tenant_id
|
|
AND so.status IN ('sale', 'done')
|
|
AND so.order_date BETWEEN p_period_start AND p_period_end
|
|
AND (p_company_id IS NULL OR so.company_id = p_company_id)
|
|
GROUP BY so.partner_id, so.company_id
|
|
ON CONFLICT (tenant_id, partner_id, company_id, period_start, period_end)
|
|
DO UPDATE SET
|
|
total_sales = EXCLUDED.total_sales,
|
|
sales_order_count = EXCLUDED.sales_order_count,
|
|
avg_order_value = EXCLUDED.avg_order_value,
|
|
calculated_at = NOW();
|
|
|
|
GET DIAGNOSTICS v_customers_ranked = ROW_COUNT;
|
|
|
|
-- 2. Calcular métricas de compras por partner
|
|
INSERT INTO core.partner_rankings (
|
|
tenant_id, partner_id, company_id, period_start, period_end,
|
|
total_purchases, purchase_order_count, avg_purchase_value
|
|
)
|
|
SELECT
|
|
p_tenant_id,
|
|
po.partner_id,
|
|
COALESCE(p_company_id, po.company_id),
|
|
p_period_start,
|
|
p_period_end,
|
|
COALESCE(SUM(po.amount_total), 0),
|
|
COUNT(*),
|
|
COALESCE(AVG(po.amount_total), 0)
|
|
FROM purchase.purchase_orders po
|
|
WHERE po.tenant_id = p_tenant_id
|
|
AND po.status IN ('confirmed', 'done')
|
|
AND po.order_date BETWEEN p_period_start AND p_period_end
|
|
AND (p_company_id IS NULL OR po.company_id = p_company_id)
|
|
GROUP BY po.partner_id, po.company_id
|
|
ON CONFLICT (tenant_id, partner_id, company_id, period_start, period_end)
|
|
DO UPDATE SET
|
|
total_purchases = EXCLUDED.total_purchases,
|
|
purchase_order_count = EXCLUDED.purchase_order_count,
|
|
avg_purchase_value = EXCLUDED.avg_purchase_value,
|
|
calculated_at = NOW();
|
|
|
|
GET DIAGNOSTICS v_suppliers_ranked = ROW_COUNT;
|
|
|
|
-- 3. Calcular rankings de clientes (por total de ventas)
|
|
WITH ranked AS (
|
|
SELECT
|
|
id,
|
|
ROW_NUMBER() OVER (ORDER BY total_sales DESC) as rank,
|
|
total_sales,
|
|
SUM(total_sales) OVER () as grand_total,
|
|
SUM(total_sales) OVER (ORDER BY total_sales DESC) as cumulative_total
|
|
FROM core.partner_rankings
|
|
WHERE tenant_id = p_tenant_id
|
|
AND period_start = p_period_start
|
|
AND period_end = p_period_end
|
|
AND total_sales > 0
|
|
)
|
|
UPDATE core.partner_rankings pr
|
|
SET
|
|
sales_rank = r.rank,
|
|
customer_abc = CASE
|
|
WHEN r.cumulative_total / NULLIF(r.grand_total, 0) <= 0.80 THEN 'A'
|
|
WHEN r.cumulative_total / NULLIF(r.grand_total, 0) <= 0.95 THEN 'B'
|
|
ELSE 'C'
|
|
END,
|
|
customer_score = CASE
|
|
WHEN r.rank = 1 THEN 100
|
|
ELSE GREATEST(0, 100 - (r.rank - 1) * 5)
|
|
END
|
|
FROM ranked r
|
|
WHERE pr.id = r.id;
|
|
|
|
-- 4. Calcular rankings de proveedores (por total de compras)
|
|
WITH ranked AS (
|
|
SELECT
|
|
id,
|
|
ROW_NUMBER() OVER (ORDER BY total_purchases DESC) as rank,
|
|
total_purchases,
|
|
SUM(total_purchases) OVER () as grand_total,
|
|
SUM(total_purchases) OVER (ORDER BY total_purchases DESC) as cumulative_total
|
|
FROM core.partner_rankings
|
|
WHERE tenant_id = p_tenant_id
|
|
AND period_start = p_period_start
|
|
AND period_end = p_period_end
|
|
AND total_purchases > 0
|
|
)
|
|
UPDATE core.partner_rankings pr
|
|
SET
|
|
purchase_rank = r.rank,
|
|
supplier_abc = CASE
|
|
WHEN r.cumulative_total / NULLIF(r.grand_total, 0) <= 0.80 THEN 'A'
|
|
WHEN r.cumulative_total / NULLIF(r.grand_total, 0) <= 0.95 THEN 'B'
|
|
ELSE 'C'
|
|
END,
|
|
supplier_score = CASE
|
|
WHEN r.rank = 1 THEN 100
|
|
ELSE GREATEST(0, 100 - (r.rank - 1) * 5)
|
|
END
|
|
FROM ranked r
|
|
WHERE pr.id = r.id;
|
|
|
|
-- 5. Calcular score overall
|
|
UPDATE core.partner_rankings
|
|
SET overall_score = COALESCE(
|
|
(COALESCE(customer_score, 0) + COALESCE(supplier_score, 0)) /
|
|
NULLIF(
|
|
CASE WHEN customer_score IS NOT NULL THEN 1 ELSE 0 END +
|
|
CASE WHEN supplier_score IS NOT NULL THEN 1 ELSE 0 END,
|
|
0
|
|
),
|
|
0
|
|
)
|
|
WHERE tenant_id = p_tenant_id
|
|
AND period_start = p_period_start
|
|
AND period_end = p_period_end;
|
|
|
|
-- 6. Actualizar campos desnormalizados en partners
|
|
UPDATE core.partners p
|
|
SET
|
|
customer_rank = pr.sales_rank,
|
|
supplier_rank = pr.purchase_rank,
|
|
customer_abc = pr.customer_abc,
|
|
supplier_abc = pr.supplier_abc,
|
|
total_sales_ytd = pr.total_sales,
|
|
total_purchases_ytd = pr.total_purchases,
|
|
last_ranking_date = CURRENT_DATE
|
|
FROM core.partner_rankings pr
|
|
WHERE p.id = pr.partner_id
|
|
AND p.tenant_id = p_tenant_id
|
|
AND pr.tenant_id = p_tenant_id
|
|
AND pr.period_start = p_period_start
|
|
AND pr.period_end = p_period_end;
|
|
|
|
GET DIAGNOSTICS v_partners_processed = ROW_COUNT;
|
|
|
|
RETURN QUERY SELECT v_partners_processed, v_customers_ranked, v_suppliers_ranked;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION core.calculate_partner_rankings IS
|
|
'Calcula rankings ABC de partners basado en ventas/compras.
|
|
Parámetros:
|
|
- p_tenant_id: Tenant obligatorio
|
|
- p_company_id: Opcional, filtrar por empresa
|
|
- p_period_start: Inicio del período (default: hace 1 año)
|
|
- p_period_end: Fin del período (default: hoy)';
|
|
|
|
-- ============================================================================
|
|
-- 6. VISTA: Top Partners
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE VIEW core.top_partners_view AS
|
|
SELECT
|
|
p.id,
|
|
p.tenant_id,
|
|
p.name,
|
|
p.email,
|
|
p.is_customer,
|
|
p.is_supplier,
|
|
p.customer_rank,
|
|
p.supplier_rank,
|
|
p.customer_abc,
|
|
p.supplier_abc,
|
|
p.total_sales_ytd,
|
|
p.total_purchases_ytd,
|
|
p.last_ranking_date,
|
|
CASE
|
|
WHEN p.customer_abc = 'A' THEN 'Cliente VIP'
|
|
WHEN p.customer_abc = 'B' THEN 'Cliente Regular'
|
|
WHEN p.customer_abc = 'C' THEN 'Cliente Ocasional'
|
|
ELSE NULL
|
|
END as customer_category,
|
|
CASE
|
|
WHEN p.supplier_abc = 'A' THEN 'Proveedor Estratégico'
|
|
WHEN p.supplier_abc = 'B' THEN 'Proveedor Regular'
|
|
WHEN p.supplier_abc = 'C' THEN 'Proveedor Ocasional'
|
|
ELSE NULL
|
|
END as supplier_category
|
|
FROM core.partners p
|
|
WHERE p.deleted_at IS NULL
|
|
AND (p.customer_rank IS NOT NULL OR p.supplier_rank IS NOT NULL);
|
|
|
|
-- ============================================================================
|
|
-- ROLLBACK SCRIPT
|
|
-- ============================================================================
|
|
/*
|
|
DROP VIEW IF EXISTS core.top_partners_view;
|
|
DROP FUNCTION IF EXISTS core.calculate_partner_rankings(UUID, UUID, DATE, DATE);
|
|
DROP TABLE IF EXISTS core.partner_rankings;
|
|
|
|
ALTER TABLE core.partners
|
|
DROP COLUMN IF EXISTS customer_rank,
|
|
DROP COLUMN IF EXISTS supplier_rank,
|
|
DROP COLUMN IF EXISTS customer_abc,
|
|
DROP COLUMN IF EXISTS supplier_abc,
|
|
DROP COLUMN IF EXISTS last_ranking_date,
|
|
DROP COLUMN IF EXISTS total_sales_ytd,
|
|
DROP COLUMN IF EXISTS total_purchases_ytd;
|
|
*/
|
|
|
|
-- ============================================================================
|
|
-- VERIFICACIÓN
|
|
-- ============================================================================
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'core' AND tablename = 'partner_rankings') THEN
|
|
RAISE EXCEPTION 'Error: Tabla partner_rankings no fue creada';
|
|
END IF;
|
|
|
|
RAISE NOTICE 'Migración completada exitosamente: Partner Rankings';
|
|
END $$;
|