<?php include 'inc/funtions.php';
if (isset($_SESSION['user_type']) && $_SESSION['user_type'] == 'Admin') {
    $user_id = $_SESSION['user_id'];
    $business_id = $_SESSION['business_id'];
    $user = getUserId($user_id);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Dashboard</title>
    
    <link rel="stylesheet" href="assets/css/bootstrap.css">
    
    <link rel="stylesheet" href="assets/vendors/chartjs/Chart.min.css">
    <link rel="stylesheet" href="assets/vendors/simple-datatables/style.css">
    <link rel="stylesheet" href="assets/vendors/perfect-scrollbar/perfect-scrollbar.css">
    <link rel="stylesheet" href="assets/css/app.css">
    <link rel="shortcut icon" href="assets/images/favicon.png" type="image/x-icon">
    <script src="assets/vendors/chartjs/chart.umd.min.js"></script>
</head>
<body>
    <div id="app">
        <?php include('inc/header.php'); ?>
            <div class="main-content container-fluid">
                <div class="page-title">
                    <h3>Analysis Dashboard</h3>
                    <p class="text-subtitle text-muted"></p>
                </div>
                <div class="row mb-3 align-items-end">
                    <div class="col-md-12">
                        <form id="filterForm" method="GET" action="" class="bg-light p-3 rounded border">
                            <div class="row align-items-end g-2">
                                <div class="col-md-3">
                                    <label class="small fw-bold text-muted">Filter Type</label>
                                    <select name="filter" id="filterSelect" class="form-select form-select-sm" onchange="toggleDateInputs()">
                                        <option value="day" <?php if($filter == 'day') echo 'selected'; ?>>Today</option>
                                        <option value="week" <?php if($filter == 'week') echo 'selected'; ?>>This Week</option>
                                        <option value="month" <?php if($filter == 'month') echo 'selected'; ?>>This Month</option>
                                        <option value="year" <?php if($filter == 'year') echo 'selected'; ?>>This Year</option>
                                        <option value="custom" <?php if($filter == 'custom') echo 'selected'; ?>>Custom Range</option>
                                    </select>
                                </div>

                                <div id="customDates" class="col-md-7 <?php echo ($filter == 'custom') ? '' : 'd-none'; ?>">
                                    <div class="row g-2">
                                        <div class="col-6">
                                            <label class="small fw-bold text-muted">Start Date</label>
                                            <input type="date" name="start_date" class="form-control form-control-sm" value="<?php echo $_GET['start_date'] ?? ''; ?>">
                                        </div>
                                        <div class="col-6">
                                            <label class="small fw-bold text-muted">End Date</label>
                                            <input type="date" name="end_date" class="form-control form-control-sm" value="<?php echo $_GET['end_date'] ?? ''; ?>">
                                        </div>
                                    </div>
                                </div>

                                <div class="col-md-2">
                                    <button type="submit" class="btn btn-sm btn-dark w-100">Apply Filter</button>
                                </div>
                            </div>
                        </form>
                    </div>

                    <!-- <div class="col-md-6 text-end">
                        <button class="btn btn-danger" onclick="exportPDF()">Export PDF</button>
                        <button class="btn btn-success" onclick="exportExcel()">Export Excel</button>
                    </div> -->
                </div>
                <section class="section">
                    <div class="row">
                        <div class="col-md-3">
                            <div class="card p-3 shadow-sm border-0 bg-primary text-white" style="height: 140px;">
                                <div class="d-flex justify-content-between align-items-start">
                                    <div>
                                        <small class="text-uppercase font-weight-bold" style="opacity: 0.8; font-size: 0.75rem;">Total Revenue</small>
                                        <h2 class="mb-0">₦ <?php echo number_format($gross_revenue, 2); ?></h2>
                                    </div>
                                    <div class="text-right">
                                        <i class="fas <?php echo ($rev_diff_percent >= 0) ? 'fa-arrow-up' : 'fa-arrow-down'; ?>"></i>
                                        <small class="d-block" ><?php echo round(abs($rev_diff_percent)); ?>%</small>
                                    </div>
                                </div>

                                <div class="progress mt-3" style="height: 6px; background: rgba(255,255,255,0.2);">
                                    <div class="progress-bar bg-white" 
                                         role="progressbar" 
                                         style="width: <?php echo $bar_width; ?>%" 
                                         aria-valuenow="<?php echo $bar_width; ?>" 
                                         aria-valuemin="0" 
                                         aria-valuemax="100">
                                    </div>
                                </div>
                                
                                <small class="mt-2 d-block" style="opacity: 0.7; font-size: 0.75rem;">
                                    vs Prev. <?php echo ucfirst($filter); ?>: ₦<?php echo number_format($prev_revenue); ?>
                                </small>
                            </div>
                        </div>

                        <div class="col-md-3">
                            <div class="card p-3 shadow-sm border-0 bg-info text-white" style="height: 140px;">
                                <small class="text-uppercase" style="opacity: 0.7; font-size: 0.75rem;">Exp / Main Costs</small>
                                <h3 class="font-weight-bold mb-1">
                                    ₦ <?php echo number_format($total_maintenance); ?>
                                </h3>
                                
                                <div class="d-flex align-items-center">
                                    <small class="mr-2">
                                        Prev: ₦ <?php echo number_format($prev_total_maintenance); ?>
                                    </small>
                                    
                                    <small class="<?php echo $maint_trend_color; ?> font-weight-bold" style="opacity: 0.7; font-size: 0.75rem;">
                                        <?php echo ($total_maintenance > $prev_total_maintenance) ? '↑' : '↓'; ?> 
                                        ₦<?php echo number_format(abs($maint_diff)); ?>
                                    </small>
                                </div>
                            </div>                            
                        </div>

                        <div class="col-md-3">
                            <div class="card p-3 shadow-sm border-0 bg-primary text-white" style="height: 140px;">
                                <small style="opacity: 0.7; font-size: 0.75rem;">TOTAL SALES VOLUME</small>
                                <h2><?php echo number_format($total_sales_vol); ?></h2>
                                <small style="opacity: 0.7; font-size: 0.75rem;">From <?php echo $total_trips; ?> ATC Records</small>
                            </div>
                        </div>

                        <div class="col-md-3">
                            <div class="card p-3 shadow-sm border-0 bg-info text-white" style="height: 140px;">
                                <small style="opacity: 0.7; font-size: 0.75rem;">TOTAL TRIPS</small>
                                <h3><?php echo number_format($curr_trips); ?></h3>
                                <small class="<?php echo $trend_color; ?>" style="opacity: 0.7; font-size: 1.0rem;">
                                    <?php echo $trend_icon . round(abs($diff)); ?>% vs Previous <?php echo ucfirst($filter); ?>
                                </small>
                            </div>
                        </div>
                    </div>
                    <div class="row mb-4">
                        <div class="col-md-8">
                            <div class="card shadow-sm border-0">
                                <div class="card-body">
                                    <div class="justify-content-between align-items-center mb-4">
                                        <h5 class="card-title text-muted">Most Frequent Customers</h5>
                                        <p class="small text-muted mb-4">Ranking by total number of bookings</p>                                        
                                    </div>

                                    <div style="height: 300px;">
                                        <canvas id="tripFrequencyChart"></canvas>
                                    </div>
                                </div>
                            </div>
                            
                        </div>
                    <div class="col-md-4">
                        <div class="card shadow-sm p-4 border-0">
                            <h5 class="text-muted mb-4">Financial Health Check</h5>
                            
                            <div class="text-center mb-4">
                                <h1 class="display-4 font-weight-bold <?php echo $health_color; ?>">
                                    <?php echo round($margin_percentage); ?>%
                                </h1>
                                <p class="text-uppercase small font-weight-bold text-muted">Net Profit Margin</p>
                            </div>

                            <div class="mt-4">
                                <div class="d-flex justify-content-between border-bottom py-2">
                                    <div>
                                        <span class="text-muted small d-block">Gross Revenue</span>
                                        <small class="<?php echo $rev_trend_color; ?> font-weight-bold">
                                            <?php echo ($rev_diff >= 0 ? '↑' : '↓') . round(abs($rev_diff)); ?>% vs Prev.
                                        </small>
                                    </div>
                                    <span class="font-weight-bold align-self-center">₦<?php echo number_format($gross_revenue); ?></span>
                                </div>

                                <div class="d-flex justify-content-between border-bottom py-2">
                                    <span class="text-muted small">Operating Costs</span>
                                    <span class="text-danger">- ₦<?php echo number_format($operating_costs); ?></span>
                                </div>

                                <div class="d-flex justify-content-between pt-3">
                                    <span class="font-weight-bold">Net Profit</span>
                                    <span class="font-weight-bold <?php echo $health_color; ?>" style="font-size: 1.2rem;">
                                        ₦<?php echo number_format($net_profit); ?>
                                    </span>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="row">
                        <div class="col-md-12">
                            <div class="card border-0 shadow-sm mt-4">
                                <div class="card-header bg-white py-3">
                                    <h5 class="mb-0 fw-bold">Route Profitability Analysis</h5>
                                    <small class="text-muted">Filtering by: <?php echo ucfirst($filter); ?></small>
                                </div>
                                <div class="table-responsive">
                                    <table class="table table-hover align-middle mb-0" id="table1">
                                        <thead>
                                            <tr>
                                                <th>Route Path</th>
                                                <th class="text-center">Trips</th>
                                                <th>Total Revenue</th>
                                                <th>Total Expense</th>
                                                <th class="text-end">Profit/Trip</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php
                                            $route_sql = Querydb("SELECT 
                                                t.*, 
                                                tr.license_plate, tr.model as truck_model,
                                                d.first_name as fn, d.last_name as ln, 
                                                atc.atc_number, atc.volume as atc_volume,
                                                c.name as customer_name
                                            FROM trips t
                                            LEFT JOIN trucks tr ON t.truck_id = tr.truck_id
                                            LEFT JOIN drivers d ON t.driver_id = d.driver_id
                                            LEFT JOIN atc_records atc ON t.atc_id = atc.atc_id
                                            LEFT JOIN customers c ON t.customer_id = c.customer_id
                                            $date_condition AND  t.business_id = '$business_id' 
                                            ORDER BY t.create_at DESC");
                                            while($route = mysqli_fetch_assoc($route_sql)) {  
                                                $tr = ($route['haulage_amount']*$route['unit']) + ($route['promo_amount']*$route['unit']) + ($route['incentive_amount']*$route['unit']) + ($route['selling_price']*$route['unit']);
                                                $ep = ($route['cost_price']*$route['unit']) + ($route['driver_allowence']) + ($route['cost_per_liter']*$route['liters_filled']);
                                            ?>
                                            <tr>
                                                <td>
                                                    <div class="fw-bold"><?php echo $route['license_plate']; ?></div>
                                                    <small class="text-muted"><?php echo $route['fn'].' '.$route['ln']; ?></small>
                                                </td>
                                                <td class="text-center">
                                                    <div class="fw-bold"><?php echo $route['atc_number']; ?></div>
                                                    <small class="text-muted"><?php echo $route['customer_name']; ?></small>
                                                </td>
                                                <td>₦<?php echo number_format($tr, 2); ?></td>
                                                <td class="text-danger">₦<?php echo number_format($ep, 2); ?></td>
                                                <td class="text-end">₦<?php echo number_format($tr - $ep, 2); ?></td>
                                            </tr>
                                            <?php } ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>                                    
                        </div>
                    </div>
                    <div class="row">
                        <div class="col-md-12">
                            <div class="card border-0 shadow-sm mt-4">
                                <div class="card-header bg-white py-3">
                                    <h5 class="mb-0 fw-bold">Route Profitability Analysis</h5>
                                    <small class="text-muted">Filtering by: <?php echo ucfirst($filter); ?></small>
                                </div>
                                <div class="table-responsive">
                                    <table class="table table-hover align-middle mb-0" id="table2">
                                        <thead>
                                            <tr>
                                                <th>Route Path</th>
                                                <th class="text-center">Trips</th>
                                                <th>Total Revenue</th>
                                                <th>Total Expense</th>
                                                <th class="text-end">Profit/Truck</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                           <?php
                                            $filter = $_GET['filter'] ?? 'month';
                                            $start_date = $_GET['start_date'] ?? '';
                                            $end_date = $_GET['end_date'] ?? '';

                                            // Default Condition
                                            $date_condition = "AND MONTH(actual_end_time) = MONTH(CURDATE()) AND YEAR(actual_end_time) = YEAR(CURDATE())";

                                            if ($filter == 'custom' && !empty($start_date) && !empty($end_date)) {
                                                $date_condition = "AND DATE(actual_end_time) BETWEEN '$start_date' AND '$end_date'";
                                                $m_date = "AND date_performed BETWEEN '$start_date' AND '$end_date'"; // For Maintenance table
                                            } else {
                                                // Mapping filters to SQL
                                                $mapping = [
                                                    'day'   => "AND DATE(actual_end_time) = CURDATE()",
                                                    'week'  => "AND YEARWEEK(actual_end_time, 1) = YEARWEEK(CURDATE(), 1)",
                                                    'month' => "AND MONTH(actual_end_time) = MONTH(CURDATE()) AND YEAR(actual_end_time) = YEAR(CURDATE())",
                                                    'year'  => "AND YEAR(actual_end_time) = YEAR(CURDATE())"
                                                ];
                                                $date_condition = $mapping[$filter] ?? $mapping['month'];
                                                
                                                // Mapping for Maintenance subquery (uses different column name)
                                                $m_date = str_replace('actual_end_time', 'date_performed', $date_condition);
                                            }

                                            // Final WHERE clauses
                                            $main_where = "WHERE status = 'Completed' $date_condition";
                                            $sub_where  = "WHERE 1=1 $m_date"; 


                                            $query = "SELECT 
                                                        tr.license_plate,
                                                        COUNT(t.trip_id) as total_trips,
                                                        SUM(t.haulage_amount * t.unit) as total_haulage,
                                                        SUM(t.promo_amount * t.unit) as total_promo,
                                                        SUM(t.incentive_amount * t.unit) as total_incentive,
                                                        SUM(t.selling_price * t.unit) as total_selling,
                                                        SUM(t.cost_price * t.unit) as total_cost_price,
                                                        SUM(t.driver_allowence) as total_allowance,
                                                        SUM(t.cost_per_liter * t.liters_filled) as total_fuel,
                                                        IFNULL(m_total.total_m_cost, 0) as main_cost
                                                    FROM trucks tr
                                                    LEFT JOIN trips t ON tr.truck_id = t.truck_id AND t.business_id = '$business_id'
                                                    LEFT JOIN (
                                                        SELECT truck_id, SUM(cost) as total_m_cost 
                                                        FROM maintenance 
                                                        $sub_where AND business_id = '$business_id' 
                                                        GROUP BY truck_id 
                                                    ) m_total ON tr.truck_id = m_total.truck_id
                                                    $main_where AND  tr.business_id = '$business_id' 
                                                    GROUP BY tr.truck_id 
                                                    ORDER BY total_trips DESC";

                                            $route_sql = Querydb($query);
                                            ?>
                                            <tbody>
                                            <?php while($route = mysqli_fetch_assoc($route_sql)) {   
                                                // Total Revenue (Income)
                                                $revenue = $route['total_haulage'] + $route['total_promo'] + $route['total_incentive'] + $route['total_selling'];

                                                // Total Expenses (Outgoings)
                                                $expenses = $route['total_cost_price'] + $route['total_allowance'] + $route['total_fuel'] + $route['main_cost']; 
                                                
                                                $profit = $revenue - $expenses;
                                            ?>
                                                <tr>
                                                    <td><div class="fw-bold"><?php echo htmlspecialchars($route['license_plate']); ?></div></td>
                                                    <td class="text-center"><span class="badge bg-primary"><?php echo $route['total_trips']; ?> Trips</span></td>
                                                    <td>₦<?php echo number_format($revenue, 2); ?></td>
                                                    <td class="text-danger">₦<?php echo number_format($expenses, 2); ?></td>
                                                    <td class="text-end fw-bold <?php echo ($profit >= 0) ? 'text-success' : 'text-danger'; ?>">
                                                        ₦<?php echo number_format($profit, 2); ?>
                                                    </td>
                                                </tr>
                                            <?php } ?>                                            
                                        </tbody>
                                    </table>
                                </div>
                            </div>                                    
                        </div>
                    </div>
                    <div class="card shadow-sm border-0">
                        <div class="card-header bg-white border-0 pt-4 px-4">
                            <h5 class="card-title text-muted mb-0">Truck Performance & Maintenance</h5>
                            <small class="text-muted">Comparing usage vs. costs for the selected period</small>
                        </div>
                        <div class="card-body px-0">
                            <div class="table-responsive">
                                <table class="table table-hover align-items-center mb-0" id="table1">
                                    <thead class="thead-light">
                                        <tr>
                                            <th class="px-4">License Plate</th>
                                            <th class="text-center">Trips</th>
                                            <th>Maint. Cost</th>
                                            <th>Prev. Maint.</th>
                                            <th class="text-center">Trend</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while ($row = mysqli_fetch_assoc($truck_table_results)){
                                            $curr = $row['current_maint_total'] ?? 0;
                                            $prev = $row['prev_maint_total'] ?? 0;
                                            $trips = $row['trip_count'] ?? 0;
                                            $diff = $curr - $prev;
                                            $trend_class = ($curr > $prev) ? 'text-danger' : 'text-success';
                                        ?>
                                        <tr>
                                            <td class="px-4 font-weight-bold"><?php echo $row['license_plate']; ?></td>
                                            <td class="text-center">
                                                <span class=" p-2"><?php echo $trips; ?> Trips</span>
                                            </td>
                                            <td class="font-weight-bold">₦ <?php echo number_format($curr); ?></td>
                                            <td class="text-muted small">₦ <?php echo number_format($prev); ?></td>
                                            <td class="text-center <?php echo $trend_class; ?> font-weight-bold">
                                                <?php if ($prev > 0) { ?>
                                                    <?php echo ($curr > $prev ? '↑' : '↓'); ?> ₦<?php echo number_format(abs($diff)); ?>
                                                <?php } else { ?>
                                                    <span class="text-muted small">--</span>
                                                <?php } ?>
                                            </td>
                                        </tr>
                                        <?php }; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </section> <?php
                // Fetching the report data
                $filter = $_GET['filter'] ?? 'day'; // Default to today
                $where_clause = "status = 'Completed'";

                $filter = $_GET['filter'] ?? '';
                $start_date = $_GET['start_date'] ?? '';
                $end_date = $_GET['end_date'] ?? '';

                $where_clause = "status = 'Completed'";
                $prev_where = "status = 'Completed'";

                if (!empty($start_date) && !empty($end_date)) {
                    // CUSTOM RANGE LOGIC
                    $where_clause .= " AND DATE(actual_end_time) BETWEEN '$start_date' AND '$end_date'";
                    
                    // Calculate difference to find previous period
                    $diff = strtotime($end_date) - strtotime($start_date);
                    $days = round($diff / (60 * 60 * 24)) + 1;
                    
                    $prev_start = date('Y-m-d', strtotime($start_date . " - $days days"));
                    $prev_end = date('Y-m-d', strtotime($end_date . " - $days days"));
                    $prev_where .= " AND DATE(actual_end_time) BETWEEN '$prev_start' AND '$prev_end'";

                } else {
                    // PRESET FILTERS (Your existing logic + Prev Period logic)
                    if ($filter == 'day') {
                        $where_clause .= " AND DATE(actual_end_time) = CURDATE()";
                        $prev_where .= " AND DATE(actual_end_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
                    } elseif ($filter == 'week') {
                        $where_clause .= " AND YEARWEEK(actual_end_time, 1) = YEARWEEK(CURDATE(), 1)";
                        $prev_where .= " AND YEARWEEK(actual_end_time, 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK), 1)";
                    } elseif ($filter == 'month') {
                        $where_clause .= " AND MONTH(actual_end_time) = MONTH(CURDATE()) AND YEAR(actual_end_time) = YEAR(CURDATE())";
                        $prev_where .= " AND MONTH(actual_end_time) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(actual_end_time) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))";
                    } else {
                        // Default to today if nothing is selected
                        $where_clause .= " AND DATE(actual_end_time) = CURDATE()";
                        $prev_where .= " AND DATE(actual_end_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
                    }
                }


                $report_sql = Querydb("SELECT DATE(actual_end_time) as r_date, COUNT(*) as qty, SUM(haulage_amount * unit) as haul, SUM(promo_amount * unit) as promo, SUM(incentive_amount * unit) as inc, SUM(selling_price * unit) as sell, SUM((haulage_amount * unit) + (promo_amount * unit) + (incentive_amount * unit) + (selling_price * unit)) as profit FROM trips WHERE $where_clause AND business_id = '$business_id'  GROUP BY r_date ORDER BY r_date DESC");

                ?>
                <div class="">
                <div class="card shadow-sm border-0">
                    <div class="card-header bg-white border-0 px-4">
                         <h2>Daily Performance Report</h2>
                    </div>                   
                    <div class="card-body px-0">
                        <div class="table-responsive">
                            <table class="table" id="table1" border="1" cellpadding="10" style="width:100%; border-collapse: collapse;">
                                <thead>
                                    <tr style="background-color: #f2f2f2;">
                                        <th>Date</th>
                                        <th>Trips</th>
                                        <th>Total Haulage</th>
                                        <th>Promos Given</th>
                                        <th>Incentives Paid</th>
                                        <th>Sell Price</th>
                                        <th>Total Income</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php while($row = mysqli_fetch_assoc($report_sql)) { ?>
                                        
                                    <tr>
                                        <td><?php echo $row['r_date']; ?></td>
                                        <td><?php echo $row['qty']; ?></td>
                                        <td><?php echo number_format($row['haul'], 2); ?></td>
                                        <td><?php echo number_format($row['promo'], 2); ?></td>
                                        <td><?php echo number_format($row['inc'], 2); ?></td>
                                        <td><?php echo number_format($row['sell'], 2); ?></td>
                                        <td style="font-weight: bold; color: green;">
                                            <?php echo number_format($row['profit'], 2); ?>
                                        </td>
                                    </tr>
                                    <?php } ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
<?php  include('inc/footer.php'); } else {
     header('Location: pages-error-404.html');
    exit;
} ?>