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

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

// Get user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT username, user_role, full_name, email FROM users WHERE user_id = $user_id";
$result_user_details = $conn->query($sql_user_details);

if ($result_user_details && $row_user_details = $result_user_details->fetch_assoc()) {
    $username = $row_user_details['username'];
    $user_role = $row_user_details['user_role'];
    $full_name = $row_user_details['full_name'];
    $email = $row_user_details['email'];
} else {
    header('Location: login.php');
    exit();
}

// Handle filters
$start_date = $_GET['start_date'] ?? '';
$end_date = $_GET['end_date'] ?? '';
$status = $_GET['status'] ?? 'all';
$shipping_line = $_GET['shipping_line'] ?? '';

// Build query
$query = "SELECT 
            cd.*, 
            f.file_number,
            f.consignee_name,
            f.obl_no,
            COALESCE(cd.refund_amount, 0) as refunded_amount,
            COALESCE(cd.refund_date, '') as refund_date,
            CASE 
                WHEN cd.refund_date IS NOT NULL THEN 'Refunded'
                WHEN cd.date_deposited <= DATE_SUB(NOW(), INTERVAL cd.free_days DAY) THEN 'Overdue'
                ELSE 'Active'
            END as deposit_status,
            DATEDIFF(NOW(), cd.date_deposited) as days_held,
            cd.free_days - DATEDIFF(NOW(), cd.date_deposited) as days_remaining
          FROM container_deposit cd
          LEFT JOIN files f ON cd.bl = f.obl_no
          WHERE 1=1";

$params = [];
$types = "";

if ($start_date) {
    $query .= " AND cd.date_deposited >= ?";
    $params[] = $start_date;
    $types .= "s";
}

if ($end_date) {
    $query .= " AND cd.date_deposited <= ?";
    $params[] = $end_date;
    $types .= "s";
}

if ($status !== 'all') {
    if ($status === 'active') {
        $query .= " AND cdr.refund_date IS NULL AND cd.date_deposited > DATE_SUB(NOW(), INTERVAL cd.free_days DAY)";
    } elseif ($status === 'overdue') {
        $query .= " AND cdr.refund_date IS NULL AND cd.date_deposited <= DATE_SUB(NOW(), INTERVAL cd.free_days DAY)";
    } elseif ($status === 'refunded') {
        $query .= " AND cdr.refund_date IS NOT NULL";
    }
}

if ($shipping_line) {
    $query .= " AND cd.shipping_line = ?";
    $params[] = $shipping_line;
    $types .= "s";
}

$query .= " ORDER BY cd.date_deposited DESC";

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

// Get shipping lines for filter
$shipping_lines_query = "SELECT DISTINCT shipping_line FROM container_deposit ORDER BY shipping_line";
$shipping_lines_result = $conn->query($shipping_lines_query);

?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Container Deposit Report')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <!-- DataTables CSS -->
    <link href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/buttons/2.2.2/css/buttons.bootstrap5.min.css" rel="stylesheet">
    
    <!-- Select2 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
</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' => 'Reports', 'title' => 'Container Deposit Report')); ?>

                    <!-- Filters Card -->
                    <div class="card">
                        <div class="card-header">
                            <h4 class="card-title mb-0">Filter Options</h4>
                        </div>
                        <div class="card-body">
                            <form method="GET" class="row g-3">
                                <div class="col-md-3">
                                    <label class="form-label">Start Date</label>
                                    <input type="date" class="form-control" name="start_date" value="<?php echo $start_date; ?>">
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">End Date</label>
                                    <input type="date" class="form-control" name="end_date" value="<?php echo $end_date; ?>">
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Status</label>
                                    <select class="form-select" name="status">
                                        <option value="all" <?php echo $status === 'all' ? 'selected' : ''; ?>>All</option>
                                        <option value="active" <?php echo $status === 'active' ? 'selected' : ''; ?>>Active</option>
                                        <option value="overdue" <?php echo $status === 'overdue' ? 'selected' : ''; ?>>Overdue</option>
                                        <option value="refunded" <?php echo $status === 'refunded' ? 'selected' : ''; ?>>Refunded</option>
                                    </select>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label">Shipping Line</label>
                                    <select class="form-select" name="shipping_line">
                                        <option value="">All</option>
                                        <?php while($line = $shipping_lines_result->fetch_assoc()): ?>
                                            <option value="<?php echo htmlspecialchars($line['shipping_line']); ?>"
                                                    <?php echo $shipping_line === $line['shipping_line'] ? 'selected' : ''; ?>>
                                                <?php echo htmlspecialchars($line['shipping_line']); ?>
                                            </option>
                                        <?php endwhile; ?>
                                    </select>
                                </div>
                                <div class="col-12">
                                    <button type="submit" class="btn btn-primary">Apply Filters</button>
                                    <a href="container_deposit_report.php" class="btn btn-secondary">Reset</a>
                                    <button type="button" class="btn btn-success" onclick="exportToExcel()">
                                        <i class="ri-file-excel-line align-middle"></i> Export to Excel
                                    </button>
                                </div>
                            </form>
                        </div>
                    </div>

                    <!-- Summary Cards Row -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Total Deposits</h6>
                                            <h2 class="mb-0">$<span class="counter-value" data-target="<?php 
                                                $total = 0;
                                                $result->data_seek(0);
                                                while($row = $result->fetch_assoc()) {
                                                    $total += $row['deposit_amount'];
                                                }
                                                echo number_format($total, 2);
                                            ?>">0</span></h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded bg-primary">
                                                <span class="avatar-title bg-soft-primary text-primary rounded fs-4">
                                                    <i class="ri-money-dollar-circle-line"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Total Refunded</h6>
                                            <h2 class="mb-0">$<span class="counter-value" data-target="<?php 
                                                $refunded = 0;
                                                $result->data_seek(0);
                                                while($row = $result->fetch_assoc()) {
                                                    $refunded += $row['refunded_amount'];
                                                }
                                                echo number_format($refunded, 2);
                                            ?>">0</span></h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded bg-success">
                                                <span class="avatar-title bg-soft-success text-success rounded fs-4">
                                                    <i class="ri-arrow-left-circle-line"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Outstanding Amount</h6>
                                            <h2 class="mb-0">$<span class="counter-value" data-target="<?php 
                                                echo number_format($total - $refunded, 2);
                                            ?>">0</span></h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded bg-warning">
                                                <span class="avatar-title bg-soft-warning text-warning rounded fs-4">
                                                    <i class="ri-time-line"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <h6 class="text-muted mb-3">Overdue Containers</h6>
                                            <h2 class="mb-0"><span class="counter-value" data-target="<?php 
                                                $overdue = 0;
                                                $result->data_seek(0);
                                                while($row = $result->fetch_assoc()) {
                                                    if($row['deposit_status'] === 'Overdue') {
                                                        $overdue++;
                                                    }
                                                }
                                                echo $overdue;
                                            ?>">0</span></h2>
                                        </div>
                                        <div class="flex-shrink-0">
                                            <div class="avatar-sm rounded bg-danger">
                                                <span class="avatar-title bg-soft-danger text-danger rounded fs-4">
                                                    <i class="ri-alert-line"></i>
                                                </span>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Report Table Card -->
                    <div class="card">
                        <div class="card-header">
                            <h4 class="card-title mb-0">Container Deposits List</h4>
                        </div>
                        <div class="card-body">
                            <div class="table-responsive">
                                <table id="deposit-report-table" class="table table-bordered dt-responsive nowrap table-striped align-middle">
                                    <thead>
                                        <tr>
                                            <th>File Number</th>
                                            <th>Container Number</th>
                                            <th>Shipping Line</th>
                                            <th>Deposit Amount</th>
                                            <th>Deposit Date</th>
                                            <th>Free Days</th>
                                            <th>Days Held</th>
                                            <th>Days Remaining</th>
                                            <th>Status</th>
                                            <th>Refund Amount</th>
                                            <th>Refund Date</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php 
                                        $result->data_seek(0);
                                        while($row = $result->fetch_assoc()): 
                                        ?>
                                        <tr>
                                            <td>
                                                <a href="view_file.php?id=<?php echo $row['id']; ?>">
                                                    <?php echo htmlspecialchars($row['file_number']); ?>
                                                </a>
                                            </td>
                                            <td><?php echo htmlspecialchars($row['container_number']); ?></td>
                                            <td><?php echo htmlspecialchars($row['shipping_line']); ?></td>
                                            <td><?php echo number_format($row['deposit_amount'], 2); ?></td>
                                            <td><?php echo date('Y-m-d', strtotime($row['deposit_date'])); ?></td>
                                            <td><?php echo $row['free_days']; ?></td>
                                            <td><?php echo $row['days_held']; ?></td>
                                            <td>
                                                <?php if($row['deposit_status'] !== 'Refunded'): ?>
                                                    <?php echo $row['days_remaining']; ?>
                                                <?php else: ?>
                                                    -
                                                <?php endif; ?>
                                            </td>
                                            <td>
                                                <span class="badge bg-<?php 
                                                    echo $row['deposit_status'] === 'Active' ? 'success' : 
                                                        ($row['deposit_status'] === 'Overdue' ? 'danger' : 'info');
                                                ?>">
                                                    <?php echo $row['deposit_status']; ?>
                                                </span>
                                            </td>
                                            <td><?php echo $row['refunded_amount'] ? number_format($row['refunded_amount'], 2) : '-'; ?></td>
                                            <td><?php echo $row['refund_date'] ? date('Y-m-d', strtotime($row['refund_date'])) : '-'; ?></td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <!-- Required datatable 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>
    
    <!-- Buttons examples -->
    <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/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/js/app.js"></script>
    
    <script>
        $(document).ready(function() {
            // Initialize DataTable with your existing configuration
            var table = $('#deposit-report-table').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'collection',
                        text: '<i class="ri-download-2-line"></i> Export',
                        buttons: [
                            'copy',
                            'excel',
                            'csv',
                            'pdf',
                            'print'
                        ]
                    }
                ],
                pageLength: 25,
                order: [[4, 'desc']], // Sort by deposit date by default
                responsive: true,
                language: {
                    search: '<i class="ri-search-line"></i>',
                    searchPlaceholder: "Search..."
                }
            });

            // Initialize Select2 for select elements
            $('.form-select').select2({
                width: '100%'
            });
        });
    </script>
</body>
</html> 