<?php
// csr/ajax/get_technicians.php
require_once '../../config/database.php';
session_start();

header('Content-Type: application/json');

if (!isset($_SESSION['csr_id'])) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit();
}

$database = new Database();
$db = $database->getConnection();

$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$limit = 12; // Items per page
$offset = ($page - 1) * $limit;

$status = isset($_GET['status']) ? $_GET['status'] : '';
$city = isset($_GET['city']) ? $_GET['city'] : '';
$skill = isset($_GET['skill']) ? $_GET['skill'] : '';
$search = isset($_GET['search']) ? $_GET['search'] : '';

// Build query conditions
$conditions = [];
$params = [];

if (!empty($status)) {
    $conditions[] = "t.status_id = :status";
    $params[':status'] = $status;
}

if (!empty($city)) {
    $conditions[] = "t.city_id = :city";
    $params[':city'] = $city;
}

if (!empty($skill)) {
    $conditions[] = "t.id IN (SELECT technician_id FROM technician_skills WHERE appliance_type_id = :skill)";
    $params[':skill'] = $skill;
}

if (!empty($search)) {
    $conditions[] = "(t.first_name LIKE :search OR t.last_name LIKE :search OR t.email LIKE :search OR t.phone_number LIKE :search OR t.employee_id LIKE :search)";
    $params[':search'] = "%$search%";
}

$where_clause = !empty($conditions) ? "WHERE " . implode(" AND ", $conditions) : "";

// Get total count for pagination
$count_query = "SELECT COUNT(*) as total FROM technicians t $where_clause";
$count_stmt = $db->prepare($count_query);
foreach ($params as $key => $value) {
    $count_stmt->bindValue($key, $value);
}
$count_stmt->execute();
$total = $count_stmt->fetch(PDO::FETCH_ASSOC)['total'];
$total_pages = ceil($total / $limit);

// Get technicians with pagination
$query = "SELECT t.*, 
            ts.status_name, ts.color_code as status_color,
            c.city_name,
            CONCAT(LEFT(t.first_name, 1), LEFT(t.last_name, 1)) as initials,
            CONCAT(t.first_name, ' ', t.last_name) as full_name,
            (SELECT sr.request_number FROM service_assignments sa 
             JOIN service_requests sr ON sa.request_id = sr.id
             WHERE sa.technician_id = t.id 
               AND sa.status IN ('assigned', 'en_route', 'on_site')
               AND sr.job_status_id NOT IN (4, 5)
             ORDER BY sa.assigned_at DESC
             LIMIT 1) as current_job
          FROM technicians t
          LEFT JOIN technician_statuses ts ON t.status_id = ts.id
          LEFT JOIN cities c ON t.city_id = c.id
          $where_clause
          ORDER BY 
            CASE 
                WHEN t.status_id = 1 THEN 1
                WHEN t.status_id = 2 THEN 2
                WHEN t.status_id = 3 THEN 3
                WHEN t.status_id = 4 THEN 4
                ELSE 5
            END,
            t.first_name
          LIMIT :offset, :limit";

$stmt = $db->prepare($query);
foreach ($params as $key => $value) {
    $stmt->bindValue($key, $value);
}
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();

$technicians = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get statistics for all technicians (without filters)
$stats_query = "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status_id = 1 THEN 1 ELSE 0 END) as available,
                SUM(CASE WHEN status_id = 2 THEN 1 ELSE 0 END) as on_job,
                SUM(CASE WHEN status_id = 4 THEN 1 ELSE 0 END) as on_leave
              FROM technicians WHERE is_active = 1";
$stats_stmt = $db->query($stats_query);
$stats = $stats_stmt->fetch(PDO::FETCH_ASSOC);

// Ensure all stats have values
$stats = [
    'total' => (int)($stats['total'] ?? 0),
    'available' => (int)($stats['available'] ?? 0),
    'on_job' => (int)($stats['on_job'] ?? 0),
    'on_leave' => (int)($stats['on_leave'] ?? 0)
];

echo json_encode([
    'success' => true,
    'technicians' => $technicians,
    'stats' => $stats,
    'pagination' => [
        'current_page' => $page,
        'total_pages' => $total_pages,
        'total_records' => $total
    ]
]);