<?php

declare(strict_types=1);

namespace ApiModel\Infrastructure\Persistence\Repositories;

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

final class PdoExpenseRepository implements ExpenseRepositoryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    public function listByCompany(string $companyId, ?string $branchId = null): array
    {
        $sql = 'SELECT e.*, a.code as account_code, a.name as account_name
                 FROM purchases_expenses e
                 JOIN chart_accounts a ON e.chart_account_id = a.id
                 WHERE e.company_id = ?';
        $params = [$companyId];

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

        $sql .= ' ORDER BY e.payment_date DESC, e.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 e.*, a.code as account_code, a.name as account_name
             FROM purchases_expenses e
             JOIN chart_accounts a ON e.chart_account_id = a.id
             WHERE e.id = ?
             LIMIT 1'
        );
        $st->execute([$id]);
        $row = $st->fetch(PDO::FETCH_ASSOC);

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

    public function findLinesByExpenseId(string $expenseId): 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_expense_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.expense_id = ?
             ORDER BY l.line_order ASC'
        );
        $st->execute([$expenseId]);

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

    public function insert(array $expense): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO purchases_expenses (
                id, company_id, branch_id, expense_number, payment_date,
                payment_method, amount, currency_code, exchange_rate, chart_account_id, notes, created_by
             ) VALUES (
                :id, :company_id, :branch_id, :expense_number, :payment_date,
                :payment_method, :amount, :currency_code, :exchange_rate, :chart_account_id, :notes, :created_by
             )'
        );
        $st->execute([
            'id' => $expense['id'],
            'company_id' => $expense['company_id'],
            'branch_id' => $expense['branch_id'] ?: null,
            'expense_number' => $expense['expense_number'],
            'payment_date' => $expense['payment_date'],
            'payment_method' => $expense['payment_method'] ?: 'cash',
            'amount' => $expense['amount'],
            'currency_code' => $expense['currency_code'] ?: 'USD',
            'exchange_rate' => $expense['exchange_rate'],
            'chart_account_id' => $expense['chart_account_id'],
            'notes' => $expense['notes'] ?: null,
            'created_by' => $expense['created_by'] ?: null,
        ]);
    }

    public function update(string $id, array $expense): void
    {
        $st = $this->pdo->prepare(
            'UPDATE purchases_expenses SET
                branch_id = :branch_id,
                payment_date = :payment_date,
                payment_method = :payment_method,
                amount = :amount,
                currency_code = :currency_code,
                exchange_rate = :exchange_rate,
                chart_account_id = :chart_account_id,
                notes = :notes,
                updated_at = CURRENT_TIMESTAMP
             WHERE id = :id'
        );
        $st->execute([
            'id' => $id,
            'branch_id' => $expense['branch_id'] ?: null,
            'payment_date' => $expense['payment_date'],
            'payment_method' => $expense['payment_method'] ?: 'cash',
            'amount' => $expense['amount'],
            'currency_code' => $expense['currency_code'] ?: 'USD',
            'exchange_rate' => $expense['exchange_rate'],
            'chart_account_id' => $expense['chart_account_id'],
            'notes' => $expense['notes'] ?: null,
        ]);
    }

    public function insertLines(string $expenseId, array $lines): void
    {
        $st = $this->pdo->prepare(
            'INSERT INTO purchases_expense_lines (
                id, expense_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,
                $expenseId,
                $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 $expenseId): void
    {
        $st = $this->pdo->prepare('DELETE FROM purchases_expense_lines WHERE expense_id = ?');
        $st->execute([$expenseId]);
    }

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

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

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