<?php

namespace App\Repositories;

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

class GroupRepository
{
    protected $db;

    public function __construct()
    {
        $this->db = Database::getConnection();
    }

    public function getAreaByGroup(Group $group)
    {
        $query = "SELECT a.* 
                  FROM areas a
                  INNER JOIN area_group_types agt ON a.id = agt.area_id
                  WHERE agt.id = :area_group_type_id
                  AND a.deleted_at IS NULL 
                  AND agt.deleted_at IS NULL";

        $stmt = $this->db->prepare($query);
        $stmt->execute([':area_group_type_id' => $group->area_group_type_id]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($data) {
            $area = new Area();
            foreach ($data as $key => $value) {
                $area->$key = $value;
            }
            return $area;
        }

        return null;
    }

    public function getGroupTypeByGroup(Group $group): ?object
    {
        $query = "SELECT gt.* 
                  FROM group_types gt
                  INNER JOIN area_group_types agt ON gt.id = agt.group_type_id
                  WHERE agt.id = :area_group_type_id
                  AND gt.deleted_at IS NULL 
                  AND agt.deleted_at IS NULL";

        $stmt = $this->db->prepare($query);
        $stmt->execute([':area_group_type_id' => $group->area_group_type_id]);
        return $stmt->fetch(PDO::FETCH_OBJ) ?: null;
    }

    public function getSubGroupsByGroup(Group $group): array
    {
        $query = "SELECT * FROM subgroups WHERE group_id = :group_id AND deleted_at IS NULL";
        $stmt = $this->db->prepare($query);
        $stmt->execute([':group_id' => $group->id]);
        return $stmt->fetchAll(PDO::FETCH_OBJ);
    }

    public function hasSubgroups(int $groupId): bool
    {
        $query = "SELECT COUNT(*) 
                  FROM subgroups 
                  WHERE group_id = :group_id AND deleted_at IS NULL";

        $stmt = $this->db->prepare($query);
        $stmt->execute([':group_id' => $groupId]);
        return $stmt->fetchColumn() > 0;
    }
}