<?php 
include 'inc/Header.php';

$currentYear = isset($_GET['year']) ? (int)$_GET['year'] : date('Y');

// Dropdown years: current + previous 5
$yearOptions = [];
for ($i = 0; $i < 6; $i++) {
    $yearOptions[] = date('Y') - $i;
}

// Get monthly registration counts
$userCounts = array_fill(1, 12, 0);
$ownerCounts = array_fill(1, 12, 0);

// Users
$userResult = $service->query("SELECT MONTH(rdate) as month, COUNT(*) as count FROM tbl_user WHERE YEAR(rdate) = $currentYear GROUP BY MONTH(rdate)");
while ($row = $userResult->fetch_assoc()) {
    $userCounts[(int)$row['month']] = (int)$row['count'];
}

// Lorry Owners
$ownerResult = $service->query("SELECT MONTH(rdate) as month, COUNT(*) as count FROM tbl_lowner WHERE YEAR(rdate) = $currentYear GROUP BY MONTH(rdate)");
while ($row = $ownerResult->fetch_assoc()) {
    $ownerCounts[(int)$row['month']] = (int)$row['count'];
} 

$vehicleCounts = [];
$vehicleLabels = [];
$vehicleQuery =$service->query("
    SELECT v.title, COUNT(l.vehicle_id) as total 
    FROM tbl_lorry l
    JOIN tbl_vehicle v ON v.id = l.vehicle_id
    GROUP BY l.vehicle_id
");
while ($row = $vehicleQuery->fetch_assoc()) {
    $vehicleLabels[] = $row['title'];
    $vehicleCounts[] = $row['total'];
}

$vehicleCountss = [];
$vehicleLabelss = [];

$vehicleQuerys = $service->query("
    SELECT v.title, COUNT(l.vehicle_id) as total 
    FROM tbl_load l
    JOIN tbl_vehicle v ON v.id = l.vehicle_id
    WHERE YEAR(l.post_date) = $currentYear
    GROUP BY l.vehicle_id
");

while ($row = $vehicleQuerys->fetch_assoc()) {
    $vehicleLabelss[] = $row['title'];
    $vehicleCountss[] = $row['total'];
}

$stateLabels = [];
$stateCounts = [];

$stateQuery = $service->query("
    SELECT s.title, COUNT(l.pick_state_id) as total
    FROM tbl_load l
    JOIN tbl_state s ON s.id = l.pick_state_id
    WHERE YEAR(l.post_date) = $currentYear
    GROUP BY l.pick_state_id
    ORDER BY total DESC
");

while ($row = $stateQuery->fetch_assoc()) {
    $stateLabels[] = $row['title'];
    $stateCounts[] = $row['total'];
}


$dropStateLabels = [];
$dropStateCounts = [];

$dropStateQuery = $service->query("
    SELECT s.title, COUNT(l.drop_state_id) as total
    FROM tbl_load l
    JOIN tbl_state s ON s.id = l.drop_state_id
    WHERE YEAR(l.post_date) = $currentYear
    GROUP BY l.drop_state_id
    ORDER BY total DESC
");

while ($row = $dropStateQuery->fetch_assoc()) {
    $dropStateLabels[] = $row['title'];
    $dropStateCounts[] = $row['total'];
}

$loadTypeLabels = [];
$loadTypeCounts = [];

// Query to fetch count of POST_LOAD and FIND_LORRY load types
$loadTypeQuery = $service->query("
    SELECT l.load_type, COUNT(l.id) as total
    FROM tbl_load l
    WHERE l.load_type IN ('POST_LOAD', 'FIND_LORRY')
    AND  YEAR(l.post_date) = $currentYear
    GROUP BY l.load_type
");

while ($row = $loadTypeQuery->fetch_assoc()) {
    $loadTypeLabels[] = $row['load_type'];
    $loadTypeCounts[] = $row['total'];
}

$ownerVehicleCounts = [];
$ownerVehicleLabels = [];

$ownerVehicleQuery = $service->query("
    SELECT v.title AS vehicle_type, COUNT(l.id) AS owner_count
    FROM tbl_lorry l
    JOIN tbl_vehicle v ON v.id = l.vehicle_id
    GROUP BY v.title
");

while ($row = $ownerVehicleQuery->fetch_assoc()) {
    $ownerVehicleLabels[] = $row['vehicle_type'];
    $ownerVehicleCounts[] = (int)$row['owner_count'];
}

$statusCounts = [];
$statusLabels = ['Pending', 'Load_start', 'Completed', 'Accepted', 'Cancelled']; // Using your actual statuses
$statusQuery = $service->query("
    SELECT l.load_status, COUNT(l.id) as total
    FROM tbl_load l
    WHERE YEAR(l.post_date) = $currentYear
    GROUP BY l.load_status
");

$statusMap = [];
while ($row = $statusQuery->fetch_assoc()) {
    $statusMap[$row['load_status']] = (int)$row['total'];
}

// Fill any missing statuses with 0 count
foreach ($statusLabels as $status) {
    if (!isset($statusMap[$status])) {
        $statusMap[$status] = 0;
    }
}

// Now, populate the counts in the correct order
$statusCounts = array_values($statusMap);


$pickupStateCounts = [];
$pickupStateLabels = [];
$pickupStateQuery = $service->query("
    SELECT s.title, COUNT(l.pick_state_id) as total
    FROM tbl_load l
    JOIN tbl_state s ON s.id = l.pick_state_id
    WHERE YEAR(l.post_date) = $currentYear
    GROUP BY l.pick_state_id
    ORDER BY total DESC
");

while ($row = $pickupStateQuery->fetch_assoc()) {
    $pickupStateLabels[] = $row['title'];
    $pickupStateCounts[] = (int)$row['total'];
}

$dropStateCounts = [];
$dropStateLabels = [];
$dropStateQuery = $service->query("
    SELECT s.title, COUNT(l.drop_state_id) as total
    FROM tbl_load l
    JOIN tbl_state s ON s.id = l.drop_state_id
    WHERE YEAR(l.post_date) = $currentYear
    GROUP BY l.drop_state_id
    ORDER BY total DESC
");

while ($row = $dropStateQuery->fetch_assoc()) {
    $dropStateLabels[] = $row['title'];
    $dropStateCounts[] = (int)$row['total'];
}

$vehicleRevenueCounts = [];
$vehicleRevenueLabels = [];

$vehicleRevenueQuery = $service->query("
    SELECT v.title, SUM(l.total_amt) as total_revenue
    FROM tbl_load l
    JOIN tbl_vehicle v ON v.id = l.vehicle_id
    WHERE YEAR(l.post_date) = $currentYear
    GROUP BY l.vehicle_id
    ORDER BY total_revenue DESC
");

while ($row = $vehicleRevenueQuery->fetch_assoc()) {
    $vehicleRevenueLabels[] = $row['title'];  // Vehicle type
    $vehicleRevenueCounts[] = (float)$row['total_revenue'];  // Total revenue for that vehicle type
}

$userGrowthCounts = [];
$userGrowthLabels = [];

$userGrowthQuery = $service->query("
    SELECT MONTH(rdate) as month, COUNT(*) as total_users
    FROM tbl_user
    WHERE YEAR(rdate) = $currentYear
    GROUP BY MONTH(rdate)
    ORDER BY month ASC
");

while ($row = $userGrowthQuery->fetch_assoc()) {
    $userGrowthLabels[] = date('M', mktime(0, 0, 0, $row['month'], 10));  // Get month name (Jan, Feb, ...)
    $userGrowthCounts[] = (int)$row['total_users'];  // User count for that month
}

?>

    <!-- Loader ends-->
    <!-- tap on top starts-->
    <div class="tap-top">
  <svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" 
       viewBox="0 0 24 24" fill="none" stroke="currentColor" 
       stroke-width="2" stroke-linecap="round" stroke-linejoin="round">
    <polyline points="17 11 12 6 7 11"></polyline>
    <polyline points="17 18 12 13 7 18"></polyline>
  </svg>
</div>
    <!-- tap on tap ends-->
    <!-- page-wrapper Start-->
    <div class="page-wrapper compact-wrapper" id="pageWrapper">
      <!-- Page Header Start-->
     <?php include 'inc/Navbar.php'; ?>
      <!-- Page Header Ends                              -->
      <!-- Page Body Start-->
      <div class="page-body-wrapper">
        <!-- Page Sidebar Start-->
        <?php 
	   include 'inc/Sidebar.php';
	   ?>
        <!-- Page Sidebar Ends-->
        <div class="page-body">
          <div class="container-fluid">        
            <div class="page-title">
              <div class="row">
                <div class="col-12 col-sm-6">
                  <h3>Report Management</h3>
                </div>
                
              </div>
            </div>
          </div>
          <!-- Container-fluid starts-->
          <div class="container-fluid general-widget">
        <div class="row">
          <div class="col-sm-12">
      <form method="GET" class="mb-3">
        <div class="dropdown-container">
          <select name="year" onchange="this.form.submit()" class="custom-dropdown">
            <?php foreach ($yearOptions as $year): ?>
              <option value="<?= $year ?>" <?= $year == $currentYear ? 'selected' : '' ?>><?= $year ?></option>
            <?php endforeach; ?>
          </select>
        </div>
      </form>
    </div>

            <div class="row">
  <!-- User & Lorry Owner Chart Card -->
  <div class="col-md-6 ">
    <div class="card">
      <div class="card-header d-flex justify-content-between align-items-center">
        <h5 class="mb-0">User & Lorry Owner Stats - <?= $currentYear ?></h5>
       
      </div>
      <div class="card-body">
        <canvas id="regChart" height="200"></canvas>
      </div>
    </div>
  </div>

<div class="col-md-6">
  <div class="card mb-4">
    <div class="card-header">
      <h5>User Growth Over Time - <?= $currentYear ?></h5>
    </div>
    <div class="card-body">
      <canvas id="userGrowthChart" height="200"></canvas>
    </div>
  </div>
</div>

  <!-- Vehicle Pie Chart Card -->
  <div class="col-md-6">
    <div class="card">
      <div class="card-header">
        <h5 class="mb-0">Most Used Vehicle Type in Lorrys Register</h5>
      </div>
      <div class="card-body">
        <canvas id="vehiclePieChart" height="200"></canvas>
      </div>
    </div>
  </div>
  
  <div class="col-md-6"> 
    <div class="card">
      <div class="card-header">
        <h5 class="mb-0">Most Booked Vehicles - <?= $currentYear ?></h5>
      </div>
      <div class="card-body">
        <canvas id="mostBookedVehicleChart" height="200"></canvas>
      </div>
    </div>
  </div>
  
   <div class="col-md-6">
    <div class="card mb-4">
  <div class="card-header">
    <h5>Most Booked Pickup States - <?= $currentYear ?></h5>
  </div>
  <div class="card-body">
    <canvas id="statePieChart" height="200"></canvas>
  </div>
</div>
  </div>
  
  <div class="col-md-6">
    <div class="card mb-4">
  <div class="card-header">
    <h5>Most Booked Drop States - <?= $currentYear ?></h5>
  </div>
  <div class="card-body">
    <canvas id="dropStatePieChart" height="200"></canvas>
  </div>
</div>
  </div>
  
  <div class="col-md-6">
    <div class="card mb-4">
  <div class="card-header">
    <h5>Most Load Type Book - <?= $currentYear ?></h5>
  </div>
  <div class="card-body">
    <canvas id="loadTypePieChart" height="200"></canvas>
  </div>
</div>
  </div>
  
  <div class="col-md-6">
  <div class="card mb-4">
    <div class="card-header">
      <h5>Booking Status Analysis - <?= $currentYear ?></h5>
    </div>
    <div class="card-body">
      <canvas id="statusPieChart" height="200"></canvas>
    </div>
  </div>
</div>

  <div class="col-md-6">
  <div class="card mb-4">
    <div class="card-header">
      <h5>Lorry Owner vs. Vehicle Types</h5>
    </div>
    <div class="card-body">
      <canvas id="ownerVehicleChart" height="200"></canvas>
    </div>
  </div>
</div>


  
  
  <div class="col-md-6">
  <div class="card mb-4">
    <div class="card-header">
      <h5>Top Pickup States for Loads - <?= $currentYear ?></h5>
    </div>
    <div class="card-body">
      <canvas id="pickupStateChart" height="200"></canvas>
    </div>
  </div>
</div>

<div class="col-md-6">
  <div class="card mb-4">
    <div class="card-header">
      <h5>Top Drop-off States for Loads - <?= $currentYear ?></h5>
    </div>
    <div class="card-body">
      <canvas id="dropStateChart" height="200"></canvas>
    </div>
  </div>
</div>

<div class="col-md-6">
  <div class="card mb-4">
    <div class="card-header">
      <h5>Revenue by Vehicle Type - <?= $currentYear ?></h5>
    </div>
    <div class="card-body">
      <canvas id="vehicleRevenueChart" height="200"></canvas>
    </div>
  </div>
</div>


</div>
          </div>
        </div>
      </div>
          <!-- Container-fluid Ends-->
        </div>
        <!-- footer start-->
       
      </div>
    </div>
    <!-- latest jquery-->
   <?php include 'inc/Footer.php';?>
    <!-- login js-->
    <!-- Plugin used-->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
  const ctx = document.getElementById('regChart').getContext('2d');
  new Chart(ctx, {
    type: 'bar',
    data: {
      labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
      datasets: [
        {
          label: 'Users',
          data: <?= json_encode(array_values($userCounts)) ?>,
          backgroundColor: 'rgba(54, 162, 235, 0.7)'
        },
        {
          label: 'Lorry Owners',
          data: <?= json_encode(array_values($ownerCounts)) ?>,
          backgroundColor: 'rgba(255, 99, 132, 0.7)'
        }
      ]
    },
    options: {
      responsive: true,
      plugins: {
        legend: {
          position: 'top'
        }
      },
      scales: {
        y: {
          beginAtZero: true,
          ticks: {
            precision: 0
          }
        }
      }
    }
  });
  
  const vehiclePieCtx = document.getElementById('vehiclePieChart').getContext('2d');
const vehiclePieChart = new Chart(vehiclePieCtx, {
    type: 'pie',
    data: {
        labels: <?= json_encode($vehicleLabels) ?>,
        datasets: [{
            label: 'Vehicle Count',
            data: <?= json_encode($vehicleCounts) ?>,
            backgroundColor: [
                '#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0', '#9966FF'
            ],
            hoverOffset: 10
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: { display: true, text: 'Vehicle Usage in Lorries' }
        }
    }
});

const bookedVehicleCtx = document.getElementById('mostBookedVehicleChart').getContext('2d');
const bookedVehicleChart = new Chart(bookedVehicleCtx, {
    type: 'pie',
    data: {
        labels: <?= json_encode($vehicleLabelss) ?>,
        datasets: [{
            label: 'Bookings',
            data: <?= json_encode($vehicleCountss) ?>,
            backgroundColor: [
                '#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0', '#9966FF', '#FF9F40'
            ],
            hoverOffset: 10
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: { display: true, text: 'Most Booked Vehicles' }
        }
    }
});

 const statePieCtx = document.getElementById('statePieChart').getContext('2d');
  new Chart(statePieCtx, {
    type: 'pie',
    data: {
      labels: <?= json_encode($stateLabels) ?>,
      datasets: [{
        label: 'Bookings by State',
        data: <?= json_encode($stateCounts) ?>,
        backgroundColor: [
          '#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0', '#9966FF', '#FF9F40', '#C9CBCF'
        ],
        hoverOffset: 10
      }]
    },
    options: {
      responsive: true,
      plugins: {
        title: {
          display: true,
          text: 'Most Booked States (Pickups)'
        }
      }
    }
  });
  
   const dropStatePieCtx = document.getElementById('dropStatePieChart').getContext('2d');
  new Chart(dropStatePieCtx, {
    type: 'pie',
    data: {
      labels: <?= json_encode($dropStateLabels) ?>,
      datasets: [{
        label: 'Drop Bookings by State',
        data: <?= json_encode($dropStateCounts) ?>,
        backgroundColor: [
          '#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0', '#9966FF', '#FF9F40', '#C9CBCF'
        ],
        hoverOffset: 10
      }]
    },
    options: {
      responsive: true,
      plugins: {
        title: {
          display: true,
          text: 'Most Booked Drop States'
        }
      }
    }
  });
  
  
   const dropStatePieCtxs = document.getElementById('loadTypePieChart').getContext('2d');
  new Chart(dropStatePieCtxs, {
    type: 'pie',
    data: {
      labels: <?= json_encode($loadTypeLabels) ?>,
      datasets: [{
        label: 'Most Load Type Book',
        data: <?= json_encode($loadTypeCounts) ?>,
        backgroundColor: [
          '#FF6384', '#36A2EB', '#FFCE56', '#4BC0C0', '#9966FF', '#FF9F40', '#C9CBCF'
        ],
        hoverOffset: 10
      }]
    },
    options: {
      responsive: true,
      plugins: {
        title: {
          display: true,
          text: 'Most Booked Drop States'
        }
      }
    }
  });
  
  
  const ownerVehicleCtx = document.getElementById('ownerVehicleChart').getContext('2d');
new Chart(ownerVehicleCtx, {
    type: 'bar',
    data: {
        labels: <?= json_encode($ownerVehicleLabels) ?>,
        datasets: [{
            label: 'Number of Lorry Owners',
            data: <?= json_encode($ownerVehicleCounts) ?>,
            backgroundColor: 'rgba(54, 162, 235, 0.7)', // Blue color for owners
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: {
                display: true,
                text: 'Lorry Owner vs. Vehicle Types'
            },
            legend: {
                position: 'top'
            }
        },
        scales: {
            x: {
                beginAtZero: true
            },
            y: {
                beginAtZero: true
            }
        }
    }
});

const statusPieCtx = document.getElementById('statusPieChart').getContext('2d');
new Chart(statusPieCtx, {
    type: 'pie',
    data: {
        labels: <?= json_encode($statusLabels) ?>,
        datasets: [{
            label: 'Booking Statuses',
            data: <?= json_encode($statusCounts) ?>,
            backgroundColor: [
                '#FF6384', // Red for Pending
                '#36A2EB', // Blue for Load_start
                '#FFCE56', // Yellow for Completed
                '#4BC0C0', // Green for Accepted
                '#9966FF'  // Purple for Cancelled
            ],
            hoverOffset: 10
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: {
                display: true,
                text: 'Booking Status Distribution'
            }
        }
    }
});
   
   
   const pickupStateCtx = document.getElementById('pickupStateChart').getContext('2d');
new Chart(pickupStateCtx, {
    type: 'bar',
    data: {
        labels: <?= json_encode($pickupStateLabels) ?>,
        datasets: [{
            label: 'Pickup States',
            data: <?= json_encode($pickupStateCounts) ?>,
            backgroundColor: '#36A2EB',
            borderColor: '#36A2EB',
            borderWidth: 1
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: {
                display: true,
                text: 'Top Pickup States for Loads'
            }
        },
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
});

const dropStateCtx = document.getElementById('dropStateChart').getContext('2d');
new Chart(dropStateCtx, {
    type: 'bar',
    data: {
        labels: <?= json_encode($dropStateLabels) ?>,
        datasets: [{
            label: 'Drop-off States',
            data: <?= json_encode($dropStateCounts) ?>,
            backgroundColor: '#FF6384',
            borderColor: '#FF6384',
            borderWidth: 1
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: {
                display: true,
                text: 'Top Drop-off States for Loads'
            }
        },
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
});


const vehicleRevenueCtx = document.getElementById('vehicleRevenueChart').getContext('2d');
new Chart(vehicleRevenueCtx, {
    type: 'bar',
    data: {
        labels: <?= json_encode($vehicleRevenueLabels) ?>,
        datasets: [{
            label: 'Revenue ($)',
            data: <?= json_encode($vehicleRevenueCounts) ?>,
            backgroundColor: '#FFCE56',  // Yellow
            borderColor: '#FFCE56',
            borderWidth: 1
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: {
                display: true,
                text: 'Revenue by Vehicle Type'
            }
        },
        scales: {
            y: {
                beginAtZero: true,
                ticks: {
                    callback: function(value) { return '$' + value.toLocaleString(); }
                }
            }
        }
    }
});

const userGrowthCtx = document.getElementById('userGrowthChart').getContext('2d');
new Chart(userGrowthCtx, {
    type: 'line',
    data: {
        labels: <?= json_encode($userGrowthLabels) ?>,
        datasets: [{
            label: 'New Users',
            data: <?= json_encode($userGrowthCounts) ?>,
            borderColor: '#36A2EB',  // Blue
            backgroundColor: 'rgba(54, 162, 235, 0.2)',  // Light Blue
            fill: true,
            tension: 0.4
        }]
    },
    options: {
        responsive: true,
        plugins: {
            title: {
                display: true,
                text: 'User Growth Over Time (Monthly)'
            }
        },
        scales: {
            y: {
                beginAtZero: true,
                ticks: {
                    precision: 0
                }
            }
        }
    }
});

</script>
<style>
    /* General styling for the dropdown container */
.dropdown-container {
  display: inline-block;
  position: relative;
  font-family: 'Arial', sans-serif;
}

/* Custom dropdown styling */
.custom-dropdown {
  padding: 12px 25px;
  font-size: 16px;
  font-weight: 600;
  border-radius: 12px;
  border: 2px solid #007bff;
  background-color: #f9f9f9;
  color: #333;
  cursor: pointer;
  transition: all 0.3s ease-in-out;
  appearance: none;
  -webkit-appearance: none;
  -moz-appearance: none;
  width: auto;
  max-width: 250px;
  box-shadow: 0 2px 8px rgba(0, 123, 255, 0.1);
}

/* Hover effect for better interactivity */
.custom-dropdown:hover {
  border-color: #0056b3;
  background-color: #e3f2fd;
}

/* Focus effect to show when selected */
.custom-dropdown:focus {
  outline: none;
  box-shadow: 0 0 5px rgba(0, 123, 255, 0.6);
  border-color: #0056b3;
  background-color: #e3f2fd;
}

/* Custom arrow styling */
.custom-dropdown::after {
  content: '\f107'; /* Font Awesome arrow icon */
  font-family: 'FontAwesome';
  position: absolute;
  right: 15px;
  top: 50%;
  transform: translateY(-50%);
  pointer-events: none;
  color: #007bff;
}

/* Optional: Styling for small screens */
@media (max-width: 767px) {
  .custom-dropdown {
    width: 100%;
    font-size: 14px;
  }
}

/* Adding a smooth transition effect */
.custom-dropdown {
  transition: all 0.4s ease;
}

/* Add transition for focus and hover */
.custom-dropdown:focus, .custom-dropdown:hover {
  transform: scale(1.05);
}
</style>
  </body>

</html>