<?php
session_start();
require_once 'config/config.php';
require_once BASE_PATH . '/includes/auth_validate.php';
include_once("../db.php");

function ensureCategoryStatsTable($conn)
{
    $sql = "CREATE TABLE IF NOT EXISTS category_stats (
        id INT(11) NOT NULL AUTO_INCREMENT,
        year INT(4) NOT NULL,
        category VARCHAR(255) NOT NULL,
        num_of_vehicle INT(11) NOT NULL DEFAULT 0,
        archived_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY year_category (year, category)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci";

    return mysqli_query($conn, $sql);
}

$currentYear = (int) date('Y');
$archiveCounts = isset($_POST['archive_counts']);

ensureCategoryStatsTable($conn);

$vehicleIds = [];
$idSql = "SELECT id FROM vehicles WHERE YEAR(created_date) = ? AND status = 1";
if ($idStmt = mysqli_prepare($conn, $idSql)) {
    mysqli_stmt_bind_param($idStmt, "i", $currentYear);
    mysqli_stmt_execute($idStmt);
    $idResult = mysqli_stmt_get_result($idStmt);
    while ($row = mysqli_fetch_assoc($idResult)) {
        $vehicleIds[] = (int) $row['id'];
    }
}

if ($archiveCounts) {
    $statsSql = "SELECT category, COUNT(*) AS num_of_vehicle
                 FROM vehicles
                 WHERE YEAR(created_date) = ? AND status = 1
                 GROUP BY category";

    if ($statsStmt = mysqli_prepare($conn, $statsSql)) {
        mysqli_stmt_bind_param($statsStmt, "i", $currentYear);
        mysqli_stmt_execute($statsStmt);
        $statsResult = mysqli_stmt_get_result($statsStmt);

        while ($row = mysqli_fetch_assoc($statsResult)) {
            $category = $row['category'] ?: 'Uncategorized';
            $count = (int) $row['num_of_vehicle'];
            $upsertStmt = mysqli_prepare(
                $conn,
                "INSERT INTO category_stats (year, category, num_of_vehicle)
                 VALUES (?, ?, ?)
                 ON DUPLICATE KEY UPDATE num_of_vehicle = VALUES(num_of_vehicle), archived_at = CURRENT_TIMESTAMP"
            );
            mysqli_stmt_bind_param($upsertStmt, "isi", $currentYear, $category, $count);
            mysqli_stmt_execute($upsertStmt);
        }
    }
}

if (count($vehicleIds) > 0) {
    $idList = implode(',', $vehicleIds);
    mysqli_query($conn, "DELETE FROM vote WHERE vehicle_id IN ($idList)");
    mysqli_query($conn, "DELETE FROM password_used WHERE vehicle_id IN ($idList)");
    mysqli_query($conn, "DELETE FROM vehicles WHERE id IN ($idList)");
}

$_SESSION['success'] = count($vehicleIds) . " current vehicle(s) cleared." . ($archiveCounts ? " Category counts were archived." : "");
header("Location: customers.php");
exit;