<?php include 'layouts/session.php'; ?>
<?php include 'layouts/main.php'; ?>
<?php include 'layouts/dbconn.php'; ?>

<!DOCTYPE html>
<html lang="en">
<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Operational Report')); ?>
    
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css">
    
    <!-- DataTables CSS -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/dataTables.bootstrap5.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.4.1/css/responsive.bootstrap5.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.3.6/css/buttons.bootstrap5.min.css">
    
    <!-- Daterange picker -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/daterangepicker@3.1.0/daterangepicker.css">
    
    <!-- Custom CSS -->
    <?php include 'layouts/head-css.php'; ?>
    
    <style>
        .report-card {
            transition: all 0.3s;
        }
        .report-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 5px 15px rgba(0,0,0,0.1);
        }
        .kpi-value {
            font-size: 24px;
            font-weight: bold;
        }
        .kpi-title {
            font-size: 14px;
            color: #6c757d;
        }
        .chart-container {
            height: 300px;
            position: relative;
        }
        .dt-buttons {
            margin-bottom: 15px;
        }
    </style>
</head>

<body>
    <!-- Begin page -->
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <!-- ============================================================== -->
        <!-- Start right Content here -->
        <!-- ============================================================== -->
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Reports', 'title' => 'Operational Report')); ?>
                    
                    <!-- Filters Section -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-body">
                                    <form id="reportFilters" method="GET">
                                        <div class="row g-3">
                                            <div class="col-md-3">
                                                <label for="daterange" class="form-label">Date Range</label>
                                                <input type="text" class="form-control" id="daterange" name="daterange">
                                            </div>
                                            <div class="col-md-3">
                                                <label for="destination" class="form-label">Destination</label>
                                                <select class="form-select" id="destination" name="destination">
                                                    <option value="">All Destinations</option>
                                                    <?php
                                                    // Get all destinations from the database
                                                    $sql = "SELECT DISTINCT destination FROM files WHERE destination != '' ORDER BY destination";
                                                    $result = $conn->query($sql);
                                                    
                                                    if ($result && $result->num_rows > 0) {
                                                        while($row = $result->fetch_assoc()) {
                                                            echo '<option value="' . htmlspecialchars($row['destination']) . '">' . htmlspecialchars($row['destination']) . '</option>';
                                                        }
                                                    }
                                                    ?>
                                                </select>
                                            </div>
                                            <div class="col-md-3">
                                                <label for="customer" class="form-label">Customer</label>
                                                <select class="form-select" id="customer" name="customer">
                                                    <option value="">All Customers</option>
                                                    <?php
                                                    // Get all customers from the database
                                                    $sql = "SELECT customer_id, first_name FROM customers WHERE status = 1 ORDER BY first_name";
                                                    $result = $conn->query($sql);
                                                    
                                                    if ($result && $result->num_rows > 0) {
                                                        while($row = $result->fetch_assoc()) {
                                                            echo '<option value="' . $row['customer_id'] . '">' . htmlspecialchars($row['first_name']) . '</option>';
                                                        }
                                                    }
                                                    ?>
                                                </select>
                                            </div>
                                            <div class="col-md-3">
                                                <label for="transporter" class="form-label">Transporter</label>
                                                <select class="form-select" id="transporter" name="transporter">
                                                    <option value="">All Transporters</option>
                                                    <?php
                                                    // Get all transporters from the database
                                                    $sql = "SELECT id, name FROM transporter_info WHERE status = 1 ORDER BY name";
                                                    $result = $conn->query($sql);
                                                    
                                                    if ($result && $result->num_rows > 0) {
                                                        while($row = $result->fetch_assoc()) {
                                                            echo '<option value="' . $row['id'] . '">' . htmlspecialchars($row['name']) . '</option>';
                                                        }
                                                    }
                                                    ?>
                                                </select>
                                            </div>
                                            <div class="col-md-3">
                                                <label for="reportType" class="form-label">Report Type</label>
                                                <select class="form-select" id="reportType" name="reportType">
                                                    <option value="summary">Summary Report</option>
                                                    <option value="detailed">Detailed Report</option>
                                                    <option value="transit">Transit Report</option>
                                                    <option value="performance">Performance Report</option>
                                                </select>
                                            </div>
                                            <div class="col-md-3 d-flex align-items-end">
                                                <button type="submit" class="btn btn-primary w-100">Generate Report</button>
                                            </div>
                                            <div class="col-md-3 d-flex align-items-end">
                                                <button type="button" id="exportReport" class="btn btn-success w-100">Export Report</button>
                                            </div>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- KPI Summary Cards -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="card report-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium mb-2 kpi-title">Total Shipments</p>
                                            <?php
                                            // Get total shipments with date filter if provided
                                            $where_clause = "";
                                            if (!empty($_GET['daterange'])) {
                                                $date_range = explode(' - ', $_GET['daterange']);
                                                $start_date = date('Y-m-d', strtotime($date_range[0]));
                                                $end_date = date('Y-m-d', strtotime($date_range[1]));
                                                $where_clause = " WHERE DATE(created_date) BETWEEN '$start_date' AND '$end_date'";
                                            }
                                            
                                            $sql = "SELECT COUNT(*) as total FROM files" . $where_clause;
                                            $result = $conn->query($sql);
                                            $totalShipments = 0;
                                            if ($result && $result->num_rows > 0) {
                                                $row = $result->fetch_assoc();
                                                $totalShipments = $row['total'];
                                            }
                                            ?>
                                            <h4 class="mb-0 kpi-value"><?php echo $totalShipments; ?></h4>
                                        </div>
                                        <div class="avatar-sm rounded-circle bg-primary align-self-center mini-stat-icon">
                                            <span class="avatar-title rounded-circle bg-primary">
                                                <i class="bx bx-package font-size-24"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="card report-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium mb-2 kpi-title">Transit Time (Avg)</p>
                                            <?php
                                            // Calculate average transit time with filters
                                            $join_clause = " JOIN files f ON cd.file_number = f.file_number";
                                            $where_clause = " WHERE cd.truck_exit_date IS NOT NULL AND cd.delivery_date IS NOT NULL";
                                            
                                            if (!empty($_GET['daterange'])) {
                                                $date_range = explode(' - ', $_GET['daterange']);
                                                $start_date = date('Y-m-d', strtotime($date_range[0]));
                                                $end_date = date('Y-m-d', strtotime($date_range[1]));
                                                $where_clause .= " AND DATE(f.created_date) BETWEEN '$start_date' AND '$end_date'";
                                            }
                                            
                                            if (!empty($_GET['destination'])) {
                                                $destination = $conn->real_escape_string($_GET['destination']);
                                                $where_clause .= " AND f.destination = '$destination'";
                                            }
                                            
                                            if (!empty($_GET['customer'])) {
                                                $customer = intval($_GET['customer']);
                                                $where_clause .= " AND f.customer = $customer";
                                            }
                                            
                                            $sql = "SELECT AVG(DATEDIFF(cd.delivery_date, cd.truck_exit_date)) as avg_transit 
                                                    FROM container_details cd" . $join_clause . $where_clause;
                                            $result = $conn->query($sql);
                                            $avgTransit = 0;
                                            if ($result && $result->num_rows > 0) {
                                                $row = $result->fetch_assoc();
                                                $avgTransit = $row['avg_transit'] ? round($row['avg_transit'], 1) : 0;
                                            }
                                            ?>
                                            <h4 class="mb-0 kpi-value"><?php echo $avgTransit; ?> days</h4>
                                        </div>
                                        <div class="avatar-sm rounded-circle bg-success align-self-center mini-stat-icon">
                                            <span class="avatar-title rounded-circle bg-success">
                                                <i class="bx bx-time font-size-24"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="card report-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium mb-2 kpi-title">On-Time Delivery Rate</p>
                                            <?php
                                            // Calculate on-time delivery rate with filters
                                            $where_clause = " WHERE cd.delivery_date IS NOT NULL AND f.eta IS NOT NULL";
                                            
                                            if (!empty($_GET['daterange'])) {
                                                $date_range = explode(' - ', $_GET['daterange']);
                                                $start_date = date('Y-m-d', strtotime($date_range[0]));
                                                $end_date = date('Y-m-d', strtotime($date_range[1]));
                                                $where_clause .= " AND DATE(f.created_date) BETWEEN '$start_date' AND '$end_date'";
                                            }
                                            
                                            if (!empty($_GET['destination'])) {
                                                $destination = $conn->real_escape_string($_GET['destination']);
                                                $where_clause .= " AND f.destination = '$destination'";
                                            }
                                            
                                            if (!empty($_GET['customer'])) {
                                                $customer = intval($_GET['customer']);
                                                $where_clause .= " AND f.customer = $customer";
                                            }
                                            
                                            $sql = "SELECT 
                                                    COUNT(*) as total,
                                                    SUM(CASE WHEN cd.delivery_date <= DATE_ADD(f.eta, INTERVAL 2 DAY) THEN 1 ELSE 0 END) as on_time
                                                FROM container_details cd
                                                JOIN files f ON cd.file_number = f.file_number"
                                                . $where_clause;
                                            $result = $conn->query($sql);
                                            $onTimeRate = 0;
                                            if ($result && $result->num_rows > 0) {
                                                $row = $result->fetch_assoc();
                                                if ($row['total'] > 0) {
                                                    $onTimeRate = round(($row['on_time'] / $row['total']) * 100, 1);
                                                }
                                            }
                                            ?>
                                            <h4 class="mb-0 kpi-value"><?php echo $onTimeRate; ?>%</h4>
                                        </div>
                                        <div class="avatar-sm rounded-circle bg-info align-self-center mini-stat-icon">
                                            <span class="avatar-title rounded-circle bg-info">
                                                <i class="bx bx-timer font-size-24"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="col-xl-3 col-md-6">
                            <div class="card report-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-muted fw-medium mb-2 kpi-title">Container Utilization</p>
                                            <?php
                                            // Get container utilization with filters
                                            $join_clause = " JOIN files f ON cd.file_number = f.file_number";
                                            $where_clause = "";
                                            
                                            if (!empty($_GET['daterange'])) {
                                                $date_range = explode(' - ', $_GET['daterange']);
                                                $start_date = date('Y-m-d', strtotime($date_range[0]));
                                                $end_date = date('Y-m-d', strtotime($date_range[1]));
                                                $where_clause .= " WHERE DATE(f.created_date) BETWEEN '$start_date' AND '$end_date'";
                                            }
                                            
                                            if (!empty($_GET['destination'])) {
                                                $destination = $conn->real_escape_string($_GET['destination']);
                                                $where_clause .= (!empty($where_clause) ? " AND" : " WHERE") . " f.destination = '$destination'";
                                            }
                                            
                                            if (!empty($_GET['customer'])) {
                                                $customer = intval($_GET['customer']);
                                                $where_clause .= (!empty($where_clause) ? " AND" : " WHERE") . " f.customer = $customer";
                                            }
                                            
                                            $sql = "SELECT COUNT(*) as total_containers,
                                                    SUM(CASE WHEN cd.delivery_date IS NOT NULL THEN 1 ELSE 0 END) as delivered
                                                    FROM container_details cd" . $join_clause . $where_clause;
                                            $result = $conn->query($sql);
                                            $utilization = 0;
                                            if ($result && $result->num_rows > 0) {
                                                $row = $result->fetch_assoc();
                                                if ($row['total_containers'] > 0) {
                                                    $utilization = round(($row['delivered'] / $row['total_containers']) * 100, 1);
                                                }
                                            }
                                            ?>
                                            <h4 class="mb-0 kpi-value"><?php echo $utilization; ?>%</h4>
                                        </div>
                                        <div class="avatar-sm rounded-circle bg-warning align-self-center mini-stat-icon">
                                            <span class="avatar-title rounded-circle bg-warning">
                                                <i class="bx bx-cube font-size-24"></i>
                                            </span>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Charts Section -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Shipments by Destination</h4>
                                    <div class="chart-container">
                                        <canvas id="destinationChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Shipments by Month</h4>
                                    <div class="chart-container">
                                        <canvas id="shipmentTrendsChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Transit Time by Destination</h4>
                                    <div class="chart-container">
                                        <canvas id="transitTimeChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Top 5 Transporters by Volume</h4>
                                    <div class="chart-container">
                                        <canvas id="transporterChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Report Data Table -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-body">
                                    <h4 class="card-title">Operational Data</h4>
                                    <p class="card-title-desc">Detailed view of operational performance</p>
                                    
                                    <?php
                                    // Build query based on filters
                                    $where_clause = "";
                                    
                                    if (!empty($_GET['daterange'])) {
                                        $date_range = explode(' - ', $_GET['daterange']);
                                        $start_date = date('Y-m-d', strtotime($date_range[0]));
                                        $end_date = date('Y-m-d', strtotime($date_range[1]));
                                        $where_clause .= " WHERE DATE(f.created_date) BETWEEN '$start_date' AND '$end_date'";
                                    }
                                    
                                    if (!empty($_GET['destination'])) {
                                        $destination = $conn->real_escape_string($_GET['destination']);
                                        $where_clause .= (!empty($where_clause) ? " AND" : " WHERE") . " f.destination = '$destination'";
                                    }
                                    
                                    if (!empty($_GET['customer'])) {
                                        $customer = intval($_GET['customer']);
                                        $where_clause .= (!empty($where_clause) ? " AND" : " WHERE") . " f.customer = $customer";
                                    }
                                    
                                    if (!empty($_GET['transporter'])) {
                                        $transporter = intval($_GET['transporter']);
                                        $transporter_sql = "SELECT name FROM transporter_info WHERE id = $transporter";
                                        $transporter_result = $conn->query($transporter_sql);
                                        if ($transporter_result && $transporter_result->num_rows > 0) {
                                            $transporter_row = $transporter_result->fetch_assoc();
                                            $transporter_name = $conn->real_escape_string($transporter_row['name']);
                                            $where_clause .= (!empty($where_clause) ? " AND" : " WHERE") . " cd.transporter_name = '$transporter_name'";
                                        }
                                    }
                                    
                                    // Determine report type and adjust query
                                    $report_type = !empty($_GET['reportType']) ? $_GET['reportType'] : 'summary';
                                    $limit = "";
                                    
                                    if ($report_type == 'summary') {
                                        $limit = " LIMIT 50";
                                    }
                                    ?>
                                    
                                    <table id="operationalTable" class="table table-bordered dt-responsive nowrap w-100">
                                        <thead>
                                            <tr>
                                                <th>File Number</th>
                                                <th>Customer</th>
                                                <th>Destination</th>
                                                <th>ETA</th>
                                                <th>Container Code</th>
                                                <th>Transit Time (Days)</th>
                                                <th>Delivery Date</th>
                                                <th>Status</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php
                                            // Get operational data - adjust query based on the filters
                                            $sql = "SELECT f.file_number, c.first_name as customer_name, f.destination, 
                                                    f.eta, cd.container_code, 
                                                    DATEDIFF(cd.delivery_date, cd.truck_exit_date) as transit_time,
                                                    cd.delivery_date,
                                                    CASE 
                                                        WHEN cd.empty_return_date IS NOT NULL THEN 'Completed'
                                                        WHEN cd.delivery_date IS NOT NULL THEN 'Delivered'
                                                        WHEN cd.truck_exit_date IS NOT NULL THEN 'In Transit'
                                                        ELSE 'Pending'
                                                    END as status
                                                FROM files f
                                                LEFT JOIN customers c ON f.customer = c.customer_id
                                                LEFT JOIN container_details cd ON f.file_number = cd.file_number"
                                                . $where_clause . 
                                                " ORDER BY f.created_date DESC"
                                                . $limit;
                                            $result = $conn->query($sql);
                                            
                                            if ($result && $result->num_rows > 0) {
                                                while($row = $result->fetch_assoc()) {
                                                    echo "<tr>";
                                                    echo "<td>" . htmlspecialchars($row['file_number']) . "</td>";
                                                    echo "<td>" . htmlspecialchars($row['customer_name'] ?? 'N/A') . "</td>";
                                                    echo "<td>" . htmlspecialchars($row['destination'] ?? 'N/A') . "</td>";
                                                    echo "<td>" . ($row['eta'] ? date('Y-m-d', strtotime($row['eta'])) : '-') . "</td>";
                                                    echo "<td>" . htmlspecialchars($row['container_code'] ?? 'N/A') . "</td>";
                                                    echo "<td>" . ($row['transit_time'] ? $row['transit_time'] : '-') . "</td>";
                                                    echo "<td>" . ($row['delivery_date'] ? date('Y-m-d', strtotime($row['delivery_date'])) : '-') . "</td>";
                                                    
                                                    // Status with appropriate badge
                                                    $statusClass = '';
                                                    switch($row['status']) {
                                                        case 'Completed':
                                                            $statusClass = 'badge bg-success';
                                                            break;
                                                        case 'Delivered':
                                                            $statusClass = 'badge bg-info';
                                                            break;
                                                        case 'In Transit':
                                                            $statusClass = 'badge bg-warning';
                                                            break;
                                                        default:
                                                            $statusClass = 'badge bg-secondary';
                                                    }
                                                    echo "<td><span class='" . $statusClass . "'>" . $row['status'] . "</span></td>";
                                                    
                                                    echo "</tr>";
                                                }
                                            }
                                            ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            
            <?php include 'layouts/footer.php'; ?>
        </div>
        <!-- end main content-->
    </div>
    <!-- END layout-wrapper -->

    <?php include 'layouts/vendor-scripts.php'; ?>

    <!-- jQuery -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    
    <!-- Bootstrap Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
    
    <!-- Moment.js -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.4/moment.min.js"></script>
    
    <!-- Date Range Picker -->
    <script src="https://cdn.jsdelivr.net/npm/daterangepicker@3.1.0/daterangepicker.min.js"></script>
    
    <!-- DataTables -->
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.4.1/js/dataTables.responsive.min.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.4.1/js/responsive.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.3.6/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.bootstrap5.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.print.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.3.6/js/buttons.colVis.min.js"></script>
    
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js@3.9.1/dist/chart.min.js"></script>
    
    <!-- SweetAlert2 -->
    <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11.7.3/dist/sweetalert2.all.min.js"></script>
    
    <!-- App.js -->
    <script src="assets/js/app.js"></script>
    
    <script>
        $(document).ready(function() {
            // Initialize DataTable with export buttons
            var table = $('#operationalTable').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'copy',
                        className: 'btn btn-sm btn-secondary',
                        exportOptions: {
                            columns: ':visible'
                        }
                    },
                    {
                        extend: 'excel',
                        className: 'btn btn-sm btn-success',
                        exportOptions: {
                            columns: ':visible'
                        }
                    },
                    {
                        extend: 'pdf',
                        className: 'btn btn-sm btn-danger',
                        exportOptions: {
                            columns: ':visible'
                        }
                    },
                    {
                        extend: 'print',
                        className: 'btn btn-sm btn-info',
                        exportOptions: {
                            columns: ':visible'
                        }
                    },
                    {
                        extend: 'colvis',
                        className: 'btn btn-sm btn-dark',
                        text: 'Columns'
                    }
                ],
                lengthChange: true,
                responsive: true,
                pageLength: 25,
                language: {
                    paginate: {
                        previous: "<i class='mdi mdi-chevron-left'>",
                        next: "<i class='mdi mdi-chevron-right'>"
                    }
                },
                drawCallback: function() {
                    $('.dataTables_paginate > .pagination').addClass('pagination-rounded');
                }
            });
            
            // Initialize Daterangepicker and set default values
            var start = moment().subtract(30, 'days');
            var end = moment();
            
            // Check if daterange is already in URL
            var urlParams = new URLSearchParams(window.location.search);
            if (urlParams.has('daterange')) {
                var dateRangeParam = urlParams.get('daterange');
                var dates = dateRangeParam.split(' - ');
                if (dates.length === 2) {
                    start = moment(dates[0], 'MM/DD/YYYY');
                    end = moment(dates[1], 'MM/DD/YYYY');
                }
            }
            
            function cb(start, end) {
                $('#daterange').val(start.format('MM/DD/YYYY') + ' - ' + end.format('MM/DD/YYYY'));
            }
            
            $('#daterange').daterangepicker({
                startDate: start,
                endDate: end,
                ranges: {
                   'Today': [moment(), moment()],
                   'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
                   'Last 7 Days': [moment().subtract(6, 'days'), moment()],
                   'Last 30 Days': [moment().subtract(29, 'days'), moment()],
                   'This Month': [moment().startOf('month'), moment().endOf('month')],
                   'Last Month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
                }
            }, cb);
            
            cb(start, end);
            
            // Set selected values from URL parameters
            if (urlParams.has('destination')) {
                $('#destination').val(urlParams.get('destination'));
            }
            if (urlParams.has('customer')) {
                $('#customer').val(urlParams.get('customer'));
            }
            if (urlParams.has('transporter')) {
                $('#transporter').val(urlParams.get('transporter'));
            }
            if (urlParams.has('reportType')) {
                $('#reportType').val(urlParams.get('reportType'));
            }
            
            // Export report functionality
            $('#exportReport').click(function() {
                // Get filter values
                var formData = $('#reportFilters').serialize();
                
                // Add export parameter
                formData += '&export=true';
                
                // Redirect to export functionality
                window.location.href = 'export_report_ops.php?' + formData;
            });
            
            // Form submission with SweetAlert confirmation
            $('#reportFilters').on('submit', function(e) {
                // Don't immediately submit
                e.preventDefault();
                
                // Show loading message
                Swal.fire({
                    title: 'Generating Report',
                    text: 'Please wait while we process your request...',
                    allowOutsideClick: false,
                    didOpen: () => {
                        Swal.showLoading();
                    }
                });
                
                // Submit the form after a brief delay to show loading
                setTimeout(() => {
                    this.submit();
                }, 500);
            });
            
            // Initialize charts with data from the database
            initializeCharts();
        });
        
        function initializeCharts() {
            <?php
            // Prepare chart data with filters
            $where_clause = "";
            
            if (!empty($_GET['daterange'])) {
                $date_range = explode(' - ', $_GET['daterange']);
                $start_date = date('Y-m-d', strtotime($date_range[0]));
                $end_date = date('Y-m-d', strtotime($date_range[1]));
                $where_clause .= " WHERE DATE(created_date) BETWEEN '$start_date' AND '$end_date'";
            }
            
            if (!empty($_GET['customer'])) {
                $customer = intval($_GET['customer']);
                $where_clause .= (!empty($where_clause) ? " AND" : " WHERE") . " customer = $customer";
            }
            
            // Chart 1: Shipments by Destination
            $sql = "SELECT destination, COUNT(*) as count 
                    FROM files " . $where_clause . 
                    (!empty($where_clause) ? " AND" : " WHERE") . " destination != '' 
                    GROUP BY destination 
                    ORDER BY count DESC 
                    LIMIT 10";
            $result = $conn->query($sql);
            
            $destinationLabels = [];
            $destinationData = [];
            
            if ($result && $result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                    $destinationLabels[] = $row['destination'];
                    $destinationData[] = $row['count'];
                }
            }
            
            // Chart 2: Shipments by Month
            $sql = "SELECT 
                    DATE_FORMAT(created_date, '%Y-%m') as month,
                    COUNT(*) as count
                FROM files
                WHERE created_date IS NOT NULL
                " . (!empty($where_clause) ? " AND" . substr($where_clause, 6) : "") . "
                GROUP BY DATE_FORMAT(created_date, '%Y-%m')
                ORDER BY month
                LIMIT 12";
            $result = $conn->query($sql);
            
            $monthLabels = [];
            $monthData = [];
            
            if ($result && $result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                    // Format month for display
                    $date = new DateTime($row['month'] . '-01');
                    $monthLabels[] = $date->format('M Y');
                    $monthData[] = $row['count'];
                }
            }
            
            // Chart 3: Transit Time by Destination
            $sql = "SELECT f.destination, AVG(DATEDIFF(cd.delivery_date, cd.truck_exit_date)) as avg_transit 
                    FROM container_details cd
                    JOIN files f ON cd.file_number = f.file_number
                    WHERE cd.truck_exit_date IS NOT NULL AND cd.delivery_date IS NOT NULL AND f.destination != ''
                    " . (!empty($where_clause) ? " AND" . substr($where_clause, 6) : "") . "
                    GROUP BY f.destination
                    ORDER BY avg_transit DESC
                    LIMIT 10";
            $result = $conn->query($sql);
            
            $transitDestinationLabels = [];
            $transitTimeData = [];
            
            if ($result && $result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                    $transitDestinationLabels[] = $row['destination'];
                    $transitTimeData[] = round($row['avg_transit'], 1);
                }
            }
            
            // Chart 4: Top Transporters
            $sql = "SELECT 
                    COALESCE(cd.transporter_name, 'Unknown') as transporter,
                    COUNT(*) as count
                FROM container_details cd
                JOIN files f ON cd.file_number = f.file_number
                WHERE cd.transporter_name != ''
                " . (!empty($where_clause) ? " AND" . substr($where_clause, 6) : "") . "
                GROUP BY cd.transporter_name
                ORDER BY count DESC
                LIMIT 5";
            $result = $conn->query($sql);
            
            $transporterLabels = [];
            $transporterData = [];
            $transporterColors = [
                'rgba(255, 99, 132, 0.7)',
                'rgba(54, 162, 235, 0.7)',
                'rgba(255, 206, 86, 0.7)',
                'rgba(75, 192, 192, 0.7)',
                'rgba(153, 102, 255, 0.7)'
            ];
            
            if ($result && $result->num_rows > 0) {
                while($row = $result->fetch_assoc()) {
                    $transporterLabels[] = $row['transporter'];
                    $transporterData[] = $row['count'];
                }
            }
            ?>
            
            // Chart 1: Shipments by Destination
            var destinationCtx = document.getElementById('destinationChart').getContext('2d');
            if (destinationCtx) {
                var destinationChart = new Chart(destinationCtx, {
                    type: 'bar',
                    data: {
                        labels: <?php echo json_encode($destinationLabels); ?>,
                        datasets: [{
                            label: 'Shipment Count',
                            data: <?php echo json_encode($destinationData); ?>,
                            backgroundColor: 'rgba(54, 162, 235, 0.7)',
                            borderColor: 'rgba(54, 162, 235, 1)',
                            borderWidth: 1
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        scales: {
                            y: {
                                beginAtZero: true,
                                precision: 0
                            }
                        },
                        plugins: {
                            legend: {
                                display: false
                            },
                            tooltip: {
                                mode: 'index',
                                intersect: false
                            }
                        }
                    }
                });
            }
            
            // Chart 2: Shipments by Month
            var trendsCtx = document.getElementById('shipmentTrendsChart').getContext('2d');
            if (trendsCtx) {
                var trendsChart = new Chart(trendsCtx, {
                    type: 'line',
                    data: {
                        labels: <?php echo json_encode($monthLabels); ?>,
                        datasets: [{
                            label: 'Shipment Count',
                            data: <?php echo json_encode($monthData); ?>,
                            fill: false,
                            backgroundColor: 'rgba(75, 192, 192, 0.7)',
                            borderColor: 'rgba(75, 192, 192, 1)',
                            tension: 0.4,
                            pointBackgroundColor: 'rgba(75, 192, 192, 1)',
                            pointBorderColor: '#fff',
                            pointHoverBackgroundColor: '#fff',
                            pointHoverBorderColor: 'rgba(75, 192, 192, 1)'
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        scales: {
                            y: {
                                beginAtZero: true,
                                precision: 0
                            }
                        },
                        plugins: {
                            tooltip: {
                                mode: 'index',
                                intersect: false
                            }
                        }
                    }
                });
            }
            
            // Chart 3: Transit Time by Destination
            var transitCtx = document.getElementById('transitTimeChart').getContext('2d');
            if (transitCtx) {
                var transitChart = new Chart(transitCtx, {
                    type: 'bar',
                    data: {
                        labels: <?php echo json_encode($transitDestinationLabels); ?>,
                        datasets: [{
                            label: 'Avg. Transit Days',
                            data: <?php echo json_encode($transitTimeData); ?>,
                            backgroundColor: 'rgba(255, 159, 64, 0.7)',
                            borderColor: 'rgba(255, 159, 64, 1)',
                            borderWidth: 1
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        scales: {
                            y: {
                                beginAtZero: true
                            }
                        },
                        plugins: {
                            legend: {
                                display: false
                            },
                            tooltip: {
                                mode: 'index',
                                intersect: false
                            }
                        }
                    }
                });
            }
            
            // Chart 4: Top Transporters
            var transporterCtx = document.getElementById('transporterChart').getContext('2d');
            if (transporterCtx) {
                var transporterChart = new Chart(transporterCtx, {
                    type: 'pie',
                    data: {
                        labels: <?php echo json_encode($transporterLabels); ?>,
                        datasets: [{
                            data: <?php echo json_encode($transporterData); ?>,
                            backgroundColor: <?php echo json_encode($transporterColors); ?>,
                            borderWidth: 1,
                            borderColor: '#fff'
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        plugins: {
                            legend: {
                                position: 'right',
                                labels: {
                                    boxWidth: 15
                                }
                            },
                            tooltip: {
                                callbacks: {
                                    label: function(context) {
                                        var label = context.label || '';
                                        var value = context.raw || 0;
                                        var total = context.dataset.data.reduce((a, b) => a + b, 0);
                                        var percentage = Math.round((value / total) * 100);
                                        return label + ': ' + value + ' (' + percentage + '%)';
                                    }
                                }
                            }
                        }
                    }
                });
            }
        }
    </script>
</body>
</html>    