<?php
require_once('fpdf/fpdf.php');

/**
 * Generate a PDF invoice and return the PDF content as a string
 * 
 * @param int $invoice_id The ID of the invoice to generate
 * @param mysqli $conn Database connection
 * @return string The PDF content
 */
function generateInvoicePDF($invoice_id, $conn) {
    // Get invoice details with explicit field selection
    $invoice_query = "SELECT i.invoice_id, i.invoice_number, i.customer_id, i.file_number, 
                      i.total_amount, i.name, i.description, i.quantity, i.price, 
                      i.issue_date, i.due_date, i.notes, i.status, i.user_id,
                      c.first_name, c.last_name, c.address, c.phone_number, 
                      c.email as client_email, f.currency, f.destination, f.obl_no, f.consignee_name,
                      u.full_name as created_by
                      FROM invoices i 
                      LEFT JOIN customers c ON i.customer_id = c.customer_id
                      LEFT JOIN files f ON i.file_number = f.file_number
                      LEFT JOIN users u ON i.user_id = u.user_id
                      WHERE i.invoice_id = ?";
    
    $invoice_stmt = $conn->prepare($invoice_query);
    $invoice_stmt->bind_param('i', $invoice_id);
    $invoice_stmt->execute();
    $invoice_result = $invoice_stmt->get_result();
    
    if ($invoice_result->num_rows === 0) {
        throw new Exception("Invoice not found");
    }
    
    $invoice = $invoice_result->fetch_assoc();
    
    // Debug log - optional, remove in production
    error_log('Invoice data: ' . json_encode($invoice));
    
    // Set fallback values for missing fields
    if (empty($invoice['name'])) {
        $invoice['name'] = 'Shipping Services';
    }
    
    if (empty($invoice['description'])) {
        $invoice['description'] = 'Logistics and freight services for shipment ' . $invoice['file_number'];
    }
    
    if (empty($invoice['quantity']) || $invoice['quantity'] <= 0) {
        $invoice['quantity'] = 1;
    }
    
    if (empty($invoice['price']) || $invoice['price'] <= 0) {
        $invoice['price'] = $invoice['total_amount'];
    }
    
    // Make sure we have a currency
    if (empty($invoice['currency'])) {
        $invoice['currency'] = '$'; // Default to USD if no currency is specified
    }
    
    // Get company details from settings
    $settings_query = "SELECT setting_key, setting_value FROM settings 
                      WHERE setting_key IN ('system_name', 'address', 'tel_1', 'tel_2')";
    $settings_result = $conn->query($settings_query);
    $settings = [];
    
    if ($settings_result->num_rows > 0) {
        while ($row = $settings_result->fetch_assoc()) {
            $settings[$row['setting_key']] = $row['setting_value'];
        }
    }
    
    // Create PDF
    $pdf = new FPDF('P', 'mm', 'A4');
    $pdf->AddPage();
    
    // Set font
    $pdf->SetFont('Arial', 'B', 14);
    
    // Company details (header)
    $pdf->SetTextColor(64, 81, 137); // Dark blue color for header
    $pdf->Cell(0, 10, isset($settings['system_name']) ? $settings['system_name'] : 'Royal Freight System', 0, 1, 'C');
    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(100, 100, 100); // Gray for address
    if (isset($settings['address'])) {
        $pdf->Cell(0, 5, $settings['address'], 0, 1, 'C');
    }
    if (isset($settings['tel_1'])) {
        $pdf->Cell(0, 5, 'Tel: ' . $settings['tel_1'] . (isset($settings['tel_2']) ? ' / ' . $settings['tel_2'] : ''), 0, 1, 'C');
    }
    
    // Invoice title
    $pdf->Ln(10);
    $pdf->SetFont('Arial', 'B', 16);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->Cell(0, 10, 'INVOICE', 0, 1, 'C');
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 6, 'Invoice #: ' . $invoice['invoice_number'], 0, 1, 'C');
    
    // Line separator
    $pdf->Ln(5);
    $pdf->SetDrawColor(200, 200, 200);
    $pdf->Line(10, $pdf->GetY(), 200, $pdf->GetY());
    $pdf->Ln(5);
    
    // Invoice details and client info in two columns
    $pdf->SetFont('Arial', 'B', 10);
    
    // Left column - Client info
    $pdf->SetTextColor(50, 50, 50);
    $pdf->Cell(95, 6, 'BILL TO:', 0, 0);
    
    // Right column - Invoice details
    $pdf->Cell(95, 6, 'INVOICE DETAILS:', 0, 1);
    
    $pdf->SetFont('Arial', '', 10);
    
    // Client details
    $pdf->SetX(10);
    $client_name = $invoice['first_name'] . ' ' . $invoice['last_name'];
    $pdf->Cell(95, 5, $client_name, 0, 0);
    
    // Invoice date
    $pdf->Cell(30, 5, 'Issue Date:', 0, 0);
    $pdf->Cell(65, 5, date('d/m/Y', strtotime($invoice['issue_date'])), 0, 1);
    
    // Client address
    $pdf->SetX(10);
    if (!empty($invoice['address'])) {
        $pdf->MultiCell(95, 5, $invoice['address'], 0, 'L');
        $pdf->SetX(105); // Reset to right column
    } else {
        $pdf->Cell(95, 5, '', 0, 0);
    }
    
    // Due date
    $pdf->Cell(30, 5, 'Due Date:', 0, 0);
    $pdf->Cell(65, 5, date('d/m/Y', strtotime($invoice['due_date'])), 0, 1);
    
    // Client contacts
    $pdf->SetX(10);
    if (!empty($invoice['phone_number'])) {
        $pdf->Cell(95, 5, 'Tel: ' . $invoice['phone_number'], 0, 0);
    } else {
        $pdf->Cell(95, 5, '', 0, 0);
    }
    
    // File number
    $pdf->Cell(30, 5, 'File Number:', 0, 0);
    $pdf->Cell(65, 5, $invoice['file_number'], 0, 1);
    
    // Client email
    $pdf->SetX(10);
    if (!empty($invoice['client_email'])) {
        $pdf->Cell(95, 5, 'Email: ' . $invoice['client_email'], 0, 0);
    } else {
        $pdf->Cell(95, 5, '', 0, 0);
    }
    
    // Status
    $pdf->Cell(30, 5, 'Status:', 0, 0);
    $pdf->SetFont('Arial', 'B', 10);
    switch($invoice['status']) {
        case 'paid':
            $pdf->SetTextColor(40, 167, 69); // green
            break;
        case 'pending':
            $pdf->SetTextColor(255, 193, 7); // yellow/amber
            break;
        case 'cancelled':
            $pdf->SetTextColor(220, 53, 69); // red
            break;
        default:
            $pdf->SetTextColor(0, 0, 0); // black
    }
    $pdf->Cell(65, 5, ucfirst($invoice['status']), 0, 1);
    $pdf->SetTextColor(0, 0, 0);
    
    // Additional shipment details if available
    if (!empty($invoice['consignee_name']) || !empty($invoice['obl_no']) || !empty($invoice['destination'])) {
        $pdf->Ln(5);
        $pdf->SetFont('Arial', 'B', 10);
        $pdf->Cell(0, 6, 'SHIPMENT DETAILS:', 0, 1);
        $pdf->SetFont('Arial', '', 10);
        
        if (!empty($invoice['consignee_name'])) {
            $pdf->Cell(30, 5, 'Consignee:', 0, 0);
            $pdf->Cell(65, 5, $invoice['consignee_name'], 0, 1);
        }
        
        if (!empty($invoice['obl_no'])) {
            $pdf->Cell(30, 5, 'BL Number:', 0, 0);
            $pdf->Cell(65, 5, $invoice['obl_no'], 0, 1);
        }
        
        if (!empty($invoice['destination'])) {
            $pdf->Cell(30, 5, 'Destination:', 0, 0);
            $pdf->Cell(65, 5, $invoice['destination'], 0, 1);
        }
    }
    
    // Invoice items table
    $pdf->Ln(10);
    $pdf->SetFont('Arial', 'B', 10);
    $pdf->SetFillColor(64, 81, 137);
    $pdf->SetTextColor(255, 255, 255);
    
    // Table header
    $pdf->Cell(95, 8, 'Description', 1, 0, 'C', true);
    $pdf->Cell(25, 8, 'Quantity', 1, 0, 'C', true);
    $pdf->Cell(30, 8, 'Unit Price', 1, 0, 'C', true);
    $pdf->Cell(40, 8, 'Amount', 1, 1, 'C', true);
    
    // Table content
    $pdf->SetFont('Arial', '', 10);
    $pdf->SetTextColor(0, 0, 0);
    $pdf->SetFillColor(245, 246, 250);
    $fill = false;
    
    // Use name or description, with fallbacks
    $description = !empty($invoice['description']) ? $invoice['description'] : 
                  (!empty($invoice['name']) ? $invoice['name'] : 
                  'Shipping services for ' . $invoice['file_number']);
    
    // If description is too long, trim it
    if (strlen($description) > 70) {
        $description = substr($description, 0, 67) . '...';
    }
    
    // Ensure quantity is at least 1
    $quantity = max(1, intval($invoice['quantity']));
    
    // Calculate unit price if it's not set
    $unit_price = $invoice['price'];
    if ($unit_price <= 0 && $quantity > 0) {
        $unit_price = $invoice['total_amount'] / $quantity;
    }
    
    // Standard single-line table row
    $pdf->Cell(95, 7, $description, 1, 0, 'L', $fill);
    $pdf->Cell(25, 7, $quantity, 1, 0, 'C', $fill);
    $pdf->Cell(30, 7, $invoice['currency'] . ' ' . number_format($unit_price, 2), 1, 0, 'R', $fill);
    $pdf->Cell(40, 7, $invoice['currency'] . ' ' . number_format($invoice['total_amount'], 2), 1, 1, 'R', $fill);
    
    // Total section
    $pdf->Ln(5);
    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(150, 7, 'TOTAL:', 0, 0, 'R');
    $pdf->Cell(40, 7, $invoice['currency'] . ' ' . number_format($invoice['total_amount'], 2), 0, 1, 'R');
    
    // Notes
    if (!empty($invoice['notes'])) {
        $pdf->Ln(10);
        $pdf->SetFont('Arial', 'B', 10);
        $pdf->Cell(0, 6, 'NOTES:', 0, 1);
        $pdf->SetFont('Arial', '', 10);
        $pdf->MultiCell(0, 5, $invoice['notes'], 0, 'L');
    }
    
    // Payment instructions
    $pdf->Ln(10);
    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(0, 6, 'PAYMENT INSTRUCTIONS:', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    $pdf->MultiCell(0, 5, "Please make payment by the due date to avoid delays in service.\nThank you for your business.", 0, 'L');
    
    // Footer
    $pdf->Ln(10);
    $pdf->SetFont('Arial', 'I', 8);
    $pdf->SetTextColor(128, 128, 128);
    $pdf->Cell(0, 5, 'This invoice was generated by ' . ($invoice['created_by'] ?? 'the system') . ' on ' . date('d/m/Y H:i'), 0, 1, 'C');
    $pdf->Cell(0, 5, 'For questions concerning this invoice, please contact accounting@royalfreight.co.tz', 0, 1, 'C');
    
    // Return PDF as string
    return $pdf->Output('S');
}
?>