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

if (!isset($_SESSION['csr_id'])) {
    http_response_code(401);
    echo 'Unauthorized';
    exit();
}

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

$currentCsrId = (int)$_SESSION['csr_id'];
$format = isset($_GET['format']) ? strtolower(trim($_GET['format'])) : 'csv';
$startDate = isset($_GET['start_date']) ? trim($_GET['start_date']) : '';
$endDate = 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;
$reportAccess = getCsrReportAccessContext($db, $currentCsrId, $requestedTeamId, $requestedCsrId);
if (!$reportAccess['can_download']) {
    http_response_code(403);
    echo 'Download access is limited to Team Leaders and CSR Manager.';
    exit();
}

if ($startDate !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) {
    $startDate = '';
}

if ($endDate !== '' && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $endDate)) {
    $endDate = '';
}

if ($startDate !== '' && $endDate !== '' && strtotime($startDate) > strtotime($endDate)) {
    $tmp = $startDate;
    $startDate = $endDate;
    $endDate = $tmp;
}

function getCsrNameForExport($db, $csrId) {
    $stmt = $db->prepare("SELECT CONCAT(first_name, ' ', last_name) AS csr_name, email FROM csr_users WHERE id = :csr_id");
    $stmt->execute([':csr_id' => $csrId]);
    $csr = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$csr) {
        return ['name' => 'CSR', 'email' => ''];
    }
    return ['name' => $csr['csr_name'], 'email' => $csr['email']];
}

function calculateCsrReportStatsForExport($db, $currentCsrId, $startDate = '', $endDate = '') {
    $buildReportDateCondition = function ($column, $prefix, &$params) use ($startDate, $endDate) {
        $condition = '';

        if ($startDate !== '') {
            $condition .= " AND DATE($column) >= :{$prefix}_start_date";
            $params[":{$prefix}_start_date"] = $startDate;
        }

        if ($endDate !== '') {
            $condition .= " AND DATE($column) <= :{$prefix}_end_date";
            $params[":{$prefix}_end_date"] = $endDate;
        }

        return $condition;
    };

    $stats = [
        'total_tickets_handled' => 0,
        'total_technician_assigned' => 0,
        'total_in_progress' => 0,
        'total_completed' => 0,
        'total_canceled' => 0,
        'filter_start_date' => $startDate,
        'filter_end_date' => $endDate
    ];

    $params = [
        ':csr_id_locks' => $currentCsrId,
        ':csr_id_assignments' => $currentCsrId,
        ':csr_id_activity' => $currentCsrId,
        ':csr_id_logs' => $currentCsrId
    ];
    $locksDateCondition = $buildReportDateCondition('locked_at', 'handled_locks', $params);
    $assignmentsDateCondition = $buildReportDateCondition('assigned_at', 'handled_assignments', $params);
    $activityDateCondition = $buildReportDateCondition('created_at', 'handled_activity', $params);
    $logsDateCondition = $buildReportDateCondition('created_at', 'handled_logs', $params);

    $stmt = $db->prepare("SELECT COUNT(DISTINCT handled.request_id) FROM (
            SELECT request_id FROM csr_request_locks WHERE csr_id = :csr_id_locks {$locksDateCondition}
            UNION
            SELECT request_id FROM service_assignments WHERE assigned_by = :csr_id_assignments {$assignmentsDateCondition}
            UNION
            SELECT request_id FROM csr_activity WHERE csr_id = :csr_id_activity AND request_id IS NOT NULL {$activityDateCondition}
            UNION
            SELECT request_id FROM request_logs WHERE user_role = 'csr' AND user_id = :csr_id_logs AND request_id IS NOT NULL {$logsDateCondition}
        ) handled");
    $stmt->execute($params);
    $stats['total_tickets_handled'] = (int)$stmt->fetchColumn();

    $params = [':csr_id' => $currentCsrId];
    $assignedDateCondition = $buildReportDateCondition('assigned_at', 'tech_assigned', $params);
    $stmt = $db->prepare("SELECT COUNT(DISTINCT request_id) FROM service_assignments WHERE assigned_by = :csr_id {$assignedDateCondition}");
    $stmt->execute($params);
    $stats['total_technician_assigned'] = (int)$stmt->fetchColumn();

    $params = [
        ':csr_id_locks' => $currentCsrId,
        ':csr_id_assignments' => $currentCsrId,
        ':csr_id_activity' => $currentCsrId,
        ':csr_id_logs' => $currentCsrId
    ];
    $locksDateCondition = $buildReportDateCondition('locked_at', 'inprogress_locks', $params);
    $assignmentsDateCondition = $buildReportDateCondition('assigned_at', 'inprogress_assignments', $params);
    $activityDateCondition = $buildReportDateCondition('created_at', 'inprogress_activity', $params);
    $logsDateCondition = $buildReportDateCondition('created_at', 'inprogress_logs', $params);

    $stmt = $db->prepare("SELECT COUNT(DISTINCT sr.id)
        FROM service_requests sr
        WHERE sr.job_status_id = 3
        AND sr.id IN (
            SELECT request_id FROM csr_request_locks WHERE csr_id = :csr_id_locks {$locksDateCondition}
            UNION
            SELECT request_id FROM service_assignments WHERE assigned_by = :csr_id_assignments {$assignmentsDateCondition}
            UNION
            SELECT request_id FROM csr_activity WHERE csr_id = :csr_id_activity AND request_id IS NOT NULL {$activityDateCondition}
            UNION
            SELECT request_id FROM request_logs WHERE user_role = 'csr' AND user_id = :csr_id_logs AND request_id IS NOT NULL {$logsDateCondition}
        )");
    $stmt->execute($params);
    $stats['total_in_progress'] = (int)$stmt->fetchColumn();

    $params = [
        ':csr_id_logs' => $currentCsrId,
        ':csr_id_activity' => $currentCsrId,
        ':csr_id_notes' => $currentCsrId
    ];
    $logsDateCondition = $buildReportDateCondition('created_at', 'completed_logs', $params);
    $activityDateCondition = $buildReportDateCondition('created_at', 'completed_activity', $params);
    $notesDateCondition = $buildReportDateCondition('created_at', 'completed_notes', $params);

    $stmt = $db->prepare("SELECT COUNT(DISTINCT completed.request_id) FROM (
            SELECT request_id FROM request_logs
            WHERE user_role = 'csr' AND user_id = :csr_id_logs AND action = 'status_change' AND CAST(new_value AS CHAR) = '4' {$logsDateCondition}
            UNION
            SELECT request_id FROM csr_activity
            WHERE csr_id = :csr_id_activity AND action = 'status_change' AND details LIKE '%to 4%' {$activityDateCondition}
            UNION
            SELECT request_id FROM request_notes
            WHERE csr_id = :csr_id_notes AND note_content LIKE '%Status updated to:%Completed%' {$notesDateCondition}
        ) completed");
    $stmt->execute($params);
    $stats['total_completed'] = (int)$stmt->fetchColumn();

    $params = [
        ':csr_id_logs' => $currentCsrId,
        ':csr_id_activity' => $currentCsrId,
        ':csr_id_notes' => $currentCsrId
    ];
    $logsDateCondition = $buildReportDateCondition('created_at', 'canceled_logs', $params);
    $activityDateCondition = $buildReportDateCondition('created_at', 'canceled_activity', $params);
    $notesDateCondition = $buildReportDateCondition('created_at', 'canceled_notes', $params);

    $stmt = $db->prepare("SELECT COUNT(DISTINCT canceled.request_id) FROM (
            SELECT request_id FROM request_logs
            WHERE user_role = 'csr' AND user_id = :csr_id_logs AND action = 'status_change' AND CAST(new_value AS CHAR) = '5' {$logsDateCondition}
            UNION
            SELECT request_id FROM csr_activity
            WHERE csr_id = :csr_id_activity AND action = 'status_change' AND details LIKE '%to 5%' {$activityDateCondition}
            UNION
            SELECT request_id FROM request_notes
            WHERE csr_id = :csr_id_notes AND note_content LIKE '%Status updated to:%Cancel%' {$notesDateCondition}
        ) canceled");
    $stmt->execute($params);
    $stats['total_canceled'] = (int)$stmt->fetchColumn();

    return $stats;
}

function getRangeLabelForExport($startDate, $endDate) {
    if ($startDate !== '' && $endDate !== '') {
        return $startDate . ' to ' . $endDate;
    }
    if ($startDate !== '') {
        return 'From ' . $startDate;
    }
    if ($endDate !== '') {
        return 'Until ' . $endDate;
    }
    return 'All available records';
}

function pdfEscapeText($text) {
    return str_replace(['\\', '(', ')'], ['\\\\', '\\(', '\\)'], (string)$text);
}

function outputSimplePdfReport($csrInfo, $rangeLabel, $selectedStats, $todayStats) {
    $lines = [
        'CSR Report',
        'Generated: ' . date('Y-m-d h:i A'),
        'CSR: ' . $csrInfo['name'] . ($csrInfo['email'] ? ' <' . $csrInfo['email'] . '>' : ''),
        '',
        'Filtered / Selected Date Range: ' . $rangeLabel,
        'Total tickets handled: ' . $selectedStats['total_tickets_handled'],
        'Total technician assigned: ' . $selectedStats['total_technician_assigned'],
        'Total in progress status: ' . $selectedStats['total_in_progress'],
        'Total completed: ' . $selectedStats['total_completed'],
        'Total canceled: ' . $selectedStats['total_canceled'],
        '',
        'Today Only - resets every 12 AM (' . date('Y-m-d') . ')',
        'Today tickets handled: ' . $todayStats['total_tickets_handled'],
        'Today technician assigned: ' . $todayStats['total_technician_assigned'],
        'Today in progress status: ' . $todayStats['total_in_progress'],
        'Today completed: ' . $todayStats['total_completed'],
        'Today canceled: ' . $todayStats['total_canceled']
    ];

    $content = "BT\n/F1 20 Tf\n50 790 Td\n";
    $first = true;
    foreach ($lines as $index => $line) {
        $fontSize = ($index === 0) ? 20 : 11;
        if ($index === 0) {
            $content .= "(" . pdfEscapeText($line) . ") Tj\n";
            $content .= "/F1 11 Tf\n0 -28 Td\n";
            continue;
        }
        $safeLine = pdfEscapeText($line);
        $content .= "({$safeLine}) Tj\n0 -18 Td\n";
    }
    $content .= "ET";

    $objects = [];
    $objects[] = "<< /Type /Catalog /Pages 2 0 R >>";
    $objects[] = "<< /Type /Pages /Kids [3 0 R] /Count 1 >>";
    $objects[] = "<< /Type /Page /Parent 2 0 R /MediaBox [0 0 612 842] /Resources << /Font << /F1 4 0 R >> >> /Contents 5 0 R >>";
    $objects[] = "<< /Type /Font /Subtype /Type1 /BaseFont /Helvetica >>";
    $objects[] = "<< /Length " . strlen($content) . " >>\nstream\n" . $content . "\nendstream";

    $pdf = "%PDF-1.4\n";
    $offsets = [0];
    foreach ($objects as $i => $obj) {
        $offsets[$i + 1] = strlen($pdf);
        $pdf .= ($i + 1) . " 0 obj\n" . $obj . "\nendobj\n";
    }
    $xrefOffset = strlen($pdf);
    $pdf .= "xref\n0 " . (count($objects) + 1) . "\n";
    $pdf .= "0000000000 65535 f \n";
    for ($i = 1; $i <= count($objects); $i++) {
        $pdf .= sprintf("%010d 00000 n \n", $offsets[$i]);
    }
    $pdf .= "trailer\n<< /Size " . (count($objects) + 1) . " /Root 1 0 R >>\nstartxref\n" . $xrefOffset . "\n%%EOF";

    $filename = 'csr_report_' . date('Ymd_His') . '.pdf';
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Content-Length: ' . strlen($pdf));
    echo $pdf;
    exit();
}

function htmlEscapeForExcel($value) {
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

function outputExcelTableReport($csrInfo, $rangeLabel, $selectedStats, $todayStats) {
    $filename = 'csr_report_table_' . date('Ymd_His') . '.xls';

    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    $generated = date('Y-m-d h:i A');
    $today = date('Y-m-d');

    echo "\xEF\xBB\xBF";
    ?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <style>
        body {
            font-family: Arial, sans-serif;
            color: #1f2937;
        }
        .title {
            font-size: 20px;
            font-weight: bold;
            background: #0f3d5f;
            color: #ffffff;
        }
        .meta-label {
            font-weight: bold;
            background: #d9eaf7;
        }
        .section {
            font-size: 14px;
            font-weight: bold;
            background: #145374;
            color: #ffffff;
        }
        .header {
            font-weight: bold;
            background: #b7d7f0;
            border: 1px solid #6b7280;
        }
        .cell {
            border: 1px solid #6b7280;
        }
        .number {
            border: 1px solid #6b7280;
            text-align: center;
            font-weight: bold;
        }
        .note {
            color: #6b7280;
            font-style: italic;
        }
    </style>
    <link rel="stylesheet" href="../assets/ushs-global-theme.css">
</head>
<body>
<table border="0" cellpadding="6" cellspacing="0">
    <tr><td colspan="3" class="title">CSR Report</td></tr>
    <tr><td class="meta-label">Generated</td><td colspan="2"><?php echo htmlEscapeForExcel($generated); ?></td></tr>
    <tr><td class="meta-label">CSR</td><td colspan="2"><?php echo htmlEscapeForExcel($csrInfo['name']); ?></td></tr>
    <tr><td class="meta-label">Email</td><td colspan="2"><?php echo htmlEscapeForExcel($csrInfo['email']); ?></td></tr>
    <tr><td colspan="3"></td></tr>

    <tr><td colspan="3" class="section">Filtered / Selected Date Range</td></tr>
    <tr><td class="meta-label">Date Range</td><td colspan="2"><?php echo htmlEscapeForExcel($rangeLabel); ?></td></tr>
    <tr><td class="header">Metric</td><td class="header">Total</td><td class="header">Description</td></tr>
    <tr><td class="cell">Total tickets handled</td><td class="number"><?php echo (int)$selectedStats['total_tickets_handled']; ?></td><td class="cell">Tickets you handled in the selected date range</td></tr>
    <tr><td class="cell">Total technician assigned</td><td class="number"><?php echo (int)$selectedStats['total_technician_assigned']; ?></td><td class="cell">Technician assignments made by you</td></tr>
    <tr><td class="cell">Total in progress status</td><td class="number"><?php echo (int)$selectedStats['total_in_progress']; ?></td><td class="cell">Your handled tickets currently in progress</td></tr>
    <tr><td class="cell">Total completed</td><td class="number"><?php echo (int)$selectedStats['total_completed']; ?></td><td class="cell">Tickets completed by you</td></tr>
    <tr><td class="cell">Total canceled</td><td class="number"><?php echo (int)$selectedStats['total_canceled']; ?></td><td class="cell">Tickets canceled by you</td></tr>
    <tr><td colspan="3"></td></tr>

    <tr><td colspan="3" class="section">Today Only - resets every 12 AM</td></tr>
    <tr><td class="meta-label">Today Date</td><td colspan="2"><?php echo htmlEscapeForExcel($today); ?></td></tr>
    <tr><td class="header">Metric</td><td class="header">Total</td><td class="header">Description</td></tr>
    <tr><td class="cell">Today tickets handled</td><td class="number"><?php echo (int)$todayStats['total_tickets_handled']; ?></td><td class="cell">Tickets you handled today only</td></tr>
    <tr><td class="cell">Today technician assigned</td><td class="number"><?php echo (int)$todayStats['total_technician_assigned']; ?></td><td class="cell">Technician assignments you made today</td></tr>
    <tr><td class="cell">Today in progress status</td><td class="number"><?php echo (int)$todayStats['total_in_progress']; ?></td><td class="cell">Handled today and currently in progress</td></tr>
    <tr><td class="cell">Today completed</td><td class="number"><?php echo (int)$todayStats['total_completed']; ?></td><td class="cell">Tickets completed by you today</td></tr>
    <tr><td class="cell">Today canceled</td><td class="number"><?php echo (int)$todayStats['total_canceled']; ?></td><td class="cell">Tickets canceled by you today</td></tr>
    <tr><td colspan="3" class="note">This Excel file is generated using the current role-based CSR report scope.</td></tr>
</table>
</body>
</html>
    <?php
    exit();
}

try {
    $csrInfo = getCsrNameForExport($db, $currentCsrId);
    $csrInfo['name'] = $reportAccess['role_label'] . ' - ' . getCsrReportScopeLabel($reportAccess);
    $selectedStats = calculateCsrReportStatsForScope($db, $reportAccess['allowed_csr_ids'], $startDate, $endDate);
    $todayStats = calculateCsrReportStatsForScope($db, $reportAccess['allowed_csr_ids'], date('Y-m-d'), date('Y-m-d'));
    $rangeLabel = getRangeLabelForExport($startDate, $endDate);

    if ($format === 'pdf') {
        outputSimplePdfReport($csrInfo, $rangeLabel, $selectedStats, $todayStats);
    }

    if ($format === 'xls' || $format === 'excel') {
        outputExcelTableReport($csrInfo, $rangeLabel, $selectedStats, $todayStats);
    }

    $filename = 'csr_report_' . date('Ymd_His') . '.csv';
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    $output = fopen('php://output', 'w');
    fputcsv($output, ['CSR Report']);
    fputcsv($output, ['Generated', date('Y-m-d h:i A')]);
    fputcsv($output, ['CSR', $csrInfo['name']]);
    fputcsv($output, ['Email', $csrInfo['email']]);
    fputcsv($output, []);

    fputcsv($output, ['Filtered / Selected Date Range', $rangeLabel]);
    fputcsv($output, ['Metric', 'Total']);
    fputcsv($output, ['Total tickets handled', $selectedStats['total_tickets_handled']]);
    fputcsv($output, ['Total technician assigned', $selectedStats['total_technician_assigned']]);
    fputcsv($output, ['Total in progress status', $selectedStats['total_in_progress']]);
    fputcsv($output, ['Total completed', $selectedStats['total_completed']]);
    fputcsv($output, ['Total canceled', $selectedStats['total_canceled']]);
    fputcsv($output, []);

    fputcsv($output, ['Today Only - resets every 12 AM', date('Y-m-d')]);
    fputcsv($output, ['Metric', 'Total']);
    fputcsv($output, ['Today tickets handled', $todayStats['total_tickets_handled']]);
    fputcsv($output, ['Today technician assigned', $todayStats['total_technician_assigned']]);
    fputcsv($output, ['Today in progress status', $todayStats['total_in_progress']]);
    fputcsv($output, ['Today completed', $todayStats['total_completed']]);
    fputcsv($output, ['Today canceled', $todayStats['total_canceled']]);
    fclose($output);
    exit();
} catch (Exception $e) {
    http_response_code(500);
    echo 'Export error: ' . $e->getMessage();
    exit();
}
?>
