<?php

declare(strict_types=1);

namespace ApiModel\Service;

use ApiModel\Infrastructure\Persistence\Database;
use ApiModel\Shared\Exceptions\BadRequestException;
use ApiModel\Shared\Exceptions\NotFoundException;
use PDO;

final class PurchaseOrderService
{
    public function __construct(
        private readonly PDO $pdo
    ) {}

    /** @return list<array<string, mixed>> */
    public function listOrders(string $companyId, ?string $branchId = null): array
    {
        $sql = 'SELECT po.*, c.name as provider_name 
                FROM public.purchase_orders po
                JOIN public.contacts c ON po.provider_id = c.id
                WHERE po.company_id = ?';
        $params = [$companyId];

        if ($branchId !== null) {
            $sql .= ' AND po.branch_id = ?';
            $params[] = $branchId;
        }

        $sql .= ' ORDER BY po.order_number DESC';

        $st = $this->pdo->prepare($sql);
        $st->execute($params);

        return $st->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @return array<string, mixed> */
    public function getOrder(string $companyId, string $id): array
    {
        $st = $this->pdo->prepare('SELECT po.*, c.name as provider_name 
                                   FROM public.purchase_orders po
                                   JOIN public.contacts c ON po.provider_id = c.id
                                   WHERE po.id = ? AND po.company_id = ?');
        $st->execute([$id, $companyId]);
        $order = $st->fetch(PDO::FETCH_ASSOC);

        if (!$order) {
            throw new NotFoundException('Orden no encontrada');
        }

        // Get lines
        $stLines = $this->pdo->prepare('SELECT pol.*, ca.code as chart_account_code, ca.name as chart_account_name, bc.code as classifier_code, bc.name as classifier_name 
                                         FROM public.purchase_order_lines pol
                                         JOIN public.chart_accounts ca ON pol.chart_account_id = ca.id
                                         JOIN budget.classifiers bc ON pol.classifier_id = bc.id
                                         WHERE pol.purchase_order_id = ?
                                         ORDER BY pol.line_order ASC');
        $stLines->execute([$id]);
        $order['lines'] = $stLines->fetchAll(PDO::FETCH_ASSOC);

        return $order;
    }

    /** @param array<string, mixed> $data @return array<string, mixed> */
    public function saveOrder(string $companyId, ?string $branchId, ?string $userId, array $data): array
    {
        $id = trim((string) ($data['id'] ?? ''));
        $isUpdate = $id !== '' && $id !== '0';

        $this->pdo->beginTransaction();

        try {
            if ($isUpdate) {
                $st = $this->pdo->prepare('SELECT * FROM public.purchase_orders WHERE id = ? AND company_id = ?');
                $st->execute([$id, $companyId]);
                $existing = $st->fetch(PDO::FETCH_ASSOC);
                if (!$existing) {
                    throw new NotFoundException('Orden no encontrada');
                }
                if ($existing['status'] !== 'draft') {
                    throw new BadRequestException('Solo se pueden modificar órdenes en estado borrador');
                }
                $orderNumber = $existing['order_number'];
            } else {
                $id = Database::uuid();
                // Generate sequential order number
                $prefix = $data['order_type'] === 'service' ? 'OS-' : 'OC-';
                $stNum = $this->pdo->prepare("SELECT COUNT(*) FROM public.purchase_orders WHERE company_id = ? AND order_type = ?");
                $stNum->execute([$companyId, $data['order_type']]);
                $count = (int) $stNum->fetchColumn();
                $orderNumber = $prefix . str_pad((string) ($count + 1), 6, '0', STR_PAD_LEFT);
            }

            $providerId = trim((string) ($data['provider_id'] ?? ''));
            $issueDate = trim((string) ($data['issue_date'] ?? date('Y-m-d')));
            $description = trim((string) ($data['description'] ?? ''));
            $lines = $data['lines'] ?? [];

            if ($providerId === '') {
                throw new BadRequestException('El proveedor es obligatorio');
            }
            if (empty($lines)) {
                throw new BadRequestException('La orden debe tener al menos un renglón');
            }

            // Compute totals
            $subtotal = 0.0;
            $taxAmount = 0.0;
            $total = 0.0;

            $processedLines = [];
            foreach ($lines as $idx => $line) {
                $lineDesc = trim((string) ($line['description'] ?? ''));
                $qty = (float) ($line['quantity'] ?? 1.0);
                $price = (float) ($line['price'] ?? 0.0);
                $lineTaxId = isset($line['tax_rate_id']) && trim((string) $line['tax_rate_id']) !== '' ? trim((string) $line['tax_rate_id']) : null;
                $chartAccountId = trim((string) ($line['chart_account_id'] ?? ''));
                $classifierId = trim((string) ($line['classifier_id'] ?? ''));

                if ($lineDesc === '') {
                    throw new BadRequestException('La descripción es obligatoria en todos los renglones');
                }
                if ($chartAccountId === '') {
                    throw new BadRequestException('La imputación contable es obligatoria en todos los renglones');
                }
                if ($classifierId === '') {
                    throw new BadRequestException('La imputación presupuestaria es obligatoria en todos los renglones');
                }

                $lineTotal = $qty * $price;
                $lineTax = 0.0;
                if ($lineTaxId) {
                    $stTax = $this->pdo->prepare('SELECT rate FROM public.tax_rates WHERE id = ?');
                    $stTax->execute([$lineTaxId]);
                    $rate = (float) $stTax->fetchColumn();
                    $lineTax = $lineTotal * ($rate / 100.0);
                }

                $subtotal += $lineTotal;
                $taxAmount += $lineTax;

                $processedLines[] = [
                    'id' => Database::uuid(),
                    'description' => $lineDesc,
                    'quantity' => $qty,
                    'price' => $price,
                    'tax_rate_id' => $lineTaxId,
                    'tax_amount' => $lineTax,
                    'total' => $lineTotal + $lineTax,
                    'chart_account_id' => $chartAccountId,
                    'classifier_id' => $classifierId,
                    'line_order' => $idx
                ];
            }

            $total = $subtotal + $taxAmount;

            if ($isUpdate) {
                $stUpd = $this->pdo->prepare('UPDATE public.purchase_orders 
                                              SET provider_id = ?, issue_date = ?, description = ?, subtotal = ?, tax_amount = ?, total = ?, updated_at = CURRENT_TIMESTAMP 
                                              WHERE id = ?');
                $stUpd->execute([$providerId, $issueDate, $description, $subtotal, $taxAmount, $total, $id]);

                // Delete old lines
                $stDel = $this->pdo->prepare('DELETE FROM public.purchase_order_lines WHERE purchase_order_id = ?');
                $stDel->execute([$id]);
            } else {
                $stIns = $this->pdo->prepare('INSERT INTO public.purchase_orders (id, company_id, branch_id, order_number, order_type, provider_id, issue_date, description, subtotal, tax_amount, total, status, created_by) 
                                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \'draft\', ?)');
                $stIns->execute([$id, $companyId, $branchId, $orderNumber, $data['order_type'], $providerId, $issueDate, $description, $subtotal, $taxAmount, $total, $userId]);
            }

            // Insert lines
            $stInsLine = $this->pdo->prepare('INSERT INTO public.purchase_order_lines (id, purchase_order_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, classifier_id, line_order) 
                                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
            foreach ($processedLines as $l) {
                $stInsLine->execute([$l['id'], $id, $l['description'], $l['quantity'], $l['price'], $l['tax_rate_id'], $l['tax_amount'], $l['total'], $l['chart_account_id'], $l['classifier_id'], $l['line_order']]);
            }

            $this->pdo->commit();
            return $this->getOrder($companyId, $id);

        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

    public function deleteOrder(string $companyId, string $id): void
    {
        $st = $this->pdo->prepare('SELECT status FROM public.purchase_orders WHERE id = ? AND company_id = ?');
        $st->execute([$id, $companyId]);
        $status = $st->fetchColumn();

        if (!$status) {
            throw new NotFoundException('Orden no encontrada');
        }
        if ($status !== 'draft') {
            throw new BadRequestException('Solo se pueden eliminar órdenes en estado borrador');
        }

        $stDel = $this->pdo->prepare('DELETE FROM public.purchase_orders WHERE id = ?');
        $stDel->execute([$id]);
    }

    public function precommitOrder(string $companyId, string $id): array
    {
        $order = $this->getOrder($companyId, $id);
        if ($order['status'] !== 'draft') {
            throw new BadRequestException('Solo se pueden precomprometer órdenes en estado borrador');
        }

        $this->pdo->beginTransaction();

        try {
            // Find fiscal year
            $year = (int) date('Y', strtotime($order['issue_date']));
            $stYear = $this->pdo->prepare("SELECT id FROM budget.fiscal_years WHERE year = ? AND status = 'open'");
            $stYear->execute([$year]);
            $fiscalYearId = $stYear->fetchColumn();

            if (!$fiscalYearId) {
                throw new BadRequestException("No existe un año fiscal abierto para el año {$year}");
            }

            // Find organizational unit (cost center) from defaults or we can just pick the main/first active cost center.
            // As fallback, we query budget.organizational_units
            $stOrg = $this->pdo->prepare("SELECT id FROM budget.organizational_units WHERE is_active = TRUE LIMIT 1");
            $stOrg->execute();
            $orgUnitId = $stOrg->fetchColumn();
            if (!$orgUnitId) {
                throw new BadRequestException("No hay unidades organizativas activas en el presupuesto");
            }

            // Check availability for each line
            foreach ($order['lines'] as $line) {
                $classifierId = $line['classifier_id'];
                $amount = (float) $line['total'];

                // 1. Get formulated amount
                $stForm = $this->pdo->prepare("SELECT current_amount FROM budget.formulations 
                                               WHERE fiscal_year_id = ? AND classifier_id = ? AND org_unit_id = ?");
                $stForm->execute([$fiscalYearId, $classifierId, $orgUnitId]);
                $currentAmount = (float) $stForm->fetchColumn();

                // 2. Get executed amount
                $stExec = $this->pdo->prepare("SELECT COALESCE(SUM(amount), 0) FROM budget.executions 
                                               WHERE fiscal_year_id = ? AND classifier_id = ? AND org_unit_id = ?");
                $stExec->execute([$fiscalYearId, $classifierId, $orgUnitId]);
                $executed = (float) $stExec->fetchColumn();

                // 3. Get precommitted amount
                $stPre = $this->pdo->prepare("SELECT COALESCE(SUM(amount), 0) FROM budget.pre_commitments 
                                              WHERE fiscal_year_id = ? AND classifier_id = ? AND org_unit_id = ? AND status = 'active'");
                $stPre->execute([$fiscalYearId, $classifierId, $orgUnitId]);
                $precommitted = (float) $stPre->fetchColumn();

                $available = $currentAmount - ($executed + $precommitted);

                if ($available < $amount) {
                    $stName = $this->pdo->prepare("SELECT name, code FROM budget.classifiers WHERE id = ?");
                    $stName->execute([$classifierId]);
                    $cData = $stName->fetch(PDO::FETCH_ASSOC);
                    throw new BadRequestException("Fondos insuficientes para la partida {$cData['code']} - {$cData['name']}. Disponible: " . number_format($available, 2) + ", Requerido: " . number_format($amount, 2));
                }

                // Create pre-commitment
                $stPreIns = $this->pdo->prepare("INSERT INTO budget.pre_commitments (id, fiscal_year_id, org_unit_id, classifier_id, source_document_id, amount, status) 
                                                 VALUES (?, ?, ?, ?, ?, ?, 'active')");
                $stPreIns->execute([Database::uuid(), $fiscalYearId, $orgUnitId, $classifierId, $id, $amount]);
            }

            // Update order status to approved
            $stUpd = $this->pdo->prepare("UPDATE public.purchase_orders SET status = 'approved' WHERE id = ?");
            $stUpd->execute([$id]);

            $this->pdo->commit();
            return $this->getOrder($companyId, $id);

        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}
