<?php
declare(strict_types=1);

namespace App\Modules\SportsActivity\Controllers\Swimming;

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

class SwimmingDashboardController extends Controller
{
    public function index(Request $request): Response
    {
        $db = App::getInstance()->db();
        $today = date('Y-m-d');
        $monthStart = date('Y-m-01');
        $monthEnd = date('Y-m-t');

        $discipline = $db->selectOne(
            "SELECT id FROM sa_disciplines WHERE code = 'SWIMMING' AND is_archived = 0"
        );
        if (!$discipline) {
            return $this->redirect('/sa')->withError('لم يتم العثور على نشاط السباحة');
        }
        $disciplineId = (int) $discipline['id'];

        $counts = $db->selectOne("
            SELECT
                (SELECT COUNT(DISTINCT gp.player_id) FROM sa_group_players gp
                 INNER JOIN sa_groups g ON g.id = gp.group_id
                 INNER JOIN sa_programs p ON p.id = g.program_id
                 WHERE p.discipline_id = ? AND gp.status = 'active') as active_players,
                (SELECT COUNT(*) FROM sa_groups g
                 INNER JOIN sa_programs p ON p.id = g.program_id
                 WHERE p.discipline_id = ? AND g.status = 'active' AND g.is_archived = 0) as active_groups,
                (SELECT COUNT(DISTINCT gp.player_id) FROM sa_group_players gp
                 INNER JOIN sa_groups g ON g.id = gp.group_id
                 INNER JOIN sa_programs p ON p.id = g.program_id
                 WHERE p.discipline_id = ? AND gp.status = 'pending_payment') as pending_payment,
                (SELECT COALESCE(SUM(pr.amount), 0) FROM payment_requests pr
                 INNER JOIN sa_group_players gp ON gp.payment_request_id = pr.id
                 INNER JOIN sa_groups g ON g.id = gp.group_id
                 INNER JOIN sa_programs p ON p.id = g.program_id
                 WHERE p.discipline_id = ? AND pr.status = 'completed'
                 AND pr.processed_at BETWEEN ? AND ?) as revenue_this_month
        ", [$disciplineId, $disciplineId, $disciplineId, $disciplineId, $monthStart . ' 00:00:00', $monthEnd . ' 23:59:59']);

        $groups = $db->select("
            SELECT g.id, g.name_ar, g.current_count, g.status,                    p.name_ar as program_name, p.max_capacity, p.monthly_fee_member, p.monthly_fee_nonmember,
                   c.full_name_ar as coach_name,
                   (SELECT COUNT(*) FROM sa_group_players gp WHERE gp.group_id = g.id AND gp.status = 'active') as active_count,
                   (SELECT COUNT(*) FROM sa_group_players gp WHERE gp.group_id = g.id AND gp.status = 'pending_payment') as pending_count
            FROM sa_groups g
            INNER JOIN sa_programs p ON p.id = g.program_id
            LEFT JOIN sa_coaches c ON c.id = g.coach_id
            WHERE p.discipline_id = ? AND g.is_archived = 0
            ORDER BY g.status ASC, g.name_ar ASC
        ", [$disciplineId]);

        $recentRegistrations = $db->select("
            SELECT r.id, r.registration_number, r.status, r.created_at,
                   pl.full_name_ar, pl.photo_path, pl.medical_status,
                   g.name_ar as group_name
            FROM sa_registrations r
            INNER JOIN sa_players pl ON pl.id = r.player_id
            LEFT JOIN sa_groups g ON g.id = r.group_id
            LEFT JOIN sa_programs p ON p.id = g.program_id
            WHERE r.status != 'cancelled'
            AND (p.discipline_id = ? OR EXISTS (
                SELECT 1 FROM sa_group_players gp2
                INNER JOIN sa_groups g2 ON g2.id = gp2.group_id
                INNER JOIN sa_programs p3 ON p3.id = g2.program_id
                WHERE p3.discipline_id = ? AND gp2.player_id = r.player_id
            ))
            ORDER BY r.created_at DESC
            LIMIT 10
        ", [$disciplineId, $disciplineId]);

        $unassignedPlayers = $db->select("
            SELECT pl.id, pl.full_name_ar, pl.date_of_birth, pl.medical_status, pl.created_at
            FROM sa_players pl
            WHERE pl.is_archived = 0
            AND pl.medical_status IN ('fit', 'conditional', 'pending')
            AND NOT EXISTS (
                SELECT 1 FROM sa_group_players gp
                INNER JOIN sa_groups g ON g.id = gp.group_id
                INNER JOIN sa_programs p ON p.id = g.program_id
                WHERE p.discipline_id = ? AND gp.player_id = pl.id AND gp.status IN ('active', 'pending_payment')
            )
            AND EXISTS (
                SELECT 1 FROM sa_player_documents doc
                WHERE doc.player_id = pl.id AND doc.document_type = 'medical_cert'
            )
            ORDER BY pl.created_at DESC
            LIMIT 10
        ", [$disciplineId]);

        $programs = $db->select("
            SELECT p.id, p.name_ar, p.monthly_fee_member, p.monthly_fee_nonmember, p.max_capacity,
                   COUNT(DISTINCT g.id) as group_count,
                   COALESCE(SUM(g.current_count), 0) as total_enrolled
            FROM sa_programs p
            LEFT JOIN sa_groups g ON g.program_id = p.id AND g.is_archived = 0 AND g.status = 'active'
            WHERE p.discipline_id = ? AND p.is_archived = 0
            GROUP BY p.id, p.name_ar, p.monthly_fee_member, p.monthly_fee_nonmember, p.max_capacity
            ORDER BY p.name_ar
        ", [$disciplineId]);

        return $this->view('SportsActivity.Views.swimming.dashboard', [
            'counts'              => $counts,
            'groups'              => $groups,
            'recentRegistrations' => $recentRegistrations,
            'unassignedPlayers'   => $unassignedPlayers,
            'programs'            => $programs,
            'disciplineId'        => $disciplineId,
        ]);
    }
}
