<?php
include 'layouts/session.php';
include 'layouts/dbconn.php';
require('fpdf/fpdf.php');

// Check if the user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Get the filter parameters
$daterange = isset($_GET['daterange']) ? $_GET['daterange'] : '';
$destination = isset($_GET['destination']) ? $_GET['destination'] : '';
$customer = isset($_GET['customer']) ? $_GET['customer'] : '';
$transporter = isset($_GET['transporter']) ? $_GET['transporter'] : '';
$reportType = isset($_GET['reportType']) ? $_GET['reportType'] : 'summary';

// Process date range
$startDate = null;
$endDate = null;
if (!empty($daterange)) {
    $dates = explode(' - ', $daterange);
    if (count($dates) == 2) {
        $startDate = date('Y-m-d', strtotime($dates[0]));
        $endDate = date('Y-m-d', strtotime($dates[1]));
    }
}

// Custom PDF class with header and footer
class OperationalReportPDF extends FPDF {
    // Page header
    function Header() {
        // Logo
        //$this->Image('assets/images/logo.png', 10, 6, 30);
        
        // Arial bold 15
        $this->SetFont('Arial', 'B', 15);
        
        // Move to the right
        $this->Cell(60);
        
        // Title
        $this->Cell(80, 10, 'Royal Freight Operational Report', 0, 0, 'C');
        
        // Date
        $this->SetFont('Arial', 'I', 10);
        $this->Cell(50, 10, 'Generated: ' . date('Y-m-d H:i'), 0, 0, 'R');
        
        // Line break
        $this->Ln(20);
    }

    // Page footer
    function Footer() {
        // Position at 1.5 cm from bottom
        $this->SetY(-15);
        
        // Arial italic 8
        $this->SetFont('Arial', 'I', 8);
        
        // Page number
        $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
    }
    
    // Better table
    function ImprovedTable($header, $data) {
        // Colors, line width and bold font
        $this->SetFillColor(52, 144, 220);
        $this->SetTextColor(255);
        $this->SetDrawColor(128, 128, 128);
        $this->SetLineWidth(.3);
        $this->SetFont('', 'B');
        
        // Header
        $w = array(25, 35, 25, 25, 40, 20, 25);
        for($i=0; $i<count($header); $i++)
            $this->Cell($w[$i], 7, $header[$i], 1, 0, 'C', true);
        $this->Ln();
        
        // Color and font restoration
        $this->SetFillColor(224, 235, 255);
        $this->SetTextColor(0);
        $this->SetFont('');
        
        // Data
        $fill = false;
        foreach($data as $row) {
            $this->Cell($w[0], 6, $row[0], 'LR', 0, 'L', $fill);
            $this->Cell($w[1], 6, $row[1], 'LR', 0, 'L', $fill);
            $this->Cell($w[2], 6, $row[2], 'LR', 0, 'L', $fill);
            $this->Cell($w[3], 6, $row[3], 'LR', 0, 'C', $fill);
            $this->Cell($w[4], 6, $row[4], 'LR', 0, 'L', $fill);
            $this->Cell($w[5], 6, $row[5], 'LR', 0, 'C', $fill);
            $this->Cell($w[6], 6, $row[6], 'LR', 0, 'C', $fill);
            $this->Ln();
            $fill = !$fill;
        }
        
        // Closing line
        $this->Cell(array_sum($w), 0, '', 'T');
    }
}

// Create PDF instance
$pdf = new OperationalReportPDF('L', 'mm', 'A4');
$pdf->AliasNbPages();
$pdf->AddPage();
$pdf->SetFont('Arial', '', 10);

// Add report filters information
$pdf->SetFont('Arial', 'B', 12);
$pdf->Cell(0, 10, 'Report Filters:', 0, 1);
$pdf->SetFont('Arial', '', 10);

$pdf->Cell(40, 6, 'Date Range:', 0, 0);
$pdf->Cell(0, 6, $daterange ? $daterange : 'All Time', 0, 1);

$pdf->Cell(40, 6, 'Destination:', 0, 0);
$pdf->Cell(0, 6, $destination ? $destination : 'All Destinations', 0, 1);

$pdf->Cell(40, 6, 'Customer:', 0, 0);
if ($customer) {
    $sql = "SELECT first_name FROM customers WHERE customer_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $customer);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $pdf->Cell(0, 6, $row['first_name'], 0, 1);
    } else {
        $pdf->Cell(0, 6, 'Unknown Customer', 0, 1);
    }
} else {
    $pdf->Cell(0, 6, 'All Customers', 0, 1);
}

$pdf->Cell(40, 6, 'Transporter:', 0, 0);
if ($transporter) {
    $sql = "SELECT name FROM transporter_info WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $transporter);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $pdf->Cell(0, 6, $row['name'], 0, 1);
    } else {
        $pdf->Cell(0, 6, 'Unknown Transporter', 0, 1);
    }
} else {
    $pdf->Cell(0, 6, 'All Transporters', 0, 1);
}

$pdf->Cell(40, 6, 'Report Type:', 0, 0);
$pdf->Cell(0, 6, ucfirst($reportType) . ' Report', 0, 1);

$pdf->Ln(10);

// Build the query based on filters
$query = "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 1=1";

$params = array();
$types = "";

if ($startDate && $endDate) {
    $query .= " AND f.created_date BETWEEN ? AND ?";
    $params[] = $startDate;
    $params[] = $endDate;
    $types .= "ss";
}

if ($destination) {
    $query .= " AND f.destination = ?";
    $params[] = $destination;
    $types .= "s";
}

if ($customer) {
    $query .= " AND f.customer = ?";
    $params[] = $customer;
    $types .= "i";
}

if ($transporter) {
    $query .= " AND (cd.transporter_name = ? OR f.transporter = ?)";
    
    // Get transporter name from ID
    $transporterName = "";
    $sql = "SELECT name FROM transporter_info WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $transporter);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $transporterName = $row['name'];
    }
    
    $params[] = $transporterName;
    $params[] = $transporter;
    $types .= "ss";
}

$query .= " ORDER BY f.created_date DESC LIMIT 1000";

// Execute the query
$stmt = $conn->prepare($query);
if (!empty($params)) {
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

// Add KPI summary based on report type
$pdf->SetFont('Arial', 'B', 12);
$pdf->Cell(0, 10, 'Key Performance Indicators:', 0, 1);
$pdf->SetFont('Arial', '', 10);

// Total shipments count
$countQuery = "SELECT COUNT(*) as total FROM files";
$countResult = $conn->query($countQuery);
$totalShipments = 0;
if ($row = $countResult->fetch_assoc()) {
    $totalShipments = $row['total'];
}

// Average transit time
$transitQuery = "SELECT AVG(DATEDIFF(delivery_date, truck_exit_date)) as avg_transit 
                FROM container_details 
                WHERE truck_exit_date IS NOT NULL AND delivery_date IS NOT NULL";
$transitResult = $conn->query($transitQuery);
$avgTransit = 0;
if ($row = $transitResult->fetch_assoc()) {
    $avgTransit = round($row['avg_transit'], 1);
}

// On-time delivery rate
$onTimeQuery = "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN cd.delivery_date <= DATE_ADD(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 cd.delivery_date IS NOT NULL AND eta IS NOT NULL";
$onTimeResult = $conn->query($onTimeQuery);
$onTimeRate = 0;
if ($row = $onTimeResult->fetch_assoc()) {
    if ($row['total'] > 0) {
        $onTimeRate = round(($row['on_time'] / $row['total']) * 100, 1);
    }
}

// 2-column layout for KPIs
$pdf->Cell(90, 6, 'Total Shipments: ' . $totalShipments, 0, 0);
$pdf->Cell(90, 6, 'Average Transit Time: ' . $avgTransit . ' days', 0, 1);
$pdf->Cell(90, 6, 'On-Time Delivery Rate: ' . $onTimeRate . '%', 0, 0);

// Container utilization
$utilizationQuery = "SELECT COUNT(*) as total_containers,
                    SUM(CASE WHEN delivery_date IS NOT NULL THEN 1 ELSE 0 END) as delivered
                    FROM container_details";
$utilizationResult = $conn->query($utilizationQuery);
$utilization = 0;
if ($row = $utilizationResult->fetch_assoc()) {
    if ($row['total_containers'] > 0) {
        $utilization = round(($row['delivered'] / $row['total_containers']) * 100, 1);
    }
}
$pdf->Cell(90, 6, 'Container Utilization: ' . $utilization . '%', 0, 1);

$pdf->Ln(10);

// Add the operational data table
$pdf->SetFont('Arial', 'B', 12);
$pdf->Cell(0, 10, 'Operational Data:', 0, 1);

// Prepare the table header and data
$header = array('File Number', 'Customer', 'Destination', 'ETA', 'Container', 'Transit', 'Delivery');
$data = array();

while ($row = $result->fetch_assoc()) {
    $eta = $row['eta'] ? date('Y-m-d', strtotime($row['eta'])) : '-';
    $transitTime = $row['transit_time'] ? $row['transit_time'] . ' days' : '-';
    $deliveryDate = $row['delivery_date'] ? date('Y-m-d', strtotime($row['delivery_date'])) : '-';
    
    $data[] = array(
        $row['file_number'],
        $row['customer_name'],
        $row['destination'],
        $eta,
        $row['container_code'],
        $transitTime,
        $deliveryDate
    );
}

$pdf->ImprovedTable($header, $data);

// If the report type is detailed, add more information
if ($reportType == 'detailed' || $reportType == 'performance') {
    $pdf->AddPage();
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 10, 'Performance Analysis:', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    
    // Top destinations by volume
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->Cell(0, 10, 'Top Destinations by Volume:', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    
    $destQuery = "SELECT destination, COUNT(*) as count 
                FROM files 
                WHERE destination != '' 
                GROUP BY destination 
                ORDER BY count DESC 
                LIMIT 10";
    $destResult = $conn->query($destQuery);
    
    if ($destResult->num_rows > 0) {
        $pdf->Cell(60, 6, 'Destination', 1, 0, 'C', true);
        $pdf->Cell(30, 6, 'Count', 1, 1, 'C', true);
        
        $fill = false;
        while($row = $destResult->fetch_assoc()) {
            $pdf->Cell(60, 6, $row['destination'], 1, 0, 'L', $fill);
            $pdf->Cell(30, 6, $row['count'], 1, 1, 'C', $fill);
            $fill = !$fill;
        }
    }
    
    $pdf->Ln(10);
    
    // Top transporters by volume
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->Cell(0, 10, 'Top Transporters by Volume:', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    
    $transQuery = "SELECT 
                COALESCE(cd.transporter_name, 'Unknown') as transporter,
                COUNT(*) as count
            FROM container_details cd
            WHERE cd.transporter_name != ''
            GROUP BY cd.transporter_name
            ORDER BY count DESC
            LIMIT 10";
    $transResult = $conn->query($transQuery);
    
    if ($transResult->num_rows > 0) {
        $pdf->Cell(80, 6, 'Transporter', 1, 0, 'C', true);
        $pdf->Cell(30, 6, 'Count', 1, 1, 'C', true);
        
        $fill = false;
        while($row = $transResult->fetch_assoc()) {
            $pdf->Cell(80, 6, $row['transporter'], 1, 0, 'L', $fill);
            $pdf->Cell(30, 6, $row['count'], 1, 1, 'C', $fill);
            $fill = !$fill;
        }
    }
    
    $pdf->Ln(10);
    
    // Transit time by destination
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->Cell(0, 10, 'Average Transit Time by Destination:', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    
    $transitDestQuery = "SELECT f.destination, AVG(DATEDIFF(cd.delivery_date, cd.truck_exit_date)) as avg_transit,
                        COUNT(*) as count 
                    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 != ''
                    GROUP BY f.destination
                    ORDER BY avg_transit DESC
                    LIMIT 10";
    $transitDestResult = $conn->query($transitDestQuery);
    
    if ($transitDestResult->num_rows > 0) {
        $pdf->Cell(60, 6, 'Destination', 1, 0, 'C', true);
        $pdf->Cell(40, 6, 'Avg. Transit Days', 1, 0, 'C', true);
        $pdf->Cell(30, 6, 'Shipment Count', 1, 1, 'C', true);
        
        $fill = false;
        while($row = $transitDestResult->fetch_assoc()) {
            $pdf->Cell(60, 6, $row['destination'], 1, 0, 'L', $fill);
            $pdf->Cell(40, 6, round($row['avg_transit'], 1), 1, 0, 'C', $fill);
            $pdf->Cell(30, 6, $row['count'], 1, 1, 'C', $fill);
            $fill = !$fill;
        }
    }
}

// Output the PDF
$pdf->Output('operational_report_' . date('Y-m-d') . '.pdf', 'D');
?>