<?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 (adjust as needed)
    $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_status = isset($_GET['status']) ? $_GET['status'] : '';
$filter_file_number = isset($_GET['file_number']) ? $_GET['file_number'] : '';

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

// 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();

// Fetch data about customers for filtering
$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;
}
?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Pre-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;
        }
        .dt-buttons {
            margin-bottom: 15px;
        }
        .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);
        }
        .summary-title {
            color: #495057;
            margin-bottom: 0.5rem;
        }
        .summary-value {
            font-size: 1.5rem;
            font-weight: 600;
        }
    </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' => 'Pre-Proforma Totals')); ?>

                    <!-- Start of filters -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card filter-card">
                                <div class="card-header bg-light">
                                    <h5 class="card-title mb-0">Filters</h5>
                                </div>
                                <div class="card-body">
                                    <form method="GET" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
                                        <div class="row">
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="customer" class="form-label">Customer</label>
                                                    <select class="form-select" 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-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-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-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>
                                        <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>
                    </div>
                    <!-- End of filters -->

                    <!-- Summary Section -->
                    <div class="row summary-card">
                        <?php
                        $total_files = 0;
                        $total_amount = 0;
                        $total_vat = 0;
                        $total_all_in = 0;
                        $currencies = [];

                        // 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
                                ];
                            }
                            
                            $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'];
                            }
                        }
                        
                        // Reset the result pointer
                        $result->data_seek(0);
                        ?>

                        <div class="col-md-3">
                            <div class="summary-box">
                                <div class="summary-title">Total Files</div>
                                <div class="summary-value"><?php echo $total_files; ?></div>
                            </div>
                        </div>

                        <?php foreach ($currencies as $currency => $values): ?>
                        <div class="col-md-3">
                            <div class="summary-box">
                                <div class="summary-title">Total Charges (<?php echo $currency; ?>)</div>
                                <div class="summary-value"><?php echo number_format($values['charges'], 2); ?></div>
                            </div>
                        </div>
                        
                        <div class="col-md-3">
                            <div class="summary-box">
                                <div class="summary-title">Total VAT (<?php echo $currency; ?>)</div>
                                <div class="summary-value"><?php echo number_format($values['vat'], 2); ?></div>
                            </div>
                        </div>
                        
                        <div class="col-md-3">
                            <div class="summary-box">
                                <div class="summary-title">Total All-In (<?php echo $currency; ?>)</div>
                                <div class="summary-value"><?php echo number_format($values['all_in'], 2); ?></div>
                            </div>
                        </div>
                        <?php endforeach; ?>
                    </div>
                    <!-- End Summary Section -->

                    <!-- 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">Pre-Proforma Totals</h5>
                                    <div>
                                        <a href="javascript:void(0);" onclick="exportToPDF()" class="btn btn-danger btn-sm">
                                            <i class="ri-file-pdf-line align-middle me-1"></i> Export to PDF
                                        </a>
                                        <a href="javascript:void(0);" onclick="exportToExcel()" class="btn btn-success btn-sm ms-1">
                                            <i class="ri-file-excel-line align-middle me-1"></i> Export to Excel
                                        </a>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table id="totals-datatable" class="table table-bordered dt-responsive nowrap" style="border-collapse: collapse; border-spacing: 0; width: 100%;">
                                            <thead>
                                                <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>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'];
                                                ?>
                                                <tr>
                                                    <td><?php echo htmlspecialchars($row['file_number']); ?></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><?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 font-weight-bold"><?php echo number_format($grand_total, 2); ?></td>
                                                    <td class="text-end"><?php echo ($all_in_amount > 0) ? number_format($all_in_amount, 2) : 'N/A'; ?></td>
                                                    <td>
                                                        <div class="d-flex gap-2">
                                                            <a href="pre_proforma_details.php?file_number=<?php echo urlencode($row['file_number']); ?>" class="btn btn-sm btn-primary">
                                                                <i class="ri-eye-line"></i> Details
                                                            </a>
                                                            <a href="view_file.php?id=<?php echo $file_id; ?>" class="btn btn-sm btn-info">
                                                                <i class="ri-file-list-line"></i> File
                                                            </a>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php
                                                    }
                                                } else {
                                                    echo "<tr><td colspan='11' class='text-center'>No pre-proforma records found</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'; ?>
    <script src="assets/js/app.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/responsive.bootstrap5.min.js"></script>
<link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
<script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
<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>

    <script>
        $(document).ready(function() {
            // Initialize datepickers
            $(".datepicker").flatpickr({
                dateFormat: "Y-m-d"
            });
            
            // Initialize DataTable with export options
            var table = $('#totals-datatable').DataTable({
                lengthChange: true,
                buttons: [
                    'copy', 'excel', 'pdf', 'csv', 'print'
                ],
                responsive: true
            });
            
            // Move export buttons to a more visible location
            table.buttons().container().appendTo('#totals-datatable_wrapper .col-md-6:eq(0)');
        });

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

        // Export to Excel function
        function exportToExcel() {
            window.location.href = 'export_pre_proforma_excel.php?<?php echo http_build_query($_GET); ?>';
        }
    </script>
</body>
</html>