<?php
include('include/auth.php');
?>
<!doctype html>
<html class="fixed dark">
<?php
include('include/head.php');

// Sanitize and validate input
$startdate_raw = $_GET['sdateg2'] ?? '';
$startdate = strtotime($startdate_raw);
if (!$startdate) {
    die("Invalid or missing start date.");
}

$sdate = date("Y-m-d H:i:s", $startdate);

// Process vehicle type and cargroup
$vehicleType = isset($_GET['vehicle_type']) ? (int)$_GET['vehicle_type'] : null;
$cargroup_raw = $_GET['cargroup'] ?? '';
$cargroup = is_numeric($cargroup_raw) ? (int)$cargroup_raw : null;
$isAllGroups = ($cargroup_raw === 'all');

// Generate 14-day date range
$dateRanges = [];
for ($i = 0; $i < 14; $i++) {
    $dateRanges[] = strtotime("+$i day", $startdate);
}

// Fetch car groups
if ($isAllGroups) {
    $carGroupsQuery = $db2->prepare("
        SELECT c.id, c.name, COUNT(DISTINCT f.veh_id) AS total_units
        FROM jostj_vikrentcar_cars c
        INNER JOIN fleet f ON c.id = f.veh_id AND f.active = 1
        WHERE c.idcat = ?
        GROUP BY c.id, c.name
        HAVING total_units > 0
    ");
    $carGroupsQuery->bind_param("i", $vehicleType);
} else {
    $carGroupsQuery = $db2->prepare("
        SELECT c.id, c.name, COUNT(DISTINCT f.veh_id) AS total_units
        FROM jostj_vikrentcar_cars c
        INNER JOIN car_availability_groups cag ON c.id = cag.car_id
        LEFT JOIN fleet f ON c.id = f.veh_id AND f.active = 1
        WHERE cag.avail_group = ?
        GROUP BY c.id, c.name
        HAVING total_units > 0
    ");
    $carGroupsQuery->bind_param("i", $cargroup);
}

if (!$carGroupsQuery) {
    die("SQL Error: " . $db2->error);
}

$carGroupsQuery->execute();
$carGroupsResult = $carGroupsQuery->get_result();

$carGroups = [];
while ($row = $carGroupsResult->fetch_assoc()) {
    $carGroups[] = $row;
}
$carGroupsQuery->close();
?>

<style>
    .cell-green {
        background-color: green!important;
        color: white!important;
        font-weight: bold!important;
        text-align: center!important;
    }

    .cell-orange {
        background-color: orange!important;
        color: white!important;
        font-weight: bold!important;
        text-align: center!important;
    }

    .cell-red {
        background-color: red!important;
        color: white!important;
        font-weight: bold!important;
        text-align: center!important;
    }
</style>
<body>
    <section class="body">
        <header class="header">
            <div class="logo-container">
                <a href="index.php" class="logo">
                    <img src="img/logo-light.png" width="75" height="35" alt="Web Voyage" />
                </a>
                <div class="d-md-none toggle-sidebar-left" data-toggle-class="sidebar-left-opened" data-target="html" data-fire-event="sidebar-left-opened">
                    <i class="fas fa-bars" aria-label="Toggle sidebar"></i>
                </div>
            </div>

            <div class="header-right">
                <?php include('include/search.php'); ?>
                <span class="separator"></span>
                <?php include('include/notifications.php'); ?>
                <span class="separator"></span>
                <?php include('include/userbox.php'); ?>
            </div>
        </header>

        <div class="inner-wrapper">
            <?php include('include/nav.php'); ?>

            <section role="main" class="content-body">
                <header class="page-header">
                    <h2>Car Availability: Availability Group <?php echo htmlspecialchars($cargroup); ?></h2>
                    <div class="right-wrapper text-right">
                        <ol class="breadcrumbs">
                            <li><a href="index.php"><i class="fas fa-home"></i></a></li>
                            <li><span>Availability</span></li>
                            <li><span>Car Groups</span></li>
                        </ol>
                        <a class="sidebar-right-toggle" data-open="sidebar-right"><i class="fas fa-chevron-left"></i></a>
                    </div>
                </header>

                <div class="row">
                    <div class="col">
                        <section class="card">
                            <header class="card-header">
                                <h2 class="card-title">Availability From: <?php echo date("d/m/Y", $startdate); ?></h2>
                            </header>

                            <div class="card-body">
                                <table class="table table-bordered table-striped mb-0" id="datatable-tabletools">
    <thead>
        <tr>
            <th>Vehicle</th>
            <th>Total Units</th>
            <?php foreach ($dateRanges as $date) : ?>
                <th><?php echo date("d/m", $date); ?></th>
            <?php endforeach; ?>
        </tr>
    </thead>
    <tbody>
    <?php 
    $totalUnits = 0; 
    $totalAvailability = array_fill(0, count($dateRanges), 0); // Initialize totals array
    ?>
    
    <?php foreach ($carGroups as $group) : ?>
    <?php
    // Fetch active units
    $fleetQuery = $db2->prepare("
        SELECT COUNT(*) AS total_units
        FROM fleet f
        WHERE f.veh_id = ? AND f.active = 1
    ");
    $fleetQuery->bind_param("i", $group['id']);
    $fleetQuery->execute();
    $fleetResult = $fleetQuery->get_result();
    $totalUnitsForGroup = $fleetResult->fetch_assoc()['total_units'] ?? 0;
    $fleetQuery->close();

    $totalUnits += $totalUnitsForGroup;

    // Calculate per-day availability
    $dailyAvailability = [];
    foreach ($dateRanges as $index => $date) {
        $dateString = date("Y-m-d H:i:s", $date);
       $reservedQuery = $db2->prepare("
    SELECT COUNT(*) AS reserved_count
    FROM jostj_vikrentcar_orders
    WHERE idcar = ?
      AND DATE(?) BETWEEN DATE(FROM_UNIXTIME(ritiro)) 
      AND DATE(DATE_ADD(FROM_UNIXTIME(consegna), INTERVAL 2 HOUR))
      AND status IN ('contract', 'confirmed')
");
        $reservedQuery->bind_param("is", $group['id'], $dateString);
        $reservedQuery->execute();
        $reservedResult = $reservedQuery->get_result();
        $reservedCount = $reservedResult->fetch_assoc()['reserved_count'] ?? 0;
        $reservedQuery->close();

   $availableUnits = $totalUnitsForGroup - $reservedCount;
        $dailyAvailability[] = $availableUnits;
        $totalAvailability[$index] += $availableUnits;
    }
    ?>
    <?php if (!$isAllGroups): ?>
    <tr>
        <td><?php echo htmlspecialchars($group['name']); ?></td>
        <td><?php echo $totalUnitsForGroup; ?></td>
        <?php foreach ($dailyAvailability as $availability) : ?>
           <td class="<?php 
    echo ($availability < 0 ? 'cell-red' : 
         ($availability < 1 ? 'cell-orange' : 
         'cell-green')); 
?>">
                <?php echo $availability; ?>
            </td>
        <?php endforeach; ?>
    </tr>
    <?php endif; ?>
<?php endforeach; ?>


    <!-- Total Row -->
    <tr style="font-weight: bold;">
        <td>Total</td>
        <td><?php echo $totalUnits; ?></td>
        <?php foreach ($totalAvailability as $availability) : ?>
            <td class="<?php 
                echo ($availability < 1 ? 'cell-red' : 
                     ($availability < 2 ? 'cell-orange' : 
                     'cell-green')); 
            ?>">
                <?php echo $availability; ?>
            </td>
        <?php endforeach; ?>
    </tr>
</tbody>

</table>
<p class="mt-3" style="font-style: italic; font-size: 0.9em; color: #ccc;">
    Note: Totals reflect all active vehicles of the selected vehicle type, regardless of availability group assignment. 
    This view includes vehicles even if they are not mapped to a specific availability group.
</p>

                            </div>
                        </section>
                    </div>
                </div>
            </section>
        </div>
    </section>

    <!-- Vendor Scripts -->
    <script src="vendor/jquery/jquery.js"></script>
    <script src="vendor/bootstrap/js/bootstrap.js"></script>
    <script src="vendor/datatables/media/js/jquery.dataTables.min.js"></script>
    <script src="vendor/datatables/media/js/dataTables.bootstrap4.min.js"></script>
    <script src="js/theme.js"></script>
    <script src="js/custom.js"></script>
    <script src="js/theme.init.js"></script>
</body>
</html>
