<?php

class SportApiController extends Controller
{
    public function sportsDatatable(Request $request): void
    {
        $draw    = (int) $request->get('draw', 1);
        $start   = (int) $request->get('start', 0);
        $length  = (int) $request->get('length', 25);
        $search  = $request->get('search')['value'] ?? '';
        $orderCol= (int) ($request->get('order')[0]['column'] ?? 0);
        $orderDir= ($request->get('order')[0]['dir'] ?? 'asc') === 'desc' ? 'DESC' : 'ASC';

        $columns = ['s.sport_code','s.name_en','sc.name_en','s.is_active','s.sort_order'];
        $db = $this->db();

        $where = '1=1';
        $params = [];
        if ($search) {
            $where .= " AND (s.name_en LIKE ? OR s.name_ar LIKE ? OR s.sport_code LIKE ?)";
            $s = "%{$search}%";
            $params = [$s, $s, $s];
        }
        if ($request->get('category_id')) {
            $where .= " AND s.category_id = ?";
            $params[] = $request->get('category_id');
        }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sports s WHERE 1=1");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sports s LEFT JOIN sport_categories sc ON s.category_id = sc.id WHERE {$where}", $params);

        $orderColumn = $columns[$orderCol] ?? 's.sort_order';
        $rows = $db->fetchAll(
            "SELECT s.*, sc.name_en as category_name, sc.name_ar as category_name_ar
             FROM sports s LEFT JOIN sport_categories sc ON s.category_id = sc.id
             WHERE {$where} ORDER BY {$orderColumn} {$orderDir} LIMIT {$length} OFFSET {$start}",
            $params
        );

        $data = array_map(function($r) {
            $r['status_badge'] = $r['is_active'] ? '<span class="badge bg-success">Active</span>' : '<span class="badge bg-secondary">Inactive</span>';
            $r['actions'] = '<a href="/sports/view/' . $r['id'] . '" class="btn btn-sm btn-info" title="View"><i class="fas fa-eye"></i></a> '
                . '<a href="/sports/' . $r['id'] . '/edit" class="btn btn-sm btn-warning" title="Edit"><i class="fas fa-edit"></i></a>';
            return $r;
        }, $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function seasonsDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1';
        $params = [];
        if ($search) {
            $where .= " AND (ss.name_en LIKE ? OR ss.season_code LIKE ?)";
            $s = "%{$search}%"; $params = [$s, $s];
        }
        if ($request->get('sport_id')) { $where .= " AND ss.sport_id = ?"; $params[] = $request->get('sport_id'); }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_seasons ss WHERE 1=1");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_seasons ss WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT ss.*, sp.name_en as sport_name FROM sport_seasons ss
             LEFT JOIN sports sp ON ss.sport_id = sp.id WHERE {$where} ORDER BY ss.start_date DESC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'status_badge' => status_badge($r['status']),
            'actions' => '<a href="/sports/seasons/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function coachesDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1';
        $params = [];
        if ($search) {
            $where .= " AND (c.first_name_en LIKE ? OR c.last_name_en LIKE ? OR c.coach_code LIKE ? OR c.phone LIKE ?)";
            $s = "%{$search}%"; $params = [$s, $s, $s, $s];
        }
        if ($request->get('status')) { $where .= " AND c.status = ?"; $params[] = $request->get('status'); }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_coaches c");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_coaches c WHERE {$where}", $params);
        $rows = $db->fetchAll("SELECT c.* FROM sport_coaches c WHERE {$where} ORDER BY c.first_name_en ASC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'full_name' => e($r['first_name_en'] . ' ' . $r['last_name_en']),
            'status_badge' => status_badge($r['status']),
            'actions' => '<a href="/sports/coaches/' . $r['id'] . '" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a> '
                . '<a href="/sports/coaches/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function classesDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1';
        $params = [];
        if ($search) { $where .= " AND (cl.name_en LIKE ? OR cl.class_code LIKE ?)"; $s = "%{$search}%"; $params = [$s, $s]; }
        if ($request->get('sport_id')) { $where .= " AND cl.sport_id = ?"; $params[] = $request->get('sport_id'); }
        if ($request->get('status')) { $where .= " AND cl.status = ?"; $params[] = $request->get('status'); }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_classes cl");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_classes cl WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT cl.*, sp.name_en as sport_name,
                    CONCAT(co.first_name_en, ' ', co.last_name_en) as coach_name,
                    f.name_en as facility_name,
                    (SELECT COUNT(*) FROM sport_class_enrollments e WHERE e.class_id = cl.id AND e.status IN ('enrolled','active')) as enrolled_count
             FROM sport_classes cl
             LEFT JOIN sports sp ON cl.sport_id = sp.id
             LEFT JOIN sport_coaches co ON cl.coach_id = co.id
             LEFT JOIN facilities f ON cl.facility_id = f.id
             WHERE {$where} ORDER BY cl.created_at DESC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'spots' => ($r['enrolled_count'] ?? 0) . '/' . $r['max_participants'],
            'status_badge' => status_badge($r['status']),
            'actions' => '<a href="/sports/classes/' . $r['id'] . '" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a> '
                . '<a href="/sports/classes/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function teamsDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1'; $params = [];
        if ($search) { $where .= " AND (t.name_en LIKE ? OR t.team_code LIKE ?)"; $s = "%{$search}%"; $params = [$s, $s]; }
        if ($request->get('sport_id')) { $where .= " AND t.sport_id = ?"; $params[] = $request->get('sport_id'); }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_teams t");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_teams t WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT t.*, sp.name_en as sport_name, CONCAT(co.first_name_en,' ',co.last_name_en) as coach_name
             FROM sport_teams t LEFT JOIN sports sp ON t.sport_id = sp.id LEFT JOIN sport_coaches co ON t.coach_id = co.id
             WHERE {$where} ORDER BY t.name_en ASC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'players' => $r['current_players'] . '/' . ($r['max_players'] ?: '∞'),
            'status_badge' => status_badge($r['status']),
            'record' => $r['wins'] . 'W-' . $r['losses'] . 'L-' . $r['draws'] . 'D',
            'actions' => '<a href="/sports/teams/' . $r['id'] . '" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a> '
                . '<a href="/sports/teams/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function leaguesDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1'; $params = [];
        if ($search) { $where .= " AND (l.name_en LIKE ? OR l.league_code LIKE ?)"; $s = "%{$search}%"; $params = [$s, $s]; }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_leagues l");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_leagues l WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT l.*, sp.name_en as sport_name, ss.name_en as season_name,
                    (SELECT COUNT(*) FROM sport_league_teams lt WHERE lt.league_id = l.id) as team_count
             FROM sport_leagues l LEFT JOIN sports sp ON l.sport_id = sp.id LEFT JOIN sport_seasons ss ON l.season_id = ss.id
             WHERE {$where} ORDER BY l.start_date DESC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'status_badge' => status_badge($r['status']),
            'actions' => '<a href="/sports/leagues/' . $r['id'] . '" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a> '
                . '<a href="/sports/leagues/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function matchesDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1'; $params = [];
        if ($search) { $where .= " AND (m.match_number LIKE ?)"; $params = ["%{$search}%"]; }
        if ($request->get('sport_id')) { $where .= " AND m.sport_id = ?"; $params[] = $request->get('sport_id'); }
        if ($request->get('status')) { $where .= " AND m.status = ?"; $params[] = $request->get('status'); }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_matches m");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_matches m WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT m.*, sp.name_en as sport_name, ht.name_en as home_team, at.name_en as away_team, f.name_en as facility_name
             FROM sport_matches m
             LEFT JOIN sports sp ON m.sport_id = sp.id
             LEFT JOIN sport_teams ht ON m.home_team_id = ht.id
             LEFT JOIN sport_teams at ON m.away_team_id = at.id
             LEFT JOIN facilities f ON m.facility_id = f.id
             WHERE {$where} ORDER BY m.match_date DESC, m.start_time DESC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'score' => $r['status'] === 'completed' ? ($r['home_score'] . ' - ' . $r['away_score']) : '-',
            'status_badge' => status_badge($r['status']),
            'actions' => '<a href="/sports/matches/' . $r['id'] . '" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function tournamentsDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1'; $params = [];
        if ($search) { $where .= " AND (t.name_en LIKE ? OR t.tournament_code LIKE ?)"; $s = "%{$search}%"; $params = [$s, $s]; }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_tournaments t");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_tournaments t WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT t.*, sp.name_en as sport_name
             FROM sport_tournaments t LEFT JOIN sports sp ON t.sport_id = sp.id
             WHERE {$where} ORDER BY t.start_date DESC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'participants' => $r['current_participants'] . '/' . ($r['max_participants'] ?: '∞'),
            'status_badge' => status_badge($r['status']),
            'actions' => '<a href="/sports/tournaments/' . $r['id'] . '" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a> '
                . '<a href="/sports/tournaments/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function equipmentDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1'; $params = [];
        if ($search) { $where .= " AND (e.name_en LIKE ? OR e.equipment_code LIKE ?)"; $s = "%{$search}%"; $params = [$s, $s]; }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_equipment e");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_equipment e WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT e.*, sp.name_en as sport_name, f.name_en as facility_name
             FROM sport_equipment e LEFT JOIN sports sp ON e.sport_id = sp.id LEFT JOIN facilities f ON e.facility_id = f.id
             WHERE {$where} ORDER BY e.name_en ASC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'stock' => $r['quantity_available'] . '/' . $r['quantity_total'],
            'condition_badge' => status_badge($r['condition_status']),
            'actions' => '<a href="/sports/equipment/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>'
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function rentalsDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $db = $this->db();

        $where = '1=1'; $params = [];
        if ($search) { $where .= " AND (r.rental_number LIKE ? OR m.full_name_en LIKE ?)"; $s = "%{$search}%"; $params = [$s, $s]; }
        if ($request->get('status')) { $where .= " AND r.status = ?"; $params[] = $request->get('status'); }

        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_equipment_rentals r LEFT JOIN members m ON r.member_id=m.id WHERE 1=1");
        $filtered = $db->fetchColumn("SELECT COUNT(*) FROM sport_equipment_rentals r LEFT JOIN members m ON r.member_id=m.id WHERE {$where}", $params);
        $rows = $db->fetchAll(
            "SELECT r.*, eq.name_en as equipment_name, m.full_name_en as member_name, m.membership_number
             FROM sport_equipment_rentals r
             LEFT JOIN sport_equipment eq ON r.equipment_id = eq.id
             LEFT JOIN members m ON r.member_id = m.id
             WHERE {$where} ORDER BY r.created_at DESC LIMIT {$length} OFFSET {$start}", $params);

        $data = array_map(fn($r) => array_merge($r, [
            'status_badge' => status_badge($r['status']),
            'actions' => $r['status'] === 'active' ?
                '<form method="POST" action="/sports/equipment/rentals/' . $r['id'] . '/return" style="display:inline">' . csrf_field()
                . '<button type="submit" class="btn btn-sm btn-success" title="Return"><i class="fas fa-undo"></i> Return</button></form>' : ''
        ]), $rows);

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function membershipsDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $db = $this->db();

        $rows = $db->fetchAll(
            "SELECT sm.*, sp.name_en as sport_name, m.full_name_en as member_name, m.membership_number
             FROM sport_memberships sm
             LEFT JOIN sports sp ON sm.sport_id = sp.id LEFT JOIN members m ON sm.member_id = m.id
             ORDER BY sm.created_at DESC LIMIT {$length} OFFSET {$start}");
        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_memberships");

        $data = array_map(fn($r) => array_merge($r, ['status_badge' => status_badge($r['status'])]), $rows);
        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $total, 'data' => $data]);
    }

    public function achievementsDatatable(Request $request): void
    {
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $db = $this->db();

        $rows = $db->fetchAll(
            "SELECT a.*, sp.name_en as sport_name, m.full_name_en as member_name, m.membership_number
             FROM sport_member_achievements a
             LEFT JOIN sports sp ON a.sport_id = sp.id LEFT JOIN members m ON a.member_id = m.id
             ORDER BY a.achievement_date DESC LIMIT {$length} OFFSET {$start}");
        $total = $db->fetchColumn("SELECT COUNT(*) FROM sport_member_achievements");

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $total, 'data' => $rows]);
    }

    public function sportsList(Request $request): void
    {
        $rows = $this->db()->fetchAll("SELECT id, sport_code, name_en, name_ar FROM sports WHERE is_active = 1 ORDER BY name_en");
        $this->json($rows);
    }

    public function coachesList(Request $request): void
    {
        $rows = $this->db()->fetchAll("SELECT id, coach_code, CONCAT(first_name_en,' ',last_name_en) as name FROM sport_coaches WHERE status = 'active' ORDER BY first_name_en");
        $this->json($rows);
    }

    public function teamsList(Request $request): void
    {
        $sportId = $request->get('sport_id');
        $where = "t.status = 'active'";
        $params = [];
        if ($sportId) { $where .= " AND t.sport_id = ?"; $params[] = $sportId; }
        $rows = $this->db()->fetchAll("SELECT t.id, t.team_code, t.name_en FROM sport_teams t WHERE {$where} ORDER BY t.name_en", $params);
        $this->json($rows);
    }
}