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

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

// Get user role for permissions
$user_id = $_SESSION['user_id'];
$sql_user = "SELECT role_id FROM users WHERE user_id = ?";
$stmt_user = $conn->prepare($sql_user);
$stmt_user->bind_param('i', $user_id);
$stmt_user->execute();
$result_user = $stmt_user->get_result();
$role_id = ($result_user->fetch_assoc())['role_id'];

// Check if user has permission to view financial information
function hasPermission($role_id) {
    // Define roles that can access financial information
    $allowed_roles = [1, 2, 4]; // Admin, General Manager, Finance
    return in_array($role_id, $allowed_roles);
}

if (!hasPermission($role_id)) {
    $_SESSION['error_message'] = "You don't have permission to access this page.";
    header('Location: index.php');
    exit();
}

// Set default filter values
$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'] : '';
$filter_destination = isset($_GET['destination']) ? $_GET['destination'] : '';

// 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 COUNT(*) FROM invoices WHERE file_number = p.file_number) as invoice_count,
                 (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)) {
    if ($filter_invoice_status == 'no_invoice') {
        $query .= " AND NOT EXISTS (SELECT 1 FROM invoices WHERE file_number = p.file_number) ";
    } else {
        $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";
    }
}

if (!empty($filter_destination)) {
    $query .= " AND f.destination LIKE ? ";
    $params[] = "%$filter_destination%";
    $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";

// Add sorting
$query .= " ORDER BY p.created_at DESC";

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

// Fetch customers for dropdown
$customer_query = "SELECT customer_id, first_name, last_name FROM customers ORDER BY first_name, last_name";
$customer_result = $conn->query($customer_query);
$customers = [];
while ($row = $customer_result->fetch_assoc()) {
    $customers[] = $row;
}

// Fetch unique destinations for dropdown
$destination_query = "SELECT DISTINCT destination FROM files WHERE destination IS NOT NULL AND destination != '' ORDER BY destination";
$destination_result = $conn->query($destination_query);
$destinations = [];
while ($row = $destination_result->fetch_assoc()) {
    $destinations[] = $row['destination'];
}
?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Proforma Totals')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <!-- DataTables CSS -->
    <link href="assets/libs/datatables.net-bs4/css/dataTables.bootstrap4.min.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/datatables.net-buttons-bs4/css/buttons.bootstrap4.min.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/datatables.net-responsive-bs4/css/responsive.bootstrap4.min.css" rel="stylesheet" type="text/css" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
    <style>
        .filter-card {
            margin-bottom: 1.5rem;
        }
        .summary-card {
            margin-bottom: 2rem;
        }
        .summary-box {
            background-color: #f8f9fa;
            border-radius: 0.25rem;
            padding: 1rem;
            margin-bottom: 1rem;
            box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        .summary-box:hover {
            transform: translateY(-3px);
            box-shadow: 0 0.5rem 1rem rgba(0, 0, 0, 0.1);
        }
        .summary-title {
            color: #495057;
            margin-bottom: 0.5rem;
            font-size: 0.875rem;
            font-weight: 500;
        }
        .summary-value {
            font-size: 1.75rem;
            font-weight: 600;
            color: #343a40;
        }
        .badge-pending {
            background-color: #f7b84b;
            color: #fff;
        }
        .badge-paid {
            background-color: #0ab39c;
            color: #fff;
        }
        .badge-cancelled {
            background-color: #f06548;
            color: #fff;
        }
        .dt-buttons {
            margin-bottom: 15px;
        }
        .cursor-pointer {
            cursor: pointer;
        }
        .bg-light-success {
            background-color: rgba(10, 179, 156, 0.1);
        }
        .bg-light-warning {
            background-color: rgba(247, 184, 75, 0.1);
        }
        .bg-light-danger {
            background-color: rgba(240, 101, 72, 0.1);
        }
        .bg-light-secondary {
            background-color: rgba(108, 117, 125, 0.1);
        }
        .text-success {
            color: #0ab39c !important;
        }
        .text-warning {
            color: #f7b84b !important;
        }
        .text-danger {
            color: #f06548 !important;
        }
        .text-secondary {
            color: #6c757d !important;
        }
        .card-header-tab {
            padding: 1rem 1.5rem;
            background-color: #f8f9fa;
            border-bottom: 0;
        }
        .amount-value {
            font-weight: 600;
        }
        .filter-pill {
            background-color: #f8f9fa;
            border-radius: 100px;
            padding: 0.25rem 0.75rem;
            margin-right: 0.5rem;
            margin-bottom: 0.5rem;
            display: inline-block;
            font-size: 0.875rem;
            border: 1px solid #dee2e6;
        }
        .filter-pill .close {
            margin-left: 0.5rem;
            font-size: 0.75rem;
            opacity: 0.7;
        }
        .filter-pill .close:hover {
            opacity: 1;
        }
        .filter-section {
            display: flex;
            flex-wrap: wrap;
            align-items: center;
            margin-bottom: 1rem;
        }
        .filter-section .btn-sm {
            border-radius: 100px;
            padding: 0.25rem 0.75rem;
        }
        .filter-section .clear-all {
            margin-left: auto;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Financial', 'title' => 'Proforma Totals')); ?>

                    <?php if (isset($_SESSION['success_message'])): ?>
                    <div class="alert alert-success alert-dismissible fade show" role="alert">
                        <?php echo $_SESSION['success_message']; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                    <?php unset($_SESSION['success_message']); ?>
                    <?php endif; ?>

                    <?php if (isset($_SESSION['error_message'])): ?>
                    <div class="alert alert-danger alert-dismissible fade show" role="alert">
                        <?php echo $_SESSION['error_message']; ?>
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                    <?php unset($_SESSION['error_message']); ?>
                    <?php endif; ?>

                    <!-- Active Filters Display -->
                    <?php if ($filter_customer > 0 || !empty($filter_date_from) || !empty($filter_date_to) || !empty($filter_file_number) || !empty($filter_invoice_status) || !empty($filter_destination)): ?>
                    <div class="card mb-3">
                        <div class="card-body">
                            <div class="filter-section">
                                <span class="me-2"><i class="ri-filter-line me-1"></i><strong>Active Filters:</strong></span>
                                
                                <?php if ($filter_customer > 0): 
                                    $customer_name = '';
                                    foreach ($customers as $customer) {
                                        if ($customer['customer_id'] == $filter_customer) {
                                            $customer_name = $customer['first_name'] . ' ' . $customer['last_name'];
                                            break;
                                        }
                                    }
                                ?>
                                <div class="filter-pill">
                                    Customer: <?php echo htmlspecialchars($customer_name); ?>
                                    <a href="<?php echo removeQueryParam($_SERVER['REQUEST_URI'], 'customer'); ?>" class="close" aria-label="Close"><i class="ri-close-line"></i></a>
                                </div>
                                <?php endif; ?>
                                
                                <?php if (!empty($filter_date_from)): ?>
                                <div class="filter-pill">
                                    From Date: <?php echo htmlspecialchars($filter_date_from); ?>
                                    <a href="<?php echo removeQueryParam($_SERVER['REQUEST_URI'], 'date_from'); ?>" class="close" aria-label="Close"><i class="ri-close-line"></i></a>
                                </div>
                                <?php endif; ?>
                                
                                <?php if (!empty($filter_date_to)): ?>
                                <div class="filter-pill">
                                    To Date: <?php echo htmlspecialchars($filter_date_to); ?>
                                    <a href="<?php echo removeQueryParam($_SERVER['REQUEST_URI'], 'date_to'); ?>" class="close" aria-label="Close"><i class="ri-close-line"></i></a>
                                </div>
                                <?php endif; ?>
                                
                                <?php if (!empty($filter_file_number)): ?>
                                <div class="filter-pill">
                                    File Number: <?php echo htmlspecialchars($filter_file_number); ?>
                                    <a href="<?php echo removeQueryParam($_SERVER['REQUEST_URI'], 'file_number'); ?>" class="close" aria-label="Close"><i class="ri-close-line"></i></a>
                                </div>
                                <?php endif; ?>
                                
                                <?php if (!empty($filter_destination)): ?>
                                <div class="filter-pill">
                                    Destination: <?php echo htmlspecialchars($filter_destination); ?>
                                    <a href="<?php echo removeQueryParam($_SERVER['REQUEST_URI'], 'destination'); ?>" class="close" aria-label="Close"><i class="ri-close-line"></i></a>
                                </div>
                                <?php endif; ?>
                                
                                <?php if (!empty($filter_invoice_status)): ?>
                                <div class="filter-pill">
                                    Invoice Status: <?php echo $filter_invoice_status == 'no_invoice' ? 'No Invoice' : ucfirst($filter_invoice_status); ?>
                                    <a href="<?php echo removeQueryParam($_SERVER['REQUEST_URI'], 'invoice_status'); ?>" class="close" aria-label="Close"><i class="ri-close-line"></i></a>
                                </div>
                                <?php endif; ?>
                                
                                <a href="<?php echo strtok($_SERVER['REQUEST_URI'], '?'); ?>" class="btn btn-sm btn-light clear-all">
                                    <i class="ri-refresh-line me-1"></i> Clear All Filters
                                </a>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>

                    <!-- Start of filters (collapsed by default) -->
                    <div class="card filter-card">
                        <div class="card-header d-flex justify-content-between align-items-center" data-bs-toggle="collapse" data-bs-target="#filterCollapse" aria-expanded="false" aria-controls="filterCollapse" role="button">
                            <h5 class="card-title mb-0"><i class="ri-filter-line me-1"></i> Filters</h5>
                            <span><i class="ri-arrow-down-s-line"></i></span>
                        </div>
                        <div class="collapse" id="filterCollapse">
                            <div class="card-body">
                                <form method="GET" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
                                    <div class="row">
                                        <div class="col-md-4 col-lg-3">
                                            <div class="mb-3">
                                                <label for="customer" class="form-label">Customer</label>
                                                <select class="form-select select2" id="customer" name="customer">
                                                    <option value="0">All Customers</option>
                                                    <?php foreach ($customers as $customer): ?>
                                                        <option value="<?php echo $customer['customer_id']; ?>" <?php echo ($filter_customer == $customer['customer_id']) ? 'selected' : ''; ?>>
                                                            <?php echo htmlspecialchars($customer['first_name'] . ' ' . $customer['last_name']); ?>
                                                        </option>
                                                    <?php endforeach; ?>
                                                </select>
                                            </div>
                                        </div>
                                        <div class="col-md-4 col-lg-3">
                                            <div class="mb-3">
                                                <label for="date_from" class="form-label">Date From</label>
                                                <input type="text" class="form-control datepicker" id="date_from" name="date_from" value="<?php echo $filter_date_from; ?>" placeholder="YYYY-MM-DD">
                                            </div>
                                        </div>
                                        <div class="col-md-4 col-lg-3">
                                            <div class="mb-3">
                                                <label for="date_to" class="form-label">Date To</label>
                                                <input type="text" class="form-control datepicker" id="date_to" name="date_to" value="<?php echo $filter_date_to; ?>" placeholder="YYYY-MM-DD">
                                            </div>
                                        </div>
                                        <div class="col-md-4 col-lg-3">
                                            <div class="mb-3">
                                                <label for="file_number" class="form-label">File Number</label>
                                                <input type="text" class="form-control" id="file_number" name="file_number" value="<?php echo $filter_file_number; ?>" placeholder="Enter file number">
                                            </div>
                                        </div>
                                        <div class="col-md-4 col-lg-3">
                                            <div class="mb-3">
                                                <label for="destination" class="form-label">Destination</label>
                                                <select class="form-select select2" id="destination" name="destination">
                                                    <option value="">All Destinations</option>
                                                    <?php foreach ($destinations as $destination): ?>
                                                        <option value="<?php echo htmlspecialchars($destination); ?>" <?php echo ($filter_destination == $destination) ? 'selected' : ''; ?>>
                                                            <?php echo htmlspecialchars($destination); ?>
                                                        </option>
                                                    <?php endforeach; ?>
                                                </select>
                                            </div>
                                        </div>
                                        <div class="col-md-4 col-lg-3">
                                            <div class="mb-3">
                                                <label for="invoice_status" class="form-label">Invoice Status</label>
                                                <select class="form-select" id="invoice_status" name="invoice_status">
                                                    <option value="">All Statuses</option>
                                                    <option value="pending" <?php echo ($filter_invoice_status == 'pending') ? 'selected' : ''; ?>>Pending</option>
                                                    <option value="paid" <?php echo ($filter_invoice_status == 'paid') ? 'selected' : ''; ?>>Paid</option>
                                                    <option value="cancelled" <?php echo ($filter_invoice_status == 'cancelled') ? 'selected' : ''; ?>>Cancelled</option>
                                                    <option value="no_invoice" <?php echo ($filter_invoice_status == 'no_invoice') ? 'selected' : ''; ?>>No Invoice</option>
                                                </select>
                                            </div>
                                        </div>
                                    </div>
                                    <div class="d-flex justify-content-end">
                                        <a href="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" class="btn btn-light me-2">Reset</a>
                                        <button type="submit" class="btn btn-primary">Apply Filters</button>
                                    </div>
                                </form>
                            </div>
                        </div>
                    </div>
                    <!-- End of filters -->

                    <!-- Summary Cards -->
                    <?php
                    $total_files = 0;
                    $currencies = [];
                    $invoice_status_count = [
                        'pending' => 0,
                        'paid' => 0,
                        'cancelled' => 0,
                        'no_invoice' => 0
                    ];

                    // Process the data to calculate summary values
                    $result_for_summary = $result; // Create a copy for summary calculations
                    while ($row = $result_for_summary->fetch_assoc()) {
                        $total_files++;
                        
                        // Keep track of totals per currency
                        if (!isset($currencies[$row['currency']])) {
                            $currencies[$row['currency']] = [
                                'charges' => 0,
                                'vat' => 0,
                                'all_in' => 0,
                                'grand_total' => 0
                            ];
                        }
                        
                        $currencies[$row['currency']]['charges'] += $row['total_charges'];
                        $currencies[$row['currency']]['vat'] += $row['total_vat'];
                        $currencies[$row['currency']]['grand_total'] += $row['total_charges'] + $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']++;
                        }
                    }
                    
                    // Reset the result pointer
                    $result->data_seek(0);
                    ?>

                    <div class="row summary-card">
                        <!-- File count summary -->
                        <div class="col-md-3">
                            <div class="summary-box bg-light-primary">
                                <div class="summary-title">Total Files</div>
                                <div class="summary-value"><?php echo $total_files; ?></div>
                                <div class="mt-2">
                                    <span class="text-muted">Processed proforma invoices</span>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Invoice status counts -->
                        <div class="col-md-3">
                            <div class="summary-box bg-light-success">
                                <div class="summary-title">Paid Invoices</div>
                                <div class="summary-value text-success"><?php echo $invoice_status_count['paid']; ?></div>
                                <div class="mt-2">
                                    <span class="text-muted"><?php echo $invoice_status_count['paid'] > 0 ? round(($invoice_status_count['paid'] / $total_files) * 100) : 0; ?>% of total</span>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-md-3">
                            <div class="summary-box bg-light-warning">
                                <div class="summary-title">Pending Invoices</div>
                                <div class="summary-value text-warning"><?php echo $invoice_status_count['pending']; ?></div>
                                <div class="mt-2">
                                    <span class="text-muted"><?php echo $invoice_status_count['pending'] > 0 ? round(($invoice_status_count['pending'] / $total_files) * 100) : 0; ?>% of total</span>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-md-3">
                            <div class="summary-box bg-light-secondary">
                                <div class="summary-title">Without Invoice</div>
                                <div class="summary-value text-secondary"><?php echo $invoice_status_count['no_invoice']; ?></div>
                                <div class="mt-2">
                                    <span class="text-muted"><?php echo $invoice_status_count['no_invoice'] > 0 ? round(($invoice_status_count['no_invoice'] / $total_files) * 100) : 0; ?>% of total</span>
                                </div>
                            </div>
                        </div>

                        <!-- Currency summary -->
                        <?php foreach ($currencies as $currency => $values): ?>
                        <div class="col-md-6">
                            <div class="card mt-3">
                                <div class="card-header card-header-tab">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-money-dollar-circle-line me-1"></i>
                                        <?php echo htmlspecialchars($currency); ?> Summary
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="row">
                                        <div class="col-6">
                                            <div class="d-flex justify-content-between mb-3">
                                                <span>Total Charges:</span>
                                                <span class="amount-value"><?php echo number_format($values['charges'], 2); ?></span>
                                            </div>
                                            <div class="d-flex justify-content-between mb-3">
                                                <span>Total VAT:</span>
                                                <span class="amount-value"><?php echo number_format($values['vat'], 2); ?></span>
                                            </div>
                                        </div>
                                        <div class="col-6">
                                            <div class="d-flex justify-content-between mb-3">
                                                <span>Grand Total:</span>
                                                <span class="amount-value"><?php echo number_format($values['grand_total'], 2); ?></span>
                                            </div>
                                            <div class="d-flex justify-content-between">
                                                <span>All-In Amount:</span>
                                                <span class="amount-value"><?php echo number_format($values['all_in'], 2); ?></span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <?php endforeach; ?>
                    </div>
                    <!-- End Summary Cards -->

                    <!-- Totals Table -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header d-flex justify-content-between align-items-center">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-file-list-3-line me-1"></i> Proforma Invoices
                                        <?php if ($total_files > 0): ?>
                                        <span class="text-muted fs-6 ms-1">(<?php echo $total_files; ?> records)</span>
                                        <?php endif; ?>
                                    </h5>
                                    <div>
                                        <button type="button" class="btn btn-light btn-sm" data-bs-toggle="collapse" data-bs-target="#filterCollapse">
                                            <i class="ri-filter-line align-middle me-1"></i> Filters
                                        </button>
                                        <a href="view_pre_proforma_totals.php" class="btn btn-info btn-sm ms-1">
                                            <i class="ri-file-list-line align-middle me-1"></i> Pre-Proformas
                                        </a>
                                        <div class="btn-group ms-1">
                                            <button type="button" class="btn btn-primary btn-sm dropdown-toggle" data-bs-toggle="dropdown" aria-expanded="false">
                                                <i class="ri-download-line align-middle me-1"></i> Export <i class="mdi mdi-chevron-down"></i>
                                            </button>
                                            <div class="dropdown-menu">
                                                <a class="dropdown-item" href="javascript:void(0);" onclick="exportToPDF()">
                                                    <i class="ri-file-pdf-line align-middle me-1"></i> Export to PDF
                                                </a>
                                                <a class="dropdown-item" href="javascript:void(0);" onclick="exportToExcel()">
                                                    <i class="ri-file-excel-line align-middle me-1"></i> Export to Excel
                                                </a>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table id="totals-datatable" class="table table-bordered dt-responsive nowrap table-hover" style="border-collapse: collapse; border-spacing: 0; width: 100%;">
                                            <thead class="table-light">
                                                <tr>
                                                    <th>File Number</th>
                                                    <th>Client</th>
                                                    <th>Consignee</th>
                                                    <th>Destination</th>
                                                    <th>Date Created</th>
                                                    <th>Currency</th>
                                                    <th>Total Charges</th>
                                                    <th>Total VAT</th>
                                                    <th>Grand Total</th>
                                                    <th>All-In Amount</th>
                                                    <th>Invoice Status</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php
                                                if ($result->num_rows > 0) {
                                                    while ($row = $result->fetch_assoc()) {
                                                        $grand_total = $row['total_charges'] + $row['total_vat'];
                                                        $all_in_amount = $row['all_in_amount'];
                                                        $file_id_query = "SELECT id FROM files WHERE file_number = ?";
                                                        $file_id_stmt = $conn->prepare($file_id_query);
                                                        $file_id_stmt->bind_param('s', $row['file_number']);
                                                        $file_id_stmt->execute();
                                                        $file_id_result = $file_id_stmt->get_result();
                                                        $file_id = ($file_id_result->fetch_assoc())['id'];
                                                        
                                                        // Get latest invoice details
                                                        $invoice_query = "SELECT invoice_id, invoice_number, status FROM invoices 
                                                                         WHERE file_number = ? 
                                                                         ORDER BY created_at DESC LIMIT 1";
                                                        $invoice_stmt = $conn->prepare($invoice_query);
                                                        $invoice_stmt->bind_param('s', $row['file_number']);
                                                        $invoice_stmt->execute();
                                                        $invoice_result = $invoice_stmt->get_result();
                                                        $invoice = $invoice_result->fetch_assoc();
                                                ?>
                                                <tr>
                                                    <td>
                                                        <a href="proforma_details.php?file_number=<?php echo urlencode($row['file_number']); ?>" class="fw-medium">
                                                            <?php echo htmlspecialchars($row['file_number']); ?>
                                                        </a>
                                                    </td>
                                                    <td><?php echo htmlspecialchars($row['first_name'] . ' ' . $row['last_name']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['consignee_name']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['destination']); ?></td>
                                                    <td><?php echo date('Y-m-d', strtotime($row['created_date'])); ?></td>
                                                    <td class="text-center"><?php echo htmlspecialchars($row['currency']); ?></td>
                                                    <td class="text-end"><?php echo number_format($row['total_charges'], 2); ?></td>
                                                    <td class="text-end"><?php echo number_format($row['total_vat'], 2); ?></td>
                                                    <td class="text-end fw-medium"><?php echo number_format($grand_total, 2); ?></td>
                                                    <td class="text-end"><?php echo ($all_in_amount > 0) ? number_format($all_in_amount, 2) : '<span class="text-muted">N/A</span>'; ?></td>
                                                    <td class="text-center">
                                                        <?php if ($invoice): ?>
                                                            <?php if ($invoice['status'] == 'pending'): ?>
                                                                <span class="badge badge-pending">Pending</span>
                                                            <?php elseif ($invoice['status'] == 'paid'): ?>
                                                                <span class="badge badge-paid">Paid</span>
                                                            <?php elseif ($invoice['status'] == 'cancelled'): ?>
                                                                <span class="badge badge-cancelled">Cancelled</span>
                                                            <?php endif; ?>
                                                        <?php else: ?>
                                                            <span class="badge bg-secondary">No Invoice</span>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>
                                                        <div class="dropdown">
                                                            <a class="btn btn-sm btn-light dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false">
                                                                <i class="ri-more-2-fill"></i>
                                                            </a>
                                                            <ul class="dropdown-menu dropdown-menu-end">
                                                                <li>
                                                                    <a class="dropdown-item" href="proforma_details.php?file_number=<?php echo urlencode($row['file_number']); ?>">
                                                                        <i class="ri-eye-line text-primary me-2"></i> View Details
                                                                    </a>
                                                                </li>
                                                                <li>
                                                                    <a class="dropdown-item" href="view_file.php?id=<?php echo $file_id; ?>">
                                                                        <i class="ri-file-list-line text-info me-2"></i> View File
                                                                    </a>
                                                                </li>
                                                                <?php if ($invoice): ?>
                                                                <li>
                                                                    <a class="dropdown-item" href="view_invoice.php?id=<?php echo $invoice['invoice_id']; ?>">
                                                                        <i class="ri-bill-line text-warning me-2"></i> View Invoice
                                                                    </a>
                                                                </li>
                                                                <?php else: ?>
                                                                <li>
                                                                    <a class="dropdown-item" href="create_invoice.php?file_id=<?php echo $file_id; ?>">
                                                                        <i class="ri-add-line text-success me-2"></i> Create Invoice
                                                                    </a>
                                                                </li>
                                                                <?php endif; ?>
                                                                <li><hr class="dropdown-divider"></li>
                                                                <li>
                                                                    <a class="dropdown-item" href="export_proforma_detail_pdf.php?file_number=<?php echo urlencode($row['file_number']); ?>">
                                                                        <i class="ri-file-pdf-line text-danger me-2"></i> Export PDF
                                                                    </a>
                                                                </li>
                                                            </ul>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php
                                                    }
                                                } else {
                                                    echo "<tr><td colspan='12' class='text-center py-4'>
                                                        <div class='text-muted'>
                                                            <i class='ri-information-line fs-24 align-middle me-2'></i>
                                                            No proforma records found
                                                        </div>
                                                    </td></tr>";
                                                }
                                                ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <!-- End Totals Table -->
                </div>
                <!-- container-fluid -->
            </div>
            <!-- End Page-content -->

            <?php include 'layouts/footer.php'; ?>
        </div>
        <!-- end main content-->
    </div>
    <!-- END layout-wrapper -->

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <!-- DataTables JS -->
    <script src="assets/libs/datatables.net/js/jquery.dataTables.min.js"></script>
    <script src="assets/libs/datatables.net-bs4/js/dataTables.bootstrap4.min.js"></script>
    <script src="assets/libs/datatables.net-buttons/js/dataTables.buttons.min.js"></script>
    <script src="assets/libs/datatables.net-buttons-bs4/js/buttons.bootstrap4.min.js"></script>
    <script src="assets/libs/jszip/jszip.min.js"></script>
    <script src="assets/libs/pdfmake/build/pdfmake.min.js"></script>
    <script src="assets/libs/pdfmake/build/vfs_fonts.js"></script>
    <script src="assets/libs/datatables.net-buttons/js/buttons.html5.min.js"></script>
    <script src="assets/libs/datatables.net-buttons/js/buttons.print.min.js"></script>
    <script src="assets/libs/datatables.net-buttons/js/buttons.colVis.min.js"></script>
    <script src="assets/libs/datatables.net-responsive/js/dataTables.responsive.min.js"></script>
    <script src="assets/libs/datatables.net-responsive-bs4/js/responsive.bootstrap4.min.js"></script>
    
    <!-- Select2 JS -->
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    
    <!-- Flatpickr JS -->
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
    
    <!-- App js -->
    <script src="assets/js/app.js"></script>

    <script>
        $(document).ready(function() {
            // Initialize datepickers
            $(".datepicker").flatpickr({
                dateFormat: "Y-m-d",
                allowInput: true,
                altInput: true,
                altFormat: "F j, Y"
            });
            
            // Initialize select2
            $(".select2").select2({
                width: '100%'
            });
            
            // Initialize DataTable with export options
            var table = $('#totals-datatable').DataTable({
                lengthChange: true,
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'collection',
                        text: '<i class="ri-download-2-line me-1"></i> Export',
                        buttons: [
                            {
                                extend: 'copyHtml5',
                                text: '<i class="ri-file-copy-line me-1"></i> Copy',
                                titleAttr: 'Copy',
                                exportOptions: {
                                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
                                }
                            },
                            {
                                extend: 'excelHtml5',
                                text: '<i class="ri-file-excel-2-line me-1"></i> Excel',
                                titleAttr: 'Excel',
                                exportOptions: {
                                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
                                }
                            },
                            {
                                extend: 'pdfHtml5',
                                text: '<i class="ri-file-pdf-line me-1"></i> PDF',
                                titleAttr: 'PDF',
                                exportOptions: {
                                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
                                }
                            },
                            {
                                extend: 'csvHtml5',
                                text: '<i class="ri-file-text-line me-1"></i> CSV',
                                titleAttr: 'CSV',
                                exportOptions: {
                                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
                                }
                            },
                            {
                                extend: 'print',
                                text: '<i class="ri-printer-line me-1"></i> Print',
                                titleAttr: 'Print',
                                exportOptions: {
                                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
                                }
                            }
                        ]
                    }
                ],
                responsive: true,
                order: [[4, 'desc']], // Sort by date created descending
                columnDefs: [
                    { responsivePriority: 1, targets: 0 }, // File number
                    { responsivePriority: 2, targets: -1 }, // Actions
                    { responsivePriority: 3, targets: 8 }, // Grand total
                    { responsivePriority: 4, targets: 10 } // Invoice status
                ],
                language: {
                    search: "<i class='ri-search-line me-1'></i>Search:",
                    paginate: {
                        previous: "<i class='mdi mdi-chevron-left'>",
                        next: "<i class='mdi mdi-chevron-right'>"
                    }
                },
                lengthMenu: [
                    [10, 25, 50, 100, -1],
                    [10, 25, 50, 100, "All"]
                ]
            });
            
            // Move export buttons to a more visible location (hide default)
            table.buttons().container().addClass('d-none');
            
            // Show active filter accordion if filters are applied
            if (window.location.search && window.location.search.length > 1) {
                setTimeout(function() {
                    $("#filterCollapse").collapse('show');
                }, 500);
            }
        });

        // Export to PDF function
        function exportToPDF() {
            window.location.href = 'export_proforma_pdf.php?<?php echo http_build_query($_GET); ?>';
        }

        // Export to Excel function
        function exportToExcel() {
            window.location.href = 'export_proforma_excel.php?<?php echo http_build_query($_GET); ?>';
        }
        
        // Helper function to remove a query parameter from URL
        <?php
        function removeQueryParam($url, $param) {
            $parts = parse_url($url);
            if (isset($parts['query'])) {
                parse_str($parts['query'], $query);
                unset($query[$param]);
                $parts['query'] = http_build_query($query);
            }
            
            return (isset($parts['scheme']) ? $parts['scheme'] . '://' : '') .
                   (isset($parts['host']) ? $parts['host'] : '') .
                   (isset($parts['port']) ? ':' . $parts['port'] : '') .
                   (isset($parts['path']) ? $parts['path'] : '') .
                   (isset($parts['query']) && !empty($parts['query']) ? '?' . $parts['query'] : '') .
                   (isset($parts['fragment']) ? '#' . $parts['fragment'] : '');
        }
        ?>
    </script>
</body>
</html>