<?php

namespace App\Repositories;

use App\Helpers\DateHelper;
use App\Models\Group;
use App\Models\Location;
use App\Models\Modality;
use App\Models\Procedure;
use App\Models\ProcedureRoute;
use App\Models\Process;
use App\Models\Subgroup;
use App\Models\UserComunity;
use Config\Database;
use Exception;
use PDO;

class ProcessRepository
{

    public function getDistinctPeriods()
    {
        $db = Database::getConnection();
        $query = "SELECT DISTINCT period FROM processes ORDER BY period DESC";
        $stmt = $db->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    public function generateExpedientNumber()
    {
        $db = Database::getConnection();

        $stmt = $db->prepare("SELECT id, start_number, current_number FROM expedient_counters WHERE deleted_at IS NULL LIMIT 1 FOR UPDATE");
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$row) {
            $startNumber = 1;
            $currentNumber = 0;

            $stmtInsert = $db->prepare("INSERT INTO expedient_counters (start_number, current_number) VALUES (:start_number, :current_number)");
            $stmtInsert->execute([
                ':start_number' => $startNumber,
                ':current_number' => $currentNumber
            ]);
            
            $stmt = $db->prepare("SELECT id, start_number, current_number FROM expedient_counters WHERE id = :id FOR UPDATE");
            $lastId = $db->lastInsertId();
            $stmt->execute([':id' => $lastId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$row) {
                throw new Exception("No se pudo inicializar el contador de expedientes.");
            }
        } else {
            $startNumber = (int) $row['start_number'];
            $currentNumber = (int) $row['current_number'];
        }

        $nextNumber = max($currentNumber + 1, $startNumber);
        $expNumber = 'EXP' . str_pad($nextNumber, 6, '0', STR_PAD_LEFT);

        $stmtUpdate = $db->prepare("UPDATE expedient_counters SET current_number = :current_number WHERE id = :id");
        $stmtUpdate->execute([
            ':current_number' => $nextNumber,
            ':id' => $row['id']
        ]);

        return $expNumber;
    }

    public function generateUniqueKey() {
        $db = Database::getConnection();
        do {
            $key = strtoupper(substr(md5(uniqid(mt_rand(), true)), 0, 4));
            $query = "SELECT COUNT(*) FROM processes WHERE `key` = ?";
            $stmt = $db->prepare($query);
            $stmt->execute([$key]);
            $exists = $stmt->fetchColumn();
        } while ($exists > 0);
        
        return $key;
    }

    public function updateLastLocation(int $processId, int $lastLocationId): bool
    {
        $db = Database::getConnection();

        $sql = "UPDATE processes 
                   SET last_location = :last_location 
                 WHERE id = :id";

        $stmt = $db->prepare($sql);
        return $stmt->execute([
            ':last_location' => $lastLocationId,
            ':id' => $processId
        ]);
    }

    public function searchProcess($data)
    {
        $db = Database::getConnection();

        $query = "
            SELECT p.*
            FROM processes p
            INNER JOIN procedures pr ON p.procedure_id = pr.id
            WHERE ( ? = '' OR pr.modality_id = ? )
            AND p.period = ?
            AND p.num_exped = ?
            AND ( ? = '' OR p.`key` = ? )
            LIMIT 1
        ";

        $stmt = $db->prepare($query);
        $stmt->execute([
            $data['modality_id'],
            $data['modality_id'],
            $data['period'],
            $data['num_exp'],
            $data['key'],
            $data['key']
        ]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result ? new Process($result) : null;
    }

    public function getByUserId($userId)
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT * FROM processes WHERE user_id = :user_id AND deleted_at IS NULL ORDER BY entry_date DESC");
        $stmt->bindParam(":user_id", $userId, PDO::PARAM_INT);
        $stmt->execute();
        
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $processes = [];
    
        foreach ($results as $row) {
            $process = new Process();
            foreach ($row as $key => $value) {
                $process->$key = $value; 
            }
            $processes[] = $process;
        }
        return $processes;
    }

    public function getResolvedByUserId($userId)
    {
        $db = Database::getConnection();
        $stmt = $db->prepare("SELECT * FROM processes WHERE user_id = :user_id AND resolved = 1 AND deleted_at IS NULL ORDER BY entry_date DESC");
        $stmt->bindParam(":user_id", $userId, PDO::PARAM_INT);
        $stmt->execute();

        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $processes = [];

        foreach ($results as $row) {
            $process = new Process();
            foreach ($row as $key => $value) {
                $process->$key = $value; 
            }
            $processes[] = $process;
        }
        return $processes;
    }

    public function getDerivedProcessesByGroup(int $groupId, ?int $subgroupId): array
    {
        $db = Database::getConnection();

        // Usamos una subconsulta para obtener la "actual location" por proceso para ese grupo/subgrupo (MAX(id))
        // y la unimos a processes. Alias con prefijos para hidratar genéricamente.
        $sql = "
            SELECT
                /* --- PROCESS p_* --- */
                p.id                AS p_id,
                p.num_exped         AS p_num_exped,
                p.entry_date        AS p_entry_date,
                p.period            AS p_period,
                p.folios            AS p_folios,
                p.user_id           AS p_user_id,
                p.`key`             AS p_key,
                p.subject           AS p_subject,
                p.rationale         AS p_rationale,
                p.reset             AS p_reset,
                p.active            AS p_active,
                p.deleted_at        AS p_deleted_at,
                p.last_location     AS p_last_location,
                p.procedure_id      AS p_procedure_id,
                p.custom_procedure  AS p_custom_procedure,
                p.resolved          AS p_resolved,
                p.responsible_id    AS p_responsible_id,
                p.group_derived     AS p_group_derived,
                p.subgroup_derived  AS p_subgroup_derived,
                p.general_state     AS p_general_state,
                p.re_entered        AS p_re_entered,
                p.correction_deadline AS p_correction_deadline,
                p.derivation_mode   AS p_derivation_mode

                /* --- CURRENT LOCATION l_* (para ese grupo/subgrupo) --- */
            , l.id                AS l_id,
                l.process_id        AS l_process_id,
                l.group_id          AS l_group_id,
                l.subgroup_id       AS l_subgroup_id,
                l.procedure_route_id AS l_procedure_route_id,
                l.state             AS l_state,
                l.derived_date      AS l_derived_date,
                l.receipt_date      AS l_receipt_date,
                l.attended_date     AS l_attended_date,
                l.comment           AS l_comment,
                l.deleted_at        AS l_deleted_at

                /* --- PROCEDURE ROUTE pr_* --- */
            , pr.id               AS pr_id,
                pr.procedure_id     AS pr_procedure_id,
                pr.group_id         AS pr_group_id,
                pr.subgroup_id      AS pr_subgroup_id,
                pr.previous_step_id AS pr_previous_step_id,
                pr.next_step_id     AS pr_next_step_id,
                pr.created_at       AS pr_created_at,
                pr.active           AS pr_active,
                pr.deleted_at       AS pr_deleted_at

                /* --- GROUP g_* --- */
            , g.id                AS g_id,
                g.abbreviation      AS g_abbreviation,
                g.description       AS g_description,
                g.area_group_type_id AS g_area_group_type_id,
                g.active            AS g_active,
                g.deleted_at        AS g_deleted_at

                /* --- SUBGROUP sg_* --- */
            , sg.id               AS sg_id,
                sg.description      AS sg_description,
                sg.active           AS sg_active,
                sg.deleted_at       AS sg_deleted_at

                /* --- USER (del proceso) u_* --- */
            , uc.id               AS u_id,
                uc.name             AS u_name,
                uc.paternal_surname AS u_paternal_surname,
                uc.maternal_surname AS u_maternal_surname,
                uc.dni              AS u_dni,
                uc.ruc              AS u_ruc,
                uc.company_name     AS u_company_name,
                uc.email            AS u_email,
                uc.phone            AS u_phone,
                uc.deleted_at       AS u_deleted_at

                /* --- PROCEDURE proc_* --- */
            , proc.id        AS proc_id
            , proc.description      AS proc_description
            , proc.group_id      AS proc_group_id
            , proc.subgroup_id    AS proc_subgroup_id
            , proc.time     AS proc_time
            , proc.modality_id  AS proc_modality_id
            , proc.active   AS proc_active
            , proc.correction_days  AS proc_correction_days
            , proc.deleted_at AS proc_deleted_at

                /* --- Flags para primer/último nodo (sin N+1) --- */
            , CASE WHEN pr.previous_step_id IS NULL THEN 1 ELSE 0 END AS l_is_first_node,
                CASE WHEN pr.next_step_id     IS NULL THEN 1 ELSE 0 END AS l_is_final_node

            FROM processes p

            /* Subtabla: última location por proceso PARA ESTE grupo/subgrupo */
            INNER JOIN (
                SELECT l1.*
                FROM locations l1
                INNER JOIN (
                    SELECT process_id, MAX(id) AS max_id
                    FROM locations
                    WHERE group_id = :groupId
                    AND deleted_at IS NULL
        ";  

        // 👇 dinámicamente agregamos el filtro según $subgroupId
        if ($subgroupId !== null) {
            $sql .= " AND subgroup_id = :subgroupId ";
        } else {
            $sql .= " AND subgroup_id IS NULL ";
        }

        $sql .= "
                    GROUP BY process_id
                ) lm ON lm.process_id = l1.process_id AND l1.id = lm.max_id
            ) l ON l.process_id = p.id

            /* Resto de relaciones sin N+1 */
            LEFT JOIN procedure_routes pr
                ON pr.id = l.procedure_route_id
                AND pr.active = 1
                AND pr.deleted_at IS NULL

            LEFT JOIN `groups` g
                ON g.id = l.group_id
                AND g.deleted_at IS NULL

            LEFT JOIN subgroups sg
                ON sg.id = l.subgroup_id
                AND sg.deleted_at IS NULL

            LEFT JOIN comunity_users uc
                ON uc.id = p.user_id
                AND uc.deleted_at IS NULL

            LEFT JOIN procedures proc
                ON proc.id = p.procedure_id
                AND proc.deleted_at IS NULL

            WHERE p.deleted_at IS NULL
            ORDER BY p.entry_date DESC
        ";


        $stmt = $db->prepare($sql);

        // Bind params (si $subgroupId es null, PDO lo maneja y la condición OR del SQL se encarga)
        $stmt->bindValue(':groupId', $groupId, PDO::PARAM_INT);

        if ($subgroupId !== null) {
            $stmt->bindValue(':subgroupId', $subgroupId, PDO::PARAM_INT);
        }

        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $processes = [];
        foreach ($rows as $row) {
            // --- Hydrate PROCESS ---
            $process = $this->hydrateFromPrefix(new Process(), $row, 'p_');

            // --- Hydrate CURRENT LOCATION ---
            $location = $this->hydrateFromPrefix(new Location(), $row, 'l_');

            // --- Hydrate ROUTE (si existe) ---
            $route = null;
            if (!empty($row['pr_id'])) {
                $route = $this->hydrateFromPrefix(new ProcedureRoute(), $row, 'pr_');
            }

            // --- Hydrate GROUP (si existe) ---
            $group = null;
            if (!empty($row['g_id'])) {
                $group = $this->hydrateFromPrefix(new Group(), $row, 'g_');
            }

            // --- Hydrate SUBGROUP (si existe) ---
            $subgroup = null;
            if (!empty($row['sg_id'])) {
                $subgroup = $this->hydrateFromPrefix(new Subgroup(), $row, 'sg_');
            }

            // --- Hydrate USER (del proceso, no de la location) ---
            $user = null;
            if (!empty($row['u_id'])) {
                $user = $this->hydrateFromPrefix(new UserComunity(), $row, 'u_');
            }

            $procedure = null;
            if (!empty($row['proc_id'])) {
                $procedure = $this->hydrateFromPrefix(new Procedure(), $row, 'proc_');
            }
            $process->procedure = $procedure;

            // Colgamos relaciones en memoria (sin invocar métodos que disparen queries)
            if ($route) {
                // Si prefieres colgar group/subgroup al route:
                $route->group = $group;
                $route->subgroup = $subgroup;
            }

            // Puedes colgarlos también directo en la Location si te resulta más cómodo en la vista:
            $location->group = $group;
            $location->subgroup = $subgroup;
            $location->route = $route;

            // Flags de primer/último nodo (vienen del SELECT)
            $location->is_first_node = !empty($row['l_is_first_node']);
            $location->is_final_node = !empty($row['l_is_final_node']);

            // Finalmente colgamos todo en el proceso
            $process->actualLocation = $location;
            $process->user = $user;

            $processes[] = $process;
        }

        return $processes;
    }

    /**
     * Hidrata un objeto asignando todas las columnas cuyo alias empiece con $prefix.
     * Ej: p_id -> id, p_num_exped -> num_exped, etc.
     */
    private function hydrateFromPrefix(object $obj, array $row, string $prefix): object
    {
        $len = strlen($prefix);
        foreach ($row as $key => $value) {
            if (strncmp($key, $prefix, $len) === 0) {
                $prop = substr($key, $len); // quitar prefijo
                // setear aunque sea null; el modelo tiene props públicas
                $obj->$prop = $value;
            }
        }
        return $obj;
    }

    public function markAsResolved(int $processId): bool
    {
        $db = Database::getConnection();
        $query = "UPDATE processes 
                  SET resolved = 1, general_state = 'RESUELTO' 
                  WHERE id = ?";

        $stmt = $db->prepare($query);
        return $stmt->execute([$processId]);
    }

    public function getUnresolvedProcessesByPeriod($period): array
    {
        $db = Database::getConnection();

        $sql = "
            SELECT p.*, 
                pr.id AS procedure_id, pr.description AS procedure_description, pr.time AS procedure_time, pr.modality_id AS procedure_modality_id,
                pr.active AS procedure_active, pr.correction_days AS procedure_correction_days
            FROM processes p
            INNER JOIN procedures pr ON p.procedure_id = pr.id
            WHERE p.period = :period
            AND p.deleted_at IS NULL
            AND p.resolved = 0
        ";

        $stmt = $db->prepare($sql);
        $stmt->execute([':period' => $period]);

        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $processes = [];
        foreach ($rows as $row) {
            // Construyes el Procedure
            $procedureData = [
                'id' => $row['procedure_id'],
                'description' => $row['procedure_description'],
                'time' => $row['procedure_time'],
                'modality_id' => $row['procedure_modality_id'],
                'active' => $row['procedure_active'],
                'correction_days' => $row['procedure_correction_days'],
            ];
            $procedure = new Procedure($procedureData);

            // Construyes el Process
            $processData = $row;
            unset($processData['procedure_id'], $processData['procedure_description'], $processData['procedure_time'], $processData['procedure_modality_id'], $processData['procedure_active'], $processData['procedure_correction_days']);

            $process = new Process($processData);
            $process->procedure = $procedure; // asocias la relación

            $processes[] = $process;
        }

        return $processes;
    }



    public function searchByName(array $filters): array {
        $sql = "SELECT p.* 
                FROM processes p
                JOIN comunity_users u ON u.id = p.user_id
                WHERE u.paternal_surname LIKE :paternal
                  AND u.maternal_surname LIKE :maternal
                  AND u.name LIKE :name
                  AND p.deleted_at IS NULL";
        
        $params = [
            ':paternal' => '%' . $filters['paternal_surname'] . '%',
            ':maternal' => '%' . $filters['maternal_surname'] . '%',
            ':name' => '%' . $filters['name'] . '%',
        ];
    
        if ($filters['start_date']) {
            $sql .= " AND p.entry_date >= :start_date";
            $params[':start_date'] = $filters['start_date'];
        }
    
        if ($filters['end_date']) {
            $nextDay = date('Y-m-d', strtotime($filters['end_date'] . ' +1 day'));
            $sql .= " AND p.entry_date < :end_date";
            $params[':end_date'] = $nextDay;
        }
    
        $stmt = Database::getConnection()->prepare($sql);
        $stmt->execute($params);
    
        $processes = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $processes[] = new Process($row);
        }
    
        return $processes;
    }
    

    public function searchByDni($filters) {
        $sql = "SELECT p.* 
                FROM processes p
                JOIN comunity_users u ON u.id = p.user_id
                WHERE dni = :dni
                AND p.deleted_at IS NULL";
    
        if ($filters['start_date']) {
            $sql .= " AND entry_date >= :start_date";
        }
        if ($filters['end_date']) {
            $sql .= " AND entry_date < :end_date";
        }
    
        $stmt = Database::getConnection()->prepare($sql);
    
        $stmt->bindParam(':dni', $filters['dni']);
        
        if ($filters['start_date']) {
            $stmt->bindParam(':start_date', $filters['start_date']);
        }
        if ($filters['end_date']) {
            $nextDay = date('Y-m-d', strtotime($filters['end_date'] . ' +1 day'));
            $stmt->bindParam(':end_date', $nextDay);
        }
    
        $stmt->execute();
    
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        $processes = [];
        foreach ($results as $row) {
            $processes[] = new Process($row);
        }
    
        return $processes;
    }  
    
    public function searchByDateRange(array $filters): array {
        $sql = "SELECT p.* 
                FROM processes p
                WHERE p.entry_date >= :start_date
                AND p.entry_date < :end_date
                AND p.deleted_at IS NULL";
    
        $nextDay = date('Y-m-d', strtotime($filters['end_date'] . ' +1 day'));

        $params = [
            ':start_date' => $filters['start_date'],
            ':end_date' => $nextDay,
        ];
    
        $stmt = Database::getConnection()->prepare($sql);
        $stmt->execute($params);
    
        $processes = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $processes[] = new Process($row);
        }
    
        return $processes;
    }

    public function searchByPriority($filters) {  // YA NO EXISTIRÁ EL STATE_ID
        $sql = "SELECT p.* 
                FROM processes p
                WHERE p.state_id = :priority
                AND p.deleted_at IS NULL";
    
        if (!empty($filters['start_date'])) {
            $sql .= " AND p.entry_date >= :start_date";
        }
        if (!empty($filters['end_date'])) {
            $sql .= " AND p.entry_date < :end_date";
        }
    
        $stmt = Database::getConnection()->prepare($sql);
    
        $stmt->bindParam(':priority', $filters['priority'], PDO::PARAM_INT);
    
        if (!empty($filters['start_date'])) {
            $stmt->bindParam(':start_date', $filters['start_date']);
        }
        if (!empty($filters['end_date'])) {
            $nextDay = date('Y-m-d', strtotime($filters['end_date'] . ' +1 day'));
            $stmt->bindParam(':end_date', $nextDay);
        }
    
        $stmt->execute();
    
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        $processes = [];
        foreach ($results as $row) {
            $processes[] = new Process($row);
        }
    
        return $processes;
    }

    public function searchByProcedure($filters) {
        $sql = "SELECT p.* 
                FROM processes p
                WHERE p.procedure_id = :procedure_id
                AND p.deleted_at IS NULL";
    
        if (!empty($filters['start_date'])) {
            $sql .= " AND p.entry_date >= :start_date";
        }
        if (!empty($filters['end_date'])) {
            $sql .= " AND p.entry_date < :end_date";
        }
    
        $stmt = Database::getConnection()->prepare($sql);
    
        $stmt->bindParam(':procedure_id', $filters['procedure_id'], PDO::PARAM_INT);
    
        if (!empty($filters['start_date'])) {
            $stmt->bindParam(':start_date', $filters['start_date']);
        }
        if (!empty($filters['end_date'])) {
            $nextDay = date('Y-m-d', strtotime($filters['end_date'] . ' +1 day'));
            $stmt->bindParam(':end_date', $nextDay);
        }
    
        $stmt->execute();
    
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        $processes = [];
        foreach ($results as $row) {
            $processes[] = new Process($row);
        }
    
        return $processes;
    }

    public function searchByDerivationDate($filters) {
        $sql = "SELECT 
                l.*, 
                p.id AS process_id,
                p.num_exped, 
                p.subject, 
                p.period,
                g.description AS group_name, 
                sg.description AS subgroup_name,
                pr.description AS procedure_description,
                CONCAT(u.name, ' ', u.paternal_surname, ' ', u.maternal_surname) AS user_name,
                u.dni AS user_dni
            FROM locations l
            JOIN processes p ON p.id = l.process_id
            LEFT JOIN `groups` g ON g.id = l.group_id
            LEFT JOIN subgroups sg ON sg.id = l.subgroup_id
            LEFT JOIN procedures pr ON pr.id = p.procedure_id 
            LEFT JOIN comunity_users u ON u.id = p.user_id
            WHERE l.derived_date IS NOT NULL
            AND p.deleted_at IS NULL";


    
        $groupingType = $filters['grouping_type'] ?? null;
    
        if ($groupingType === 'day' && !empty($filters['day_date'])) {
            $nextDay = date('Y-m-d', strtotime($filters['day_date'] . ' +1 day'));
            $sql .= " AND l.derived_date >= :day_start AND l.derived_date < :day_end";
        } elseif ($groupingType === 'month' && !empty($filters['month']) && !empty($filters['month_year'])) {
            $sql .= " AND MONTH(l.derived_date) = :month AND YEAR(l.derived_date) = :month_year";
        } elseif ($groupingType === 'year' && !empty($filters['year'])) {
            $sql .= " AND YEAR(l.derived_date) = :year";
        } elseif ($groupingType === 'period' && !empty($filters['period'])) {
            $sql .= " AND p.period = :period";
        }
    
        $sql .= " ORDER BY l.derived_date DESC";
    
        $stmt = Database::getConnection()->prepare($sql);
    
        if ($groupingType === 'day' && !empty($filters['day_date'])) {
            $stmt->bindParam(':day_start', $filters['day_date']);
            $stmt->bindParam(':day_end', $nextDay);
        } elseif ($groupingType === 'month' && !empty($filters['month']) && !empty($filters['month_year'])) {
            $stmt->bindParam(':month', $filters['month']);
            $stmt->bindParam(':month_year', $filters['month_year']);
        } elseif ($groupingType === 'year' && !empty($filters['year'])) {
            $stmt->bindParam(':year', $filters['year']);
        } elseif ($groupingType === 'period' && !empty($filters['period'])) {
            $stmt->bindParam(':period', $filters['period']);
        }
    
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        return $results;
    }

    public function searchByState($filters = [])
    {
        $conn = Database::getConnection();

        $sql = "
            SELECT
                p.*, 
                proc.description AS procedimiento,
                proc.time AS total_days,
                g.description AS grupo,
                sg.description AS subgrupo,
                a.description AS area,
                gt.description AS tipo_grupo,
                l.group_id,
                l.subgroup_id
            FROM processes p
            LEFT JOIN locations l ON p.last_location = l.id
            LEFT JOIN `groups` g ON l.group_id = g.id
            LEFT JOIN subgroups sg ON l.subgroup_id = sg.id
            LEFT JOIN area_group_types agt ON g.area_group_type_id = agt.id
            LEFT JOIN areas a ON agt.area_id = a.id
            LEFT JOIN group_types gt ON agt.group_type_id = gt.id
            LEFT JOIN procedures proc ON p.procedure_id = proc.id
            WHERE p.deleted_at IS NULL
            AND l.deleted_at IS NULL
            AND p.resolved = 0
        ";


        $params = [];

        $groupingType = $filters['grouping_type'] ?? null;

        if ($groupingType === 'day' && !empty($filters['day_date'])) {
            $nextDay = date('Y-m-d', strtotime($filters['day_date'] . ' +1 day'));
            $sql .= " AND l.derived_date >= :day_start AND l.derived_date < :day_end";
            $params[':day_start'] = $filters['day_date'];
            $params[':day_end'] = $nextDay;
        } elseif ($groupingType === 'month' && !empty($filters['month']) && !empty($filters['month_year'])) {
            $sql .= " AND MONTH(l.derived_date) = :month AND YEAR(l.derived_date) = :month_year";
            $params[':month'] = $filters['month'];
            $params[':month_year'] = $filters['month_year'];
        } elseif ($groupingType === 'year' && !empty($filters['year'])) {
            $sql .= " AND YEAR(l.derived_date) = :year";
            $params[':year'] = $filters['year'];
        } elseif ($groupingType === 'period' && !empty($filters['period'])) {
            $sql .= " AND p.period = :period";
            $params[':period'] = $filters['period'];
        } elseif ($groupingType === 'range' && !empty($filters['start_date']) && !empty($filters['end_date'])) {
            $nextDay = date('Y-m-d', strtotime($filters['end_date'] . ' +1 day'));
            $sql .= " AND l.derived_date >= :start_date AND l.derived_date < :end_date";
            $params[':start_date'] = $filters['start_date'];
            $params[':end_date'] = $nextDay;
        }

        if (!empty($filters['area_id'])) {
            $sql .= " AND agt.area_id = :area_id";
            $params[':area_id'] = $filters['area_id'];
        }

        if (!empty($filters['group_type_id'])) {
            $sql .= " AND agt.group_type_id = :group_type_id";
            $params[':group_type_id'] = $filters['group_type_id'];
        }

        $groupId = $filters['group_id'] ?? null;
        $subgroupId = $filters['subgroup_id'] ?? null;

        if ($groupId) {
            $sql .= " AND l.group_id = :group_id";
            $params[':group_id'] = $groupId;

            if ($subgroupId) {
                $sql .= " AND l.subgroup_id = :subgroup_id";
                $params[':subgroup_id'] = $subgroupId;
            } else {
                $sql .= " AND l.subgroup_id IS NULL";
            }
        }

        $sql .= " ORDER BY p.id, l.derived_date";

        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return $rows;
    }

    public function searchByProcessNumber(array $filters): array
    {
        $sql = "SELECT p.*
                FROM processes p
                WHERE p.period = :period
                AND p.num_exped = :num_exped
                AND p.deleted_at IS NULL";

        $params = [
            ':period' => $filters['period'],
            ':num_exped' => $filters['num_exped'],
        ];

        $stmt = Database::getConnection()->prepare($sql);
        $stmt->execute($params);

        $processes = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $processes[] = new Process($row);
        }
        return $processes;
    }

    public function groupStateResultsByHierarchy(array $stateResults): array
    {
        $agrupados = [];

        foreach ($stateResults as $item) {
            $area = $item['area'] ?? 'Sin Área';
            $grupo = $item['grupo'] ?? 'Sin Grupo';
            $subgrupo = $item['subgrupo'] ?? null;

            if (!isset($agrupados[$area])) {
                $agrupados[$area] = [];
            }

            if (!isset($agrupados[$area][$grupo])) {
                $agrupados[$area][$grupo] = [];
            }

            $subKey = $subgrupo ?? 'default';

            if (!isset($agrupados[$area][$grupo][$subKey])) {
                $agrupados[$area][$grupo][$subKey] = [];
            }

            $agrupados[$area][$grupo][$subKey][] = $item;
        }

        return $agrupados;
    }
}