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

// Category name from URL
$category = isset($_GET['cat']) ? trim($_GET['cat']) : '';
$currentYear = date('Y');

function ensureVoteScoreColumnsForCategoryPage($conn)
{
    $columns = [];
    $result = mysqli_query($conn, "SHOW COLUMNS FROM `vote`");
    if (!$result) {
        return false;
    }

    while ($row = mysqli_fetch_assoc($result)) {
        $columns[$row['Field']] = true;
    }

    $neededColumns = [
        'fit_finish' => "TINYINT UNSIGNED DEFAULT NULL",
        'paint' => "TINYINT UNSIGNED DEFAULT NULL",
        'interior' => "TINYINT UNSIGNED DEFAULT NULL",
        'overall_look' => "TINYINT UNSIGNED DEFAULT NULL",
        'stance' => "TINYINT UNSIGNED DEFAULT NULL",
        'engine_bay' => "TINYINT UNSIGNED DEFAULT NULL",
        'wheels_tires' => "TINYINT UNSIGNED DEFAULT NULL",
        'cleanliness_detail' => "TINYINT UNSIGNED DEFAULT NULL",
        'total_score' => "SMALLINT UNSIGNED DEFAULT NULL",
        'judge_notes' => "TEXT NULL",
        'created_at' => "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
    ];

    foreach ($neededColumns as $column => $definition) {
        if (!isset($columns[$column])) {
            $sql = "ALTER TABLE `vote` ADD COLUMN `$column` $definition";
            if (!mysqli_query($conn, $sql)) {
                return false;
            }
        }
    }

    return true;
}

function ordinalPlace($place)
{
    $suffix = 'th';
    if (!in_array($place % 100, [11, 12, 13])) {
        switch ($place % 10) {
            case 1:
                $suffix = 'st';
                break;
            case 2:
                $suffix = 'nd';
                break;
            case 3:
                $suffix = 'rd';
                break;
        }
    }

    return $place . $suffix;
}
?>

<style>
    label{
        font-size: 16px !important;
    }
    p{
        font-size: 18px !important;
    }
    .flex-page-header{
        display: flex;
        justify-content: space-between;
        align-items: center;
    }
</style>

<!-- Main container -->
<div id="page-wrapper">
    <div class="row">
        <div class="col-lg-12">
            <div class="page-header flex-page-header">
                <h1>Vehicles Of <?php echo htmlspecialchars($category); ?></h1>
                <a href="categories.php" class="btn btn-primary">Back</a>
            </div>
        </div>
    </div>
    <?php include BASE_PATH . '/includes/flash_messages.php';?>

    <!-- Table -->
    <table class="table table-striped table-bordered table-condensed">
        <thead>
            <tr>
                <th width="13%">Name</th>
                <th width="8%">Place</th>
                <th width="13%">Model</th>
                <th width="13%">Score Cards</th>
                <th width="13%">Average Score</th>
                <th width="13%">Total Score</th>
                <th width="13%">Category</th>
            </tr>
        </thead>
        <tbody>
        <?php
            if ($category === '') {
                ?>
                <tr>
                    <td colspan="7" align="center">No Category Selected</td>
                </tr>
                <?php
            } elseif (!ensureVoteScoreColumnsForCategoryPage($conn)) {
                ?>
                <tr>
                    <td colspan="7" align="center">Scoring fields could not be prepared.</td>
                </tr>
                <?php
            } else {
                // ONLY show current-year vehicles that are active (status = 1)
                $sql = "SELECT
                            vehicles.name,
                            vehicles.id,
                            vehicles.vehicle_maker,
                            vehicles.vehicle_model,
                            vehicles.vehicle_year,
                            vehicles.category,
                            COUNT(vote.id) AS score_count,
                            COALESCE(SUM(vote.total_score), 0) AS total_score,
                            COALESCE(ROUND(AVG(vote.total_score), 1), 0) AS average_score
                        FROM vehicles
                        LEFT JOIN vote ON vote.vehicle_id = vehicles.id
                        WHERE vehicles.category = ?
                          AND YEAR(vehicles.created_date) = ?
                          AND vehicles.status = 1
                        GROUP BY vehicles.id, vehicles.name, vehicles.vehicle_maker, vehicles.vehicle_model, vehicles.vehicle_year, vehicles.category
                        ORDER BY total_score DESC, average_score DESC, score_count DESC, vehicles.id ASC";

                if ($stmt = mysqli_prepare($conn, $sql)) {
                    mysqli_stmt_bind_param($stmt, "si", $category, $currentYear);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);

                    if ($result && mysqli_num_rows($result) > 0) {
                        $place = 1;
                        while ($row = mysqli_fetch_assoc($result)) {
                            ?>
                            <tr>
                                <td><a href="../view.php?id=<?php echo urlencode($row['id']); ?>" target="_blank"><?php echo htmlspecialchars($row['name']); ?></a></td>
                                <td><?php echo ordinalPlace($place); ?></td>
                                <td><a href="../view.php?id=<?php echo urlencode($row['id']); ?>" target="_blank"><?php echo htmlspecialchars(trim($row['vehicle_year'] . ' ' . $row['vehicle_maker'] . ' ' . $row['vehicle_model'])); ?></a></td>
                                <td><?php echo (int)$row['score_count']; ?></td>
                                <td><?php echo htmlspecialchars($row['average_score']); ?> / 80</td>
                                <td><?php echo (int)$row['total_score']; ?></td>
                                <td><?php echo htmlspecialchars($row['category']); ?></td>
                            </tr>
                            <?php
                            $place++;
                        }
                    } else {
                        ?>
                        <tr>
                            <td colspan="7" align="center">No Related Data Found</td>
                        </tr>
                        <?php
                    }

                    mysqli_stmt_close($stmt);
                } else {
                    ?>
                    <tr>
                        <td colspan="7" align="center">Query error.</td>
                    </tr>
                    <?php
                }
            }
        ?>
        </tbody>
    </table>
    <!-- //Table -->
</div>
<!-- //Main container -->

<?php include BASE_PATH . '/includes/footer.php';?>