<?php
declare(strict_types=1);

class ProjectModel {
    private PDO $db;

    public function __construct(PDO $db) {
        $this->db = $db;
    }

    public function getListForRole(string $role, int $userId, ?int $year = null, ?string $status = null): array {
        $where = [];
        $params = [];

        if (gp_is_secretary_like_role($role)) {
            $sql = "
                SELECT p.id, p.code, p.title, p.status, c.name AS customer_name,
                       p.start_date, p.end_date_planned, p.created_at,
                       uc.username AS created_by_username,
                       uu.username AS updated_by_username
                FROM project_edit_permissions pep
                JOIN projects p ON p.id = pep.project_id
                LEFT JOIN customers c ON c.id = p.customer_id
                LEFT JOIN users uc ON uc.id = p.created_by_user_id
                LEFT JOIN users uu ON uu.id = p.updated_by_user_id
                WHERE pep.user_id = :uid AND pep.can_edit = 1
            ";
            $params[':uid'] = $userId;
        } elseif (gp_is_full_project_access_role($role)) {
            $sql = "
                SELECT p.id, p.code, p.title, p.status, c.name AS customer_name,
                       p.start_date, p.end_date_planned, p.created_at,
                       uc.username AS created_by_username,
                       uu.username AS updated_by_username
                FROM projects p
                LEFT JOIN customers c ON c.id = p.customer_id
                LEFT JOIN users uc ON uc.id = p.created_by_user_id
                LEFT JOIN users uu ON uu.id = p.updated_by_user_id
                WHERE 1=1
            ";
        } else {
            return [];
        }

        if ($year !== null && $year > 0) {
            $sql .= " AND (YEAR(p.start_date) = :year OR p.start_date IS NULL OR p.start_date = '0000-00-00' OR p.start_date = '' OR p.start_date LIKE '0000%')";
            $params[':year'] = $year;
        }

        if ($status !== null && $status !== '' && $status !== 'all') {
            $sql .= " AND p.status = :status";
            $params[':status'] = $status;
        }

        $sql .= " ORDER BY p.id DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();

    }

    public function getById(int $id): ?array {
        $stmt = $this->db->prepare("SELECT * FROM projects WHERE id = ?");
        $stmt->execute([$id]);
        $res = $stmt->fetch();
        return $res ?: null;
    }
}

