<?php
session_start();
include 'layouts/dbconn.php';

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

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

// Extend FPDF to create custom PDF
class PDFReport extends FPDF {
    // Page header
    function Header() {
        // Logo - you can change this to your company logo
        // $this->Image('logo.png', 10, 6, 30);
        
        // Font settings
        $this->SetFont('Arial', 'B', 15);
        
        // Title
        $this->Cell(0, 10, 'PROFORMA INVOICE', 0, 1, 'C');
        
        // Line break
        $this->Ln(5);
    }

    // Page footer
    function Footer() {
        // Position at 1.5 cm from bottom
        $this->SetY(-15);
        $this->SetFont('Arial', 'I', 8);
        
        // Page number
        $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
    }
    
    // File information section
    function FileInfo($file) {
        $this->SetFont('Arial', 'B', 12);
        $this->Cell(0, 10, 'File Information', 0, 1);
        
        $this->SetFont('Arial', '', 10);
        
        // File details in a table format
        $this->SetFillColor(240, 240, 240);
        $this->Cell(60, 7, 'File Number:', 1, 0, 'L', true);
        $this->Cell(130, 7, $file['file_number'], 1, 1, 'L');
        
        $this->Cell(60, 7, 'Client:', 1, 0, 'L', true);
        $this->Cell(130, 7, $file['first_name'] . ' ' . $file['last_name'], 1, 1, 'L');
        
        $this->Cell(60, 7, 'Consignee:', 1, 0, 'L', true);
        $this->Cell(130, 7, $file['consignee_name'], 1, 1, 'L');
        
        $this->Cell(60, 7, 'Destination:', 1, 0, 'L', true);
        $this->Cell(130, 7, $file['destination'], 1, 1, 'L');
        
        $this->Cell(60, 7, 'Currency:', 1, 0, 'L', true);
        $this->Cell(130, 7, $file['currency'], 1, 1, 'L');
        
        $this->Cell(60, 7, 'Shipment Type:', 1, 0, 'L', true);
        $this->Cell(130, 7, ($file['local/transit'] == 1) ? 'Local' : 'Transit', 1, 1, 'L');
        
        // Invoice information if exists
        if (isset($file['invoice_number'])) {
            $this->Ln(5);
            $this->SetFont('Arial', 'B', 12);
            $this->Cell(0, 10, 'Invoice Information', 0, 1);
            $this->SetFont('Arial', '', 10);
            
            $this->Cell(60, 7, 'Invoice Number:', 1, 0, 'L', true);
            $this->Cell(130, 7, $file['invoice_number'], 1, 1, 'L');
            
            $this->Cell(60, 7, 'Issue Date:', 1, 0, 'L', true);
            $this->Cell(130, 7, date('Y-m-d', strtotime($file['issue_date'])), 1, 1, 'L');
            
            $this->Cell(60, 7, 'Due Date:', 1, 0, 'L', true);
            $this->Cell(130, 7, date('Y-m-d', strtotime($file['due_date'])), 1, 1, 'L');
            
            $this->Cell(60, 7, 'Status:', 1, 0, 'L', true);
            $this->Cell(130, 7, ucfirst($file['status']), 1, 1, 'L');
        }
        
        // Line break
        $this->Ln(10);
    }
    
    // Charges table
    function ChargesTable($header, $data, $categories, $category_totals, $file_currency, $totals) {
        // Column widths
        $w = array(40, 25, 60, 20, 25, 25);
        
        // Header
        $this->SetFont('Arial', 'B', 10);
        $this->SetFillColor(240, 240, 240);
        
        for ($i = 0; $i < count($header); $i++) {
            $this->Cell($w[$i], 7, $header[$i], 1, 0, 'C', true);
        }
        $this->Ln();
        
        // Data
        $this->SetFont('Arial', '', 9);
        $this->SetFillColor(255, 255, 255);
        
        $current_category = '';
        
        foreach ($data as $row) {
            // If we have a new category, print the category header
            if ($current_category != $row['category']) {
                $current_category = $row['category'];
                $this->SetFont('Arial', 'B', 9);
                $this->Cell(array_sum($w), 7, $current_category, 1, 1, 'L', true);
                $this->SetFont('Arial', '', 9);
            }
            
            $this->Cell($w[0], 7, '', 1, 0, 'L');
            $this->Cell($w[1], 7, $row['cost_code'], 1, 0, 'L');
            $this->Cell($w[2], 7, $row['cost_description'] ?: $row['cost_code'], 1, 0, 'L');
            $this->Cell($w[3], 7, $row['vatable'], 1, 0, 'C');
            $this->Cell($w[4], 7, number_format($row['charge'], 2), 1, 0, 'R');
            $this->Cell($w[5], 7, number_format($row['vat_amount'], 2), 1, 0, 'R');
            $this->Ln();
        }
        
        // Category subtotals
        foreach ($categories as $category) {
            $charges = $category_totals[$category]['charges'];
            $vat = $category_totals[$category]['vat'];
            
            $this->SetFont('Arial', 'B', 9);
            $this->Cell($w[0] + $w[1] + $w[2] + $w[3], 7, $category . ' Subtotal:', 1, 0, 'R', true);
            $this->Cell($w[4], 7, number_format($charges, 2), 1, 0, 'R', true);
            $this->Cell($w[5], 7, number_format($vat, 2), 1, 0, 'R', true);
            $this->Ln();
        }
        
        // Grand total
        $this->SetFillColor(220, 220, 220);
        $this->Cell($w[0] + $w[1] + $w[2] + $w[3], 7, 'Grand Total:', 1, 0, 'R', true);
        $this->Cell($w[4], 7, number_format($totals['total_charges'], 2), 1, 0, 'R', true);
        $this->Cell($w[5], 7, number_format($totals['total_vat'], 2), 1, 0, 'R', true);
        $this->Ln();
        
        // Total including VAT
        $this->Cell($w[0] + $w[1] + $w[2] + $w[3], 7, 'Total Including VAT:', 1, 0, 'R', true);
        $this->Cell($w[4] + $w[5], 7, $file_currency . ' ' . number_format($totals['total_charges'] + $totals['total_vat'], 2), 1, 0, 'R', true);
        $this->Ln();
        
        // All-In amount if present
        if ($totals['all_in_amount'] > 0) {
            $this->SetFillColor(200, 220, 255);
            $this->Cell($w[0] + $w[1] + $w[2] + $w[3], 7, 'All-In Amount:', 1, 0, 'R', true);
            $this->Cell($w[4] + $w[5], 7, $file_currency . ' ' . number_format($totals['all_in_amount'], 2), 1, 0, 'R', true);
            $this->Ln();
        }
    }
}

// Check if file number is provided for individual export
if (isset($_GET['file_number']) && !empty($_GET['file_number'])) {
    // Individual proforma export
    $file_number = $_GET['file_number'];
    
    // Get file details
    $file_query = "SELECT f.*, c.first_name, c.last_name,
                  i.invoice_number, i.issue_date, i.due_date, i.status
                  FROM files f
                  LEFT JOIN customers c ON f.customer = c.customer_id
                  LEFT JOIN invoices i ON f.file_number = i.file_number
                  WHERE f.file_number = ?
                  ORDER BY i.created_at DESC
                  LIMIT 1";
    $file_stmt = $conn->prepare($file_query);
    $file_stmt->bind_param('s', $file_number);
    $file_stmt->execute();
    $file_result = $file_stmt->get_result();
    
    if ($file_result->num_rows === 0) {
        die("File not found");
    }
    
    $file = $file_result->fetch_assoc();
    
    // Get proforma details
    $query = "SELECT p.*, cr.description as cost_description 
              FROM proforma p 
              LEFT JOIN cost_reference cr ON p.cost_code = cr.code
              WHERE p.file_number = ?
              ORDER BY p.category, p.created_at";
    $stmt = $conn->prepare($query);
    $stmt->bind_param('s', $file_number);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $charges_data = [];
    $categories = [];
    $category_totals = [];
    
    // Group by category and calculate subtotals
    while ($row = $result->fetch_assoc()) {
        if (!in_array($row['category'], $categories)) {
            $categories[] = $row['category'];
            $category_totals[$row['category']] = [
                'charges' => 0,
                'vat' => 0
            ];
        }
        
        $category_totals[$row['category']]['charges'] += $row['charge'];
        $category_totals[$row['category']]['vat'] += $row['vat_amount'];
        
        $charges_data[] = $row;
    }
    
    // Calculate totals
    $totals_query = "SELECT SUM(charge) as total_charges, 
                            SUM(vat_amount) as total_vat,
                            MAX(CASE WHEN all_in = 'Yes' THEN all_in_amount ELSE 0 END) as all_in_amount
                     FROM proforma 
                     WHERE file_number = ?";
    $totals_stmt = $conn->prepare($totals_query);
    $totals_stmt->bind_param('s', $file_number);
    $totals_stmt->execute();
    $totals_result = $totals_stmt->get_result();
    $totals = $totals_result->fetch_assoc();
    
    // Create PDF
    $pdf = new PDFReport('P', 'mm', 'A4');
    $pdf->AliasNbPages();
    $pdf->AddPage();
    
    // Add file information
    $pdf->FileInfo($file);
    
    // Table header
    $header = array('Category', 'Cost Code', 'Description', 'Vatable', 'Charge', 'VAT Amount');
    
    // Add charges table
    $pdf->ChargesTable($header, $charges_data, $categories, $category_totals, $file['currency'], $totals);
    
    // Output the PDF
    $pdf->Output('Proforma_' . $file_number . '.pdf', 'D');
    exit;
} else {
    // Bulk export of proforma totals
    
    // Apply filters if provided
    $filter_customer = isset($_GET['customer']) ? intval($_GET['customer']) : 0;
    $filter_date_from = isset($_GET['date_from']) ? $_GET['date_from'] : '';
    $filter_date_to = isset($_GET['date_to']) ? $_GET['date_to'] : '';
    $filter_file_number = isset($_GET['file_number']) ? $_GET['file_number'] : '';
    $filter_invoice_status = isset($_GET['invoice_status']) ? $_GET['invoice_status'] : '';
    
    // Build the query with filters
    $query = "SELECT p.file_number, 
                     f.consignee_name, 
                     f.destination, 
                     f.customer,
                     c.first_name,
                     c.last_name,
                     MIN(p.created_at) as created_date,
                     f.currency,
                     SUM(p.charge) as total_charges,
                     SUM(p.vat_amount) as total_vat,
                     MAX(CASE WHEN p.all_in = 'Yes' THEN p.all_in_amount ELSE 0 END) as all_in_amount,
                     (SELECT status FROM invoices WHERE file_number = p.file_number ORDER BY created_at DESC LIMIT 1) as invoice_status
              FROM proforma p
              JOIN files f ON p.file_number = f.file_number
              LEFT JOIN customers c ON f.customer = c.customer_id
              WHERE 1=1 ";
    
    $params = [];
    $param_types = "";
    
    // Add filters to query
    if ($filter_customer > 0) {
        $query .= " AND f.customer = ? ";
        $params[] = $filter_customer;
        $param_types .= "i";
    }
    
    if (!empty($filter_date_from)) {
        $query .= " AND DATE(p.created_at) >= ? ";
        $params[] = $filter_date_from;
        $param_types .= "s";
    }
    
    if (!empty($filter_date_to)) {
        $query .= " AND DATE(p.created_at) <= ? ";
        $params[] = $filter_date_to;
        $param_types .= "s";
    }
    
    if (!empty($filter_file_number)) {
        $query .= " AND p.file_number LIKE ? ";
        $params[] = "%$filter_file_number%";
        $param_types .= "s";
    }
    
    if (!empty($filter_invoice_status)) {
        $query .= " AND (SELECT status FROM invoices WHERE file_number = p.file_number ORDER BY created_at DESC LIMIT 1) = ? ";
        $params[] = $filter_invoice_status;
        $param_types .= "s";
    }
    
    // Group by file
    $query .= " GROUP BY p.file_number, f.consignee_name, f.destination, f.customer, c.first_name, c.last_name, f.currency";
    
    // Prepare and execute the query
    $stmt = $conn->prepare($query);
    if (!empty($params)) {
        $stmt->bind_param($param_types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    
    // Calculate summary data
    $total_files = 0;
    $currencies = [];
    $invoice_status_count = [
        'pending' => 0,
        'paid' => 0,
        'cancelled' => 0,
        'no_invoice' => 0
    ];
    
    $summary_data = [];
    while ($row = $result->fetch_assoc()) {
        $total_files++;
        $summary_data[] = $row;
        
        // Keep track of totals per currency
        if (!isset($currencies[$row['currency']])) {
            $currencies[$row['currency']] = [
                'charges' => 0,
                'vat' => 0,
                'all_in' => 0
            ];
        }
        
        $currencies[$row['currency']]['charges'] += $row['total_charges'];
        $currencies[$row['currency']]['vat'] += $row['total_vat'];
        
        if ($row['all_in_amount'] > 0) {
            $currencies[$row['currency']]['all_in'] += $row['all_in_amount'];
        }
        
        // Count invoice statuses
        if (!empty($row['invoice_status'])) {
            $invoice_status_count[$row['invoice_status']]++;
        } else {
            $invoice_status_count['no_invoice']++;
        }
    }
    
    // Create PDF
    $pdf = new PDFReport('L', 'mm', 'A4'); // Landscape for report
    $pdf->AliasNbPages();
    $pdf->AddPage();
    
    // Title for report
    $pdf->SetFont('Arial', 'B', 14);
    $pdf->Cell(0, 10, 'Proforma Totals Report', 0, 1);
    
    // Report filters
    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(0, 10, 'Filters:', 0, 1);
    
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(40, 7, 'Date Range:', 0, 0);
    $pdf->Cell(0, 7, (!empty($filter_date_from) ? $filter_date_from : 'All') . ' to ' . (!empty($filter_date_to) ? $filter_date_to : 'All'), 0, 1);
    
    if ($filter_customer > 0) {
        // Get customer name
        $customer_query = "SELECT CONCAT(first_name, ' ', last_name) as name FROM customers WHERE customer_id = ?";
        $customer_stmt = $conn->prepare($customer_query);
        $customer_stmt->bind_param('i', $filter_customer);
        $customer_stmt->execute();
        $customer_result = $customer_stmt->get_result();
        $customer_name = $customer_result->fetch_assoc()['name'];
        
        $pdf->Cell(40, 7, 'Customer:', 0, 0);
        $pdf->Cell(0, 7, $customer_name, 0, 1);
    }
    
    if (!empty($filter_file_number)) {
        $pdf->Cell(40, 7, 'File Number:', 0, 0);
        $pdf->Cell(0, 7, $filter_file_number, 0, 1);
    }
    
    if (!empty($filter_invoice_status)) {
        $pdf->Cell(40, 7, 'Invoice Status:', 0, 0);
        $pdf->Cell(0, 7, ucfirst($filter_invoice_status), 0, 1);
    }
    
    $pdf->Ln(5);
    
    // Summary statistics
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 10, 'Summary:', 0, 1);
    
    $pdf->SetFont('Arial', '', 10);
    
    // Total files
    $pdf->Cell(60, 7, 'Total Files:', 1, 0, 'L', true);
    $pdf->Cell(40, 7, $total_files, 1, 1, 'L');
    
    // Currency totals
    foreach ($currencies as $currency => $values) {
        $pdf->Cell(60, 7, "Total Charges ($currency):", 1, 0, 'L', true);
        $pdf->Cell(40, 7, number_format($values['charges'], 2), 1, 1, 'L');
        
        $pdf->Cell(60, 7, "Total VAT ($currency):", 1, 0, 'L', true);
        $pdf->Cell(40, 7, number_format($values['vat'], 2), 1, 1, 'L');
        
        $pdf->Cell(60, 7, "Total All-In ($currency):", 1, 0, 'L', true);
        $pdf->Cell(40, 7, number_format($values['all_in'], 2), 1, 1, 'L');
    }
    
    // Invoice status counts
    $pdf->Cell(60, 7, "Pending Invoices:", 1, 0, 'L', true);
    $pdf->Cell(40, 7, $invoice_status_count['pending'], 1, 1, 'L');
    
    $pdf->Cell(60, 7, "Paid Invoices:", 1, 0, 'L', true);
    $pdf->Cell(40, 7, $invoice_status_count['paid'], 1, 1, 'L');
    
    $pdf->Cell(60, 7, "Cancelled Invoices:", 1, 0, 'L', true);
    $pdf->Cell(40, 7, $invoice_status_count['cancelled'], 1, 1, 'L');
    
    $pdf->Cell(60, 7, "No Invoice:", 1, 0, 'L', true);
    $pdf->Cell(40, 7, $invoice_status_count['no_invoice'], 1, 1, 'L');
    
    $pdf->Ln(10);
    
    // Main data table
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 10, 'Proforma Details:', 0, 1);
    
    // Table header
    $pdf->SetFont('Arial', 'B', 9);
    $pdf->SetFillColor(240, 240, 240);
    
    $pdf->Cell(30, 7, 'File Number', 1, 0, 'C', true);
    $pdf->Cell(40, 7, 'Client', 1, 0, 'C', true);
    $pdf->Cell(40, 7, 'Consignee', 1, 0, 'C', true);
    $pdf->Cell(30, 7, 'Destination', 1, 0, 'C', true);
    $pdf->Cell(20, 7, 'Date Created', 1, 0, 'C', true);
    $pdf->Cell(15, 7, 'Currency', 1, 0, 'C', true);
    $pdf->Cell(25, 7, 'Total Charges', 1, 0, 'C', true);
    $pdf->Cell(20, 7, 'Total VAT', 1, 0, 'C', true);
    $pdf->Cell(25, 7, 'All-In Amount', 1, 0, 'C', true);
    $pdf->Cell(20, 7, 'Invoice Status', 1, 1, 'C', true);
    
    // Table data
    $pdf->SetFont('Arial', '', 8);
    foreach ($summary_data as $row) {
        $grand_total = $row['total_charges'] + $row['total_vat'];
        $all_in_amount = $row['all_in_amount'];
        
        $pdf->Cell(30, 7, $row['file_number'], 1, 0, 'L');
        $pdf->Cell(40, 7, $row['first_name'] . ' ' . $row['last_name'], 1, 0, 'L');
        $pdf->Cell(40, 7, $row['consignee_name'], 1, 0, 'L');
        $pdf->Cell(30, 7, $row['destination'], 1, 0, 'L');
        $pdf->Cell(20, 7, date('Y-m-d', strtotime($row['created_date'])), 1, 0, 'C');
        $pdf->Cell(15, 7, $row['currency'], 1, 0, 'C');
        $pdf->Cell(25, 7, number_format($row['total_charges'], 2), 1, 0, 'R');
        $pdf->Cell(20, 7, number_format($row['total_vat'], 2), 1, 0, 'R');
        $pdf->Cell(25, 7, ($all_in_amount > 0) ? number_format($all_in_amount, 2) : 'N/A', 1, 0, 'R');
        $pdf->Cell(20, 7, !empty($row['invoice_status']) ? ucfirst($row['invoice_status']) : 'No Invoice', 1, 1, 'C');
    }
    
    // Output the PDF
    $pdf->Output('Proforma_Totals_Report.pdf', 'D');
    exit;
}
?>