<?php
// Database queries for counts

// Fleet Health
$activeVehicles = mysqli_fetch_assoc(mysqli_query($db2, "SELECT COUNT(*) as count FROM fleet WHERE active = 1"))['count'];
$inactiveVehicles = mysqli_fetch_assoc(mysqli_query($db2, "SELECT COUNT(*) as count FROM fleet WHERE active = 0"))['count'];
$soldVehicles = mysqli_fetch_assoc(mysqli_query($db2, "SELECT COUNT(*) as count FROM fleet WHERE fleet_sold = 1"))['count'];
$soldButActive = mysqli_fetch_assoc(mysqli_query($db2, "SELECT COUNT(*) as count FROM fleet WHERE active = 1 AND fleet_sold = 1"))['count'];

// Missing Information
$missingLicense = mysqli_fetch_assoc(mysqli_query($db2, "SELECT COUNT(*) as count FROM fleet WHERE active = 1 AND (lic_num IS NULL OR lic_num = '')"))['count'];
$missingCharacteristics = mysqli_fetch_assoc(mysqli_query($db2, "
    SELECT COUNT(*) as count FROM jostj_vikrentcar_cars 
    WHERE (idcarat IS NULL OR idcarat = '' OR idcarat = 0)
    AND id IN (SELECT veh_id FROM fleet WHERE active = 1)
"))['count'];
$missingImage = mysqli_fetch_assoc(mysqli_query($db2, "
    SELECT COUNT(*) as count FROM jostj_vikrentcar_cars 
    WHERE (img IS NULL OR img = '')
    AND id IN (SELECT veh_id FROM fleet WHERE active = 1)
"))['count'];
$missingCategory = mysqli_fetch_assoc(mysqli_query($db2, "
    SELECT COUNT(*) as count FROM jostj_vikrentcar_cars 
    WHERE (idcat IS NULL OR idcat = '')
    AND id IN (SELECT veh_id FROM fleet WHERE active = 1)
"))['count'];

// Compliance Checks
$missingInsurance = mysqli_fetch_assoc(mysqli_query($db2, "
    SELECT COUNT(*) as count FROM fleet 
    WHERE active = 1 
    AND (fleet_insurance_from IS NULL OR fleet_insurance_from = '' OR fleet_insurance_until IS NULL OR fleet_insurance_until = '')
"))['count'];
$missingRoadTax = mysqli_fetch_assoc(mysqli_query($db2, "
    SELECT COUNT(*) as count FROM fleet 
    WHERE active = 1 AND (road_tax IS NULL OR road_tax = '')
"))['count'];
$missingKteo = mysqli_fetch_assoc(mysqli_query($db2, "
    SELECT COUNT(*) as count FROM fleet 
    WHERE active = 1 AND (fleet_kteo IS NULL OR fleet_kteo = '')
"))['count'];

// Compliance Checks Tables Data (Fixed for Timestamp Comparison)
$expiredInsurance = mysqli_query($db2, "
    SELECT f.fleet_id, f.lic_num, c.name as vehicle_name, f.fleet_insurance_until 
    FROM fleet f 
    LEFT JOIN jostj_vikrentcar_cars c ON c.id = f.veh_id
    WHERE f.active = 1 AND f.fleet_insurance_until < $today
");
$expiringInsurance = mysqli_query($db2, "
    SELECT f.fleet_id, f.lic_num, c.name as vehicle_name, f.fleet_insurance_until 
    FROM fleet f 
    LEFT JOIN jostj_vikrentcar_cars c ON c.id = f.veh_id
    WHERE f.active = 1 
    AND f.fleet_insurance_until BETWEEN $today AND ($today + (30 * 86400))
");
$expiredRoadTax = mysqli_query($db2, "
    SELECT f.fleet_id, f.lic_num, c.name as vehicle_name, f.road_tax 
    FROM fleet f 
    LEFT JOIN jostj_vikrentcar_cars c ON c.id = f.veh_id
    WHERE f.active = 1 AND f.road_tax < $today
");
$expiredKteo = mysqli_query($db2, "
    SELECT f.fleet_id, f.lic_num, c.name as vehicle_name, f.fleet_kteo 
    FROM fleet f 
    LEFT JOIN jostj_vikrentcar_cars c ON c.id = f.veh_id
    WHERE f.active = 1 AND f.fleet_kteo < $today
");
$expiringKteo = mysqli_query($db2, "
    SELECT f.fleet_id, f.lic_num, c.name as vehicle_name, f.fleet_kteo 
    FROM fleet f 
    LEFT JOIN jostj_vikrentcar_cars c ON c.id = f.veh_id
    WHERE f.active = 1 
    AND f.fleet_kteo BETWEEN $today AND ($today + (3 * 86400))
");
?>

<!-- Fleet Health Section -->
<h3>Fleet Health</h3>
<div class="row">
    <?php
    $fleetData = [
        ["Active Vehicles", $activeVehicles, "fas fa-car", "bg-primary", ["status" => "1"]],
        ["Inactive Vehicles", $inactiveVehicles, "fas fa-car-crash", "bg-danger text-white", ["status" => "0"]],
        ["Sold Vehicles", $soldVehicles, "fas fa-handshake", "bg-success", ["sold" => "1"]],
        ["Sold but Active", $soldButActive, "fas fa-exclamation-circle", "bg-warning", ["status" => "1", "sold" => "1"]]
    ];

    foreach ($fleetData as $data) {
        list($title, $count, $icon, $bgColor, $filter) = $data;
        $url = "fleet-details.php?" . http_build_query(array_merge($_GET, $filter));
        echo "
        <div class='col-md-3'>
            <div class='card-body $bgColor'>
                <div class='widget-summary'>
                    <div class='widget-summary-col widget-summary-col-icon'>
                        <div class='summary-icon'><i class='$icon'></i></div>
                    </div>
                    <div class='widget-summary-col'>
                        <div class='summary'>
                            <h4 class='title'>$title</h4>
                            <div class='info'><strong class='amount'>$count</strong></div>
                        </div>
                        <div class='summary-footer'>
                            <a href='$url' class='text-uppercase text-white'>(view all)</a>
                        </div>
                    </div>
                </div>
            </div>
        </div>";
    }
    ?>
</div>

<!-- Missing Information Section -->
<h3>Missing Information</h3>
<div class="row">
    <?php
    $missingData = [
        ["No License Plate", $missingLicense, "fas fa-id-card", "bg-secondary", ["missing" => "license"]],
        ["No Characteristics", $missingCharacteristics, "fas fa-cogs", "bg-secondary", ["missing" => "characteristics"]],
        ["No Image", $missingImage, "fas fa-image", "bg-secondary", ["missing" => "image"]],
        ["No Category", $missingCategory, "fas fa-tags", "bg-secondary", ["missing" => "category"]]
    ];

    foreach ($missingData as $data) {
        list($title, $count, $icon, $bgColor, $filter) = $data;
        $url = "fleet-details.php?" . http_build_query(array_merge($_GET, $filter));
        echo "
        <div class='col-md-3'>
            <div class='card-body $bgColor'>
                <div class='widget-summary'>
                    <div class='widget-summary-col widget-summary-col-icon'>
                        <div class='summary-icon'><i class='$icon'></i></div>
                    </div>
                    <div class='widget-summary-col'>
                        <div class='summary'>
                            <h4 class='title'>$title</h4>
                            <div class='info'><strong class='amount'>$count</strong></div>
                        </div>
                        <div class='summary-footer'>
                            <a href='$url' class='text-uppercase'>(view all)</a>
                        </div>
                    </div>
                </div>
            </div>
        </div>";
    }
    ?>
</div>

<!-- Compliance Checks -->
<h3>Compliance Checks</h3>
<div class="row">
    <?php
    $complianceData = [
        ["No Insurance Info", $missingInsurance, "fas fa-shield-alt", "bg-danger text-white", ["ins" => "missing"]],
        ["No Road Tax Info", $missingRoadTax, "fas fa-road", "bg-warning", ["tax" => "missing"]],
        ["No KTEO Info", $missingKteo, "fas fa-tools", "bg-danger text-white", ["kteo" => "missing"]]
    ];

    foreach ($complianceData as $data) {
        list($title, $count, $icon, $bgColor, $filter) = $data;
        $url = "fleet-details.php?" . http_build_query(array_merge($_GET, $filter));
        echo "
        <div class='col-md-4'>
            <div class='card-body $bgColor'>
                <div class='widget-summary'>
                    <div class='widget-summary-col widget-summary-col-icon'>
                        <div class='summary-icon'><i class='$icon'></i></div>
                    </div>
                    <div class='widget-summary-col'>
                        <div class='summary'>
                            <h4 class='title'>$title</h4>
                            <div class='info'><strong class='amount'>$count</strong></div>
                        </div>
                        <div class='summary-footer'>
                            <a href='$url' class='text-uppercase text-white'>(view all)</a>
                        </div>
                    </div>
                </div>
            </div>
        </div>";
    }
    ?>
</div>
<div class="mb-4">
    <h2 class="text-center">Info Tables</h2>
    <hr>
</div>

<!-- Tab Navigation -->
<ul class="nav nav-tabs mb-3" id="complianceTabs" role="tablist">
    <li class="nav-item">
        <a class="nav-link active" id="insurance-tab" data-toggle="tab" href="#insurance" role="tab">Insurance</a>
    </li>
    <li class="nav-item">
        <a class="nav-link" id="roadtax-tab" data-toggle="tab" href="#roadtax" role="tab">Road Tax</a>
    </li>
    <li class="nav-item">
        <a class="nav-link" id="kteo-tab" data-toggle="tab" href="#kteo" role="tab">KTEO</a>
    </li>
</ul>

<!-- Tab Content -->
<div class="tab-content" id="complianceTabContent">

    <!-- Insurance Tab -->
    <div class="tab-pane fade show active p-3" id="insurance" role="tabpanel">
        <h3>Insurance Status</h3>
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>License Plate</th>
                    <th>Vehicle Name</th>
                    <th>Expiry Date</th>
                    <th>Status</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = mysqli_fetch_assoc($expiredInsurance)) { ?>
                <tr>
                    <td><?= $row['lic_num'] ?></td>
                    <td><?= $row['vehicle_name'] ?></td>
                    <td class="text-danger"><?= date("d-m-Y", $row['fleet_insurance_until']) ?></td>
                    <td class="text-danger">Expired</td>
                    <td><a href="vehicle.php?id=<?= $row['fleet_id'] ?>" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a></td>
                </tr>
                <?php } ?>
                
                <?php while ($row = mysqli_fetch_assoc($expiringInsurance)) { ?>
                <tr>
                    <td><?= $row['lic_num'] ?></td>
                    <td><?= $row['vehicle_name'] ?></td>
                    <td class="text-warning"><?= date("d-m-Y", $row['fleet_insurance_until']) ?></td>
                    <td class="text-warning">Expiring Soon</td>
                    <td><a href="vehicle.php?id=<?= $row['fleet_id'] ?>" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a></td>
                </tr>
                <?php } ?>
            </tbody>
        </table>
    </div>

    <!-- Road Tax Tab -->
    <div class="tab-pane fade p-3" id="roadtax" role="tabpanel">
        <h3>Road Tax Status</h3>
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>License Plate</th>
                    <th>Vehicle Name</th>
                    <th>Expiry Date</th>
                    <th>Status</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = mysqli_fetch_assoc($expiredRoadTax)) { ?>
                <tr>
                    <td><?= $row['lic_num'] ?></td>
                    <td><?= $row['vehicle_name'] ?></td>
                    <td class="text-danger"><?= date("d-m-Y", $row['road_tax']) ?></td>
                    <td class="text-danger">Expired</td>
                    <td><a href="vehicle.php?id=<?= $row['fleet_id'] ?>" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a></td>
                </tr>
                <?php } ?>
            </tbody>
        </table>
    </div>

    <!-- KTEO Tab -->
    <div class="tab-pane fade p-3" id="kteo" role="tabpanel">
        <h3>KTEO Status</h3>
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>License Plate</th>
                    <th>Vehicle Name</th>
                    <th>Expiry Date</th>
                    <th>Status</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = mysqli_fetch_assoc($expiredKteo)) { ?>
                <tr>
                    <td><?= $row['lic_num'] ?></td>
                    <td><?= $row['vehicle_name'] ?></td>
                    <td class="text-danger"><?= date("d-m-Y", $row['fleet_kteo']) ?></td>
                    <td class="text-danger">Expired</td>
                    <td><a href="vehicle.php?id=<?= $row['fleet_id'] ?>" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a></td>
                </tr>
                <?php } ?>
                
                <?php while ($row = mysqli_fetch_assoc($expiringKteo)) { ?>
                <tr>
                    <td><?= $row['lic_num'] ?></td>
                    <td><?= $row['vehicle_name'] ?></td>
                    <td class="text-warning"><?= date("d-m-Y", $row['fleet_kteo']) ?></td>
                    <td class="text-warning">Expiring Soon</td>
                    <td><a href="vehicle.php?id=<?= $row['fleet_id'] ?>" class="btn btn-sm btn-info"><i class="fas fa-eye"></i></a></td>
                </tr>
                <?php } ?>
            </tbody>
        </table>
    </div>
</div>
