<?php

namespace App\Repositories;

use App\Models\Process;
use Config\Database;
use PDO;

class DashboardUgelRepository
{
    public function getUgelProcessSummaryByGeneralState(int $period, ?int $groupId, ?int $subgroupId): array
    {
        $db = Database::getConnection();

        $query = "
            SELECT p.general_state, COUNT(*) as count
            FROM processes p
            INNER JOIN locations l ON l.id = p.last_location
            WHERE p.period = :period
            AND p.deleted_at IS NULL
            AND l.group_id = :group_id
        ";

        if ($subgroupId !== null) {
            $query .= " AND l.subgroup_id = :subgroup_id";
        } else {
            $query .= " AND l.subgroup_id IS NULL";
        }

        $query .= " GROUP BY p.general_state";

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

        if ($subgroupId !== null) {
            $stmt->execute([
                ':period' => $period,
                ':group_id' => $groupId,
                ':subgroup_id' => $subgroupId
            ]);
        } else {
            $stmt->execute([
                ':period' => $period,
                ':group_id' => $groupId
            ]);
        }

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

        $summary = [
            'EN PROCESO' => 0,
            'OBSERVADO' => 0,
            'RESUELTO' => 0,
        ];

        foreach ($results as $row) {
            $summary[$row['general_state']] = (int) $row['count'];
        }

        return $summary;
    }

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

        $query = "
            SELECT *
            FROM notifications
            WHERE group_id = :group_id
        ";

        if ($subgroupId !== null) {
            $query .= " AND subgroup_id = :subgroup_id AND user_id IS NULL";
        } else {
            $query .= " AND subgroup_id IS NULL AND user_id IS NULL";
        }

        $query .= " ORDER BY created_at DESC
                    LIMIT :limit";

        $stmt = $db->prepare($query);
        $stmt->bindValue(':group_id', $groupId, PDO::PARAM_INT);

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

        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

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

        return $rows;

        // $notifications = [];
        // foreach ($rows as $row) {
        //     $notifications[] = new Notification($row);
        // }

        // return $notifications;
    }

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

        $query = "
            SELECT l.state, COUNT(*) as count
            FROM processes p
            INNER JOIN locations l ON l.id = p.last_location
            WHERE p.period = :period
            AND p.deleted_at IS NULL
            AND l.group_id = :group_id
        ";

        if ($subgroupId !== null) {
            $query .= " AND l.subgroup_id = :subgroup_id";
        } else {
            $query .= " AND l.subgroup_id IS NULL";
        }

        $query .= " GROUP BY l.state";

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

        if ($subgroupId !== null) {
            $stmt->execute([
                ':period' => $period,
                ':group_id' => $groupId,
                ':subgroup_id' => $subgroupId
            ]);
        } else {
            $stmt->execute([
                ':period' => $period,
                ':group_id' => $groupId
            ]);
        }

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

        return $results;

        // $summary = [];
        // foreach (LocationState::getAll() as $state) {
        //     $summary[$state] = 0;
        // }

        // foreach ($results as $row) {
        //     $summary[$row['state']] = (int)$row['count'];
        // }

        // return $summary;
    }

    public function getLastDerivedProcesses($period, int $groupId, ?int $subgroupId, int $limit = 3): array
    {
        $db = Database::getConnection();

        $query = "
            SELECT p.*
            FROM processes p
            INNER JOIN locations l ON l.id = p.last_location
            WHERE p.period = :period
            AND p.deleted_at IS NULL
            AND l.group_id = :group_id
            AND l.derived_date IS NOT NULL
        ";

        if ($subgroupId !== null) {
            $query .= " AND l.subgroup_id = :subgroup_id";
        } else {
            $query .= " AND l.subgroup_id IS NULL";
        }

        $query .= " ORDER BY l.derived_date DESC LIMIT :limit";

        $stmt = $db->prepare($query);
        $stmt->bindValue(':period', $period, PDO::PARAM_INT);
        $stmt->bindValue(':group_id', $groupId, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);

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

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

        $processes = [];
        foreach ($rows as $row) {
            $processes[] = new Process($row);
        }

        return $processes;
    }
}