<?php

namespace App\Repositories;

use Config\Database;
use PDO;

class DashboardAdminRepository
{
    public function getAdminProcessSummaryByGeneralState($period): array
    {
        $db = Database::getConnection();

        $stmt = $db->prepare("
            SELECT general_state, COUNT(*) as count
            FROM processes
            WHERE period = :period
            AND deleted_at IS NULL
            GROUP BY general_state
        ");
        $stmt->execute([':period' => $period]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $summary = [];

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

        return $summary;
    }

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

        //$states = ProcessGeneralState::getAll();

        $stmt = $db->prepare("
            SELECT 
                a.description AS area,
                p.general_state,
                COUNT(DISTINCT p.id) AS count
            FROM processes p
            INNER JOIN locations l ON l.id = p.last_location
            LEFT JOIN subgroups sg ON sg.id = l.subgroup_id
            LEFT JOIN `groups` g ON g.id = COALESCE(sg.group_id, l.group_id)
            INNER JOIN area_group_types agt ON agt.id = g.area_group_type_id
            INNER JOIN areas a ON a.id = agt.area_id
            WHERE p.period = :period
            AND p.deleted_at IS NULL
            GROUP BY a.description, p.general_state;

        ");
        $stmt->execute([':period' => $period]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $summary = [];

        foreach ($rows as $row) {
            $area = $row['area'];
            $state = $row['general_state'];
            $count = (int)$row['count'];

            if (!isset($summary[$area])) {
                $summary[$area] = [
                    'area' => $area,
                    'total' => 0,
                    'EN PROCESO' => 0,
                    'OBSERVADO' => 0,
                    'RESUELTO' => 0,
                ];
            }

            $summary[$area]['total'] += $count;
            $summary[$area][$state] = $count;
        }

        return array_values(array_map(function ($item) {
            return [
                'area' => $item['area'],
                'total' => $item['total'],
                'en_proceso' => $item['EN PROCESO'] ?? 0,
                'observado' => $item['OBSERVADO'] ?? 0,
                'resuelto' => $item['RESUELTO'] ?? 0,
            ];
        }, $summary));
    }

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

        $stmt = $db->prepare("
            SELECT 
                a.description AS area,
                l.state,
                COUNT(*) AS count
            FROM processes p
            INNER JOIN locations l ON l.id = p.last_location
            LEFT JOIN subgroups sg ON sg.id = l.subgroup_id
            LEFT JOIN `groups` g ON g.id = COALESCE(sg.group_id, l.group_id)
            INNER JOIN area_group_types agt ON agt.id = g.area_group_type_id
            INNER JOIN areas a ON a.id = agt.area_id
            WHERE p.period = :period
            AND l.state IN ('PENDIENTE', 'RECIBIDO', 'DERIVADO', 'OBSERVADO', 'RESUELTO')
            AND p.deleted_at IS NULL
            GROUP BY a.description, l.state
        ");

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



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

        $summary = [];

        foreach ($rows as $row) {
            $area = $row['area'];
            $estado = $row['state'];
            $count = (int)$row['count'];

            if (!isset($summary[$area])) {
                $summary[$area] = [
                    'area' => $area,
                    'PENDIENTE' => 0,
                    'RECIBIDO' => 0,
                    'DERIVADO' => 0,
                    'OBSERVADO' => 0,
                    'RESUELTO' => 0,
                ];
            }

            $summary[$area][$estado] = $count;
        }

        return array_values($summary);
    }
}