<?php

namespace App\Repositories;

use App\Models\Area;
use App\Models\Attachment;
use App\Models\Group;
use App\Models\Location;
use App\Models\ProcedureRoute;
use App\Models\Subgroup;
use Config\Database;
use PDO;

class LocationRepository
{

    public function getActualLocation($processId, $groupId, $subgroupId = null)
    {
        $db = Database::getConnection();
        
        if ($subgroupId) {
            $query = "SELECT * FROM locations 
                    WHERE process_id = ? AND group_id = ? AND subgroup_id = ? 
                    ORDER BY id DESC LIMIT 1";
            $params = [$processId, $groupId, $subgroupId];
        } else {
            $query = "SELECT * FROM locations 
                    WHERE process_id = ? AND group_id = ? AND subgroup_id IS NULL 
                    ORDER BY id DESC LIMIT 1";
            $params = [$processId, $groupId];
        }

        $stmt = $db->prepare($query);
        $stmt->execute($params);
        $locationData = $stmt->fetch(PDO::FETCH_ASSOC);

        return $locationData ? new Location($locationData) : null;
    }   

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

        $query = "SELECT pr.* 
                  FROM procedure_routes pr
                  WHERE pr.previous_step_id = :current_route_id
                  AND pr.deleted_at IS NULL
                  AND pr.active = 1
                  LIMIT 1";

        $stmt = $db->prepare($query);
        $stmt->execute([
            ':current_route_id' => $location->procedure_route_id
        ]);

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

        return $row ? new ProcedureRoute($row) : null;
    }

    public function updateAsReceived(int $id, string $state, string $receiptDate): bool
    {
        $db = Database::getConnection();
        $query = "
            UPDATE locations 
            SET state = ?, 
                receipt_date = ?
            WHERE id = ? 
            AND state = 'PENDIENTE' 
            AND deleted_at IS NULL";

        $stmt = $db->prepare($query);
        return $stmt->execute([$state, $receiptDate, $id]);
    }

    public function updateAsDerived(int $id, string $state, string $receiptDate, string $attendedDate): bool
    {
        $db = Database::getConnection();
        $query = "
            UPDATE locations 
            SET state = ?, 
                receipt_date = ?, 
                attended_date = ?
            WHERE id = ? 
            AND state = 'PENDIENTE' 
            AND deleted_at IS NULL";

        $stmt = $db->prepare($query);
        return $stmt->execute([$state, $receiptDate, $attendedDate, $id]);
    }

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

        if ($subgroupId) {
            $sql = "SELECT * FROM locations
                    WHERE group_id = ? AND subgroup_id = ?
                      AND state = 'PENDIENTE' AND deleted_at IS NULL";
            $params = [$groupId, $subgroupId];
        } else {
            $sql = "SELECT * FROM locations
                    WHERE group_id = ? AND subgroup_id IS NULL
                      AND state = 'PENDIENTE' AND deleted_at IS NULL";
            $params = [$groupId];
        }

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

        return array_map(fn($row) => new Location($row), $rows);
    }

    public function getLocationsWithRelationsByProcessId(int $processId): array
    {
        $db = Database::getConnection();

        $query = "SELECT 
                    l.id AS location_id, l.process_id, l.group_id, l.subgroup_id, l.procedure_route_id,
                    l.state, l.derived_date, l.receipt_date, l.attended_date, l.comment, l.deleted_at,

                    g.id AS group_id, g.description AS group_description, g.area_group_type_id,

                    sg.id AS subgroup_id, sg.description AS subgroup_description,

                    a.id AS area_id, a.description AS area_description
                FROM locations l
                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 area_group_types agt ON agt.id = g.area_group_type_id AND agt.deleted_at IS NULL
                LEFT JOIN areas a ON a.id = agt.area_id AND a.deleted_at IS NULL
                WHERE l.process_id = :processId
                AND l.deleted_at IS NULL
                ORDER BY l.derived_date ASC";

        $stmt = $db->prepare($query);
        $stmt->execute([':processId' => $processId]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $locations = [];
        foreach ($results as $row) {
            // Location
            $location = new Location();
            $location->id = $row['location_id'];
            $location->process_id = $row['process_id'];
            $location->group_id = $row['group_id'];
            $location->subgroup_id = $row['subgroup_id'];
            $location->procedure_route_id = $row['procedure_route_id'];
            $location->state = $row['state'];
            $location->derived_date = $row['derived_date'];
            $location->receipt_date = $row['receipt_date'];
            $location->attended_date = $row['attended_date'];
            $location->comment = $row['comment'];
            $location->deleted_at = $row['deleted_at'];

            // Group
            if ($row['group_id']) {
                $group = new Group();
                $group->id = $row['group_id'];
                $group->description = $row['group_description'];
                $group->area_group_type_id = $row['area_group_type_id'];
                $location->group = $group;
            }

            // Subgroup
            if ($row['subgroup_id']) {
                $subgroup = new Subgroup();
                $subgroup->id = $row['subgroup_id'];
                $subgroup->description = $row['subgroup_description'];
                $location->subgroup = $subgroup;
            }else {
                $location->subgroup = null;
            }

            // Area
            if ($row['area_id']) {
                $area = new Area();
                $area->id = $row['area_id'];
                $area->description = $row['area_description'];
                $location->area = $area;
            }

            $locations[$location->id] = $location;
        }

        // ✅ Attachments en una sola query para todas las locations
        if (!empty($locations)) {
            $ids = array_keys($locations);
            $in  = str_repeat('?,', count($ids) - 1) . '?';

            $stmt = $db->prepare("SELECT * FROM attachments WHERE location_id IN ($in) AND deleted_at IS NULL");
            $stmt->execute($ids);
            $attachments = $stmt->fetchAll(PDO::FETCH_ASSOC);

            foreach ($attachments as $att) {
                $attachment = new Attachment();
                foreach ($att as $key => $value) {
                    $attachment->$key = $value;
                }
                $locations[$att['location_id']]->attachments[] = $attachment;
            }
        }

        return array_values($locations);
    }

    public function getFirstLocationByProcessId(int $processId): ?Location
    {
        $db = Database::getConnection();
        $query = "SELECT * FROM locations
                  WHERE process_id = ?
                  AND deleted_at IS NULL
                  ORDER BY derived_date ASC
                  LIMIT 1";

        $stmt = $db->prepare($query);
        $stmt->execute([$processId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$result) {
            return null;
        }

        $location = new Location();
        foreach ($result as $key => $value) {
            $location->$key = $value;
        }

        return $location;
    }
    
}