<?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'];
}

// 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(cdr.refund_amount, 0) as refunded_amount,
            COALESCE(cdr.refund_date, '') as refund_date,
            CASE 
                WHEN cdr.refund_date IS NOT NULL THEN 'Refunded'
                WHEN cd.deposit_date <= DATE_SUB(NOW(), INTERVAL cd.free_days DAY) THEN 'Overdue'
                ELSE 'Active'
            END as deposit_status,
            DATEDIFF(NOW(), cd.deposit_date) as days_held,
            cd.free_days - DATEDIFF(NOW(), cd.deposit_date) as days_remaining
          FROM container_deposits cd
          LEFT JOIN files f ON cd.file_number = f.file_number
          LEFT JOIN container_deposit_refunds cdr ON cd.id = cdr.deposit_id
          WHERE 1=1";

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

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

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

if ($status !== 'all') {
    if ($status === 'active') {
        $query .= " AND cdr.refund_date IS NULL AND cd.deposit_date > DATE_SUB(NOW(), INTERVAL cd.free_days DAY)";
    } elseif ($status === 'overdue') {
        $query .= " AND cdr.refund_date IS NULL AND cd.deposit_date <= 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.deposit_date 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_deposits ORDER BY shipping_line";
$shipping_lines_result = $conn->query($shipping_lines_query);

include 'layouts/main.php';
?>

<div class="main-content">
    <div class="page-content">
        <div class="container-fluid">
            <div class="row">
                <div class="col-12">
                    <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                        <h4 class="mb-sm-0">Container Deposit Report</h4>
                    </div>
                </div>
            </div>

            <!-- Filters -->
            <div class="card">
                <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 -->
            <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 -->
            <div class="card">
                <div class="card-body">
                    <div class="table-responsive">
                        <table id="deposit-report-table" class="table table-bordered table-striped">
                            <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>
</div>

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

<script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script>
<script>
$(document).ready(function() {
    $('#deposit-report-table').DataTable({
        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ],
        pageLength: 25
    });
});

function exportToExcel() {
    const table = document.getElementById('deposit-report-table');
    const wb = XLSX.utils.table_to_book(table, {sheet: "Container Deposits"});
    XLSX.writeFile(wb, 'Container_Deposit_Report.xlsx');
}
</script>

<?php include 'layouts/vendor-scripts.php'; ?>
</body>
</html> 