<?php

namespace App\Repositories;

use App\Models\Area;
use App\Models\Group;
use App\Models\GroupType;
use App\Models\Subgroup;
use Config\Database;
use PDO;

class AreaRepository
{
    public function getWithRelations($areaId)
    {
        $db = Database::getConnection();

        $stmt = $db->prepare("SELECT * FROM areas WHERE id = ?");
        $stmt->execute([$areaId]);
        $area = $stmt->fetch(PDO::FETCH_OBJ);

        if (!$area) {
            return null;
        }

        $stmt = $db->prepare("
            SELECT gt.*, agt.id AS area_group_type_id
            FROM group_types gt
            INNER JOIN area_group_types agt ON gt.id = agt.group_type_id
            WHERE agt.area_id = ? AND agt.deleted_at IS NULL
        ");
        $stmt->execute([$areaId]);
        $groupTypes = $stmt->fetchAll(PDO::FETCH_OBJ);

        if (!$groupTypes) {
            $area->groupTypes = [];
            return $area;
        }

        $areaGroupTypeIds = array_column($groupTypes, 'area_group_type_id');

        $in = str_repeat('?,', count($areaGroupTypeIds) - 1) . '?';
        $stmt = $db->prepare("
            SELECT g.*, agt.group_type_id
            FROM `groups` g
            INNER JOIN area_group_types agt ON g.area_group_type_id = agt.id
            WHERE g.deleted_at IS NULL
            AND g.area_group_type_id IN ($in)
        ");
        $stmt->execute($areaGroupTypeIds);
        $groups = $stmt->fetchAll(PDO::FETCH_OBJ);

        if ($groups) {
            $groupIds = array_column($groups, 'id');

            $in = str_repeat('?,', count($groupIds) - 1) . '?';
            $stmt = $db->prepare("
                SELECT * FROM subgroups
                WHERE deleted_at IS NULL
                AND group_id IN ($in)
            ");
            $stmt->execute($groupIds);
            $subgroups = $stmt->fetchAll(PDO::FETCH_OBJ);

            $subgroupsByGroup = [];
            foreach ($subgroups as $sub) {
                $subgroupsByGroup[$sub->group_id][] = $sub;
            }

            foreach ($groups as $group) {
                $group->subgroups = $subgroupsByGroup[$group->id] ?? [];
            }
        }

        $groupsByType = [];
        foreach ($groups as $g) {
            $groupsByType[$g->group_type_id][] = $g;
        }

        foreach ($groupTypes as $gt) {
            $gt->groups = $groupsByType[$gt->id] ?? [];
        }

        $area->groupTypes = $groupTypes;

        return $area;
    }

    public function getAreasWithRelations()
    {
        $db = Database::getConnection();
        
        $query = "SELECT 
                    a.id AS area_id, a.abbreviation AS area_abbreviation, a.description AS area_description, 
                    gt.id AS group_type_id, gt.abbreviation AS group_type_abbreviation, gt.description AS group_type_description,
                    g.id AS group_id, g.abbreviation AS group_abbreviation, g.description AS group_description,
                    sg.id AS subgroup_id, sg.abbreviation AS subgroup_abbreviation, sg.description AS subgroup_description
                FROM areas a
                INNER JOIN area_group_types agt ON a.id = agt.area_id
                INNER JOIN group_types gt ON agt.group_type_id = gt.id
                INNER JOIN `groups` g ON agt.id = g.area_group_type_id
                LEFT JOIN subgroups sg ON g.id = sg.group_id
                WHERE a.active = 1 AND gt.active = 1 AND g.active = 1 AND (sg.active = 1 OR sg.id IS NULL)";
        
        $stmt = $db->prepare($query);
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $areas = [];

        foreach ($results as $row) {
            $areaId = $row['area_id'];

            if (!isset($areas[$areaId])) {
                $area = new Area();
                $area->id = $areaId;
                $area->abbreviation = $row['area_abbreviation'];
                $area->description = $row['area_description'];
                $area->groupTypes = [];
                $areas[$areaId] = $area;
            } else {
                $area = $areas[$areaId];
            }

            $groupTypeId = $row['group_type_id'];
            if (!isset($area->groupTypes[$groupTypeId])) {
                $groupType = new GroupType();
                $groupType->id = $groupTypeId;
                $groupType->abbreviation = $row['group_type_abbreviation'];
                $groupType->description = $row['group_type_description'];
                $groupType->groups = [];
                $area->groupTypes[$groupTypeId] = $groupType;
            } else {
                $groupType = $area->groupTypes[$groupTypeId];
            }

            $groupId = $row['group_id'];
            if (!isset($groupType->groups[$groupId])) {
                $group = new Group();
                $group->id = $groupId;
                $group->abbreviation = $row['group_abbreviation'];
                $group->description = $row['group_description'];
                $group->subGroups = [];
                $groupType->groups[$groupId] = $group;
            } else {
                $group = $groupType->groups[$groupId];
            }

            if (!empty($row['subgroup_id'])) {
                $subGroup = new Subgroup();
                $subGroup->id = $row['subgroup_id'];
                $subGroup->abbreviation = $row['subgroup_abbreviation'];
                $subGroup->description = $row['subgroup_description'];
                $group->subGroups[] = $subGroup;
            }
        }
        
        return array_values($areas); 
    }
}