-- ═══════════════════════════════════════════════════════════════════════════════
-- SEED 09: FINANZAS Y CONTABILIDAD
-- ═══════════════════════════════════════════════════════════════════════════════
-- Proyecto: ERP Construcción
-- Autor: Claude Opus 4.5
-- Fecha: 2026-02-03
-- Descripción: Catálogo de cuentas, cuentas bancarias, CxP, CxC, pólizas
-- Dependencias: 03-empresa-tenant.sql, 06-estimaciones.sql
-- ═══════════════════════════════════════════════════════════════════════════════

-- UUIDs: 00000000-0000-0000-0009-XXXXXXXXXXXX

BEGIN;

-- ─────────────────────────────────────────────────────────────────────────────────
-- 1. CATÁLOGO DE CUENTAS CONTABLES (Simplificado SAT)
-- ─────────────────────────────────────────────────────────────────────────────────

INSERT INTO finance.chart_of_accounts (
    id, tenant_id, code, name, account_type, nature, status, level, parent_id,
    allows_direct_posting, created_at
) VALUES
-- ACTIVO (1)
('00000000-0000-0000-0009-000000000001', '00000000-0000-0000-0003-000000000001',
 '1000', 'ACTIVO', 'asset', 'debit', 'active', 1, NULL, false, NOW()),

('00000000-0000-0000-0009-000000000002', '00000000-0000-0000-0003-000000000001',
 '1100', 'Activo Circulante', 'asset', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000001', false, NOW()),

('00000000-0000-0000-0009-000000000003', '00000000-0000-0000-0003-000000000001',
 '1101', 'Caja', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),

('00000000-0000-0000-0009-000000000004', '00000000-0000-0000-0003-000000000001',
 '1102', 'Bancos', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),

('00000000-0000-0000-0009-000000000005', '00000000-0000-0000-0003-000000000001',
 '1103', 'Clientes', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),

('00000000-0000-0000-0009-000000000006', '00000000-0000-0000-0003-000000000001',
 '1104', 'Deudores Diversos', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),

('00000000-0000-0000-0009-000000000007', '00000000-0000-0000-0003-000000000001',
 '1105', 'Inventarios', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),

('00000000-0000-0000-0009-000000000008', '00000000-0000-0000-0003-000000000001',
 '1106', 'Anticipos a Proveedores', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),

('00000000-0000-0000-0009-000000000009', '00000000-0000-0000-0003-000000000001',
 '1107', 'IVA Acreditable', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000002', true, NOW()),

('00000000-0000-0000-0009-000000000010', '00000000-0000-0000-0003-000000000001',
 '1200', 'Activo Fijo', 'asset', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000001', false, NOW()),

('00000000-0000-0000-0009-000000000011', '00000000-0000-0000-0003-000000000001',
 '1201', 'Terrenos', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),

('00000000-0000-0000-0009-000000000012', '00000000-0000-0000-0003-000000000001',
 '1202', 'Edificios', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),

('00000000-0000-0000-0009-000000000013', '00000000-0000-0000-0003-000000000001',
 '1203', 'Maquinaria y Equipo', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),

('00000000-0000-0000-0009-000000000014', '00000000-0000-0000-0003-000000000001',
 '1204', 'Equipo de Transporte', 'asset', 'debit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),

('00000000-0000-0000-0009-000000000015', '00000000-0000-0000-0003-000000000001',
 '1205', 'Depreciación Acumulada', 'asset', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000010', true, NOW()),

-- PASIVO (2)
('00000000-0000-0000-0009-000000000020', '00000000-0000-0000-0003-000000000001',
 '2000', 'PASIVO', 'liability', 'credit', 'active', 1, NULL, false, NOW()),

('00000000-0000-0000-0009-000000000021', '00000000-0000-0000-0003-000000000001',
 '2100', 'Pasivo Circulante', 'liability', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000020', false, NOW()),

('00000000-0000-0000-0009-000000000022', '00000000-0000-0000-0003-000000000001',
 '2101', 'Proveedores', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),

('00000000-0000-0000-0009-000000000023', '00000000-0000-0000-0003-000000000001',
 '2102', 'Acreedores Diversos', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),

('00000000-0000-0000-0009-000000000024', '00000000-0000-0000-0003-000000000001',
 '2103', 'IVA por Pagar', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),

('00000000-0000-0000-0009-000000000025', '00000000-0000-0000-0003-000000000001',
 '2104', 'ISR por Pagar', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),

('00000000-0000-0000-0009-000000000026', '00000000-0000-0000-0003-000000000001',
 '2105', 'Retenciones por Pagar', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),

('00000000-0000-0000-0009-000000000027', '00000000-0000-0000-0003-000000000001',
 '2106', 'Anticipos de Clientes', 'liability', 'credit', 'active', 3, '00000000-0000-0000-0009-000000000021', true, NOW()),

-- CAPITAL (3)
('00000000-0000-0000-0009-000000000030', '00000000-0000-0000-0003-000000000001',
 '3000', 'CAPITAL CONTABLE', 'equity', 'credit', 'active', 1, NULL, false, NOW()),

('00000000-0000-0000-0009-000000000031', '00000000-0000-0000-0003-000000000001',
 '3100', 'Capital Social', 'equity', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000030', true, NOW()),

('00000000-0000-0000-0009-000000000032', '00000000-0000-0000-0003-000000000001',
 '3200', 'Utilidades Acumuladas', 'equity', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000030', true, NOW()),

('00000000-0000-0000-0009-000000000033', '00000000-0000-0000-0003-000000000001',
 '3300', 'Utilidad del Ejercicio', 'equity', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000030', true, NOW()),

-- INGRESOS (4)
('00000000-0000-0000-0009-000000000040', '00000000-0000-0000-0003-000000000001',
 '4000', 'INGRESOS', 'income', 'credit', 'active', 1, NULL, false, NOW()),

('00000000-0000-0000-0009-000000000041', '00000000-0000-0000-0003-000000000001',
 '4100', 'Ingresos por Obra', 'income', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000040', true, NOW()),

('00000000-0000-0000-0009-000000000042', '00000000-0000-0000-0003-000000000001',
 '4200', 'Ingresos por Ventas', 'income', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000040', true, NOW()),

('00000000-0000-0000-0009-000000000043', '00000000-0000-0000-0003-000000000001',
 '4300', 'Otros Ingresos', 'income', 'credit', 'active', 2, '00000000-0000-0000-0009-000000000040', true, NOW()),

-- GASTOS (5)
('00000000-0000-0000-0009-000000000050', '00000000-0000-0000-0003-000000000001',
 '5000', 'COSTOS Y GASTOS', 'expense', 'debit', 'active', 1, NULL, false, NOW()),

('00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0003-000000000001',
 '5100', 'Costo de Obra', 'expense', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000050', true, NOW()),

('00000000-0000-0000-0009-000000000052', '00000000-0000-0000-0003-000000000001',
 '5200', 'Gastos de Administración', 'expense', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000050', true, NOW()),

('00000000-0000-0000-0009-000000000053', '00000000-0000-0000-0003-000000000001',
 '5300', 'Gastos Financieros', 'expense', 'debit', 'active', 2, '00000000-0000-0000-0009-000000000050', true, NOW())
ON CONFLICT (id) DO NOTHING;

-- ─────────────────────────────────────────────────────────────────────────────────
-- 2. CUENTAS BANCARIAS
-- ─────────────────────────────────────────────────────────────────────────────────

INSERT INTO finance.bank_accounts (
    id, tenant_id, name, account_number, clabe, account_type, status,
    bank_name, bank_code, currency,
    initial_balance, current_balance, available_balance,
    ledger_account_id, is_default, allows_payments, allows_collections,
    created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000101', '00000000-0000-0000-0003-000000000001',
 'Cuenta Principal BBVA', '0123456789', '012345678901234567', 'checking', 'active',
 'BBVA MEXICO', '012', 'MXN',
 5000000.00, 4250000.00, 4200000.00,
 '00000000-0000-0000-0009-000000000004', true, true, true,
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000102', '00000000-0000-0000-0003-000000000001',
 'Cuenta Nómina Banorte', '9876543210', '072987654321098765', 'checking', 'active',
 'BANORTE', '072', 'MXN',
 500000.00, 320000.00, 320000.00,
 '00000000-0000-0000-0009-000000000004', false, true, false,
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000103', '00000000-0000-0000-0003-000000000001',
 'Cuenta de Inversión Santander', '5555666677', '014555566667700001', 'investment', 'active',
 'SANTANDER', '014', 'MXN',
 2000000.00, 2050000.00, 0.00,
 '00000000-0000-0000-0009-000000000004', false, false, false,
 NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;

-- ─────────────────────────────────────────────────────────────────────────────────
-- 3. CUENTAS POR PAGAR
-- ─────────────────────────────────────────────────────────────────────────────────

INSERT INTO finance.accounts_payable (
    id, tenant_id, document_number, document_type,
    supplier_id, supplier_name, supplier_rfc,
    status, invoice_date, due_date,
    subtotal, tax_amount, retention_amount, total_amount, paid_amount, balance,
    currency, payment_terms, payment_days,
    expense_account_id, project_id,
    created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000201', '00000000-0000-0000-0003-000000000001',
 'FAC-PROV-001', 'invoice',
 '00000000-0000-0000-0004-000000000801', 'Cimientos del Norte S.A. de C.V.', 'CNO010101AAA',
 'paid', '2024-03-20', '2024-04-20',
 283500.00, 45360.00, 0.00, 328860.00, 328860.00, 0.00,
 'MXN', 'Neto 30 días', 30,
 '00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0004-000000000101',
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000202', '00000000-0000-0000-0003-000000000001',
 'FAC-PROV-002', 'invoice',
 '00000000-0000-0000-0004-000000000802', 'Electrificaciones AGS S.A. de C.V.', 'EAG010101BBB',
 'pending', '2024-05-20', '2024-06-20',
 254800.00, 40768.00, 0.00, 295568.00, 0.00, 295568.00,
 'MXN', 'Neto 30 días', 30,
 '00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0004-000000000101',
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000203', '00000000-0000-0000-0003-000000000001',
 'FAC-PROV-003', 'invoice',
 '00000000-0000-0000-0004-000000000803', 'Plomería Integral de Aguascalientes S.A. de C.V.', 'PIA010101CCC',
 'pending', '2024-05-25', '2024-06-25',
 180000.00, 28800.00, 0.00, 208800.00, 0.00, 208800.00,
 'MXN', 'Neto 30 días', 30,
 '00000000-0000-0000-0009-000000000051', '00000000-0000-0000-0004-000000000101',
 NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;

-- ─────────────────────────────────────────────────────────────────────────────────
-- 4. CUENTAS POR COBRAR
-- ─────────────────────────────────────────────────────────────────────────────────

INSERT INTO finance.accounts_receivable (
    id, tenant_id, document_number, document_type,
    customer_id, customer_name, customer_rfc,
    status, invoice_date, due_date,
    subtotal, tax_amount, retention_amount, total_amount, collected_amount, balance,
    currency, payment_terms, payment_days,
    income_account_id, project_id,
    created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000301', '00000000-0000-0000-0003-000000000001',
 'FAC-CLT-001', 'estimation',
 '00000000-0000-0000-0002-000000000112', 'Cliente Demo', 'XAXX010101000',
 'collected', '2024-04-01', '2024-04-30',
 1900000.00, 304000.00, 0.00, 2204000.00, 2204000.00, 0.00,
 'MXN', 'Neto 30 días', 30,
 '00000000-0000-0000-0009-000000000042', '00000000-0000-0000-0004-000000000101',
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000302', '00000000-0000-0000-0003-000000000001',
 'FAC-CLT-002', 'estimation',
 '00000000-0000-0000-0002-000000000112', 'Cliente Demo', 'XAXX010101000',
 'pending', '2024-05-15', '2024-06-15',
 1880000.00, 300800.00, 0.00, 2180800.00, 0.00, 2180800.00,
 'MXN', 'Neto 30 días', 30,
 '00000000-0000-0000-0009-000000000042', '00000000-0000-0000-0004-000000000101',
 NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;

-- ─────────────────────────────────────────────────────────────────────────────────
-- 5. PÓLIZAS CONTABLES (Ejemplo balanceado)
-- ─────────────────────────────────────────────────────────────────────────────────

INSERT INTO finance.accounting_entries (
    id, tenant_id, entry_number, entry_type, entry_date, status,
    description, fiscal_year, fiscal_period,
    total_debit, total_credit, is_balanced,
    currency, exchange_rate, posted_at, posted_by_id,
    created_at, created_by
) VALUES
('00000000-0000-0000-0009-000000000401', '00000000-0000-0000-0003-000000000001',
 'POL-2024-001', 'purchase', '2024-03-20', 'posted',
 'Registro factura proveedor cimentaciones', 2024, 3,
 328860.00, 328860.00, true,
 'MXN', 1.0, '2024-03-20', '00000000-0000-0000-0002-000000000107',
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000402', '00000000-0000-0000-0003-000000000001',
 'POL-2024-002', 'payment', '2024-04-15', 'posted',
 'Pago a proveedor cimentaciones', 2024, 4,
 328860.00, 328860.00, true,
 'MXN', 1.0, '2024-04-15', '00000000-0000-0000-0002-000000000107',
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000403', '00000000-0000-0000-0003-000000000001',
 'POL-2024-003', 'sale', '2024-04-01', 'posted',
 'Facturación venta lote A-01', 2024, 4,
 2204000.00, 2204000.00, true,
 'MXN', 1.0, '2024-04-01', '00000000-0000-0000-0002-000000000107',
 NOW(), '00000000-0000-0000-0002-000000000107'),

('00000000-0000-0000-0009-000000000404', '00000000-0000-0000-0003-000000000001',
 'POL-2024-004', 'collection', '2024-04-25', 'posted',
 'Cobro cliente lote A-01', 2024, 4,
 2204000.00, 2204000.00, true,
 'MXN', 1.0, '2024-04-25', '00000000-0000-0000-0002-000000000107',
 NOW(), '00000000-0000-0000-0002-000000000107')
ON CONFLICT (id) DO NOTHING;

-- ─────────────────────────────────────────────────────────────────────────────────
-- 6. LÍNEAS DE PÓLIZA
-- ─────────────────────────────────────────────────────────────────────────────────

INSERT INTO finance.accounting_entry_lines (
    id, tenant_id, entry_id, line_number,
    account_id, account_code, description,
    debit, credit,
    created_at
) VALUES
-- POL-001: Compra
('00000000-0000-0000-0009-000000000501', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000401', 1,
 '00000000-0000-0000-0009-000000000051', '5100', 'Costo de obra - Cimentación',
 283500.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000502', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000401', 2,
 '00000000-0000-0000-0009-000000000009', '1107', 'IVA Acreditable',
 45360.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000503', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000401', 3,
 '00000000-0000-0000-0009-000000000022', '2101', 'Proveedores',
 0.00, 328860.00, NOW()),

-- POL-002: Pago
('00000000-0000-0000-0009-000000000504', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000402', 1,
 '00000000-0000-0000-0009-000000000022', '2101', 'Proveedores',
 328860.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000505', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000402', 2,
 '00000000-0000-0000-0009-000000000004', '1102', 'Bancos',
 0.00, 328860.00, NOW()),

-- POL-003: Venta
('00000000-0000-0000-0009-000000000506', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000403', 1,
 '00000000-0000-0000-0009-000000000005', '1103', 'Clientes',
 2204000.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000507', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000403', 2,
 '00000000-0000-0000-0009-000000000042', '4200', 'Ingresos por Ventas',
 0.00, 1900000.00, NOW()),
('00000000-0000-0000-0009-000000000508', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000403', 3,
 '00000000-0000-0000-0009-000000000024', '2103', 'IVA por Pagar',
 0.00, 304000.00, NOW()),

-- POL-004: Cobro
('00000000-0000-0000-0009-000000000509', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000404', 1,
 '00000000-0000-0000-0009-000000000004', '1102', 'Bancos',
 2204000.00, 0.00, NOW()),
('00000000-0000-0000-0009-000000000510', '00000000-0000-0000-0003-000000000001',
 '00000000-0000-0000-0009-000000000404', 2,
 '00000000-0000-0000-0009-000000000005', '1103', 'Clientes',
 0.00, 2204000.00, NOW())
ON CONFLICT (id) DO NOTHING;

-- ─────────────────────────────────────────────────────────────────────────────────
-- VERIFICACIÓN
-- ─────────────────────────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_cuentas INTEGER;
    v_bancos INTEGER;
    v_cxp INTEGER;
    v_cxc INTEGER;
    v_polizas INTEGER;
    v_balance_check INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cuentas FROM finance.chart_of_accounts;
    SELECT COUNT(*) INTO v_bancos FROM finance.bank_accounts;
    SELECT COUNT(*) INTO v_cxp FROM finance.accounts_payable;
    SELECT COUNT(*) INTO v_cxc FROM finance.accounts_receivable;
    SELECT COUNT(*) INTO v_polizas FROM finance.accounting_entries;

    -- Verificar balance de pólizas
    SELECT COUNT(*) INTO v_balance_check
    FROM finance.accounting_entries e
    WHERE ABS(total_debit - total_credit) > 0.01;

    RAISE NOTICE '══════════════════════════════════════════════════════════════';
    RAISE NOTICE 'SEED 09 - FINANZAS - COMPLETADO';
    RAISE NOTICE '══════════════════════════════════════════════════════════════';
    RAISE NOTICE 'Cuentas contables: %', v_cuentas;
    RAISE NOTICE 'Cuentas bancarias: %', v_bancos;
    RAISE NOTICE 'Cuentas por pagar: %', v_cxp;
    RAISE NOTICE 'Cuentas por cobrar: %', v_cxc;
    RAISE NOTICE 'Pólizas contables: %', v_polizas;
    RAISE NOTICE 'Pólizas desbalanceadas: % (debe ser 0)', v_balance_check;
    RAISE NOTICE '══════════════════════════════════════════════════════════════';
END $$;

COMMIT;

-- ═══════════════════════════════════════════════════════════════════════════════
-- FIN SEED 09
-- ═══════════════════════════════════════════════════════════════════════════════