<?php
class Invoice extends Model
{
    public function all(): array
    {
        return $this->fetchAll('SELECT i.*, c.name AS customer_name, COALESCE(i.total_amount - i.paid_amount, 0.00) AS outstanding_amount FROM invoices i LEFT JOIN customers c ON i.customer_id = c.id ORDER BY i.created_at DESC');
    }

    public function find(int $id): ?array
    {
        return $this->fetch('SELECT * FROM invoices WHERE id = :id LIMIT 1', ['id' => $id]);
    }

    public function findByNumber(string $invoiceNumber): ?array
    {
        return $this->fetch('SELECT * FROM invoices WHERE invoice_number = :invoice_number LIMIT 1', ['invoice_number' => $invoiceNumber]);
    }

    public function findWithCustomer(int $id): ?array
    {
        return $this->fetch(
            'SELECT i.*, c.name AS customer_name, c.email AS customer_email, c.phone AS customer_phone, c.company AS customer_company, COALESCE(i.total_amount - i.paid_amount, 0.00) AS outstanding_amount FROM invoices i LEFT JOIN customers c ON i.customer_id = c.id WHERE i.id = :id LIMIT 1',
            ['id' => $id]
        );
    }

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO invoices (invoice_number, customer_id, quotation_id, total_amount, paid_amount, due_date, status, notes, created_at) VALUES (:invoice_number, :customer_id, :quotation_id, :total_amount, :paid_amount, :due_date, :status, :notes, NOW())';
        $this->execute($sql, [
            'invoice_number' => $data['invoice_number'],
            'customer_id' => $data['customer_id'],
            'quotation_id' => $data['quotation_id'] ?? null,
            'total_amount' => $data['total_amount'],
            'paid_amount' => $data['paid_amount'],
            'due_date' => $data['due_date'] ?: null,
            'status' => $data['status'],
            'notes' => $data['notes'] ?? null,
        ]);
        return $this->lastInsertId();
    }

    public function update(int $invoiceId, array $data): bool
    {
        return $this->execute(
            'UPDATE invoices SET invoice_number = :invoice_number, customer_id = :customer_id, quotation_id = :quotation_id, total_amount = :total_amount, paid_amount = :paid_amount, due_date = :due_date, status = :status, notes = :notes WHERE id = :id',
            [
                'invoice_number' => $data['invoice_number'],
                'customer_id' => $data['customer_id'],
                'quotation_id' => $data['quotation_id'] ?? null,
                'total_amount' => $data['total_amount'],
                'paid_amount' => $data['paid_amount'],
                'due_date' => $data['due_date'] ?: null,
                'status' => $data['status'],
                'notes' => $data['notes'] ?? null,
                'id' => $invoiceId,
            ]
        );
    }

    public function delete(int $invoiceId): bool
    {
        return $this->execute('DELETE FROM invoices WHERE id = :id', ['id' => $invoiceId]);
    }

    public function getItems(int $invoiceId): array
    {
        return $this->fetchAll('SELECT * FROM invoice_items WHERE invoice_id = :invoice_id ORDER BY id ASC', ['invoice_id' => $invoiceId]);
    }

    public function saveItems(int $invoiceId, array $items): bool
    {
        $this->db->beginTransaction();
        try {
            $this->execute('DELETE FROM invoice_items WHERE invoice_id = :invoice_id', ['invoice_id' => $invoiceId]);
            $sql = 'INSERT INTO invoice_items (invoice_id, description, quantity, unit_price, total_price) VALUES (:invoice_id, :description, :quantity, :unit_price, :total_price)';
            foreach ($items as $item) {
                $this->execute($sql, [
                    'invoice_id' => $invoiceId,
                    'description' => $item['description'],
                    'quantity' => $item['quantity'],
                    'unit_price' => $item['unit_price'],
                    'total_price' => $item['total_price'],
                ]);
            }
            $this->db->commit();
            return true;
        } catch (Throwable $e) {
            $this->db->rollBack();
            return false;
        }
    }

    public function updateStatus(int $invoiceId, string $status): bool
    {
        return $this->execute('UPDATE invoices SET status = :status WHERE id = :id', ['status' => $status, 'id' => $invoiceId]);
    }

    public function countByStatus(string $status): int
    {
        $result = $this->fetch('SELECT COUNT(*) AS total FROM invoices WHERE status = :status', ['status' => $status]);
        return (int)($result['total'] ?? 0);
    }
}
