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

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

$database = new Database();
$db = $database->getConnection();
require_once '../../includes/app_helpers.php';
ensureOperationalTables($db);

try {
    // Get statistics
    $stats = [];
    
    // Total requests
    $query = "SELECT COUNT(*) as total FROM service_requests";
    $stmt = $db->query($query);
    $stats['total_requests'] = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // Pending requests (status_id = 1 for 'New Request')
    $query = "SELECT COUNT(*) as total FROM service_requests WHERE job_status_id = 1";
    $stmt = $db->query($query);
    $stats['pending_requests'] = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // Active jobs (Assigned = 2, In Progress = 3)
    $query = "SELECT COUNT(*) as total FROM service_requests WHERE job_status_id IN (2, 3)";
    $stmt = $db->query($query);
    $stats['active_jobs'] = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // Total technicians
    $query = "SELECT COUNT(*) as total FROM technicians WHERE is_active = 1";
    $stmt = $db->query($query);
    $stats['total_technicians'] = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // Available technicians (status_id = 1 for 'Available')
    $query = "SELECT COUNT(*) as total FROM technicians WHERE status_id = 1 AND is_active = 1";
    $stmt = $db->query($query);
    $stats['available_technicians'] = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // Queue and performance metrics
    $stats['claimed_tickets'] = (int)$db->query("SELECT COUNT(*) FROM csr_request_locks")->fetchColumn();
    $stats['unclaimed_tickets'] = (int)$db->query("SELECT COUNT(*) FROM service_requests sr LEFT JOIN csr_request_locks crl ON crl.request_id = sr.id WHERE crl.id IS NULL AND sr.job_status_id NOT IN (4,5)")->fetchColumn();
    $stmt = $db->prepare("SELECT COUNT(*) FROM csr_request_locks WHERE csr_id = :csr_id");
    $stmt->execute([':csr_id' => $_SESSION['csr_id']]);
    $stats['my_claimed_tickets'] = (int)$stmt->fetchColumn();
    $stats['completed_today'] = (int)$db->query("SELECT COUNT(*) FROM service_requests WHERE job_status_id = 4 AND DATE(completed_date) = CURDATE()")->fetchColumn();

    // Report metrics with role-based scope.
    require_once '../../includes/csr_report_helpers.php';
    $currentCsrId = (int)$_SESSION['csr_id'];
    $reportStartDate = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
    $reportEndDate = isset($_GET['end_date']) ? trim($_GET['end_date']) : '';
    $requestedTeamId = isset($_GET['team_id']) ? (int)$_GET['team_id'] : 0;
    $requestedCsrId = isset($_GET['csr_id']) ? (int)$_GET['csr_id'] : 0;

    [$reportStartDate, $reportEndDate] = normalizeCsrReportDates($reportStartDate, $reportEndDate);
    $reportContext = getCsrReportAccessContext($db, $currentCsrId, $requestedTeamId, $requestedCsrId);
    $report_stats = calculateCsrReportStatsForScope($db, $reportContext['allowed_csr_ids'], $reportStartDate, $reportEndDate);
    $today_report_stats = calculateCsrReportStatsForScope($db, $reportContext['allowed_csr_ids'], date('Y-m-d'), date('Y-m-d'));
    $today_report_stats['resets_at'] = '12:00 AM';
    $reportContext['selectable_csrs'] = getCsrReportSelectableCsrs($db, $reportContext['allowed_csr_ids']);

    // Request trend (compare with yesterday)
    $query = "SELECT 
                COUNT(CASE WHEN DATE(requested_at) = CURDATE() THEN 1 END) as today,
                COUNT(CASE WHEN DATE(requested_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN 1 END) as yesterday
              FROM service_requests";
    $stmt = $db->query($query);
    $trend = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($trend['yesterday'] > 0) {
        $stats['request_trend'] = round((($trend['today'] - $trend['yesterday']) / $trend['yesterday']) * 100);
    } else {
        $stats['request_trend'] = $trend['today'] > 0 ? 100 : 0;
    }
    
    // Get recent requests (with manual entry handling)
    $query = "SELECT sr.id, sr.request_number, sr.full_name, sr.requested_at,
                js.status_name, js.color_code as status_color,
                CASE 
                    WHEN sr.appliance_type_id IS NULL THEN sr.manual_appliance_type 
                    ELSE at.name 
                END as appliance_type,
                CASE WHEN sr.appliance_type_id IS NULL THEN 1 ELSE 0 END as is_manual_appliance
              FROM service_requests sr
              LEFT JOIN job_statuses js ON sr.job_status_id = js.id
              LEFT JOIN appliance_types at ON sr.appliance_type_id = at.id
              ORDER BY sr.requested_at DESC
              LIMIT 10";
    $stmt = $db->query($query);
    $recent_requests = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get technician status - FIXED: Using status_id foreign key
    $query = "SELECT 
                t.id,
                t.first_name,
                t.last_name,
                CONCAT(t.first_name, ' ', t.last_name) as full_name,
                CONCAT(LEFT(t.first_name, 1), LEFT(t.last_name, 1)) as initials,
                t.specialization,
                ts.status_name,
                ts.color_code as status_color,
                ts.icon_class
              FROM technicians t
              LEFT JOIN technician_statuses ts ON t.status_id = ts.id
              WHERE t.is_active = 1
              ORDER BY 
                CASE 
                    WHEN ts.sort_order IS NOT NULL THEN ts.sort_order
                    WHEN t.status_id = 1 THEN 1  -- Available first
                    WHEN t.status_id = 2 THEN 2  -- On Job second
                    WHEN t.status_id = 3 THEN 3  -- Break third
                    ELSE 4
                END,
                t.first_name
              LIMIT 5";
    $stmt = $db->query($query);
    $technicians = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get urgent requests (urgency_level_id = 3 for Emergency)
    $query = "SELECT sr.id, sr.request_number, sr.full_name,
                CASE 
                    WHEN sr.appliance_type_id IS NULL THEN sr.manual_appliance_type 
                    ELSE at.name 
                END as appliance_type
              FROM service_requests sr
              LEFT JOIN appliance_types at ON sr.appliance_type_id = at.id
              WHERE sr.urgency_level_id = 3 AND sr.job_status_id NOT IN (4, 5) -- Not Completed or Cancelled
              ORDER BY sr.requested_at DESC
              LIMIT 5";
    $stmt = $db->query($query);
    $urgent_requests = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo json_encode([
        'success' => true,
        'stats' => $stats,
        'report_stats' => $report_stats,
        'today_report_stats' => $today_report_stats,
        'report_context' => $reportContext,
        'recent_requests' => $recent_requests,
        'technicians' => $technicians,
        'urgent_requests' => $urgent_requests
    ]);
    
} catch (Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . $e->getMessage()
    ]);
}
?>