<?php
include '../includes/db.php';
include 'includes/header.php';
include 'includes/excel_export.php';

// Handle Filter
$where_clause = "1=1";
$status_filter = "";
$date_from = "";
$date_to = "";

if (isset($_GET['status']) && !empty($_GET['status'])) {
    $status_filter = $conn->real_escape_string($_GET['status']);
    $where_clause .= " AND a.payment_status = '$status_filter'";
}

if (isset($_GET['date_from']) && !empty($_GET['date_from'])) {
    $date_from = $conn->real_escape_string($_GET['date_from']);
    $where_clause .= " AND DATE(a.submission_date) >= '$date_from'";
}

if (isset($_GET['date_to']) && !empty($_GET['date_to'])) {
    $date_to = $conn->real_escape_string($_GET['date_to']);
    $where_clause .= " AND DATE(a.submission_date) <= '$date_to'";
}

// Fetch Payments
$sql = "SELECT a.*, u.full_name, u.cnic, p.program_name 
        FROM applications a 
        JOIN users u ON a.user_id = u.id 
        JOIN programs p ON a.program_id = p.id 
        WHERE $where_clause 
        ORDER BY a.submission_date DESC";
$result = $conn->query($sql);

// Calculate Totals
$total_amount = 0;
$paid_count = 0;
$pending_count = 0;

// We need to loop twice, so store in array
$rows = [];
while($row = $result->fetch_assoc()) {
    // Derived fields
    $row['challan_number'] = "CH-" . str_pad($row['id'], 6, '0', STR_PAD_LEFT);
    $row['challan_amount'] = 2000; // Fixed processing fee as per generate_challan.php
    
    $rows[] = $row;
    if ($row['payment_status'] == 'Paid') {
        $total_amount += $row['challan_amount'];
        $paid_count++;
    } else {
        $pending_count++;
    }
}

// Handle Excel Export
if (isset($_GET['export']) && $_GET['export'] == 'excel') {
    $export_data = [];
    $headers = ['Challan Number', 'Student Name', 'CNIC', 'Program', 'Amount', 'Status', 'Submission Date', 'Payment Date', 'Transaction ID'];
    
    foreach ($rows as $row) {
        $export_data[] = [
            $row['challan_number'],
            cleanForExcel($row['full_name']),
            cleanForExcel($row['cnic']),
            cleanForExcel($row['program_name']),
            $row['challan_amount'],
            cleanForExcel($row['payment_status']),
            formatDateTime($row['submission_date']),
            formatDateTime($row['payment_date']),
            cleanForExcel($row['transaction_id'] ?? 'N/A')
        ];
    }
    
    $filename = 'Payment_Reports_' . date('Y-m-d_H-i-s') . '.xls';
    exportToExcel($export_data, $filename, $headers);
}
?>

<div class="dashboard-container">
    <div class="sidebar admin-sidebar">
        <?php include 'includes/sidebar.php'; ?>
    </div>

    <div class="main-content">
        <div class="card">
            <div class="card-header" style="flex-wrap: wrap; height: auto;">
                <h3 class="card-title">Challan Payment Reports</h3>
                <div style="display: flex; gap: 10px;">
                    <a href="?export=excel<?php echo !empty($_GET) ? '&' . http_build_query($_GET) : ''; ?>" class="btn-success" style="padding: 5px 15px; font-size: 0.9rem; text-decoration: none; color: white;"><i class="fas fa-file-excel"></i> Export to Excel</a>
                    <button onclick="window.print()" class="btn-primary" style="padding: 5px 15px; font-size: 0.9rem;"><i class="fas fa-print"></i> Print Report</button>
                </div>
            </div>
            
            <div style="background: #f8f9fa; padding: 15px; border-radius: 8px; margin-bottom: 20px; border: 1px solid #ddd;">
                <form method="GET" action="" style="display: flex; gap: 15px; flex-wrap: wrap; align-items: flex-end;">
                    <div class="form-group" style="margin-bottom: 0;">
                        <label>Status</label>
                        <select name="status" class="form-control" style="width: 150px;">
                            <option value="">All Status</option>
                            <option value="Paid" <?php echo $status_filter == 'Paid' ? 'selected' : ''; ?>>Paid</option>
                            <option value="Pending" <?php echo $status_filter == 'Pending' ? 'selected' : ''; ?>>Pending</option>
                        </select>
                    </div>
                    <div class="form-group" style="margin-bottom: 0;">
                        <label>From Date</label>
                        <input type="date" name="date_from" class="form-control" value="<?php echo $date_from; ?>">
                    </div>
                    <div class="form-group" style="margin-bottom: 0;">
                        <label>To Date</label>
                        <input type="date" name="date_to" class="form-control" value="<?php echo $date_to; ?>">
                    </div>
                    <button type="submit" class="btn-primary" style="margin-bottom: 2px;">Filter Report</button>
                    <?php if(!empty($status_filter) || !empty($date_from) || !empty($date_to)): ?>
                        <a href="payment_reports.php" class="btn-outline" style="text-decoration: none; padding: 8px 15px; margin-bottom: 2px;">Clear</a>
                    <?php endif; ?>
                </form>
            </div>

            <!-- Summary Cards -->
            <div style="display: flex; gap: 20px; margin-bottom: 20px; flex-wrap: wrap;">
                <div class="card" style="flex: 1; text-align: center; background: #e3f2fd; border: none; padding: 20px;">
                    <h4 style="margin: 0; color: #1565c0;">Total Collected</h4>
                    <h2 style="margin: 10px 0; color: #0d47a1;">PKR <?php echo number_format($total_amount); ?></h2>
                </div>
                <div class="card" style="flex: 1; text-align: center; background: #e8f5e9; border: none; padding: 20px;">
                    <h4 style="margin: 0; color: #2e7d32;">Paid Challans</h4>
                    <h2 style="margin: 10px 0; color: #1b5e20;"><?php echo $paid_count; ?></h2>
                </div>
                <div class="card" style="flex: 1; text-align: center; background: #fff3e0; border: none; padding: 20px;">
                    <h4 style="margin: 0; color: #ef6c00;">Pending Challans</h4>
                    <h2 style="margin: 10px 0; color: #e65100;"><?php echo $pending_count; ?></h2>
                </div>
            </div>

            <table>
                <thead>
                    <tr>
                        <th>Challan ID</th>
                        <th>Student ID</th>
                        <th>Student Name</th>
                        <th>Program</th>
                        <th>Amount</th>
                        <th>Status</th>
                        <th>Payment Date</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (count($rows) > 0): ?>
                        <?php foreach($rows as $row): ?>
                        <tr>
                            <td><?php echo $row['challan_number']; ?></td>
                            <td><?php echo $row['user_id']; ?></td>
                            <td><?php echo $row['full_name']; ?> <br> <small style="color: #666;"><?php echo $row['cnic']; ?></small></td>
                            <td><?php echo $row['program_name']; ?></td>
                            <td>PKR <?php echo number_format($row['challan_amount']); ?></td>
                            <td>
                                <?php if($row['payment_status'] == 'Paid'): ?>
                                    <span class="badge badge-success">Paid</span>
                                <?php else: ?>
                                    <span class="badge badge-warning">Pending</span>
                                <?php endif; ?>
                            </td>
                            <td>
                                <?php echo $row['payment_date'] ? date('d M, Y', strtotime($row['payment_date'])) : '-'; ?>
                            </td>
                            <td>
                                <a href="../generate_challan.php?id=<?php echo $row['id']; ?>" target="_blank" class="btn-primary" style="padding: 5px 10px; font-size: 0.8rem; text-decoration: none;">
                                    <i class="fas fa-print"></i> Print
                                </a>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="8" class="text-center">No payment records found.</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

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