<?php

declare(strict_types=1);

namespace ApiModel\Infrastructure\Persistence\Repositories;

use ApiModel\Domain\Repository\BillRepositoryInterface;
use ApiModel\Infrastructure\Persistence\Database;
use PDO;

final class PdoBillRepository implements BillRepositoryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    public function listByCompany(string $companyId, ?string $branchId = null): array
    {
        $sql = 'SELECT b.*, c.name as vendor_name, b.purchase_order_id
                FROM purchases_bills b
                JOIN contacts c ON b.vendor_id = c.id
                WHERE b.company_id = ?';
        $params = [$companyId];

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

        $sql .= ' ORDER BY b.bill_date DESC, b.created_at DESC';

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

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

    public function findById(string $id): ?array
    {
        $st = $this->pdo->prepare(
            'SELECT b.*, c.name as vendor_name, b.purchase_order_id
             FROM purchases_bills b
             JOIN contacts c ON b.vendor_id = c.id
             WHERE b.id = ?
             LIMIT 1'
        );
        $st->execute([$id]);
        $row = $st->fetch(PDO::FETCH_ASSOC);

        return $row !== false ? $row : null;
    }

    public function findLinesByBillId(string $billId): array
    {
        $st = $this->pdo->prepare(
            'SELECT l.*, a.code as account_code, a.name as account_name, t.name as tax_name, t.rate as tax_rate
             FROM purchases_bill_lines l
             JOIN chart_accounts a ON l.chart_account_id = a.id
             LEFT JOIN tax_rates t ON l.tax_rate_id = t.id
             WHERE l.bill_id = ?
             ORDER BY l.line_order ASC'
        );
        $st->execute([$billId]);

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

    public function insert(array $bill): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO 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, created_by, purchase_order_id
             ) VALUES (
                :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, :created_by, :purchase_order_id
             )'
        );
        $st->execute([
            'id' => $bill['id'],
            'company_id' => $bill['company_id'],
            'branch_id' => $bill['branch_id'] ?: null,
            'vendor_id' => $bill['vendor_id'],
            'payable_account_id' => $bill['payable_account_id'],
            'bill_number' => $bill['bill_number'],
            'bill_date' => $bill['bill_date'],
            'due_date' => $bill['due_date'],
            'reference' => $bill['reference'] ?: null,
            'currency_code' => $bill['currency_code'] ?: 'USD',
            'exchange_rate' => $bill['exchange_rate'],
            'discount_amount' => $bill['discount_amount'],
            'tax_amount' => $bill['tax_amount'],
            'subtotal' => $bill['subtotal'],
            'total' => $bill['total'],
            'balance' => $bill['balance'],
            'status' => $bill['status'] ?: 'draft',
            'notes' => $bill['notes'] ?: null,
            'created_by' => $bill['created_by'] ?: null,
            'purchase_order_id' => $bill['purchase_order_id'] ?: null,
        ]);
    }

    public function update(string $id, array $bill): void
    {
        $st = $this->pdo->prepare(
            'UPDATE purchases_bills SET
                branch_id = :branch_id,
                vendor_id = :vendor_id,
                payable_account_id = :payable_account_id,
                bill_date = :bill_date,
                due_date = :due_date,
                reference = :reference,
                currency_code = :currency_code,
                exchange_rate = :exchange_rate,
                discount_amount = :discount_amount,
                tax_amount = :tax_amount,
                subtotal = :subtotal,
                total = :total,
                balance = :balance,
                status = :status,
                notes = :notes,
                purchase_order_id = :purchase_order_id,
                updated_at = CURRENT_TIMESTAMP
             WHERE id = :id'
        );
        $st->execute([
            'id' => $id,
            'branch_id' => $bill['branch_id'] ?: null,
            'vendor_id' => $bill['vendor_id'],
            'payable_account_id' => $bill['payable_account_id'],
            'bill_date' => $bill['bill_date'],
            'due_date' => $bill['due_date'],
            'reference' => $bill['reference'] ?: null,
            'currency_code' => $bill['currency_code'] ?: 'USD',
            'exchange_rate' => $bill['exchange_rate'],
            'discount_amount' => $bill['discount_amount'],
            'tax_amount' => $bill['tax_amount'],
            'subtotal' => $bill['subtotal'],
            'total' => $bill['total'],
            'balance' => $bill['balance'],
            'status' => $bill['status'] ?: 'draft',
            'notes' => $bill['notes'] ?: null,
            'purchase_order_id' => $bill['purchase_order_id'] ?: null,
        ]);
    }

    public function insertLines(string $billId, array $lines): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO purchases_bill_lines (
                id, bill_id, description, quantity, price, tax_rate_id, tax_amount, total, chart_account_id, line_order,
                budget_classifier_id, budget_org_unit_id
             ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
             )'
        );

        foreach ($lines as $index => $line) {
            $id = $line['id'] ?? Database::uuid();
            $st->execute([
                $id,
                $billId,
                $line['description'] ?: null,
                $line['quantity'],
                $line['price'],
                $line['tax_rate_id'] ?: null,
                $line['tax_amount'],
                $line['total'],
                $line['chart_account_id'],
                $index,
                $line['budget_classifier_id'] ?: null,
                $line['budget_org_unit_id'] ?: null,
            ]);
        }
    }

    public function deleteLines(string $billId): void
    {
        $st = $this->pdo->prepare('DELETE FROM purchases_bill_lines WHERE bill_id = ?');
        $st->execute([$billId]);
    }

    public function delete(string $id): void
    {
        $st = $this->pdo->prepare('DELETE FROM purchases_bills WHERE id = ?');
        $st->execute([$id]);
    }

    public function nextBillNumber(string $companyId): string
    {
        $st = $this->pdo->prepare('SELECT COUNT(*) FROM purchases_bills WHERE company_id = ?');
        $st->execute([$companyId]);
        $count = (int) $st->fetchColumn();

        return 'FB-' . str_pad((string) ($count + 1), 6, '0', STR_PAD_LEFT);
    }

    public function updateStatusAndBalance(string $id, string $status, string $balance): void
    {
        $st = $this->pdo->prepare('UPDATE purchases_bills SET status = ?, balance = ? WHERE id = ?');
        $st->execute([$status, $balance, $id]);
    }

    public function listUnpaidByVendor(string $companyId, string $vendorId): array
    {
        $st = $this->pdo->prepare(
            'SELECT b.*, c.name as vendor_name, b.purchase_order_id
             FROM purchases_bills b
             JOIN contacts c ON b.vendor_id = c.id
             WHERE b.company_id = ? AND b.vendor_id = ? AND b.balance > 0 AND b.status IN (\'posted\', \'partially_paid\')
             ORDER BY b.bill_date ASC, b.created_at ASC'
        );
        $st->execute([$companyId, $vendorId]);

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