<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Controllers\Api;

use App\Core\App;
use App\Core\Controller;
use App\Core\Request;
use App\Core\Response;

class SmartFilterApiController extends Controller
{
    public function groups(Request $request): Response
    {
        $db = App::getInstance()->db();

        $q = trim((string) $request->get('q', ''));
        $disciplineId = (int) $request->get('discipline_id', 0);
        $programId = (int) $request->get('program_id', 0);
        $coachId = (int) $request->get('coach_id', 0);
        $activeOnly = (int) $request->get('active_only', 1);

        $where = ['g.is_archived = 0'];
        $params = [];

        if ($activeOnly) {
            $where[] = "g.status = 'active'";
        }

        if ($q !== '' && strlen($q) >= 2) {
            $where[] = '(g.name_ar LIKE ? OR g.code LIKE ?)';
            $params[] = "%{$q}%";
            $params[] = "%{$q}%";
        }

        if ($disciplineId > 0) {
            $where[] = 'p.discipline_id = ?';
            $params[] = $disciplineId;
        }

        if ($programId > 0) {
            $where[] = 'g.program_id = ?';
            $params[] = $programId;
        }

        if ($coachId > 0) {
            $where[] = 'g.coach_id = ?';
            $params[] = $coachId;
        }

        $whereSql = implode(' AND ', $where);

        $results = $db->select(
            "SELECT g.id, g.code, g.name_ar, g.current_count, g.max_capacity, g.is_full,
                    c.full_name_ar as coach_name, p.name_ar as program_name, d.name_ar as discipline_name
             FROM sa_groups g
             LEFT JOIN sa_programs p ON p.id = g.program_id
             LEFT JOIN sa_disciplines d ON d.id = p.discipline_id
             LEFT JOIN sa_coaches c ON c.id = g.coach_id
             WHERE {$whereSql}
             ORDER BY g.name_ar ASC
             LIMIT 30",
            $params
        );

        return $this->json(['results' => $results]);
    }

    public function coaches(Request $request): Response
    {
        $db = App::getInstance()->db();

        $q = trim((string) $request->get('q', ''));
        $disciplineId = (int) $request->get('discipline_id', 0);

        $where = ['c.is_archived = 0', 'c.is_active = 1'];
        $params = [];
        $joins = '';

        if ($q !== '' && strlen($q) >= 2) {
            $where[] = '(c.full_name_ar LIKE ? OR c.code LIKE ?)';
            $params[] = "%{$q}%";
            $params[] = "%{$q}%";
        }

        if ($disciplineId > 0) {
            $joins = 'INNER JOIN sa_coach_disciplines cd ON cd.coach_id = c.id AND cd.discipline_id = ?';
            $params[] = $disciplineId;
        }

        $whereSql = implode(' AND ', $where);

        $results = $db->select(
            "SELECT DISTINCT c.id, c.code, c.full_name_ar, c.phone
             FROM sa_coaches c
             {$joins}
             WHERE {$whereSql}
             ORDER BY c.full_name_ar ASC
             LIMIT 20",
            $params
        );

        return $this->json(['results' => $results]);
    }

    public function disciplines(Request $request): Response
    {
        $db = App::getInstance()->db();

        $results = $db->select(
            "SELECT id, code, name_ar, icon
             FROM sa_disciplines
             WHERE is_active = 1 AND is_archived = 0
             ORDER BY sort_order ASC, name_ar ASC",
            []
        );

        return $this->json(['results' => $results]);
    }
}
