<?php
// admin/clients.php - Menaxhimi i klientëve (me AJAX të integruar dhe eksport Excel)
require_once '../includes/config.php';

if(!isset($_SESSION['admin_logged_in'])) {
    header('Location: login.php');
    exit();
}

// ============================================
// HANDLE AJAX REQUESTS (i integruar në të njëjtin file)
// ============================================
if(isset($_GET['ajax']) && isset($_GET['get_client_history'])) {
    header('Content-Type: application/json');
    
    $email = isset($_GET['email']) ? urldecode($_GET['email']) : '';
    $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
    $per_page = 10;
    $offset = ($page - 1) * $per_page;
    
    if(empty($email)) {
        echo json_encode(['success' => false, 'message' => 'Email is required']);
        exit;
    }
    
    // Get client info - MERGE by email (preferoni emrin dhe telefonin me te perdorur)
    $stmt = $pdo->prepare("
        SELECT 
            customer_name,
            customer_email,
            customer_phone,
            COUNT(*) as total_reservations,
            MAX(created_at) as last_booking
        FROM reservations 
        WHERE customer_email = ? 
        GROUP BY customer_email
        LIMIT 1
    ");
    $stmt->execute([$email]);
    $client = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if(!$client) {
        echo json_encode(['success' => false, 'message' => 'Client not found']);
        exit;
    }
    
    // Nese emri eshte bosh, merr emrin nga rezervimi me i fundit
    if(empty($client['customer_name'])) {
        $name_stmt = $pdo->prepare("SELECT customer_name FROM reservations WHERE customer_email = ? AND customer_name != '' ORDER BY created_at DESC LIMIT 1");
        $name_stmt->execute([$email]);
        $name_result = $name_stmt->fetch();
        if($name_result) {
            $client['customer_name'] = $name_result['customer_name'];
        }
    }
    
    // Nese telefoni eshte bosh, merr telefonin nga rezervimi me i fundit
    if(empty($client['customer_phone'])) {
        $phone_stmt = $pdo->prepare("SELECT customer_phone FROM reservations WHERE customer_email = ? AND customer_phone != '' ORDER BY created_at DESC LIMIT 1");
        $phone_stmt->execute([$email]);
        $phone_result = $phone_stmt->fetch();
        if($phone_result) {
            $client['customer_phone'] = $phone_result['customer_phone'];
        }
    }
    
    // Count total reservations
    $count_stmt = $pdo->prepare("SELECT COUNT(*) FROM reservations WHERE customer_email = ?");
    $count_stmt->execute([$email]);
    $total = $count_stmt->fetchColumn();
    $total_pages = ceil($total / $per_page);
    
    // Get reservations with pagination
    $stmt = $pdo->prepare("
        SELECT 
            r.*,
            c.brand, 
            c.model, 
            c.license_plate,
            i.name as insurance_name
        FROM reservations r 
        LEFT JOIN cars c ON r.car_id = c.id
        LEFT JOIN insurance_types i ON r.selected_insurance_id = i.id
        WHERE r.customer_email = ? 
        ORDER BY r.pickup_date DESC, r.created_at DESC
        LIMIT $offset, $per_page
    ");
    $stmt->execute([$email]);
    $reservations = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach($reservations as &$res) {
        if(!empty($res['selected_options'])) {
            $options_data = json_decode($res['selected_options'], true);
            if(is_array($options_data) && count($options_data) > 0) {
                $option_names = [];
                foreach($options_data as $opt) {
                    if(isset($opt['name'])) {
                        $option_names[] = $opt['name'] . (isset($opt['quantity']) && $opt['quantity'] > 1 ? ' x' . $opt['quantity'] : '');
                    }
                }
                $res['options_names'] = implode(', ', $option_names);
            } else {
                $res['options_names'] = '';
            }
        } else {
            $res['options_names'] = '';
        }
    }
    
    echo json_encode([
        'success' => true,
        'client' => $client,
        'reservations' => $reservations,
        'total_count' => $total,
        'current_page' => $page,
        'total_pages' => $total_pages,
        'per_page' => $per_page
    ]);
    exit;
}

// ============================================
// HANDLE EXPORT TO EXCEL
// ============================================
if(isset($_GET['export_excel'])) {
    // Get all clients - MERGED by email
    $search = isset($_GET['search']) ? trim($_GET['search']) : '';
    
    $sql = "SELECT 
                MAX(customer_name) as customer_name,
                customer_email,
                MAX(customer_phone) as customer_phone,
                COUNT(*) as total_reservations,
                MAX(created_at) as last_booking_date
            FROM reservations 
            WHERE customer_name != '' AND customer_email != ''
            GROUP BY customer_email";
    
    $params = [];
    if($search) {
        $sql = "SELECT 
                    MAX(customer_name) as customer_name,
                    customer_email,
                    MAX(customer_phone) as customer_phone,
                    COUNT(*) as total_reservations,
                    MAX(created_at) as last_booking_date
                FROM reservations 
                WHERE (customer_name LIKE ? OR customer_email LIKE ? OR customer_phone LIKE ?)
                GROUP BY customer_email";
        $search_param = "%$search%";
        $params = [$search_param, $search_param, $search_param];
    }
    
    $sql .= " ORDER BY last_booking_date DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $clients = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Create Excel file
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="clients_' . date('Y-m-d') . '.xls"');
    
    echo '<html>';
    echo '<head><meta charset="UTF-8"></head>';
    echo '<body>';
    echo '<table border="1">';
    echo '<tr>
            <th>#</th>
            <th>Name</th>
            <th>Email</th>
            <th>Phone</th>
            <th>Total Bookings</th>
            <th>Last Booking</th>
          </tr>';
    
    $i = 1;
    foreach($clients as $client) {
        echo '<tr>';
        echo '<td>' . $i++ . '</td>';
        echo '<td>' . htmlspecialchars($client['customer_name'] ?? 'N/A') . '</td>';
        echo '<td>' . htmlspecialchars($client['customer_email']) . '</td>';
        echo '<td>' . htmlspecialchars($client['customer_phone'] ?? 'N/A') . '</td>';
        echo '<td>' . $client['total_reservations'] . '</td>';
        echo '<td>' . date('d/m/Y H:i', strtotime($client['last_booking_date'])) . '</td>';
        echo '</tr>';
    }
    
    echo '</table>';
    echo '</body>';
    echo '</html>';
    exit;
}

// ============================================
// SORTING & FILTERS
// ============================================
$sort_column = isset($_GET['sort']) ? $_GET['sort'] : 'id';
$sort_order = isset($_GET['order']) && strtoupper($_GET['order']) == 'ASC' ? 'ASC' : 'DESC';

$search = isset($_GET['search']) ? trim($_GET['search']) : '';
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$per_page = 20;
$offset = ($page - 1) * $per_page;

// ============================================
// QUERY I RREGULLUAR - MERGE BY EMAIL
// ============================================
// Get unique clients - MERGED by email, duke preferuar emrin dhe telefonin me te perdorur
$sql = "SELECT 
            customer_email,
            MAX(customer_name) as customer_name,
            MAX(customer_phone) as customer_phone,
            COUNT(*) as total_reservations,
            MAX(created_at) as last_booking_date
        FROM reservations 
        WHERE customer_email != '' AND customer_email IS NOT NULL";

$count_sql = "SELECT COUNT(DISTINCT customer_email) FROM reservations WHERE customer_email != '' AND customer_email IS NOT NULL";

if($search) {
    $sql .= " AND (customer_name LIKE ? OR customer_email LIKE ? OR customer_phone LIKE ?)";
    $count_sql .= " AND (customer_name LIKE ? OR customer_email LIKE ? OR customer_phone LIKE ?)";
    $search_param = "%$search%";
    
    $sql .= " GROUP BY customer_email ORDER BY last_booking_date DESC LIMIT $offset, $per_page";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$search_param, $search_param, $search_param]);
    $clients = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $count_stmt = $pdo->prepare($count_sql);
    $count_stmt->execute([$search_param, $search_param, $search_param]);
    $total_clients = $count_stmt->fetchColumn();
} else {
    $sql .= " GROUP BY customer_email ORDER BY last_booking_date DESC LIMIT $offset, $per_page";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $clients = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $count_stmt = $pdo->prepare($count_sql);
    $count_stmt->execute();
    $total_clients = $count_stmt->fetchColumn();
}

// ============================================
// PASQYRO TË DHËNAT E PLOTA PËR ÇDO KLIENT
// ============================================
// Per cdo klient, marrim emrin dhe telefonin me te perdorur ne rezervimet e tij
foreach($clients as &$client) {
    if(empty($client['customer_name'])) {
        $name_stmt = $pdo->prepare("SELECT customer_name FROM reservations WHERE customer_email = ? AND customer_name != '' ORDER BY created_at DESC LIMIT 1");
        $name_stmt->execute([$client['customer_email']]);
        $name_result = $name_stmt->fetch();
        if($name_result) {
            $client['customer_name'] = $name_result['customer_name'];
        }
    }
    
    if(empty($client['customer_phone'])) {
        $phone_stmt = $pdo->prepare("SELECT customer_phone FROM reservations WHERE customer_email = ? AND customer_phone != '' ORDER BY created_at DESC LIMIT 1");
        $phone_stmt->execute([$client['customer_email']]);
        $phone_result = $phone_stmt->fetch();
        if($phone_result) {
            $client['customer_phone'] = $phone_result['customer_phone'];
        }
    }
}

$total_pages = ceil($total_clients / $per_page);

// Helper functions
function sort_url($column) {
    global $sort_column, $sort_order, $search;
    $new_order = ($sort_column == $column && $sort_order == 'ASC') ? 'DESC' : 'ASC';
    $url = "?sort=" . $column . "&order=" . $new_order;
    if($search) $url .= "&search=" . urlencode($search);
    return $url;
}

function sort_icon($column) {
    global $sort_column, $sort_order;
    if($sort_column == $column) {
        return $sort_order == 'ASC' ? '↑' : '↓';
    }
    return '↕';
}

function getStatusClass($status) {
    switch($status) {
        case 'pending': return 'status-pending';
        case 'confirmed': return 'status-confirmed';
        case 'running': return 'status-running';
        case 'completed': return 'status-completed';
        case 'cancelled': return 'status-cancelled';
        default: return 'status-pending';
    }
}

function getPaymentClass($payment_status) {
    switch($payment_status) {
        case 'paid': return 'payment-paid';
        case 'partial': return 'payment-partial';
        default: return 'payment-unpaid';
    }
}

function escapeHtml($text) {
    if(!$text) return '';
    return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
}

$page_title = 'Clients Management';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo $page_title; ?> - Admin</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; background: #f5f5f5; }
        
        .sidebar {
            width: 260px;
            background: #1a1a2e;
            color: white;
            position: fixed;
            left: 0;
            top: 0;
            bottom: 0;
            overflow-y: auto;
            z-index: 100;
        }
        .sidebar-header { padding: 24px 20px; border-bottom: 1px solid rgba(255,255,255,0.1); }
        .sidebar-header h2 { font-size: 18px; font-weight: 600; }
        .sidebar-nav { padding: 20px 0; }
        .sidebar-nav a {
            display: flex;
            align-items: center;
            gap: 12px;
            padding: 12px 20px;
            color: #cbd5e1;
            text-decoration: none;
            font-size: 14px;
            transition: all 0.2s;
        }
        .sidebar-nav a i { width: 20px; }
        .sidebar-nav a:hover, .sidebar-nav a.active { background: #0066cc; color: white; }
        
        .main-content { margin-left: 260px; padding: 24px; }
        .page-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 24px;
            flex-wrap: wrap;
            gap: 16px;
        }
        .page-header h1 { font-size: 24px; font-weight: 600; }
        .page-header h1 i { color: #0066cc; margin-right: 10px; }
        
        .btn-export {
            background: #10b981;
            color: white;
            padding: 10px 20px;
            border-radius: 8px;
            text-decoration: none;
            font-size: 14px;
            font-weight: 500;
            display: inline-flex;
            align-items: center;
            gap: 8px;
            transition: all 0.2s;
            border: none;
            cursor: pointer;
        }
        .btn-export:hover {
            background: #059669;
            transform: translateY(-1px);
        }
        
        .stats-row {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
            gap: 16px;
            margin-bottom: 24px;
        }
        .stat-box {
            background: white;
            border-radius: 12px;
            padding: 16px 20px;
            border: 1px solid #e5e7eb;
            text-align: center;
        }
        .stat-box h3 { font-size: 28px; font-weight: 700; color: #0066cc; }
        .stat-box p { font-size: 13px; color: #6b7280; margin-top: 4px; }
        
        .filters-bar {
            background: white;
            border-radius: 12px;
            border: 1px solid #e5e7eb;
            padding: 16px 20px;
            margin-bottom: 24px;
            display: flex;
            flex-wrap: wrap;
            gap: 16px;
            align-items: flex-end;
        }
        .filter-group {
            display: flex;
            flex-direction: column;
            gap: 6px;
        }
        .filter-group label {
            font-size: 12px;
            font-weight: 600;
            color: #374151;
            text-transform: uppercase;
        }
        .filter-group input {
            padding: 8px 12px;
            border: 1px solid #d1d5db;
            border-radius: 6px;
            font-size: 14px;
            min-width: 220px;
        }
        .filter-group button {
            background: #0066cc;
            color: white;
            padding: 8px 20px;
            border: none;
            border-radius: 6px;
            cursor: pointer;
            font-weight: 500;
        }
        .filter-group button.reset {
            background: #6b7280;
        }
        
        .clients-table {
            background: white;
            border-radius: 12px;
            border: 1px solid #e5e7eb;
            overflow-x: auto;
        }
        table {
            width: 100%;
            border-collapse: collapse;
        }
        th, td {
            padding: 12px 16px;
            text-align: left;
            border-bottom: 1px solid #f0f0f0;
            font-size: 13px;
        }
        th {
            background: #f9fafb;
            font-weight: 600;
            color: #374151;
        }
        th a {
            color: #374151;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            gap: 6px;
        }
        th a:hover { color: #0066cc; }
        .sort-icon { font-size: 12px; color: #9ca3af; }
        
        .client-name { font-weight: 600; color: #1e293b; }
        .client-email { color: #0066cc; }
        .client-phone { font-family: monospace; font-size: 12px; }
        
        .btn-view {
            background: #8b5cf6;
            color: white;
            padding: 6px 12px;
            border-radius: 6px;
            text-decoration: none;
            font-size: 12px;
            font-weight: 500;
            display: inline-flex;
            align-items: center;
            gap: 5px;
            border: none;
            cursor: pointer;
        }
        .btn-view:hover { background: #7c3aed; }
        
        .pagination {
            display: flex;
            justify-content: center;
            gap: 8px;
            margin-top: 24px;
            flex-wrap: wrap;
        }
        .pagination a, .pagination span {
            padding: 8px 14px;
            border: 1px solid #e5e7eb;
            border-radius: 8px;
            text-decoration: none;
            color: #374151;
            background: white;
            font-size: 13px;
            transition: all 0.2s;
        }
        .pagination a:hover {
            background: #0066cc;
            color: white;
            border-color: #0066cc;
        }
        .pagination .active {
            background: #0066cc;
            color: white;
            border-color: #0066cc;
        }
        .pagination .disabled {
            opacity: 0.5;
            cursor: not-allowed;
        }
        
        .empty-state {
            text-align: center;
            padding: 60px;
            color: #6b7280;
        }
        .empty-state i {
            font-size: 48px;
            color: #d1d5db;
            margin-bottom: 16px;
            display: block;
        }
        
        .info-text {
            text-align: center;
            font-size: 12px;
            color: #6b7280;
            margin-top: 16px;
        }
        
        /* Modal Styles */
        .modal {
            display: none;
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background: rgba(0,0,0,0.6);
            justify-content: center;
            align-items: center;
            z-index: 1000;
            backdrop-filter: blur(4px);
        }
        .modal-content {
            background: white;
            border-radius: 20px;
            width: 1100px;
            max-width: 95%;
            max-height: 90vh;
            overflow: hidden;
            animation: modalSlideIn 0.3s ease;
            box-shadow: 0 25px 50px -12px rgba(0,0,0,0.25);
            display: flex;
            flex-direction: column;
        }
        @keyframes modalSlideIn {
            from { opacity: 0; transform: translateY(-30px) scale(0.95); }
            to { opacity: 1; transform: translateY(0) scale(1); }
        }
        .modal-header {
            background: linear-gradient(135deg, #1a1a2e 0%, #16213e 100%);
            color: white;
            padding: 16px 24px;
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-shrink: 0;
        }
        .modal-header h3 { 
            font-size: 18px; 
            font-weight: 600; 
            margin: 0;
            display: flex;
            align-items: center;
            gap: 10px;
        }
        .modal-header h3 i { 
            margin-right: 4px; 
        }
        .modal-header .close-modal { 
            font-size: 28px; 
            cursor: pointer; 
            color: rgba(255,255,255,0.7); 
            transition: color 0.2s;
            line-height: 1;
            padding: 0 4px;
        }
        .modal-header .close-modal:hover { color: white; }
        .modal-body { 
            padding: 24px; 
            flex: 1;
            overflow-y: auto; 
            min-height: 300px;
        }
        .modal-footer {
            padding: 16px 24px;
            border-top: 1px solid #e2e8f0;
            background: #f8fafc;
            display: flex;
            justify-content: flex-end;
            gap: 12px;
            border-radius: 0 0 20px 20px;
            flex-shrink: 0;
        }
        .btn-secondary {
            background: #6b7280;
            color: white;
            padding: 10px 20px;
            border: none;
            border-radius: 8px;
            cursor: pointer;
            font-size: 14px;
        }
        .btn-secondary:hover {
            background: #4b5563;
        }
        
        .client-profile-card {
            background: linear-gradient(135deg, #f0f9ff 0%, #e0f2fe 100%);
            border-radius: 16px;
            padding: 20px;
            margin-bottom: 24px;
            display: flex;
            align-items: center;
            gap: 24px;
            flex-wrap: wrap;
        }
        .client-profile-card .avatar {
            width: 70px;
            height: 70px;
            background: #0066cc;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            color: white;
            font-size: 28px;
            font-weight: 700;
            flex-shrink: 0;
        }
        .client-profile-card .client-details h3 {
            font-size: 22px;
            font-weight: 700;
            color: #1e293b;
            margin-bottom: 4px;
        }
        .client-profile-card .client-details p {
            color: #475569;
            font-size: 14px;
            margin: 4px 0;
        }
        .client-profile-card .client-details p i {
            width: 20px;
            color: #0066cc;
            text-align: center;
        }
        .client-profile-card .client-stats {
            margin-left: auto;
            display: flex;
            gap: 20px;
            flex-wrap: wrap;
        }
        .client-profile-card .client-stats .stat {
            text-align: center;
            background: white;
            padding: 8px 16px;
            border-radius: 40px;
            box-shadow: 0 1px 3px rgba(0,0,0,0.05);
        }
        .client-profile-card .client-stats .stat .number {
            font-size: 20px;
            font-weight: 700;
            color: #0066cc;
        }
        .client-profile-card .client-stats .stat .label {
            font-size: 11px;
            color: #6b7280;
        }
        
        .reservation-history-table {
            width: 100%;
            border-collapse: collapse;
            font-size: 12px;
        }
        .reservation-history-table th {
            background: #f1f5f9;
            padding: 10px 12px;
            text-align: left;
            font-weight: 600;
            color: #1e293b;
            border-bottom: 1px solid #e2e8f0;
            position: sticky;
            top: 0;
        }
        .reservation-history-table td {
            padding: 10px 12px;
            border-bottom: 1px solid #f1f5f9;
            vertical-align: middle;
        }
        .reservation-history-table tr:hover { background: #f8fafc; }
        
        .status-badge {
            display: inline-block;
            padding: 3px 8px;
            border-radius: 20px;
            font-size: 10px;
            font-weight: 600;
        }
        .status-pending { background: #fef3c7; color: #92400e; }
        .status-confirmed { background: #dbeafe; color: #1e40af; }
        .status-running { background: #d1fae5; color: #065f46; }
        .status-completed { background: #e0e7ff; color: #3730a3; }
        .status-cancelled { background: #fee2e2; color: #991b1b; }
        
        .payment-paid { background: #d1fae5; color: #065f46; }
        .payment-unpaid { background: #fee2e2; color: #991b1b; }
        .payment-partial { background: #fef3c7; color: #92400e; }
        
        .history-badge { font-family: monospace; font-weight: bold; color: #0066cc; }
        
        .empty-history {
            text-align: center;
            padding: 40px;
            color: #6b7280;
        }
        .empty-history i {
            font-size: 32px;
            color: #cbd5e1;
            margin-bottom: 12px;
            display: block;
        }
        
        .btn-sm {
            background: #0066cc;
            color: white;
            padding: 4px 10px;
            border-radius: 6px;
            text-decoration: none;
            font-size: 11px;
            font-weight: 500;
            display: inline-block;
        }
        .btn-sm:hover { background: #0052a3; }
        
        .history-pagination {
            display: flex;
            justify-content: center;
            gap: 6px;
            margin-top: 16px;
            padding: 10px;
            background: #f8fafc;
            border-radius: 8px;
            flex-wrap: wrap;
        }
        .history-pagination a, .history-pagination span {
            padding: 4px 10px;
            border: 1px solid #e2e8f0;
            border-radius: 6px;
            text-decoration: none;
            color: #475569;
            background: white;
            font-size: 11px;
            cursor: pointer;
            transition: all 0.2s;
        }
        .history-pagination a:hover {
            background: #0066cc;
            color: white;
            border-color: #0066cc;
        }
        .history-pagination .active {
            background: #0066cc;
            color: white;
            border-color: #0066cc;
        }
        .history-pagination .disabled {
            opacity: 0.5;
            cursor: not-allowed;
            background: #f1f5f9;
        }
        
        .history-loading {
            text-align: center;
            padding: 30px;
        }
        .history-loading i {
            font-size: 32px;
            color: #0066cc;
        }
        
        @media (max-width: 768px) {
            .sidebar { width: 100%; position: relative; height: auto; }
            .main-content { margin-left: 0; }
            .filters-bar { flex-direction: column; align-items: stretch; }
            .filter-group input { width: 100%; }
            .client-profile-card { flex-direction: column; text-align: center; }
            .client-profile-card .client-stats { margin-left: 0; justify-content: center; }
            .stats-row { grid-template-columns: repeat(2, 1fr); }
            .page-header { flex-direction: column; align-items: stretch; }
            .btn-export { justify-content: center; }
            .modal-header h3 { font-size: 16px; }
        }
    </style>
</head>
<body>
    <div class="sidebar">
        <div class="sidebar-header"><h2><i class="fas fa-car"></i> Car Rental Admin</h2></div>
        <div class="sidebar-nav">
            <a href="dashboard.php"><i class="fas fa-tachometer-alt"></i> Dashboard</a>
            <a href="cars.php"><i class="fas fa-car"></i> Cars</a>
            <a href="create-reservation.php"><i class="fas fa-plus-square"></i> Create Reservation</a>
            <a href="reservations.php"><i class="fas fa-calendar-check"></i> Reservations</a>
            <a href="calendar.php"><i class="fas fa-calendar-alt"></i> Calendar</a>
            <a href="price-list.php"><i class="fas fa-tags"></i> Price Rules</a>
            <a href="extras.php"><i class="fas fa-cogs"></i> Extras</a>
            <a href="email-settings.php"><i class="fas fa-envelope"></i> Email Settings</a>
            <a href="settings.php"><i class="fas fa-cog"></i> Settings</a>
            <a href="manage-admins.php"><i class="fas fa-user-shield"></i> Users</a>
            <a href="clients.php" class="active"><i class="fas fa-users"></i> Clients</a>
            <a href="logout.php"><i class="fas fa-sign-out-alt"></i> Logout</a>
        </div>
    </div>
    
    <div class="main-content">
        <div class="page-header">
            <h1><i class="fas fa-users"></i> Clients</h1>
            <div style="display: flex; gap: 12px; flex-wrap: wrap;">
                <a href="?export_excel=1<?php echo $search ? '&search=' . urlencode($search) : ''; ?>" class="btn-export">
                    <i class="fas fa-file-excel"></i> Export to Excel
                </a>
            </div>
        </div>
        
        <!-- Statistics -->
        <div class="stats-row">
            <div class="stat-box">
                <?php
                    $total_clients_count = $pdo->query("SELECT COUNT(DISTINCT customer_email) FROM reservations WHERE customer_email != '' AND customer_email IS NOT NULL")->fetchColumn();
                ?>
                <h3><?php echo number_format($total_clients_count); ?></h3>
                <p>Total Clients</p>
            </div>
            <div class="stat-box">
                <?php
                    $total_res = $pdo->query("SELECT COUNT(*) FROM reservations")->fetchColumn();
                ?>
                <h3><?php echo number_format($total_res); ?></h3>
                <p>Total Reservations</p>
            </div>
            <div class="stat-box">
                <?php
                    $avg_res = $total_clients_count > 0 ? round($total_res / $total_clients_count, 1) : 0;
                ?>
                <h3><?php echo $avg_res; ?></h3>
                <p>Avg. Reservations/Client</p>
            </div>
        </div>
        
        <!-- Filters -->
        <div class="filters-bar">
            <form method="GET" id="filterForm" style="display: contents;">
                <input type="hidden" name="sort" value="<?php echo htmlspecialchars($sort_column); ?>">
                <input type="hidden" name="order" value="<?php echo htmlspecialchars($sort_order); ?>">
                <div class="filter-group">
                    <label><i class="fas fa-search"></i> Search Client</label>
                    <input type="text" name="search" placeholder="Name, email or phone..." value="<?php echo htmlspecialchars($search); ?>">
                </div>
                <div class="filter-group">
                    <button type="submit"><i class="fas fa-filter"></i> Apply</button>
                </div>
                <div class="filter-group">
                    <button type="button" class="reset" onclick="resetFilters()"><i class="fas fa-undo"></i> Reset</button>
                </div>
            </form>
        </div>
        
        <!-- Clients Table -->
        <div class="clients-table">
            <?php if(count($clients) > 0): ?>
                <table>
                    <thead>
                        <tr>
                            <th><a href="<?php echo sort_url('customer_name'); ?>">Name <span class="sort-icon"><?php echo sort_icon('customer_name'); ?></span></a></th>
                            <th><a href="<?php echo sort_url('customer_email'); ?>">Email <span class="sort-icon"><?php echo sort_icon('customer_email'); ?></span></a></th>
                            <th>Phone</th>
                            <th><a href="<?php echo sort_url('total_reservations'); ?>">Bookings <span class="sort-icon"><?php echo sort_icon('total_reservations'); ?></span></a></th>
                            <th><a href="<?php echo sort_url('last_booking_date'); ?>">Last Booking <span class="sort-icon"><?php echo sort_icon('last_booking_date'); ?></span></a></th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach($clients as $client): ?>
                        <tr>
                            <td><span class="client-name"><?php echo escapeHtml($client['customer_name'] ?? 'N/A'); ?></span></td>
                            <td><span class="client-email"><?php echo escapeHtml($client['customer_email']); ?></span></td>
                            <td><span class="client-phone"><?php echo escapeHtml($client['customer_phone'] ?? 'N/A'); ?></span></td>
                            <td><strong><?php echo $client['total_reservations']; ?></strong></td>
                            <td><?php echo date('d/m/Y H:i', strtotime($client['last_booking_date'])); ?></td>
                            <td>
                                <button class="btn-view" onclick="viewClientHistory('<?php echo urlencode($client['customer_email']); ?>', '<?php echo addslashes($client['customer_name'] ?? ''); ?>', '<?php echo addslashes($client['customer_phone'] ?? ''); ?>')">
                                    <i class="fas fa-history"></i> History
                                </button>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            <?php else: ?>
                <div class="empty-state">
                    <i class="fas fa-users-slash"></i>
                    <p>No clients found</p>
                    <?php if($search): ?>
                        <p style="margin-top: 8px;">Try changing your search</p>
                        <button onclick="resetFilters()" class="btn-add" style="margin-top: 16px; background: #6b7280; color: white; padding: 10px 20px; border: none; border-radius: 8px; cursor: pointer;">Clear Search</button>
                    <?php else: ?>
                        <p style="margin-top: 8px;">No reservations have been made yet.</p>
                    <?php endif; ?>
                </div>
            <?php endif; ?>
        </div>
        
        <?php if($total_pages > 1): ?>
        <div class="pagination">
            <?php if($page > 1): ?>
                <a href="?page=<?php echo $page-1; ?>&sort=<?php echo urlencode($sort_column); ?>&order=<?php echo urlencode($sort_order); ?><?php echo $search ? '&search=' . urlencode($search) : ''; ?>">
                    <i class="fas fa-chevron-left"></i> Previous
                </a>
            <?php else: ?>
                <span class="disabled"><i class="fas fa-chevron-left"></i> Previous</span>
            <?php endif; ?>
            
            <?php 
            $start_page = max(1, $page - 2);
            $end_page = min($total_pages, $page + 2);
            
            if($start_page > 1): ?>
                <a href="?page=1&sort=<?php echo urlencode($sort_column); ?>&order=<?php echo urlencode($sort_order); ?><?php echo $search ? '&search=' . urlencode($search) : ''; ?>">1</a>
                <?php if($start_page > 2): ?>
                    <span>...</span>
                <?php endif; ?>
            <?php endif; ?>
            
            <?php for($i = $start_page; $i <= $end_page; $i++): ?>
                <?php if($i == $page): ?>
                    <span class="active"><?php echo $i; ?></span>
                <?php else: ?>
                    <a href="?page=<?php echo $i; ?>&sort=<?php echo urlencode($sort_column); ?>&order=<?php echo urlencode($sort_order); ?><?php echo $search ? '&search=' . urlencode($search) : ''; ?>">
                        <?php echo $i; ?>
                    </a>
                <?php endif; ?>
            <?php endfor; ?>
            
            <?php if($end_page < $total_pages): ?>
                <?php if($end_page < $total_pages - 1): ?>
                    <span>...</span>
                <?php endif; ?>
                <a href="?page=<?php echo $total_pages; ?>&sort=<?php echo urlencode($sort_column); ?>&order=<?php echo urlencode($sort_order); ?><?php echo $search ? '&search=' . urlencode($search) : ''; ?>">
                    <?php echo $total_pages; ?>
                </a>
            <?php endif; ?>
            
            <?php if($page < $total_pages): ?>
                <a href="?page=<?php echo $page+1; ?>&sort=<?php echo urlencode($sort_column); ?>&order=<?php echo urlencode($sort_order); ?><?php echo $search ? '&search=' . urlencode($search) : ''; ?>">
                    Next <i class="fas fa-chevron-right"></i>
                </a>
            <?php else: ?>
                <span class="disabled">Next <i class="fas fa-chevron-right"></i></span>
            <?php endif; ?>
        </div>
        <?php endif; ?>
        
        <div class="info-text">
            Showing <?php echo count($clients); ?> of <?php echo number_format($total_clients); ?> clients
        </div>
    </div>
    
    <!-- Client History Modal -->
    <div id="historyModal" class="modal">
        <div class="modal-content">
            <div class="modal-header">
                <h3>
                    <i class="fas fa-history"></i> 
                    Client Reservation History
                </h3>
                <span class="close-modal" onclick="closeHistoryModal()">&times;</span>
            </div>
            <div class="modal-body" id="historyContent">
                <div class="history-loading">
                    <i class="fas fa-spinner fa-spin"></i>
                    <p style="margin-top: 12px;">Loading client history...</p>
                </div>
            </div>
            <div class="modal-footer">
                <button class="btn-secondary" onclick="closeHistoryModal()">Close</button>
            </div>
        </div>
    </div>
    
    <script>
        let currentClientEmail = '';
        let currentClientName = '';
        let currentClientPhone = '';
        let currentHistoryPage = 1;
        let totalHistoryPages = 1;
        
        function resetFilters() {
            window.location.href = 'clients.php';
        }
        
        function viewClientHistory(email, name, phone) {
            currentClientEmail = encodeURIComponent(email);
            currentClientName = name || 'Unknown';
            currentClientPhone = phone || 'N/A';
            
            currentHistoryPage = 1;
            
            const modalContent = document.getElementById('historyContent');
            modalContent.innerHTML = '<div class="history-loading"><i class="fas fa-spinner fa-spin"></i><p style="margin-top: 12px;">Loading client history...</p></div>';
            document.getElementById('historyModal').style.display = 'flex';
            
            loadClientHistory(1);
        }
        
        function loadClientHistory(page) {
            const modalContent = document.getElementById('historyContent');
            modalContent.innerHTML = '<div class="history-loading"><i class="fas fa-spinner fa-spin"></i><p style="margin-top: 12px;">Loading page ' + page + '...</p></div>';
            
            fetch(`?ajax=1&get_client_history=1&email=${currentClientEmail}&page=${page}`)
                .then(res => res.json())
                .then(data => {
                    if(data.success) {
                        currentHistoryPage = data.current_page;
                        totalHistoryPages = data.total_pages;
                        renderClientHistory(data);
                    } else {
                        modalContent.innerHTML = `
                            <div class="empty-history">
                                <i class="fas fa-exclamation-triangle"></i>
                                <p>Error: ${escapeHtml(data.message)}</p>
                            </div>
                        `;
                    }
                })
                .catch(err => {
                    modalContent.innerHTML = `
                        <div class="empty-history">
                            <i class="fas fa-exclamation-triangle"></i>
                            <p>Error loading history: ${err.message}</p>
                        </div>
                    `;
                });
        }
        
        function renderClientHistory(data) {
            // Use data from AJAX response
            const clientName = data.client && data.client.customer_name ? data.client.customer_name : currentClientName;
            const clientEmail = data.client && data.client.customer_email ? data.client.customer_email : decodeURIComponent(currentClientEmail);
            const clientPhone = data.client && data.client.customer_phone ? data.client.customer_phone : currentClientPhone;
            
            let html = `
                <div class="client-profile-card">
                    <div class="avatar">${clientName ? clientName.charAt(0).toUpperCase() : '?'}</div>
                    <div class="client-details">
                        <h3>${escapeHtml(clientName)}</h3>
                        <p><i class="fas fa-envelope"></i> ${escapeHtml(clientEmail)}</p>
                        <p><i class="fas fa-phone"></i> ${escapeHtml(clientPhone)}</p>
                    </div>
                    <div class="client-stats">
                        <div class="stat">
                            <div class="number">${data.total_count}</div>
                            <div class="label">Total Bookings</div>
                        </div>
                    </div>
                </div>
            `;
            
            if(data.reservations && data.reservations.length > 0) {
                html += `
                    <div style="overflow-x: auto;">
                        <table class="reservation-history-table">
                            <thead>
                                <tr>
                                    <th>Reservation Code</th>
                                    <th>Car</th>
                                    <th>Plate</th>
                                    <th>Pickup Date</th>
                                    <th>Return Date</th>
                                    <th>Days</th>
                                    <th>Total</th>
                                    <th>Status</th>
                                    <th>Payment</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody>
                `;
                
                data.reservations.forEach(r => {
                    let totalDisplay = r.grand_total ? parseFloat(r.grand_total).toFixed(2) : (r.total_price ? parseFloat(r.total_price).toFixed(2) : '0');
                    let statusClass = getStatusClass(r.status);
                    let paymentClass = getPaymentClass(r.payment_status);
                    
                    // Determine which tab to redirect to
                    let tabParam = '';
                    if (r.status === 'completed' && r.payment_status === 'paid') {
                        tabParam = '&tab=completed';
                    } else {
                        tabParam = '&tab=active';
                    }
                    
                    html += `
                        <tr>
                            <td class="history-badge">${escapeHtml(r.reservation_code)}</td>
                            <td><strong>${escapeHtml(r.brand)} ${escapeHtml(r.model)}</strong></td>
                            <td><span style="font-family:monospace;font-size:11px;background:#f1f5f9;padding:2px 6px;border-radius:4px;">${escapeHtml(r.license_plate)}</span></td>
                            <td>${r.pickup_date}<br><small>${r.pickup_time ? r.pickup_time.substring(0,5) : '-'}</small></td>
                            <td>${r.dropoff_date}<br><small>${r.dropoff_time ? r.dropoff_time.substring(0,5) : '-'}</small></td>
                            <td>${r.total_days}</td>
                            <td>€${totalDisplay}</td>
                            <td><span class="status-badge ${statusClass}">${r.status}</span></td>
                            <td><span class="status-badge ${paymentClass}">${r.payment_status == 'partial' ? 'Partial' : (r.payment_status == 'paid' ? 'Paid' : 'Unpaid')}</span></td>
                            <td><a href="reservations.php?search=${encodeURIComponent(r.reservation_code)}${tabParam}" class="btn-sm" target="_blank"><i class="fas fa-eye"></i> View</a></td>
                        </tr>
                    `;
                });
                
                html += `
                            </tbody>
                        </table>
                    </div>
                `;
            } else {
                html += `
                    <div class="empty-history">
                        <i class="fas fa-calendar-times"></i>
                        <p>No reservations found for this client</p>
                    </div>
                `;
            }
            
            // Pagination
            if(totalHistoryPages > 1) {
                html += '<div class="history-pagination">';
                if(currentHistoryPage > 1) {
                    html += `<a onclick="loadClientHistory(${currentHistoryPage - 1})"><i class="fas fa-chevron-left"></i> Prev</a>`;
                } else {
                    html += `<span class="disabled"><i class="fas fa-chevron-left"></i> Prev</span>`;
                }
                
                let startPage = Math.max(1, currentHistoryPage - 2);
                let endPage = Math.min(totalHistoryPages, currentHistoryPage + 2);
                
                if(startPage > 1) {
                    html += `<a onclick="loadClientHistory(1)">1</a>`;
                    if(startPage > 2) html += `<span>...</span>`;
                }
                
                for(let i = startPage; i <= endPage; i++) {
                    if(i === currentHistoryPage) {
                        html += `<span class="active">${i}</span>`;
                    } else {
                        html += `<a onclick="loadClientHistory(${i})">${i}</a>`;
                    }
                }
                
                if(endPage < totalHistoryPages) {
                    if(endPage < totalHistoryPages - 1) html += `<span>...</span>`;
                    html += `<a onclick="loadClientHistory(${totalHistoryPages})">${totalHistoryPages}</a>`;
                }
                
                if(currentHistoryPage < totalHistoryPages) {
                    html += `<a onclick="loadClientHistory(${currentHistoryPage + 1})">Next <i class="fas fa-chevron-right"></i></a>`;
                } else {
                    html += `<span class="disabled">Next <i class="fas fa-chevron-right"></i></span>`;
                }
                html += '</div>';
            }
            
            document.getElementById('historyContent').innerHTML = html;
        }
        
        function getStatusClass(status) {
            switch(status) {
                case 'pending': return 'status-pending';
                case 'confirmed': return 'status-confirmed';
                case 'running': return 'status-running';
                case 'completed': return 'status-completed';
                case 'cancelled': return 'status-cancelled';
                default: return 'status-pending';
            }
        }
        
        function getPaymentClass(payment_status) {
            switch(payment_status) {
                case 'paid': return 'payment-paid';
                case 'partial': return 'payment-partial';
                default: return 'payment-unpaid';
            }
        }
        
        function closeHistoryModal() {
            document.getElementById('historyModal').style.display = 'none';
            currentClientEmail = '';
        }
        
        function escapeHtml(text) {
            if(!text) return '';
            const div = document.createElement('div');
            div.textContent = text;
            return div.innerHTML;
        }
        
        window.onclick = function(event) {
            const modal = document.getElementById('historyModal');
            if(event.target == modal) {
                closeHistoryModal();
            }
        }
    </script>
</body>
</html>