<?php

declare(strict_types=1);

/**
 * Script de inicialización de datos de prueba (seeding) completo para la empresa demo.
 * Ejecutar con: php bin/db-seed-demo-data.php
 */

require_once dirname(__DIR__) . '/config/bootstrap.php';

use ApiModel\Infrastructure\Persistence\Database;

echo "=== INICIANDO SEEDING DE DATOS DE PRUEBA COMPLETO ===\n";

try {
    $pdo = Database::connect();
    echo "Conectado a la base de datos con éxito.\n";

    // 1. Limpieza de datos transaccionales existentes para evitar conflictos al re-ejecutar
    echo "Limpiando tablas de datos anteriores para asegurar un seed limpio...\n";

    // Orden de eliminación para respetar llaves foráneas
    $tablesToClean = [
        'public.payment_receive_lines',
        'public.payment_receives',
        'public.sales_adjustment_lines',
        'public.sales_adjustments',
        'public.sales_invoice_lines',
        'public.sales_invoices',
        'public.purchases_payment_lines',
        'public.purchases_payments',
        'public.purchases_bill_lines',
        'public.purchases_bills',
        'public.purchases_expense_lines',
        'public.purchases_expenses',
        'public.inventory_transfer_lines',
        'public.inventory_transfers',
        'public.inventory_adjustment_lines',
        'public.inventory_adjustments',
        'public.inventory_stock',
        'public.inventory_items',
        'public.inventory_categories',
        'public.warehouses',
        'hr.leaves',
        'hr.payroll_lines',
        'hr.payrolls',
        'hr.contracts',
        'hr.employees',
        'hr.departments',
        'public.journal_lines',
        'public.journal_entries'
    ];

    foreach ($tablesToClean as $table) {
        $pdo->exec("DELETE FROM {$table}");
        echo " - Tabla {$table} vaciada.\n";
    }

    $companyId = 'd1e2f3a4-b5c6-4d7e-8f9a-0b1c2d3e4f5a';
    $branchId = 'e2f3a4b5-c6d7-4e8f-9a0b-1c2d3e4f5a6b';
    $branchSecId = 'e2f3a4b5-c6d7-4e8f-9a0b-1c2d3e4f5a6c';

    // Asegurar que exista la sucursal secundaria
    $pdo->prepare("
        INSERT INTO public.branches (id, company_id, name, code, address, is_main, operating_currency, is_active)
        VALUES (?, ?, 'Sede Norte', 'NORTH', 'Avenida Norte #45-12', FALSE, 'USD', TRUE)
        ON CONFLICT (id) DO NOTHING
    ")->execute([$branchSecId, $companyId]);

    // 2. Cuentas contables adicionales requeridas
    echo "Verificando/Creando plan de cuentas básico...\n";
    $accounts = [
        // headers
        ['a1000001-0000-4000-8000-000000000013', '13', 'Cuentas por cobrar', 'asset', true, 'a1000001-0000-4000-8000-000000000001'],
        ['a1000001-0000-4000-8000-000000000014', '14', 'Inventarios', 'asset', true, 'a1000001-0000-4000-8000-000000000001'],
        ['a1000001-0000-4000-8000-000000000024', '24', 'Impuestos gravámenes y tasas', 'liability', true, 'a1000001-0000-4000-8000-000000000002'],
        ['a1000001-0000-4000-8000-000000000053', '53', 'Otros gastos', 'expense', true, 'a1000001-0000-4000-8000-000000000005'],
        // non-headers
        ['a1000001-0000-4000-8000-000000000131', '1305', 'Clientes nacionales', 'asset', false, 'a1000001-0000-4000-8000-000000000013'],
        ['a1000001-0000-4000-8000-000000000143', '1435', 'Mercancías', 'asset', false, 'a1000001-0000-4000-8000-000000000014'],
        ['a1000001-0000-4000-8000-000000000248', '2408', 'IVA por Pagar', 'liability', false, 'a1000001-0000-4000-8000-000000000024'],
        ['a1000001-0000-4000-8000-000000000535', '5305', 'Descuentos comerciales', 'expense', false, 'a1000001-0000-4000-8000-000000000053']
    ];

    $stmtAcc = $pdo->prepare("
        INSERT INTO public.chart_accounts (id, company_id, parent_id, code, name, account_type, is_header, is_active)
        VALUES (?, ?, ?, ?, ?, ?, ?, TRUE)
        ON CONFLICT (company_id, code) DO UPDATE 
        SET name = EXCLUDED.name, parent_id = EXCLUDED.parent_id
    ");

    foreach ($accounts as $acc) {
        $stmtAcc->execute([$acc[0], $companyId, $acc[5], $acc[1], $acc[2], $acc[3], $acc[4] ? 1 : 0]);
    }
    echo "Plan de cuentas configurado.\n";

    // 3. Crear Asientos Contables Primero (Journal Entries)
    echo "Asentando transacciones en diario contable (journal_entries)...\n";
    $stmtJE = $pdo->prepare("
        INSERT INTO public.journal_entries (
            id, company_id, branch_id, entry_number, entry_date, description, status,
            operating_currency, functional_currency, exchange_rate
        ) VALUES (
            :id, :company_id, :branch_id, :entry_number, :entry_date, :description, 'posted',
            'USD', 'USD', 1.0
        )
    ");

    $stmtJL = $pdo->prepare("
        INSERT INTO public.journal_lines (
            id, journal_entry_id, chart_account_id, debit, credit, debit_functional, credit_functional, line_order, description
        ) VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
    ");

    // JE-1: Factura FV-001 (Venta)
    $jeFv001Id = 'fa000001-0000-4000-8000-000000000001';
    $stmtJE->execute([
        'id' => $jeFv001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'FV-001',
        'entry_date' => '2026-06-01',
        'description' => 'Asiento Factura de Venta FV-001 Clientes'
    ]);
    $stmtJL->execute([Database::uuid(), $jeFv001Id, 'a1000001-0000-4000-8000-000000000131', 400.00, 0.00, 400.00, 0.00, 0, 'Cuentas por cobrar clientes']);
    $stmtJL->execute([Database::uuid(), $jeFv001Id, 'a1000001-0000-4000-8000-000000000411', 0.00, 360.00, 0.00, 360.00, 1, 'Ingresos por ventas']);
    $stmtJL->execute([Database::uuid(), $jeFv001Id, 'a1000001-0000-4000-8000-000000000248', 0.00, 68.40, 0.00, 68.40, 2, 'IVA por pagar']);
    $stmtJL->execute([Database::uuid(), $jeFv001Id, 'a1000001-0000-4000-8000-000000000535', 28.40, 0.00, 28.40, 0.00, 3, 'Descuentos comerciales concedidos']);

    // JE-2: Cobro RC-001
    $jeRc001Id = 'fa000002-0000-4000-8000-000000000002';
    $stmtJE->execute([
        'id' => $jeRc001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'RC-001',
        'entry_date' => '2026-06-10',
        'description' => 'Abono cliente RC-001'
    ]);
    $stmtJL->execute([Database::uuid(), $jeRc001Id, 'a1000001-0000-4000-8000-000000000112', 200.00, 0.00, 200.00, 0.00, 0, 'Caja/Bancos']);
    $stmtJL->execute([Database::uuid(), $jeRc001Id, 'a1000001-0000-4000-8000-000000000131', 0.00, 200.00, 0.00, 200.00, 1, 'Cuentas por cobrar clientes']);

    // JE-3: Factura Compra FP-001
    $jeFp001Id = 'fa000003-0000-4000-8000-000000000003';
    $stmtJE->execute([
        'id' => $jeFp001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'FP-001',
        'entry_date' => '2026-06-05',
        'description' => 'Compra Mobiliario FP-001'
    ]);
    $stmtJL->execute([Database::uuid(), $jeFp001Id, 'a1000001-0000-4000-8000-000000000511', 100.00, 0.00, 100.00, 0.00, 0, 'Gastos de administración']);
    $stmtJL->execute([Database::uuid(), $jeFp001Id, 'a1000001-0000-4000-8000-000000000248', 19.00, 0.00, 19.00, 0.00, 1, 'IVA descontable']);
    $stmtJL->execute([Database::uuid(), $jeFp001Id, 'a1000001-0000-4000-8000-000000000211', 0.00, 119.00, 0.00, 119.00, 2, 'Proveedores nacionales']);

    // JE-4: Factura Compra FP-002
    $jeFp002Id = 'fa000004-0000-4000-8000-000000000004';
    $stmtJE->execute([
        'id' => $jeFp002Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'FP-002',
        'entry_date' => '2026-06-18',
        'description' => 'Soporte TI FP-002'
    ]);
    $stmtJL->execute([Database::uuid(), $jeFp002Id, 'a1000001-0000-4000-8000-000000000511', 500.00, 0.00, 500.00, 0.00, 0, 'Gastos TI']);
    $stmtJL->execute([Database::uuid(), $jeFp002Id, 'a1000001-0000-4000-8000-000000000211', 0.00, 500.00, 0.00, 500.00, 1, 'Proveedores nacionales']);

    // JE-5: Pago Proveedor OP-001
    $jeOp001Id = 'fa000005-0000-4000-8000-000000000005';
    $stmtJE->execute([
        'id' => $jeOp001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'OP-001',
        'entry_date' => '2026-06-12',
        'description' => 'Pago total FP-001 OP-001'
    ]);
    $stmtJL->execute([Database::uuid(), $jeOp001Id, 'a1000001-0000-4000-8000-000000000211', 119.00, 0.00, 119.00, 0.00, 0, 'Proveedores nacionales']);
    $stmtJL->execute([Database::uuid(), $jeOp001Id, 'a1000001-0000-4000-8000-000000000112', 0.00, 119.00, 0.00, 119.00, 1, 'Bancos']);

    // JE-6: Gasto Directo G-001
    $jeG001Id = 'fa000006-0000-4000-8000-000000000006';
    $stmtJE->execute([
        'id' => $jeG001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'G-001',
        'entry_date' => '2026-06-08',
        'description' => 'Gastos varios papeleria'
    ]);
    $stmtJL->execute([Database::uuid(), $jeG001Id, 'a1000001-0000-4000-8000-000000000511', 50.00, 0.00, 50.00, 0.00, 0, 'Gastos diversos papeleria']);
    $stmtJL->execute([Database::uuid(), $jeG001Id, 'a1000001-0000-4000-8000-000000000248', 9.50, 0.00, 9.50, 0.00, 1, 'IVA descontable']);
    $stmtJL->execute([Database::uuid(), $jeG001Id, 'a1000001-0000-4000-8000-000000000111', 0.00, 59.50, 0.00, 59.50, 2, 'Caja general']);

    // JE-7: Nota Crédito NC-001
    $jeNc001Id = 'fa000007-0000-4000-8000-000000000007';
    $stmtJE->execute([
        'id' => $jeNc001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'NC-001',
        'entry_date' => '2026-06-20',
        'description' => 'Ajuste crédito NC-001'
    ]);
    $stmtJL->execute([Database::uuid(), $jeNc001Id, 'a1000001-0000-4000-8000-000000000411', 50.00, 0.00, 50.00, 0.00, 0, 'Devoluciones y rebajas']);
    $stmtJL->execute([Database::uuid(), $jeNc001Id, 'a1000001-0000-4000-8000-000000000131', 0.00, 50.00, 0.00, 50.00, 1, 'Cuentas por cobrar clientes']);

    // JE-8: Nota Débito ND-001
    $jeNd001Id = 'fa000008-0000-4000-8000-000000000008';
    $stmtJE->execute([
        'id' => $jeNd001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'ND-001',
        'entry_date' => '2026-06-21',
        'description' => 'Recargo flete ND-001'
    ]);
    $stmtJL->execute([Database::uuid(), $jeNd001Id, 'a1000001-0000-4000-8000-000000000131', 30.00, 0.00, 30.00, 0.00, 0, 'Cuentas por cobrar clientes']);
    $stmtJL->execute([Database::uuid(), $jeNd001Id, 'a1000001-0000-4000-8000-000000000411', 0.00, 30.00, 0.00, 30.00, 1, 'Ingresos conexos']);

    // JE-9: Ajuste Inventario AJ-001
    $jeAj001Id = 'fa000009-0000-4000-8000-000000000009';
    $stmtJE->execute([
        'id' => $jeAj001Id,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'AJ-001',
        'entry_date' => '2026-06-02',
        'description' => 'Cargue inicial de inventario físico'
    ]);
    $stmtJL->execute([Database::uuid(), $jeAj001Id, 'a1000001-0000-4000-8000-000000000143', 10400.00, 0.00, 10400.00, 0.00, 0, 'Mercancías']);
    $stmtJL->execute([Database::uuid(), $jeAj001Id, 'a1000001-0000-4000-8000-000000000311', 0.00, 10400.00, 0.00, 10400.00, 1, 'Capital social aportado']);

    // JE-10: Nómina Mayo 2026
    $jePayrollId = 'fa000010-0000-4000-8000-000000000010';
    $stmtJE->execute([
        'id' => $jePayrollId,
        'company_id' => $companyId,
        'branch_id' => $branchId,
        'entry_number' => 'PAY-202605',
        'entry_date' => '2026-05-31',
        'description' => 'Registro Nómina Mayo 2026'
    ]);
    $stmtJL->execute([Database::uuid(), $jePayrollId, 'a1000001-0000-4000-8000-000000000511', 4500.00, 0.00, 4500.00, 0.00, 0, 'Gastos de personal base']);
    $stmtJL->execute([Database::uuid(), $jePayrollId, 'a1000001-0000-4000-8000-000000000112', 0.00, 4250.00, 0.00, 4250.00, 1, 'Bancos - pago neto']);
    $stmtJL->execute([Database::uuid(), $jePayrollId, 'a1000001-0000-4000-8000-000000000211', 0.00, 250.00, 0.00, 250.00, 2, 'Deducciones retenidas']);

    echo "Asientos contables e historial de diario creados con éxito.\n";


    // 4. Crear Contactos adicionales si no existen
    echo "Registrando contactos (clientes y proveedores)...\n";
    $contacts = [
        ['f1e2d3c4-b5a6-4f7e-8d9c-0b1a2c3d4e5f', 'customer', 'Cliente Ejemplo S.A.', '123456789-0', 'ventas@clienteejemplo.com', '+57 311 111 1111', 'Calle Comercial #10-20'],
        ['e2d3c4b5-a6f7-4d8e-9c0b-1a2c3d4e5f6a', 'vendor', 'Proveedor Ejemplo Ltda.', '987654321-9', 'ventas@proveedorejemplo.com', '+57 322 222 2222', 'Parque Industrial Sur Bodega 5'],
        ['f1e2d3c4-b5a6-4f7e-8d9c-0b1a2c3d4e50', 'customer', 'Juan Perez', '10203040-5', 'juan.perez@email.com', '+57 300 123 4567', 'Carrera 7 #45-89'],
        ['e2d3c4b5-a6f7-4d8e-9c0b-1a2c3d4e5f60', 'vendor', 'Distribuidora Tech', '800555666-2', 'pedidos@distribuidoratech.com', '+57 315 999 8888', 'Avenida Libertadores #12-50']
    ];

    $stmtContact = $pdo->prepare("
        INSERT INTO public.contacts (id, company_id, type, name, tax_id, email, phone, address, is_active)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, TRUE)
        ON CONFLICT (company_id, tax_id) DO UPDATE
        SET name = EXCLUDED.name, email = EXCLUDED.email, phone = EXCLUDED.phone, address = EXCLUDED.address
    ");

    foreach ($contacts as $c) {
        $stmtContact->execute([$c[0], $companyId, $c[1], $c[2], $c[3], $c[4], $c[5], $c[6]]);
    }

    // 5. Crear Warehouses (Bodegas)
    echo "Creando bodegas de inventario...\n";
    $warehouses = [
        ['ab000001-0000-4000-8000-000000000001', 'Bodega Central', 'W-CENTRAL', 'Zona Industrial 45'],
        ['ab000002-0000-4000-8000-000000000002', 'Bodega Secundaria', 'W-SECUNDARIA', 'Centro de Distribución Norte']
    ];

    $stmtWh = $pdo->prepare("
        INSERT INTO public.warehouses (id, company_id, name, code, address, is_active)
        VALUES (?, ?, ?, ?, ?, TRUE)
        ON CONFLICT (company_id, code) DO UPDATE
        SET name = EXCLUDED.name, address = EXCLUDED.address
    ");

    foreach ($warehouses as $w) {
        $stmtWh->execute([$w[0], $companyId, $w[1], $w[2], $w[3]]);
    }

    // 6. Crear Categorías de Inventario
    echo "Creando categorías de inventario...\n";
    $categories = [
        ['ac000001-0000-4000-8000-000000000001', 'Tecnología', 'Equipos electrónicos, accesorios y software'],
        ['ac000002-0000-4000-8000-000000000002', 'Mobiliario', 'Muebles de oficina, escritorios y sillas']
    ];

    $stmtCat = $pdo->prepare("
        INSERT INTO public.inventory_categories (id, company_id, name, description)
        VALUES (?, ?, ?, ?)
        ON CONFLICT (company_id, name) DO UPDATE
        SET description = EXCLUDED.description
    ");

    foreach ($categories as $cat) {
        $stmtCat->execute([$cat[0], $companyId, $cat[1], $cat[2]]);
    }

    // 7. Crear Ítems de Inventario (Productos y Servicios)
    echo "Registrando productos y servicios...\n";
    $taxVat19Id = 'a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5e';
    $taxExemptId = 'c3d4e5f6-a7b8-4c9d-0e1f-2a3b4c5d6e7a';

    $accCaja = 'a1000001-0000-4000-8000-000000000111';
    $accClientes = 'a1000001-0000-4000-8000-000000000131';
    $accInventario = 'a1000001-0000-4000-8000-000000000143';
    $accVentas = 'a1000001-0000-4000-8000-000000000411';
    $accGastosPersonal = 'a1000001-0000-4000-8000-000000000511';

    $items = [
        [
            'id' => 'ad000001-0000-4000-8000-000000000001',
            'category_id' => 'ac000001-0000-4000-8000-000000000001',
            'sku' => 'PROD-LAP01',
            'name' => 'Computador Portátil Intel i7',
            'description' => 'Laptop corporativa 16GB RAM, 512GB SSD',
            'item_type' => 'product',
            'sale_price' => 1200.0000,
            'purchase_price' => 800.0000,
            'tax_rate_id' => $taxVat19Id,
            'income_account_id' => $accVentas,
            'expense_account_id' => $accGastosPersonal,
            'inventory_account_id' => $accInventario
        ],
        [
            'id' => 'ad000002-0000-4000-8000-000000000002',
            'category_id' => 'ac000002-0000-4000-8000-000000000002',
            'sku' => 'PROD-DESK02',
            'name' => 'Escritorio de Oficina en Madera',
            'description' => 'Escritorio en L acabado roble natural',
            'item_type' => 'product',
            'sale_price' => 250.0000,
            'purchase_price' => 150.0000,
            'tax_rate_id' => $taxVat19Id,
            'income_account_id' => $accVentas,
            'expense_account_id' => $accGastosPersonal,
            'inventory_account_id' => $accInventario
        ],
        [
            'id' => 'ad000003-0000-4000-8000-000000000003',
            'category_id' => 'ac000002-0000-4000-8000-000000000002',
            'sku' => 'PROD-CHAIR03',
            'name' => 'Silla Ergonómica Ejecutiva',
            'description' => 'Silla con soporte lumbar y brazos ajustables',
            'item_type' => 'product',
            'sale_price' => 180.0000,
            'purchase_price' => 110.0000,
            'tax_rate_id' => $taxVat19Id,
            'income_account_id' => $accVentas,
            'expense_account_id' => $accGastosPersonal,
            'inventory_account_id' => $accInventario
        ]
    ];

    $stmtItem = $pdo->prepare("
        INSERT INTO public.inventory_items (
            id, company_id, category_id, sku, name, description, item_type,
            sale_price, purchase_price, tax_rate_id, income_account_id,
            expense_account_id, inventory_account_id, is_active
        ) VALUES (
            :id, :company_id, :category_id, :sku, :name, :description, :item_type,
            :sale_price, :purchase_price, :tax_rate_id, :income_account_id,
            :expense_account_id, :inventory_account_id, TRUE
        )
    ");

    foreach ($items as $item) {
        $stmtItem->execute(array_merge($item, ['company_id' => $companyId]));
    }

    // 7. Stock Inicial
    echo "Registrando existencias iniciales (stock)...\n";
    $stock = [
        ['ad000001-0000-4000-8000-000000000001', 'ab000001-0000-4000-8000-000000000001', 15.0000],
        ['ad000001-0000-4000-8000-000000000001', 'ab000002-0000-4000-8000-000000000002', 5.0000],
        ['ad000002-0000-4000-8000-000000000002', 'ab000001-0000-4000-8000-000000000001', 8.0000],
        ['ad000003-0000-4000-8000-000000000003', 'ab000001-0000-4000-8000-000000000001', 12.0000]
    ];

    $stmtStock = $pdo->prepare("
        INSERT INTO public.inventory_stock (item_id, warehouse_id, quantity)
        VALUES (?, ?, ?)
    ");

    foreach ($stock as $s) {
        $stmtStock->execute($s);
    }

    // 8. Recursos Humanos (Departamentos, Empleados, Contratos, Nómina, Ausencias)
    echo "Sembrando Recursos Humanos (RRHH)...\n";
    $departments = [
        ['ae000001-0000-4000-8000-000000000001', 'Ventas y Mercadeo'],
        ['ae000002-0000-4000-8000-000000000002', 'Administración y Finanzas'],
        ['ae000003-0000-4000-8000-000000000003', 'Tecnología y Desarrollo']
    ];

    $stmtDep = $pdo->prepare("
        INSERT INTO hr.departments (id, company_id, name, is_active)
        VALUES (?, ?, ?, TRUE)
    ");
    foreach ($departments as $d) {
        $stmtDep->execute([$d[0], $companyId, $d[1]]);
    }

    $employees = [
        ['af000001-0000-4000-8000-000000000001', 'Carlos', 'Mendoza', 'carlos.mendoza@demo.local', '+57 301 444 5555', '10102020', '2025-01-15', 'ae000001-0000-4000-8000-000000000001'],
        ['af000002-0000-4000-8000-000000000002', 'María', 'Gómez', 'maria.gomez@demo.local', '+57 302 555 6666', '30304040', '2025-06-01', 'ae000002-0000-4000-8000-000000000002']
    ];

    $stmtEmp = $pdo->prepare("
        INSERT INTO hr.employees (id, company_id, first_name, last_name, email, phone, identification_number, hiring_date, department_id, is_active)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, TRUE)
    ");
    foreach ($employees as $emp) {
        $stmtEmp->execute([$emp[0], $companyId, $emp[1], $emp[2], $emp[3], $emp[4], $emp[5], $emp[6], $emp[7]]);
    }

    $contracts = [
        ['bc000001-0000-4000-8000-000000000001', 'af000001-0000-4000-8000-000000000001', 'indefinido', '2025-01-15', 2500.0000, 'active'],
        ['bc000002-0000-4000-8000-000000000002', 'af000002-0000-4000-8000-000000000002', 'temporal', '2025-06-01', 1800.0000, 'active']
    ];

    $stmtCnt = $pdo->prepare("
        INSERT INTO hr.contracts (id, employee_id, contract_type, start_date, base_salary, status)
        VALUES (?, ?, ?, ?, ?, ?)
    ");
    foreach ($contracts as $cnt) {
        $stmtCnt->execute($cnt);
    }

    // Ausencias
    $leaves = [
        ['bd000001-0000-4000-8000-000000000001', 'af000001-0000-4000-8000-000000000001', 'vacacion', '2026-06-10', '2026-06-15', 'approved', 'Vacaciones anuales autorizadas.'],
        ['bd000002-0000-4000-8000-000000000002', 'af000002-0000-4000-8000-000000000002', 'enfermedad', '2026-06-02', '2026-06-03', 'approved', 'Incapacidad médica por resfriado común.']
    ];

    $stmtLeave = $pdo->prepare("
        INSERT INTO hr.leaves (id, employee_id, leave_type, start_date, end_date, status, notes)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    foreach ($leaves as $lv) {
        $stmtLeave->execute($lv);
    }

    // Nóminas y líneas de nómina
    $payrollId = 'fa000010-0000-4000-8000-000000000010';
    $jePayrollId = 'fa000010-0000-4000-8000-000000000010';

    $pdo->prepare("
        INSERT INTO hr.payrolls (id, company_id, name, start_date, end_date, total_earnings, total_deductions, total_net, status, journal_entry_id)
        VALUES (?, ?, 'Nómina Mayo 2026', '2026-05-01', '2026-05-31', 4500.0000, 250.0000, 4250.0000, 'paid', ?)
    ")->execute([$payrollId, $companyId, $jePayrollId]);

    $payrollLines = [
        ['bf000001-0000-4000-8000-000000000001', $payrollId, 'af000001-0000-4000-8000-000000000001', 2500.0000, 200.0000, 150.0000, 2550.0000],
        ['bf000002-0000-4000-8000-000000000002', $payrollId, 'af000002-0000-4000-8000-000000000002', 1800.0000, 0.0000, 100.0000, 1700.0000]
    ];

    $stmtPayl = $pdo->prepare("
        INSERT INTO hr.payroll_lines (id, payroll_id, employee_id, base_salary, bonuses, deductions, net_pay)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    foreach ($payrollLines as $pl) {
        $stmtPayl->execute($pl);
    }

    // 9. Transacciones Contables y Operativas (Facturas, Cobros, Compras, Pagos, Gastos, Ajustes, Traspasos)
    echo "Sembrando transacciones comerciales e inventario...\n";

    // A. Facturas de Venta
    $invPostedId = 'be000001-0000-4000-8000-000000000001';
    $invDraftId = 'be000002-0000-4000-8000-000000000002';
    $customer1Id = 'f1e2d3c4-b5a6-4f7e-8d9c-0b1a2c3d4e5f'; // Cliente Ejemplo S.A.
    $customer2Id = 'f1e2d3c4-b5a6-4f7e-8d9c-0b1a2c3d4e50'; // Juan Perez

    // FV-001 (Posted)
    // subtotal = 360, tax = 68.40 (19%), discount = 28.40, total = 400.00, balance = 200.00 (parcialmente pagada)
    $pdo->prepare("
        INSERT INTO public.sales_invoices (
            id, company_id, branch_id, customer_id, receivable_account_id, invoice_number, invoice_date, due_date,
            reference, currency_code, exchange_rate, discount_amount, tax_amount, subtotal, total, balance, status, notes
        ) VALUES (
            ?, ?, ?, ?, 'a1000001-0000-4000-8000-000000000131', 'FV-001', '2026-06-01', '2026-07-01',
            'OC-8891', 'USD', 1.0, 28.4000, 68.4000, 360.0000, 400.0000, 200.0000, 'partially_paid', 'Servicios y licencias'
        )
    ")->execute([$invPostedId, $companyId, $branchId, $customer1Id]);

    // Line 1: Silla Ergonómica (Qty 2, Price 180 = 360)
    $pdo->prepare("
        INSERT INTO public.sales_invoice_lines (id, invoice_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order)
        VALUES ('bf000001-0000-4000-8000-000000000001', ?, 'Silla Ergonómica Ejecutiva - Color Negro', 2.0, 180.0000, ?, 68.4000, 360.0000, 'a1000001-0000-4000-8000-000000000411', 0)
    ")->execute([$invPostedId, $taxVat19Id]);

    // FV-002 (Draft)
    // subtotal = 1200, tax = 228 (19%), total = 1428.00, balance = 1428.00
    $pdo->prepare("
        INSERT INTO public.sales_invoices (
            id, company_id, branch_id, customer_id, receivable_account_id, invoice_number, invoice_date, due_date,
            reference, currency_code, exchange_rate, discount_amount, tax_amount, subtotal, total, balance, status, notes
        ) VALUES (
            ?, ?, ?, ?, 'a1000001-0000-4000-8000-000000000131', 'FV-002', '2026-06-15', '2026-07-15',
            'PROP-900', 'USD', 1.0, 0.0000, 228.0000, 1200.0000, 1428.0000, 1428.0000, 'draft', 'Venta Computador i7'
        )
    ")->execute([$invDraftId, $companyId, $branchId, $customer2Id]);

    $pdo->prepare("
        INSERT INTO public.sales_invoice_lines (id, invoice_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order)
        VALUES ('bf000002-0000-4000-8000-000000000001', ?, 'Computador Portátil Intel i7', 1.0, 1200.0000, ?, 228.0000, 1200.0000, 'a1000001-0000-4000-8000-000000000411', 0)
    ")->execute([$invDraftId, $taxVat19Id]);


    // B. Cobros de Clientes (Payments Received)
    $paymentId = 'ca000001-0000-4000-8000-000000000001';
    $pdo->prepare("
        INSERT INTO public.payment_receives (
            id, company_id, branch_id, customer_id, payment_number, payment_date, payment_method, amount,
            currency_code, exchange_rate, chart_account_id, notes, withholding_amount, withholding_account_id
        ) VALUES (
            ?, ?, ?, ?, 'RC-001', '2026-06-10', 'bank', 200.0000,
            'USD', 1.0, 'a1000001-0000-4000-8000-000000000112', 'Abono parcial factura FV-001', 0.0000, NULL
        )
    ")->execute([$paymentId, $companyId, $branchId, $customer1Id]);

    $pdo->prepare("
        INSERT INTO public.payment_receive_lines (id, payment_id, invoice_id, amount)
        VALUES ('cb000001-0000-4000-8000-000000000001', ?, ?, 200.0000)
    ")->execute([$paymentId, $invPostedId]);


    // C. Notas de Crédito / Débito (Sales Adjustments)
    $creditNoteId = 'dc000001-0000-4000-8000-000000000001';
    $debitNoteId = 'dc000002-0000-4000-8000-000000000002';
    $jeNc001Id = 'fa000007-0000-4000-8000-000000000007';
    $jeNd001Id = 'fa000008-0000-4000-8000-000000000008';

    // Credit Note (NC-001) - Descuento posterior o devolución
    // subtotal = 50, total = 50
    $pdo->prepare("
        INSERT INTO public.sales_adjustments (
            id, company_id, branch_id, customer_id, adjustment_type, document_number, document_date,
            reference_invoice_id, receivable_account_id, discount_amount, tax_amount, subtotal, total, status, journal_entry_id, notes
        ) VALUES (
            ?, ?, ?, ?, 'credit_note', 'NC-001', '2026-06-20',
            ?, 'a1000001-0000-4000-8000-000000000131', 0.0000, 0.0000, 50.0000, 50.0000, 'posted', ?, 'Ajuste de precio acordado'
        )
    ")->execute([$creditNoteId, $companyId, $branchId, $customer1Id, $invPostedId, $jeNc001Id]);

    $pdo->prepare("
        INSERT INTO public.sales_adjustment_lines (id, adjustment_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order)
        VALUES ('dd000001-0000-4000-8000-000000000001', ?, 'Descuento Comercial de Fidelidad', 1.0, 50.0000, NULL, 0.0000, 50.0000, 'a1000001-0000-4000-8000-000000000411', 0)
    ")->execute([$creditNoteId]);

    // Debit Note (ND-001) - Cargo adicional
    // subtotal = 30, total = 30
    $pdo->prepare("
        INSERT INTO public.sales_adjustments (
            id, company_id, branch_id, customer_id, adjustment_type, document_number, document_date,
            reference_invoice_id, receivable_account_id, discount_amount, tax_amount, subtotal, total, status, journal_entry_id, notes
        ) VALUES (
            ?, ?, ?, ?, 'debit_note', 'ND-001', '2026-06-21',
            ?, 'a1000001-0000-4000-8000-000000000131', 0.0000, 0.0000, 30.0000, 30.0000, 'posted', ?, 'Cargo extra transporte'
        )
    ")->execute([$debitNoteId, $companyId, $branchId, $customer1Id, $invPostedId, $jeNd001Id]);

    $pdo->prepare("
        INSERT INTO public.sales_adjustment_lines (id, adjustment_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order)
        VALUES ('dd000002-0000-4000-8000-000000000001', ?, 'Flete y Acarreo de Productos', 1.0, 30.0000, NULL, 0.0000, 30.0000, 'a1000001-0000-4000-8000-000000000411', 0)
    ")->execute([$debitNoteId]);


    // D. Facturas de Compra (Purchases Bills)
    $billPaidId = 'cc000001-0000-4000-8000-000000000001';
    $billUnpaidId = 'cc000002-0000-4000-8000-000000000002';
    $vendor1Id = 'e2d3c4b5-a6f7-4d8e-9c0b-1a2c3d4e5f6a'; // Proveedor Ejemplo Ltda.
    $vendor2Id = 'e2d3c4b5-a6f7-4d8e-9c0b-1a2c3d4e5f60'; // Distribuidora Tech

    // FP-001 (Posted & Paid)
    // subtotal = 100, tax = 19 (19%), total = 119, balance = 0
    $pdo->prepare("
        INSERT INTO public.purchases_bills (
            id, company_id, branch_id, vendor_id, payable_account_id, bill_number, bill_date, due_date,
            reference, currency_code, exchange_rate, discount_amount, tax_amount, subtotal, total, balance, status, notes
        ) VALUES (
            ?, ?, ?, ?, 'a1000001-0000-4000-8000-000000000211', 'FP-001', '2026-06-05', '2026-07-05',
            'FC-99011', 'USD', 1.0, 0.0000, 19.0000, 100.0000, 119.0000, 0.0000, 'paid', 'Compra de Escritorio en Madera'
        )
    ")->execute([$billPaidId, $companyId, $branchId, $vendor1Id]);

    $pdo->prepare("
        INSERT INTO public.purchases_bill_lines (id, bill_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order)
        VALUES ('cd000001-0000-4000-8000-000000000001', ?, 'Escritorio de Oficina en Madera', 1.0, 100.0000, ?, 19.0000, 100.0000, 'a1000001-0000-4000-8000-000000000511', 0)
    ")->execute([$billPaidId, $taxVat19Id]);

    // FP-002 (Posted & Unpaid)
    // subtotal = 500, total = 500, balance = 500
    $pdo->prepare("
        INSERT INTO public.purchases_bills (
            id, company_id, branch_id, vendor_id, payable_account_id, bill_number, bill_date, due_date,
            reference, currency_code, exchange_rate, discount_amount, tax_amount, subtotal, total, balance, status, notes
        ) VALUES (
            ?, ?, ?, ?, 'a1000001-0000-4000-8000-000000000211', 'FP-002', '2026-06-18', '2026-07-18',
            'TECH-449', 'USD', 1.0, 0.0000, 0.0000, 500.0000, 500.0000, 500.0000, 'posted', 'Servicio de Personal Temporal'
        )
    ")->execute([$billUnpaidId, $companyId, $branchId, $vendor2Id]);

    $pdo->prepare("
        INSERT INTO public.purchases_bill_lines (id, bill_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order)
        VALUES ('cd000002-0000-4000-8000-000000000001', ?, 'Servicio Soporte TI y Redes', 1.0, 500.0000, ?, 0.0000, 500.0000, 'a1000001-0000-4000-8000-000000000511', 0)
    ")->execute([$billUnpaidId, $taxExemptId]);


    // E. Pagos a Proveedores (Purchase Payments)
    $purPaymentId = 'ce000001-0000-4000-8000-000000000001';
    $pdo->prepare("
        INSERT INTO public.purchases_payments (
            id, company_id, branch_id, vendor_id, payment_number, payment_date, payment_method, amount,
            currency_code, exchange_rate, chart_account_id, notes, withholding_amount, withholding_account_id
        ) VALUES (
            ?, ?, ?, ?, 'OP-001', '2026-06-12', 'bank', 119.0000,
            'USD', 1.0, 'a1000001-0000-4000-8000-000000000112', 'Pago total factura FP-001', 0.0000, NULL
        )
    ")->execute([$purPaymentId, $companyId, $branchId, $vendor1Id]);

    $pdo->prepare("
        INSERT INTO public.purchases_payment_lines (id, payment_id, bill_id, amount)
        VALUES ('cf000001-0000-4000-8000-000000000001', ?, ?, 119.0000)
    ")->execute([$purPaymentId, $billPaidId]);


    // F. Gastos Directos (Expenses)
    $expenseId = 'da000001-0000-4000-8000-000000000001';
    // total = 59.50, subtotal = 50.00, tax = 9.50 (19%)
    $pdo->prepare("
        INSERT INTO public.purchases_expenses (
            id, company_id, branch_id, expense_number, payment_date, payment_method, amount,
            currency_code, exchange_rate, chart_account_id, notes
        ) VALUES (
            ?, ?, ?, 'G-001', '2026-06-08', 'cash', 59.5000,
            'USD', 1.0, 'a1000001-0000-4000-8000-000000000111', 'Compra papelería oficina'
        )
    ")->execute([$expenseId, $companyId, $branchId]);

    $pdo->prepare("
        INSERT INTO public.purchases_expense_lines (id, expense_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order)
        VALUES ('db000001-0000-4000-8000-000000000001', ?, 'Resmas papel carta y lapiceros', 1.0, 50.0000, ?, 9.5000, 50.0000, 'a1000001-0000-4000-8000-000000000511', 0)
    ")->execute([$expenseId, $taxVat19Id]);


    // G. Ajustes de Inventario (Inventory Adjustments)
    $invAdjId = 'de000001-0000-4000-8000-000000000001';
    $pdo->prepare("
        INSERT INTO public.inventory_adjustments (id, company_id, branch_id, adjustment_number, adjustment_date, notes)
        VALUES (?, ?, ?, 'AJ-001', '2026-06-02', 'Ajuste de inventario físico inicial')
    ")->execute([$invAdjId, $companyId, $branchId]);

    // Líneas
    $stmtAdjLine = $pdo->prepare("
        INSERT INTO public.inventory_adjustment_lines (id, adjustment_id, item_id, warehouse_id, quantity, unit_cost, chart_account_id)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ");
    // Laptop i7: 10 units at 800.00
    $stmtAdjLine->execute(['df000001-0000-4000-8000-000000000001', $invAdjId, 'ad000001-0000-4000-8000-000000000001', 'ab000001-0000-4000-8000-000000000001', 10.0000, 800.0000, 'a1000001-0000-4000-8000-000000000311']);
    // Silla Ergonómica: 20 units at 120.00
    $stmtAdjLine->execute(['df000002-0000-4000-8000-000000000002', $invAdjId, 'ad000003-0000-4000-8000-000000000003', 'ab000001-0000-4000-8000-000000000001', 20.0000, 120.0000, 'a1000001-0000-4000-8000-000000000311']);


    // H. Traspasos de Inventario (Inventory Transfers)
    $invTrId = 'ea000001-0000-4000-8000-000000000001';
    $pdo->prepare("
        INSERT INTO public.inventory_transfers (id, company_id, branch_id, transfer_number, transfer_date, from_warehouse_id, to_warehouse_id, notes)
        VALUES (?, ?, ?, 'TR-001', '2026-06-14', 'ab000001-0000-4000-8000-000000000001', 'ab000002-0000-4000-8000-000000000002', 'Envío stock sede norte')
    ")->execute([$invTrId, $companyId, $branchId]);

    $stmtTrLine = $pdo->prepare("
        INSERT INTO public.inventory_transfer_lines (id, transfer_id, item_id, quantity)
        VALUES (?, ?, ?, ?)
    ");
    $stmtTrLine->execute(['eb000001-0000-4000-8000-000000000001', $invTrId, 'ad000001-0000-4000-8000-000000000001', 2.0000]);
    $stmtTrLine->execute(['eb000002-0000-4000-8000-000000000002', $invTrId, 'ad000003-0000-4000-8000-000000000003', 5.0000]);

    echo "=== PROCESO DE SEEDING COMPLETADO CON ÉXITO ===\n";

} catch (Exception $e) {
    echo "ERROR: Ocurrió un fallo durante el seeding: " . $e->getMessage() . "\n";
    exit(1);
}
