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

// Sanitize and validate input
$startdate = filter_input(INPUT_GET, 'sdateg2', FILTER_SANITIZE_STRING);
$vehicle_type = filter_input(INPUT_GET, 'vehicle_type', FILTER_SANITIZE_NUMBER_INT);

// Validate start date
if ($startdate) {
    $startdate = strtotime($startdate);
} else {
    die("Invalid or missing start date.");
}

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

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

// Fetch availability groups based on vehicle type and total available units
$query = "
    SELECT ag.id, ag.name, COUNT(f.veh_id) AS total_units
    FROM availability_groups ag
    LEFT JOIN car_availability_groups cag ON ag.id = cag.avail_group
    LEFT JOIN fleet f ON cag.car_id = f.veh_id AND f.active = 1
    WHERE ag.vehicle_type = ?
    GROUP BY ag.id, ag.name
    HAVING total_units > 0
";

$groupAvailQuery = $db2->prepare($query);

if ($groupAvailQuery === false) {
    die("SQL Error: " . $db2->error); // Debugging: Shows SQL error if query fails
}

$groupAvailQuery->bind_param("i", $vehicle_type);
$groupAvailQuery->execute();
$groupAvailResult = $groupAvailQuery->get_result();

if ($groupAvailResult->num_rows == 0) {
    die("No results found for Vehicle Type: " . htmlspecialchars($vehicle_type));
}

$groupAvailabilities = [];
while ($row = $groupAvailResult->fetch_assoc()) {
    $groupAvailabilities[] = $row;
}
$groupAvailQuery->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>Group Availability for <?php echo ($vehicle_type == 1) ? 'Cars' : 'Bikes'; ?></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>Group Summary</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>Availability Group</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 ($groupAvailabilities as $group) : ?>
        <?php
        // Accumulate total units
        $totalUnits += $group['total_units'];

        // Fetch total reserved units per date
        $dailyAvailability = [];
        foreach ($dateRanges as $index => $date) {
            $dateString = date("Y-m-d H:i:s", $date);

            // Fetch reserved units per date
      $reservedQuery = $db2->prepare("
    SELECT COUNT(*) AS reserved_count
    FROM jostj_vikrentcar_orders o
    INNER JOIN car_availability_groups cag ON o.idcar = cag.car_id
    WHERE cag.avail_group = ?
    AND DATE(?) BETWEEN DATE(FROM_UNIXTIME(o.ritiro)) 
    AND DATE(DATE_ADD(FROM_UNIXTIME(o.consegna), INTERVAL 2 HOUR))
    AND o.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();

            // Calculate available units and accumulate in totals array
            $availableUnits = max(0, $group['total_units'] - $reservedCount);
            $dailyAvailability[] = $availableUnits;
            $totalAvailability[$index] += $availableUnits;
        }
        ?>
        <tr>
            <td>
                <?php echo htmlspecialchars($group['name']); ?>
                <a href="livegroupavail.php?sdateg2=<?php echo urlencode(date('Y-m-d', $startdate)); ?>&cargroup=<?php echo urlencode($group['id']); ?>"
                   target="_blank" class="on-default edit-row">
                    <i class="fa fa-eye"></i>
                </a>
            </td>
            <td><?php echo $group['total_units']; ?></td>
            <?php foreach ($dailyAvailability as $availability) : ?>
                <td class="<?php 
                    echo ($availability < 1 ? 'cell-red' : 
                         ($availability < 2 ? 'cell-orange' : 
                         'cell-green')); 
                ?>">
                    <?php echo $availability; ?>
                </td>
            <?php endforeach; ?>
        </tr>
    <?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 include only active vehicles assigned to availability groups. 
    Any vehicles not linked to an availability group are excluded.
</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>
