<?php
// Include database connection
include 'layouts/session.php';
include 'layouts/dbconn.php';

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

// Include FPDF
require('fpdf/fpdf.php');

// Extend FPDF with custom functions
class PDF extends FPDF
{
    // Page header
    function Header()
    {
        // Logo
        if (file_exists('assets/images/logo.png')) {
            $this->Image('assets/images/logo.png', 10, 6, 30);
        }
        
        // Arial bold 15
        $this->SetFont('Arial', 'B', 15);
        // Move to the right
        $this->Cell(80);
        // Title
        $this->Cell(30, 10, 'Royal Freight Management System', 0, 0, 'C');
        // Line break
        $this->Ln(10);
        
        // Subtitle
        $this->SetFont('Arial', 'B', 12);
        $this->Cell(80);
        $this->Cell(30, 10, 'Operational Report', 0, 0, 'C');
        $this->Ln(10);
        
        // Date
        $this->SetFont('Arial', 'I', 8);
        $this->Cell(0, 10, 'Generated on: ' . date('Y-m-d H:i:s'), 0, 0, 'R');
        $this->Ln(15);
    }
    
    // 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');
        
        // Company information
        $this->SetY(-10);
        $this->Cell(0, 10, 'Royal Freight Ltd - ' . date('Y'), 0, 0, 'C');
    }
    
    // Colored table
    function FancyTable($header, $data)
    {
        // Colors, line width and bold font
        $this->SetFillColor(66, 114, 196);
        $this->SetTextColor(255);
        $this->SetDrawColor(0, 0, 0);
        $this->SetLineWidth(0.3);
        $this->SetFont('Arial', 'B', 8);
        
        // Calculate width for each column based on data
        $w = array(30, 30, 25, 20, 25, 25, 25, 20);
        
        // Header
        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('Arial', '', 8);
        
        // 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);
            
            // Status cell with color
            switch($row[7]) {
                case 'Completed':
                    $this->SetFillColor(76, 175, 80); // Green
                    break;
                case 'Delivered':
                    $this->SetFillColor(33, 150, 243); // Blue
                    break;
                case 'In Transit':
                    $this->SetFillColor(255, 152, 0); // Orange
                    break;
                default:
                    $this->SetFillColor(158, 158, 158); // Grey
            }
            $this->SetTextColor(255);
            $this->Cell($w[7], 6, $row[7], 'LR', 0, 'C', true);
            $this->SetTextColor(0);
            $this->SetFillColor(224, 235, 255);
            
            $this->Ln();
            $fill = !$fill;
        }
        
        // Closing line
        $this->Cell(array_sum($w), 0, '', 'T');
    }
    
    // KPI Section
    function AddKPISection($kpiData)
    {
        $this->SetFont('Arial', 'B', 12);
        $this->Cell(0, 10, 'Key Performance Indicators', 0, 1, 'L');
        $this->Ln(2);
        
        $this->SetFont('Arial', '', 10);
        foreach($kpiData as $kpi => $value) {
            $this->Cell(60, 8, $kpi . ':', 0, 0, 'L');
            $this->Cell(0, 8, $value, 0, 1, 'L');
        }
        $this->Ln(5);
    }
    
    // Filter Info
    function AddFilterInfo($filters)
    {
        $this->SetFont('Arial', 'B', 10);
        $this->Cell(0, 10, 'Applied Filters', 0, 1, 'L');
        
        $this->SetFont('Arial', '', 9);
        foreach($filters as $filter => $value) {
            if (!empty($value)) {
                $this->Cell(40, 6, $filter . ':', 0, 0, 'L');
                $this->Cell(0, 6, $value, 0, 1, 'L');
            }
        }
        $this->Ln(5);
    }
}

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

// Add filter information
$filters = [
    'Date Range' => $_GET['daterange'] ?? 'All',
    'Destination' => $_GET['destination'] ?? 'All',
    'Customer' => '',
    'Transporter' => '',
    'Report Type' => $_GET['reportType'] ?? 'Summary'
];

// Get customer name if customer ID is provided
if (!empty($_GET['customer'])) {
    $customer_id = intval($_GET['customer']);
    $customer_sql = "SELECT first_name FROM customers WHERE customer_id = $customer_id";
    $customer_result = $conn->query($customer_sql);
    if ($customer_result && $customer_result->num_rows > 0) {
        $customer_row = $customer_result->fetch_assoc();
        $filters['Customer'] = $customer_row['first_name'];
    }
}

// Get transporter name if transporter ID is provided
if (!empty($_GET['transporter'])) {
    $transporter_id = intval($_GET['transporter']);
    $transporter_sql = "SELECT name FROM transporter_info WHERE id = $transporter_id";
    $transporter_result = $conn->query($transporter_sql);
    if ($transporter_result && $transporter_result->num_rows > 0) {
        $transporter_row = $transporter_result->fetch_assoc();
        $filters['Transporter'] = $transporter_row['name'];
    }
}

$pdf->AddFilterInfo($filters);

// Calculate KPIs
// Total Shipments
$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'];
}

// Average Transit Time
$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;
}

// On-Time Delivery Rate
$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);
    }
}

// Add KPI section
$kpiData = [
    'Total Shipments' => $totalShipments,
    'Average Transit Time' => $avgTransit . ' days',
    'On-Time Delivery Rate' => $onTimeRate . '%'
];
$pdf->AddKPISection($kpiData);

// Table headers
$header = ['File Number', 'Customer', 'Destination', 'ETA', 'Container Code', 'Transit Days', 'Delivery Date', 'Status'];

// 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";
}

// 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);
$data = [];

if ($result && $result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = [
            $row['file_number'],
            $row['customer_name'] ?? 'N/A',
            $row['destination'] ?? 'N/A',
            $row['eta'] ? date('Y-m-d', strtotime($row['eta'])) : '-',
            $row['container_code'] ?? 'N/A',
            $row['transit_time'] ? $row['transit_time'] : '-',
            $row['delivery_date'] ? date('Y-m-d', strtotime($row['delivery_date'])) : '-',
            $row['status']
        ];
    }
}

// Add the table to the PDF
$pdf->FancyTable($header, $data);

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