<?php

class FacilityApiController extends Controller
{
    public function list(Request $request): void
    {
        $db = $this->db();

        $draw   = (int) $request->get('draw', 1);
        $start  = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $statusFilter = $request->get('status', '');
        $typeFilter   = $request->get('facility_type_id', '');

        $where  = "WHERE f.is_active = 1";
        $params = [];

        if ($statusFilter) {
            $where .= " AND f.status = ?";
            $params[] = $statusFilter;
        }
        if ($typeFilter) {
            $where .= " AND f.facility_type_id = ?";
            $params[] = $typeFilter;
        }
        if ($search) {
            $where .= " AND (f.name_en LIKE ? OR f.name_ar LIKE ? OR f.facility_code LIKE ? OR ft.name_en LIKE ?)";
            $s = "%{$search}%";
            $params = array_merge($params, [$s, $s, $s, $s]);
        }

        $totalAll  = (int) $db->fetchColumn("SELECT COUNT(*) FROM facilities f WHERE f.is_active = 1");
        $totalFilt = (int) $db->fetchColumn(
            "SELECT COUNT(*) FROM facilities f LEFT JOIN facility_types ft ON f.facility_type_id = ft.id {$where}",
            $params
        );

        $orderCol = (int) ($request->get('order')[0]['column'] ?? 0);
        $orderDir = strtoupper($request->get('order')[0]['dir'] ?? 'ASC') === 'DESC' ? 'DESC' : 'ASC';
        $colMap   = ['f.id', 'f.facility_code', 'f.name_en', 'ft.name_en', 'f.capacity', 'f.status', 'f.is_bookable'];
        $orderClause = ($colMap[$orderCol] ?? 'f.id') . ' ' . $orderDir;

        $rows = $db->fetchAll(
            "SELECT f.id, f.facility_code, f.name_en, f.name_ar, f.capacity, f.status, f.is_bookable,
                    ft.name_en AS type_name_en, ft.name_ar AS type_name_ar
             FROM facilities f
             LEFT JOIN facility_types ft ON f.facility_type_id = ft.id
             {$where}
             ORDER BY {$orderClause}
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            $params
        );

        $data = [];
        foreach ($rows as $r) {
            $actions = '<a href="/facilities/' . $r['id'] . '" class="btn btn-sm btn-info" title="View"><i class="fas fa-eye"></i></a> ';
            if (Gate::can('facilities.manage')) {
                $actions .= '<a href="/facilities/' . $r['id'] . '/edit" class="btn btn-sm btn-warning" title="Edit"><i class="fas fa-edit"></i></a> ';
            }
            if ($r['is_bookable'] && Gate::can('bookings.view')) {
                $actions .= '<a href="/bookings/calendar/' . $r['id'] . '" class="btn btn-sm btn-primary" title="Calendar"><i class="fas fa-calendar"></i></a>';
            }
            $data[] = [
                'id'        => $r['id'],
                'code'      => e($r['facility_code']),
                'name'      => e($r['name_en']),
                'name_ar'   => e($r['name_ar']),
                'type'      => e($r['type_name_en'] ?? ''),
                'capacity'  => $r['capacity'] ? number_format($r['capacity']) : '-',
                'bookable'  => $r['is_bookable'] ? '<i class="fas fa-check text-success"></i>' : '<i class="fas fa-times text-muted"></i>',
                'status'    => status_badge($r['status']),
                'actions'   => $actions,
            ];
        }

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

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

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

        $total     = (int) $db->fetchColumn("SELECT COUNT(*) FROM facility_types");
        $totalFilt = (int) $db->fetchColumn("SELECT COUNT(*) FROM facility_types {$where}", $params);

        $rows = $db->fetchAll(
            "SELECT * FROM facility_types {$where} ORDER BY name_en LIMIT " . (int)$length . " OFFSET " . (int)$start,
            $params
        );

        $data = [];
        foreach ($rows as $r) {
            $facilityCount = (int) $db->fetchColumn("SELECT COUNT(*) FROM facilities WHERE facility_type_id = ?", [$r['id']]);
            $actions = '<a href="/facilities/types/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>';
            $data[] = [
                'id'         => $r['id'],
                'code'       => e($r['type_code']),
                'name_en'    => e($r['name_en']),
                'name_ar'    => e($r['name_ar']),
                'bookable'   => $r['is_bookable'] ? '<i class="fas fa-check text-success"></i>' : '<i class="fas fa-times text-muted"></i>',
                'active'     => $r['is_active'] ? status_badge('active') : status_badge('expired'),
                'facilities' => $facilityCount,
                'actions'    => $actions,
            ];
        }

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

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

        $statusFilter   = $request->get('status', '');
        $facilityFilter = $request->get('facility_id', '');
        $dateFrom       = $request->get('date_from', '');
        $dateTo         = $request->get('date_to', '');

        $where  = "WHERE 1=1";
        $params = [];

        if ($statusFilter) {
            $where .= " AND fb.status = ?";
            $params[] = $statusFilter;
        }
        if ($facilityFilter) {
            $where .= " AND fb.facility_id = ?";
            $params[] = $facilityFilter;
        }
        if ($dateFrom) {
            $where .= " AND fb.booking_date >= ?";
            $params[] = $dateFrom;
        }
        if ($dateTo) {
            $where .= " AND fb.booking_date <= ?";
            $params[] = $dateTo;
        }
        if ($search) {
            $where .= " AND (fb.booking_number LIKE ? OR m.full_name_en LIKE ? OR m.membership_number LIKE ? OR f.name_en LIKE ?)";
            $s = "%{$search}%";
            $params = array_merge($params, [$s, $s, $s, $s]);
        }

        $baseJoin = "FROM facility_bookings fb
                     LEFT JOIN members m ON fb.member_id = m.id
                     LEFT JOIN facilities f ON fb.facility_id = f.id";

        $total     = (int) $db->fetchColumn("SELECT COUNT(*) FROM facility_bookings");
        $totalFilt = (int) $db->fetchColumn("SELECT COUNT(*) {$baseJoin} {$where}", $params);

        $orderCol = (int) ($request->get('order')[0]['column'] ?? 0);
        $orderDir = strtoupper($request->get('order')[0]['dir'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';
        $colMap = ['fb.id', 'fb.booking_number', 'f.name_en', 'm.full_name_en', 'fb.booking_date', 'fb.start_time', 'fb.fee_amount', 'fb.status'];
        $orderClause = ($colMap[$orderCol] ?? 'fb.id') . ' ' . $orderDir;

        $rows = $db->fetchAll(
            "SELECT fb.*, m.full_name_en AS member_name, m.membership_number, f.name_en AS facility_name
             {$baseJoin} {$where}
             ORDER BY {$orderClause}
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            $params
        );

        $data = [];
        foreach ($rows as $r) {
            $actions = '';
            if (Gate::can('bookings.manage')) {
                if ($r['status'] === 'pending') {
                    $actions .= '<button class="btn btn-sm btn-success btn-status" data-id="' . $r['id'] . '" data-status="confirmed" title="Confirm"><i class="fas fa-check"></i></button> ';
                }
                if (in_array($r['status'], ['confirmed', 'pending'])) {
                    $actions .= '<button class="btn btn-sm btn-info btn-status" data-id="' . $r['id'] . '" data-status="checked_in" title="Check In"><i class="fas fa-sign-in-alt"></i></button> ';
                    $actions .= '<button class="btn btn-sm btn-danger btn-status" data-id="' . $r['id'] . '" data-status="cancelled" title="Cancel"><i class="fas fa-times"></i></button> ';
                }
                if ($r['status'] === 'checked_in') {
                    $actions .= '<button class="btn btn-sm btn-secondary btn-status" data-id="' . $r['id'] . '" data-status="completed" title="Complete"><i class="fas fa-flag-checkered"></i></button>';
                }
            }

            $data[] = [
                'id'       => $r['id'],
                'number'   => e($r['booking_number']),
                'facility' => e($r['facility_name'] ?? ''),
                'member'   => e($r['member_name'] ?? '') . '<br><small class="text-muted">' . e($r['membership_number'] ?? '') . '</small>',
                'date'     => e($r['booking_date']),
                'time'     => e($r['start_time']) . ' - ' . e($r['end_time']),
                'fee'      => format_money($r['fee_amount']),
                'status'   => status_badge($r['status']),
                'actions'  => $actions,
            ];
        }

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

    public function calendarData(Request $request): void
    {
        $facilityId = (int) $request->routeParam('id');
        $startDate  = $request->get('start', date('Y-m-d'));
        $endDate    = $request->get('end', date('Y-m-d', strtotime('+7 days')));

        $db = $this->db();
        $bookings = $db->fetchAll(
            "SELECT fb.id, fb.booking_number, fb.booking_date, fb.start_time, fb.end_time, fb.status, fb.booking_type,
                    m.full_name_en AS member_name, m.membership_number
             FROM facility_bookings fb
             LEFT JOIN members m ON fb.member_id = m.id
             WHERE fb.facility_id = ? AND fb.booking_date BETWEEN ? AND ? AND fb.status NOT IN ('cancelled')
             ORDER BY fb.booking_date, fb.start_time",
            [$facilityId, $startDate, $endDate]
        );

        $overrides = $db->fetchAll(
            "SELECT * FROM facility_slot_overrides WHERE facility_id = ? AND override_date BETWEEN ? AND ?",
            [$facilityId, $startDate, $endDate]
        );

        $events = [];
        foreach ($bookings as $b) {
            $colorMap = [
                'pending'    => '#ffc107',
                'confirmed'  => '#28a745',
                'checked_in' => '#17a2b8',
                'completed'  => '#6c757d',
            ];
            $events[] = [
                'id'              => $b['id'],
                'title'           => $b['member_name'] . ' (' . $b['booking_number'] . ')',
                'start'           => $b['booking_date'] . 'T' . $b['start_time'],
                'end'             => $b['booking_date'] . 'T' . $b['end_time'],
                'backgroundColor' => $colorMap[$b['status']] ?? '#007bff',
                'borderColor'     => $colorMap[$b['status']] ?? '#007bff',
                'extendedProps'   => [
                    'booking_number' => $b['booking_number'],
                    'member'         => $b['member_name'],
                    'membership_no'  => $b['membership_number'],
                    'status'         => $b['status'],
                    'type'           => $b['booking_type'],
                ],
            ];
        }

        foreach ($overrides as $ov) {
            $events[] = [
                'id'              => 'ov-' . $ov['id'],
                'title'           => '⛔ ' . ucfirst($ov['override_type']) . ($ov['reason'] ? ': ' . $ov['reason'] : ''),
                'start'           => $ov['override_date'] . ($ov['start_time'] ? 'T' . $ov['start_time'] : ''),
                'end'             => $ov['override_date'] . ($ov['end_time'] ? 'T' . $ov['end_time'] : ''),
                'backgroundColor' => '#dc3545',
                'borderColor'     => '#dc3545',
                'display'         => $ov['start_time'] ? 'auto' : 'background',
            ];
        }

        $this->json($events);
    }

    public function checkAvailability(Request $request): void
    {
        $facilityId = (int) $request->get('facility_id');
        $date       = $request->get('date');
        $startTime  = $request->get('start_time');
        $endTime    = $request->get('end_time');

        if (!$facilityId || !$date || !$startTime || !$endTime) {
            $this->json(['available' => false, 'message' => 'Missing parameters']);
            return;
        }

        $db = $this->db();
        $db->execute(
            "CALL sp_check_booking_availability(?, ?, ?, ?, @p_avail, @p_conflicts)",
            [$facilityId, $date, $startTime, $endTime]
        );
        $result = $db->fetchOne("SELECT @p_avail AS is_available, @p_conflicts AS conflict_count");

        $this->json([
            'available'     => (bool) ($result['is_available'] ?? 0),
            'conflict_count'=> (int) ($result['conflict_count'] ?? 0),
        ]);
    }

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

        $where  = "WHERE 1=1";
        $params = [];
        if ($statusFilter) {
            $where .= " AND e.status = ?";
            $params[] = $statusFilter;
        }
        if ($search) {
            $where .= " AND (e.title_en LIKE ? OR e.title_ar LIKE ? OR e.event_code LIKE ?)";
            $s = "%{$search}%";
            $params = array_merge($params, [$s, $s, $s]);
        }

        $total     = (int) $db->fetchColumn("SELECT COUNT(*) FROM club_events");
        $totalFilt = (int) $db->fetchColumn("SELECT COUNT(*) FROM club_events e LEFT JOIN facilities f ON e.facility_id = f.id {$where}", $params);

        $rows = $db->fetchAll(
            "SELECT e.*, f.name_en AS facility_name
             FROM club_events e LEFT JOIN facilities f ON e.facility_id = f.id
             {$where} ORDER BY e.start_datetime DESC
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            $params
        );

        $data = [];
        foreach ($rows as $r) {
            $actions = '<a href="/events/' . $r['id'] . '" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a> ';
            if (Gate::can('events.manage')) {
                $actions .= '<a href="/events/' . $r['id'] . '/edit" class="btn btn-sm btn-warning"><i class="fas fa-edit"></i></a>';
            }
            $data[] = [
                'id'        => $r['id'],
                'code'      => e($r['event_code']),
                'title'     => e($r['title_en']),
                'type'      => '<span class="badge bg-secondary">' . e(ucfirst($r['event_type'])) . '</span>',
                'venue'     => e($r['facility_name'] ?? $r['location'] ?? '-'),
                'date'      => format_datetime($r['start_datetime']),
                'attendees' => $r['current_attendees'] . ($r['max_attendees'] ? '/' . $r['max_attendees'] : ''),
                'status'    => status_badge($r['status']),
                'actions'   => $actions,
            ];
        }

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

    public function eventRegistrations(Request $request): void
    {
        $eventId = (int) $request->routeParam('id');
        $db = $this->db();

        $rows = $db->fetchAll(
            "SELECT r.*, m.full_name_en AS member_name, m.membership_number, m.primary_phone
             FROM club_event_registrations r
             LEFT JOIN members m ON r.member_id = m.id
             WHERE r.event_id = ?
             ORDER BY r.registration_date DESC",
            [$eventId]
        );

        $data = [];
        foreach ($rows as $r) {
            $data[] = [
                'id'            => $r['id'],
                'member'        => e($r['member_name'] ?? ''),
                'membership_no' => e($r['membership_number'] ?? ''),
                'phone'         => e($r['primary_phone'] ?? ''),
                'attendees'     => $r['total_attendees'],
                'fee'           => format_money($r['total_fee']),
                'date'          => format_datetime($r['registration_date']),
                'status'        => status_badge($r['status']),
            ];
        }

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

    public function maintenanceList(Request $request): void
    {
        $facilityId = (int) $request->routeParam('id');
        $db = $this->db();

        $draw   = (int) $request->get('draw', 1);
        $start  = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);

        $total = (int) $db->fetchColumn("SELECT COUNT(*) FROM facility_maintenance WHERE facility_id = ?", [$facilityId]);
        $rows = $db->fetchAll(
            "SELECT * FROM facility_maintenance WHERE facility_id = ? ORDER BY start_date DESC LIMIT " . (int)$length . " OFFSET " . (int)$start,
            [$facilityId]
        );

        $data = [];
        foreach ($rows as $r) {
            $data[] = [
                'id'        => $r['id'],
                'type'      => '<span class="badge bg-secondary">' . e(ucfirst($r['maintenance_type'])) . '</span>',
                'title'     => e($r['title']),
                'start'     => e($r['start_date']),
                'end'       => e($r['end_date'] ?? '-'),
                'cost'      => $r['estimated_cost'] ? format_money($r['estimated_cost']) : '-',
                'priority'  => status_badge($r['priority']),
                'status'    => status_badge($r['status']),
            ];
        }

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

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

        $total = (int) $db->fetchColumn("SELECT COUNT(*) FROM swimming_pool_readings WHERE pool_id = ?", [$poolId]);
        $rows = $db->fetchAll(
            "SELECT * FROM swimming_pool_readings WHERE pool_id = ? ORDER BY reading_date DESC, reading_time DESC LIMIT " . (int)$length . " OFFSET " . (int)$start,
            [$poolId]
        );

        $data = [];
        foreach ($rows as $r) {
            $compliance = $r['is_compliant']
                ? '<span class="badge bg-success">Compliant</span>'
                : '<span class="badge bg-danger">Non-Compliant</span>';
            $data[] = [
                'id'          => $r['id'],
                'date'        => e($r['reading_date']),
                'time'        => e($r['reading_time']),
                'ph'          => $r['ph_level'] ?? '-',
                'chlorine'    => $r['chlorine_level'] ?? '-',
                'temperature' => $r['temperature_celsius'] ? $r['temperature_celsius'] . '°C' : '-',
                'compliant'   => $compliance,
                'reason'      => e($r['non_compliance_reason'] ?? ''),
            ];
        }

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

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

        $where  = "WHERE 1=1";
        $params = [];
        if ($search) {
            $where .= " AND (pp.permit_number LIKE ? OR pp.vehicle_plate LIKE ? OR m.full_name_en LIKE ? OR m.membership_number LIKE ?)";
            $s = "%{$search}%";
            $params = [$s, $s, $s, $s];
        }

        $total     = (int) $db->fetchColumn("SELECT COUNT(*) FROM parking_permits");
        $totalFilt = (int) $db->fetchColumn(
            "SELECT COUNT(*) FROM parking_permits pp LEFT JOIN members m ON pp.member_id = m.id LEFT JOIN parking_zones pz ON pp.zone_id = pz.id {$where}",
            $params
        );

        $rows = $db->fetchAll(
            "SELECT pp.*, m.full_name_en AS member_name, m.membership_number, pz.name_en AS zone_name
             FROM parking_permits pp
             LEFT JOIN members m ON pp.member_id = m.id
             LEFT JOIN parking_zones pz ON pp.zone_id = pz.id
             {$where} ORDER BY pp.created_at DESC
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            $params
        );

        $data = [];
        foreach ($rows as $r) {
            $data[] = [
                'id'       => $r['id'],
                'number'   => e($r['permit_number']),
                'member'   => e($r['member_name'] ?? ''),
                'plate'    => e($r['vehicle_plate']),
                'vehicle'  => e(trim(($r['vehicle_make'] ?? '') . ' ' . ($r['vehicle_model'] ?? ''))),
                'zone'     => e($r['zone_name'] ?? '-'),
                'type'     => '<span class="badge bg-secondary">' . e(ucfirst($r['permit_type'])) . '</span>',
                'dates'    => e($r['start_date']) . ' → ' . e($r['end_date'] ?? '∞'),
                'status'   => status_badge($r['status']),
            ];
        }

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

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

        $dateFilter = $request->get('date', date('Y-m-d'));

        $total = (int) $db->fetchColumn(
            "SELECT COUNT(*) FROM parking_log WHERE DATE(timestamp) = ?",
            [$dateFilter]
        );

        $rows = $db->fetchAll(
            "SELECT pl.*, m.full_name_en AS member_name, pz.name_en AS zone_name
             FROM parking_log pl
             LEFT JOIN members m ON pl.member_id = m.id
             LEFT JOIN parking_zones pz ON pl.zone_id = pz.id
             WHERE DATE(pl.timestamp) = ?
             ORDER BY pl.timestamp DESC
             LIMIT " . (int)$length . " OFFSET " . (int)$start,
            [$dateFilter]
        );

        $data = [];
        foreach ($rows as $r) {
            $dirIcon = $r['direction'] === 'entry'
                ? '<span class="text-success"><i class="fas fa-arrow-right"></i> Entry</span>'
                : '<span class="text-danger"><i class="fas fa-arrow-left"></i> Exit</span>';
            $data[] = [
                'id'        => $r['id'],
                'time'      => format_datetime($r['timestamp']),
                'direction' => $dirIcon,
                'plate'     => e($r['vehicle_plate']),
                'member'    => e($r['member_name'] ?? '-'),
                'zone'      => e($r['zone_name'] ?? '-'),
                'authorized'=> $r['is_authorized'] ? '<i class="fas fa-check text-success"></i>' : '<i class="fas fa-times text-danger"></i>',
            ];
        }

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