<?php

namespace App\Models\Perfex;

use CodeIgniter\Model;

class PerfexProjectsTasksModel extends Model
{
    protected $DBGroup = 'perfex';

    public function getTasksByProjectId(int $projectId): array
    {
        if ($projectId <= 0) {
            return [];
        }

        $excludedStatusesByProject = [
            6 => [15, 12],
            4 => [15, 12],
        ];
        $excludedStatuses = $excludedStatusesByProject[$projectId] ?? [];
        $statusWhere = '';
        $params = [$projectId];

        if (!empty($excludedStatuses)) {
            $placeholders = implode(',', array_fill(0, count($excludedStatuses), '?'));
            $statusWhere = "\n              AND t.status NOT IN ($placeholders)";
            foreach ($excludedStatuses as $statusId) {
                $params[] = (int)$statusId;
            }
        }

        $sql = "
            SELECT
                t.id,
                t.name,
                t.description,
                t.status,
                t.startdate,
                t.duedate,
                t.dateadded,
                t.priority,
                t.addedfrom,
                ts.name as status_name,
                ts.color as status_color,
                c.firstname as creator_firstname,
                c.lastname as creator_lastname
            FROM tbltasks t
            LEFT JOIN tbltask_statuses ts ON ts.id = t.status
            LEFT JOIN tblstaff c ON c.staffid = t.addedfrom
            WHERE t.rel_type = 'project'
              AND t.rel_id = ?
              {$statusWhere}
            ORDER BY
                CASE
                    WHEN t.duedate IS NULL THEN 1
                    WHEN CAST(t.duedate AS CHAR) LIKE '0000-00-00%' THEN 1
                    ELSE 0
                END ASC,
                t.duedate ASC,
                t.id DESC
        ";

        return $this->db->query($sql, $params)->getResultArray();
    }

    public function getProjectMeta(int $projectId): array
    {
        if ($projectId <= 0) {
            return [];
        }

        $sql = "
            SELECT id, name, status, start_date, deadline
            FROM tblprojects
            WHERE id = ?
            LIMIT 1
        ";

        $row = $this->db->query($sql, [$projectId])->getRowArray();

        return $row ?? [];
    }
}
