<?php
require 'vendor/autoload.php'; // Load PhpSpreadsheet Library
include("inc/config.php");

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if (!isset($_GET['time_range'])) {
    die("Invalid request!");
}

$time_range = intval($_GET['time_range']); // 15 or 60 minutes
date_default_timezone_set("Asia/Kolkata");

// Get current time and calculate start time
$current_time = date("H:i");
$current_date = date("Y-m-d");
$start_time = date("H:i", strtotime("-$time_range minutes"));
$end_time = $current_time;

// Fetch Project Name & Direction
$projectQuery = "SELECT project_name, destination FROM projects LIMIT 1"; // Modify this as per your project selection logic
$projectResult = $conn->query($projectQuery);
$project = $projectResult->fetch_assoc();

$project_name = $project['project_name'] ?? "Unknown";
$destination = $project['destination'] ?? "Unknown";

// Fetch all categories for horizontal headers
$categoryQuery = "SELECT id, v_name FROM categories";
$categoryResult = $conn->query($categoryQuery);

$categories = [];
while ($catRow = $categoryResult->fetch_assoc()) {
    $categories[$catRow['id']] = $catRow['v_name'];
}

// Fetch click data for the given time range
$query = "SELECT cr.start_time, cr.category_id, SUM(cr.click_count) as total_clicks 
          FROM click_records cr
          WHERE cr.clicked_at = ? 
          AND cr.start_time BETWEEN ? AND ? 
          GROUP BY cr.start_time, cr.category_id
          ORDER BY cr.start_time ASC";

$stmt = $conn->prepare($query);
$stmt->bind_param("sss", $current_date, $start_time, $end_time);
$stmt->execute();
$result = $stmt->get_result();

// Prepare data array
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[$row["start_time"]][$row["category_id"]] = $row["total_clicks"];
}

// Create Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set Report Title
$sheet->setCellValue("A1", "Location: " . $project_name);
$sheet->mergeCells("A1:" . chr(65 + count($categories) + 2) . "1");
$sheet->getStyle("A1")->getFont()->setBold(true)->setSize(14);

$sheet->setCellValue("A2", "Direction: " . $destination);
$sheet->mergeCells("A2:" . chr(65 + count($categories) + 2) . "2");
$sheet->getStyle("A2")->getFont()->setBold(true)->setSize(12);

$sheet->setCellValue("A3", "Time Range: " . $start_time . " - " . $end_time);
$sheet->mergeCells("A3:" . chr(65 + count($categories) + 2) . "3");
$sheet->getStyle("A3")->getFont()->setBold(true)->setSize(12);

// Set column headers
$sheet->setCellValue("A5", "Start Time");
$sheet->setCellValue("B5", "End Time");

$colIndex = 3; // Column starts from C

foreach ($categories as $catName) {
    $columnLetter = chr(64 + $colIndex); // Convert index to column letter
    $sheet->setCellValue($columnLetter . "5", $catName);
    $colIndex++;
}

// Fill Data
$rowIndex = 6;
$totalCounts = array_fill_keys(array_keys($categories), 0); // Initialize total count array

foreach ($data as $time => $categoryData) {
    $endTime = date("H:i", strtotime($time . " +1 minutes")); // Calculate end time for each row
    $sheet->setCellValue("A" . $rowIndex, $time); // Start Time
    $sheet->setCellValue("B" . $rowIndex, $endTime); // End Time

    $colIndex = 3;
    foreach ($categories as $catId => $catName) {
        $columnLetter = chr(64 + $colIndex);
        $clicks = isset($categoryData[$catId]) ? $categoryData[$catId] : 0;
        $sheet->setCellValue($columnLetter . $rowIndex, $clicks);
        $totalCounts[$catId] += $clicks; // Add to total count
        $colIndex++;
    }
    $rowIndex++;
}

// Add Total Count Row
$sheet->setCellValue("A" . $rowIndex, "Total Count:");
$colIndex = 3;
foreach ($categories as $catId => $catName) {
    $columnLetter = chr(64 + $colIndex);
    $sheet->setCellValue($columnLetter . $rowIndex, $totalCounts[$catId]);
    $colIndex++;
}

// Style the Total Count Row
$sheet->getStyle("A" . $rowIndex . ":" . chr(64 + count($categories) + 2) . $rowIndex)->getFont()->setBold(true);

// Auto-fit column widths
foreach (range('A', chr(64 + count($categories) + 2)) as $column) {
    $sheet->getColumnDimension($column)->setAutoSize(true);
}

// Set Headers for Download
$filename = "click_data_" . $time_range . "min.xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save("php://output");
exit;
?>