<?php
class Document extends Model
{
    public function all(): array
    {
        return $this->fetchAll('SELECT d.*, c.name AS customer_name, p.name AS project_name, i.invoice_number FROM documents d LEFT JOIN customers c ON d.customer_id = c.id LEFT JOIN projects p ON d.project_id = p.id LEFT JOIN invoices i ON d.invoice_id = i.id ORDER BY d.created_at DESC');
    }

    public function create(array $data): bool
    {
        return $this->execute('INSERT INTO documents (customer_id, project_id, invoice_id, title, file_path, document_type, created_at) VALUES (:customer_id, :project_id, :invoice_id, :title, :file_path, :document_type, NOW())', [
            'customer_id' => $data['customer_id'] ?? null,
            'project_id' => $data['project_id'] ?? null,
            'invoice_id' => $data['invoice_id'] ?? null,
            'title' => $data['title'],
            'file_path' => $data['file_path'],
            'document_type' => $data['document_type'],
        ]);
    }

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

    public function updateById(int $id, array $data): bool
    {
        return $this->execute(
            'UPDATE documents SET customer_id = :customer_id, project_id = :project_id, invoice_id = :invoice_id, title = :title, file_path = :file_path, document_type = :document_type WHERE id = :id',
            [
                'customer_id' => $data['customer_id'] ?? null,
                'project_id' => $data['project_id'] ?? null,
                'invoice_id' => $data['invoice_id'] ?? null,
                'title' => $data['title'],
                'file_path' => $data['file_path'],
                'document_type' => $data['document_type'],
                'id' => $id,
            ]
        );
    }

    public function count(): int
    {
        $result = $this->fetch('SELECT COUNT(*) AS total FROM documents');
        return (int)($result['total'] ?? 0);
    }

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

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