-- =============================================================
-- ARCHIVO: 04-seed-catalogs.sql
-- DESCRIPCION: Datos semilla para catalogos maestros
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-18
-- =============================================================

-- =====================
-- PAISES (ISO 3166-1)
-- =====================

INSERT INTO core.countries (code, code_alpha3, name, phone_code, currency_code) VALUES
-- America del Norte
('MX', 'MEX', 'México', '+52', 'MXN'),
('US', 'USA', 'Estados Unidos', '+1', 'USD'),
('CA', 'CAN', 'Canadá', '+1', 'CAD'),
-- America Central
('GT', 'GTM', 'Guatemala', '+502', 'GTQ'),
('BZ', 'BLZ', 'Belice', '+501', 'BZD'),
('SV', 'SLV', 'El Salvador', '+503', 'USD'),
('HN', 'HND', 'Honduras', '+504', 'HNL'),
('NI', 'NIC', 'Nicaragua', '+505', 'NIO'),
('CR', 'CRI', 'Costa Rica', '+506', 'CRC'),
('PA', 'PAN', 'Panamá', '+507', 'PAB'),
-- America del Sur
('CO', 'COL', 'Colombia', '+57', 'COP'),
('VE', 'VEN', 'Venezuela', '+58', 'VES'),
('EC', 'ECU', 'Ecuador', '+593', 'USD'),
('PE', 'PER', 'Perú', '+51', 'PEN'),
('BO', 'BOL', 'Bolivia', '+591', 'BOB'),
('CL', 'CHL', 'Chile', '+56', 'CLP'),
('AR', 'ARG', 'Argentina', '+54', 'ARS'),
('UY', 'URY', 'Uruguay', '+598', 'UYU'),
('PY', 'PRY', 'Paraguay', '+595', 'PYG'),
('BR', 'BRA', 'Brasil', '+55', 'BRL'),
-- Europa
('ES', 'ESP', 'España', '+34', 'EUR'),
('DE', 'DEU', 'Alemania', '+49', 'EUR'),
('FR', 'FRA', 'Francia', '+33', 'EUR'),
('IT', 'ITA', 'Italia', '+39', 'EUR'),
('GB', 'GBR', 'Reino Unido', '+44', 'GBP'),
('PT', 'PRT', 'Portugal', '+351', 'EUR'),
('NL', 'NLD', 'Países Bajos', '+31', 'EUR'),
('BE', 'BEL', 'Bélgica', '+32', 'EUR'),
('CH', 'CHE', 'Suiza', '+41', 'CHF'),
-- Asia
('CN', 'CHN', 'China', '+86', 'CNY'),
('JP', 'JPN', 'Japón', '+81', 'JPY'),
('KR', 'KOR', 'Corea del Sur', '+82', 'KRW'),
('IN', 'IND', 'India', '+91', 'INR')
ON CONFLICT (code) DO NOTHING;

-- =====================
-- ESTADOS DE MEXICO
-- =====================

-- Obtener ID de México
DO $$
DECLARE
    mexico_id UUID;
BEGIN
    SELECT id INTO mexico_id FROM core.countries WHERE code = 'MX';

    IF mexico_id IS NOT NULL THEN
        INSERT INTO core.states (country_id, code, name, timezone, is_active) VALUES
        (mexico_id, 'AGU', 'Aguascalientes', 'America/Mexico_City', true),
        (mexico_id, 'BCN', 'Baja California', 'America/Tijuana', true),
        (mexico_id, 'BCS', 'Baja California Sur', 'America/Mazatlan', true),
        (mexico_id, 'CAM', 'Campeche', 'America/Merida', true),
        (mexico_id, 'CHP', 'Chiapas', 'America/Mexico_City', true),
        (mexico_id, 'CHH', 'Chihuahua', 'America/Chihuahua', true),
        (mexico_id, 'CMX', 'Ciudad de México', 'America/Mexico_City', true),
        (mexico_id, 'COA', 'Coahuila', 'America/Monterrey', true),
        (mexico_id, 'COL', 'Colima', 'America/Mexico_City', true),
        (mexico_id, 'DUR', 'Durango', 'America/Monterrey', true),
        (mexico_id, 'GUA', 'Guanajuato', 'America/Mexico_City', true),
        (mexico_id, 'GRO', 'Guerrero', 'America/Mexico_City', true),
        (mexico_id, 'HID', 'Hidalgo', 'America/Mexico_City', true),
        (mexico_id, 'JAL', 'Jalisco', 'America/Mexico_City', true),
        (mexico_id, 'MEX', 'Estado de México', 'America/Mexico_City', true),
        (mexico_id, 'MIC', 'Michoacán', 'America/Mexico_City', true),
        (mexico_id, 'MOR', 'Morelos', 'America/Mexico_City', true),
        (mexico_id, 'NAY', 'Nayarit', 'America/Mazatlan', true),
        (mexico_id, 'NLE', 'Nuevo León', 'America/Monterrey', true),
        (mexico_id, 'OAX', 'Oaxaca', 'America/Mexico_City', true),
        (mexico_id, 'PUE', 'Puebla', 'America/Mexico_City', true),
        (mexico_id, 'QUE', 'Querétaro', 'America/Mexico_City', true),
        (mexico_id, 'ROO', 'Quintana Roo', 'America/Cancun', true),
        (mexico_id, 'SLP', 'San Luis Potosí', 'America/Mexico_City', true),
        (mexico_id, 'SIN', 'Sinaloa', 'America/Mazatlan', true),
        (mexico_id, 'SON', 'Sonora', 'America/Hermosillo', true),
        (mexico_id, 'TAB', 'Tabasco', 'America/Mexico_City', true),
        (mexico_id, 'TAM', 'Tamaulipas', 'America/Monterrey', true),
        (mexico_id, 'TLA', 'Tlaxcala', 'America/Mexico_City', true),
        (mexico_id, 'VER', 'Veracruz', 'America/Mexico_City', true),
        (mexico_id, 'YUC', 'Yucatán', 'America/Merida', true),
        (mexico_id, 'ZAC', 'Zacatecas', 'America/Mexico_City', true)
        ON CONFLICT (country_id, code) DO NOTHING;
    END IF;
END $$;

-- =====================
-- MONEDAS (ISO 4217)
-- =====================

INSERT INTO core.currencies (code, name, symbol, decimals, rounding, active) VALUES
-- Americas
('MXN', 'Peso Mexicano', '$', 2, 0.01, true),
('USD', 'Dólar Estadounidense', '$', 2, 0.01, true),
('CAD', 'Dólar Canadiense', '$', 2, 0.01, true),
('BRL', 'Real Brasileño', 'R$', 2, 0.01, true),
('ARS', 'Peso Argentino', '$', 2, 0.01, true),
('CLP', 'Peso Chileno', '$', 0, 1, true),
('COP', 'Peso Colombiano', '$', 0, 1, true),
('PEN', 'Sol Peruano', 'S/', 2, 0.01, true),
('GTQ', 'Quetzal', 'Q', 2, 0.01, true),
-- Europa
('EUR', 'Euro', '€', 2, 0.01, true),
('GBP', 'Libra Esterlina', '£', 2, 0.01, true),
('CHF', 'Franco Suizo', 'Fr', 2, 0.01, true),
-- Asia
('JPY', 'Yen Japonés', '¥', 0, 1, true),
('CNY', 'Yuan Chino', '¥', 2, 0.01, true),
('KRW', 'Won Surcoreano', '₩', 0, 1, true),
('INR', 'Rupia India', '₹', 2, 0.01, true)
ON CONFLICT (code) DO NOTHING;

-- =====================
-- TIPOS DE CAMBIO INICIALES (MXN base)
-- =====================

DO $$
DECLARE
    mxn_id UUID;
    usd_id UUID;
    eur_id UUID;
    cad_id UUID;
BEGIN
    SELECT id INTO mxn_id FROM core.currencies WHERE code = 'MXN';
    SELECT id INTO usd_id FROM core.currencies WHERE code = 'USD';
    SELECT id INTO eur_id FROM core.currencies WHERE code = 'EUR';
    SELECT id INTO cad_id FROM core.currencies WHERE code = 'CAD';

    IF mxn_id IS NOT NULL AND usd_id IS NOT NULL THEN
        INSERT INTO core.currency_rates (tenant_id, from_currency_id, to_currency_id, rate, rate_date, source)
        VALUES
        (NULL, usd_id, mxn_id, 17.50, CURRENT_DATE, 'manual'),
        (NULL, eur_id, mxn_id, 19.20, CURRENT_DATE, 'manual'),
        (NULL, cad_id, mxn_id, 13.10, CURRENT_DATE, 'manual')
        ON CONFLICT DO NOTHING;
    END IF;
END $$;

-- =====================
-- CATEGORIAS DE UOM (Globales)
-- =====================

INSERT INTO core.uom_categories (tenant_id, name, description) VALUES
(NULL, 'Unidad', 'Unidades contables'),
(NULL, 'Peso', 'Unidades de peso/masa'),
(NULL, 'Longitud', 'Unidades de longitud'),
(NULL, 'Volumen', 'Unidades de volumen'),
(NULL, 'Área', 'Unidades de área'),
(NULL, 'Tiempo', 'Unidades de tiempo')
ON CONFLICT (tenant_id, name) DO NOTHING;

-- =====================
-- UOM (Globales)
-- =====================

DO $$
DECLARE
    cat_unit UUID;
    cat_peso UUID;
    cat_longitud UUID;
    cat_volumen UUID;
    cat_area UUID;
    cat_tiempo UUID;
BEGIN
    SELECT id INTO cat_unit FROM core.uom_categories WHERE name = 'Unidad' AND tenant_id IS NULL;
    SELECT id INTO cat_peso FROM core.uom_categories WHERE name = 'Peso' AND tenant_id IS NULL;
    SELECT id INTO cat_longitud FROM core.uom_categories WHERE name = 'Longitud' AND tenant_id IS NULL;
    SELECT id INTO cat_volumen FROM core.uom_categories WHERE name = 'Volumen' AND tenant_id IS NULL;
    SELECT id INTO cat_area FROM core.uom_categories WHERE name = 'Área' AND tenant_id IS NULL;
    SELECT id INTO cat_tiempo FROM core.uom_categories WHERE name = 'Tiempo' AND tenant_id IS NULL;

    -- Unidad
    IF cat_unit IS NOT NULL THEN
        INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
        (NULL, cat_unit, 'Pieza', 'pz', 'reference', 1, true),
        (NULL, cat_unit, 'Docena', 'doc', 'bigger', 12, true),
        (NULL, cat_unit, 'Ciento', 'cto', 'bigger', 100, true),
        (NULL, cat_unit, 'Millar', 'mil', 'bigger', 1000, true),
        (NULL, cat_unit, 'Par', 'par', 'bigger', 2, true)
        ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
    END IF;

    -- Peso
    IF cat_peso IS NOT NULL THEN
        INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
        (NULL, cat_peso, 'Kilogramo', 'kg', 'reference', 1, true),
        (NULL, cat_peso, 'Gramo', 'g', 'smaller', 0.001, true),
        (NULL, cat_peso, 'Miligramo', 'mg', 'smaller', 0.000001, true),
        (NULL, cat_peso, 'Tonelada', 't', 'bigger', 1000, true),
        (NULL, cat_peso, 'Libra', 'lb', 'smaller', 0.453592, true),
        (NULL, cat_peso, 'Onza', 'oz', 'smaller', 0.0283495, true)
        ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
    END IF;

    -- Longitud
    IF cat_longitud IS NOT NULL THEN
        INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
        (NULL, cat_longitud, 'Metro', 'm', 'reference', 1, true),
        (NULL, cat_longitud, 'Centímetro', 'cm', 'smaller', 0.01, true),
        (NULL, cat_longitud, 'Milímetro', 'mm', 'smaller', 0.001, true),
        (NULL, cat_longitud, 'Kilómetro', 'km', 'bigger', 1000, true),
        (NULL, cat_longitud, 'Pulgada', 'in', 'smaller', 0.0254, true),
        (NULL, cat_longitud, 'Pie', 'ft', 'smaller', 0.3048, true),
        (NULL, cat_longitud, 'Yarda', 'yd', 'smaller', 0.9144, true)
        ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
    END IF;

    -- Volumen
    IF cat_volumen IS NOT NULL THEN
        INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
        (NULL, cat_volumen, 'Litro', 'L', 'reference', 1, true),
        (NULL, cat_volumen, 'Mililitro', 'mL', 'smaller', 0.001, true),
        (NULL, cat_volumen, 'Metro cúbico', 'm³', 'bigger', 1000, true),
        (NULL, cat_volumen, 'Galón US', 'gal', 'bigger', 3.78541, true),
        (NULL, cat_volumen, 'Onza líquida', 'fl oz', 'smaller', 0.0295735, true)
        ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
    END IF;

    -- Área
    IF cat_area IS NOT NULL THEN
        INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
        (NULL, cat_area, 'Metro cuadrado', 'm²', 'reference', 1, true),
        (NULL, cat_area, 'Centímetro cuadrado', 'cm²', 'smaller', 0.0001, true),
        (NULL, cat_area, 'Kilómetro cuadrado', 'km²', 'bigger', 1000000, true),
        (NULL, cat_area, 'Hectárea', 'ha', 'bigger', 10000, true),
        (NULL, cat_area, 'Pie cuadrado', 'ft²', 'smaller', 0.092903, true)
        ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
    END IF;

    -- Tiempo
    IF cat_tiempo IS NOT NULL THEN
        INSERT INTO core.uom (tenant_id, category_id, name, symbol, uom_type, factor, is_active) VALUES
        (NULL, cat_tiempo, 'Hora', 'h', 'reference', 1, true),
        (NULL, cat_tiempo, 'Minuto', 'min', 'smaller', 0.0166667, true),
        (NULL, cat_tiempo, 'Día', 'd', 'bigger', 24, true),
        (NULL, cat_tiempo, 'Semana', 'sem', 'bigger', 168, true)
        ON CONFLICT (tenant_id, category_id, name) DO NOTHING;
    END IF;
END $$;

-- =====================
-- FIN DEL ARCHIVO
-- =====================