<?php

class ReportController extends Controller
{
    public function index(): void
    {
        Auth::requireRole(['owner', 'admin', 'accountant', 'dispatcher']);
        $db = Database::connection();
        $start = $_GET['start'] ?? date('Y-m-01');
        $end = $_GET['end'] ?? date('Y-m-t');

        $stmt = $db->prepare("SELECT COUNT(*) AS total_loads, COALESCE(SUM(rate),0) AS revenue, COALESCE(SUM(miles),0) AS miles FROM loads WHERE DATE(created_at) BETWEEN ? AND ? AND status <> 'cancelled'");
        $stmt->execute([$start, $end]);
        $summary = $stmt->fetch();

        $stmt = $db->prepare("SELECT drivers.name, COUNT(loads.id) AS loads_count, COALESCE(SUM(loads.rate),0) AS revenue, COALESCE(SUM(loads.miles),0) AS miles
                              FROM drivers LEFT JOIN loads ON loads.driver_id = drivers.id AND DATE(loads.created_at) BETWEEN ? AND ? AND loads.status <> 'cancelled'
                              GROUP BY drivers.id ORDER BY revenue DESC LIMIT 10");
        $stmt->execute([$start, $end]);
        $driverReport = $stmt->fetchAll();

        $stmt = $db->prepare("SELECT trucks.unit_number, COUNT(loads.id) AS loads_count, COALESCE(SUM(loads.rate),0) AS revenue, COALESCE(SUM(loads.miles),0) AS miles
                              FROM trucks LEFT JOIN loads ON loads.truck_id = trucks.id AND DATE(loads.created_at) BETWEEN ? AND ? AND loads.status <> 'cancelled'
                              GROUP BY trucks.id ORDER BY revenue DESC LIMIT 10");
        $stmt->execute([$start, $end]);
        $truckReport = $stmt->fetchAll();

        $stmt = $db->prepare("SELECT brokers.company_name, COUNT(loads.id) AS loads_count, COALESCE(SUM(loads.rate),0) AS revenue
                              FROM brokers LEFT JOIN loads ON loads.broker_id = brokers.id AND DATE(loads.created_at) BETWEEN ? AND ? AND loads.status <> 'cancelled'
                              GROUP BY brokers.id ORDER BY revenue DESC LIMIT 10");
        $stmt->execute([$start, $end]);
        $brokerReport = $stmt->fetchAll();

        $invoiceSummary = $db->query("SELECT status, COUNT(*) AS total, COALESCE(SUM(total),0) AS amount FROM invoices GROUP BY status")->fetchAll();
        $maintenanceCost = (float) $db->query("SELECT COALESCE(SUM(cost),0) FROM maintenance_records WHERE MONTH(service_date)=MONTH(CURDATE()) AND YEAR(service_date)=YEAR(CURDATE())")->fetchColumn();

        $this->view('reports/index', compact('start', 'end', 'summary', 'driverReport', 'truckReport', 'brokerReport', 'invoiceSummary', 'maintenanceCost'));
    }
}
