<?php
class Quotation extends Model
{
    public function all(): array
    {
        return $this->fetchAll('SELECT q.*, c.name AS customer_name FROM quotations q LEFT JOIN customers c ON q.customer_id = c.id ORDER BY q.created_at DESC');
    }

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

    public function findById(int $id): ?array
    {
        return $this->fetch('SELECT q.*, c.name AS customer_name FROM quotations q LEFT JOIN customers c ON q.customer_id = c.id WHERE q.id = :id LIMIT 1', ['id' => $id]);
    }

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

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO quotations (quotation_number, customer_id, client_name, client_email, total_amount, labour_cost, material_cost, discount, vat, status, created_by, created_at) VALUES (:quotation_number, :customer_id, :client_name, :client_email, :total_amount, :labour_cost, :material_cost, :discount, :vat, :status, :created_by, NOW())';
        $this->execute($sql, [
            'quotation_number' => $data['quotation_number'],
            'customer_id' => $data['customer_id'] ?? null,
            'client_name' => $data['client_name'],
            'client_email' => $data['client_email'] ?? null,
            'total_amount' => $data['total_amount'] ?? 0.00,
            'labour_cost' => $data['labour_cost'] ?? 0.00,
            'material_cost' => $data['material_cost'] ?? 0.00,
            'discount' => $data['discount'] ?? 0.00,
            'vat' => $data['vat'] ?? 0.00,
            'status' => $data['status'] ?? 'Pending',
            'created_by' => $data['created_by'] ?? null,
        ]);
        return $this->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $sql = 'UPDATE quotations SET quotation_number = :quotation_number, customer_id = :customer_id, client_name = :client_name, client_email = :client_email, total_amount = :total_amount, labour_cost = :labour_cost, material_cost = :material_cost, discount = :discount, vat = :vat, status = :status WHERE id = :id';
        return $this->execute($sql, [
            'quotation_number' => $data['quotation_number'],
            'customer_id' => $data['customer_id'] ?? null,
            'client_name' => $data['client_name'],
            'client_email' => $data['client_email'] ?? null,
            'total_amount' => $data['total_amount'] ?? 0.00,
            'labour_cost' => $data['labour_cost'] ?? 0.00,
            'material_cost' => $data['material_cost'] ?? 0.00,
            'discount' => $data['discount'] ?? 0.00,
            'vat' => $data['vat'] ?? 0.00,
            'status' => $data['status'] ?? 'Pending',
            'id' => $id,
        ]);
    }

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

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

    public function saveItems(int $quotationId, array $items): bool
    {
        $this->db->beginTransaction();
        try {
            $this->execute('DELETE FROM quotation_items WHERE quotation_id = :quotation_id', ['quotation_id' => $quotationId]);
            $sql = 'INSERT INTO quotation_items (quotation_id, description, quantity, unit_price, total_price) VALUES (:quotation_id, :description, :quantity, :unit_price, :total_price)';
            foreach ($items as $item) {
                $this->execute($sql, [
                    'quotation_id' => $quotationId,
                    '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 $id, string $status): bool
    {
        return $this->execute('UPDATE quotations SET status = :status WHERE id = :id', ['status' => $status, 'id' => $id]);
    }
}
