<?php

declare(strict_types=1);

namespace ApiModel\Infrastructure\Persistence\Repositories;

use ApiModel\Domain\Repository\InvoiceRepositoryInterface;
use PDO;

final class PdoInvoiceRepository implements InvoiceRepositoryInterface
{
    public function __construct(private readonly PDO $pdo) {}

    public function listByCompany(string $companyId, ?string $branchId = null): array
    {
        $sql = 'SELECT i.*, c.name as customer_name
                FROM sales_invoices i
                JOIN contacts c ON i.customer_id = c.id
                WHERE i.company_id = ?';
        $params = [$companyId];

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

        $sql .= ' ORDER BY i.invoice_date DESC, i.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 i.*, c.name as customer_name
             FROM sales_invoices i
             JOIN contacts c ON i.customer_id = c.id
             WHERE i.id = ?
             LIMIT 1'
        );
        $st->execute([$id]);
        $row = $st->fetch(PDO::FETCH_ASSOC);

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

    public function findLinesByInvoiceId(string $invoiceId): 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 sales_invoice_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.invoice_id = ?
             ORDER BY l.line_order ASC'
        );
        $st->execute([$invoiceId]);

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

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

    public function update(string $id, array $invoice): void
    {
        $st = $this->pdo->prepare(
            'UPDATE sales_invoices SET
                branch_id = :branch_id,
                customer_id = :customer_id,
                receivable_account_id = :receivable_account_id,
                invoice_date = :invoice_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,
                updated_at = CURRENT_TIMESTAMP
             WHERE id = :id'
        );
        $st->execute([
            'id' => $id,
            'branch_id' => $invoice['branch_id'] ?: null,
            'customer_id' => $invoice['customer_id'],
            'receivable_account_id' => $invoice['receivable_account_id'],
            'invoice_date' => $invoice['invoice_date'],
            'due_date' => $invoice['due_date'],
            'reference' => $invoice['reference'] ?: null,
            'currency_code' => $invoice['currency_code'] ?: 'USD',
            'exchange_rate' => $invoice['exchange_rate'],
            'discount_amount' => $invoice['discount_amount'],
            'tax_amount' => $invoice['tax_amount'],
            'subtotal' => $invoice['subtotal'],
            'total' => $invoice['total'],
            'balance' => $invoice['balance'],
            'status' => $invoice['status'] ?: 'draft',
            'notes' => $invoice['notes'] ?: null,
        ]);
    }

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

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

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

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

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

        return 'FC-' . 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 sales_invoices SET status = ?, balance = ? WHERE id = ?');
        $st->execute([$status, $balance, $id]);
    }

    public function listUnpaidByCustomer(string $companyId, string $customerId): array
    {
        $st = $this->pdo->prepare(
            'SELECT i.*, c.name as customer_name
             FROM sales_invoices i
             JOIN contacts c ON i.customer_id = c.id
             WHERE i.company_id = ? AND i.customer_id = ? AND i.balance > 0 AND i.status IN (\'posted\', \'partially_paid\')
             ORDER BY i.invoice_date ASC, i.created_at ASC'
        );
        $st->execute([$companyId, $customerId]);

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