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

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

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

    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO projects (customer_id, name, client_name, project_manager, start_date, end_date, status, progress, budget, notes, created_at) VALUES (:customer_id, :name, :client_name, :project_manager, :start_date, :end_date, :status, :progress, :budget, :notes, NOW())';
        $this->execute($sql, [
            'customer_id' => $data['customer_id'] ?? null,
            'name' => $data['name'],
            'client_name' => $data['client_name'],
            'project_manager' => $data['project_manager'] ?? null,
            'start_date' => $data['start_date'] ?? null,
            'end_date' => $data['end_date'] ?? null,
            'status' => $data['status'] ?? 'Planning',
            'progress' => (int)($data['progress'] ?? 0),
            'budget' => $data['budget'] ?? 0.00,
            'notes' => $data['notes'] ?? null,
        ]);
        return $this->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $sql = 'UPDATE projects SET customer_id = :customer_id, name = :name, client_name = :client_name, project_manager = :project_manager, start_date = :start_date, end_date = :end_date, status = :status, progress = :progress, budget = :budget, notes = :notes WHERE id = :id';
        return $this->execute($sql, [
            'customer_id' => $data['customer_id'] ?? null,
            'name' => $data['name'],
            'client_name' => $data['client_name'],
            'project_manager' => $data['project_manager'] ?? null,
            'start_date' => $data['start_date'] ?? null,
            'end_date' => $data['end_date'] ?? null,
            'status' => $data['status'] ?? 'Planning',
            'progress' => (int)($data['progress'] ?? 0),
            'budget' => $data['budget'] ?? 0.00,
            'notes' => $data['notes'] ?? null,
            'id' => $id,
        ]);
    }

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