<?php
/**
 * Admin Payment Report
 * View payment reports with date range filtering
 * Shows paid and unpaid users with premium/basic breakdown
 */

session_start();
require_once '../db_connect.php';

// Allow both admin and user_admin roles
if(!isset($_SESSION['user_id']) || !in_array($_SESSION['role'], ['admin', 'user_admin'])) {
    header('Location: ../login');
    exit;
}

$current_year = date('Y');

// Get filter dates
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-01-01');
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-d');
$filter_type = isset($_GET['user_type']) ? $_GET['user_type'] : '';
$filter_status = isset($_GET['payment_status']) ? $_GET['payment_status'] : '';

// Get payment settings
$settings_result = mysqli_query($conn, "SELECT setting_key, setting_value FROM payment_settings");
$settings = [];
while($s = mysqli_fetch_assoc($settings_result)) {
    $settings[$s['setting_key']] = $s['setting_value'];
}
$yearly_fee = $settings['yearly_fee'] ?? 50000;
$premium_yearly_fee = $settings['premium_yearly_fee'] ?? 100000;

// Build conditions for queries
$date_condition = "AND up.payment_date BETWEEN '$date_from' AND '$date_to'";

// Statistics for the date range
$stats_sql = "SELECT
    COUNT(DISTINCT CASE WHEN up.status = 'confirmed' THEN up.user_id END) as total_paid_users,
    COUNT(DISTINCT CASE WHEN up.status = 'confirmed' AND u.user_type = 'premium' THEN up.user_id END) as premium_paid,
    COUNT(DISTINCT CASE WHEN up.status = 'confirmed' AND u.user_type = 'basic' THEN up.user_id END) as basic_paid,
    COALESCE(SUM(CASE WHEN up.status = 'confirmed' THEN up.amount ELSE 0 END), 0) as total_amount,
    COALESCE(SUM(CASE WHEN up.status = 'confirmed' AND u.user_type = 'premium' THEN up.amount ELSE 0 END), 0) as premium_amount,
    COALESCE(SUM(CASE WHEN up.status = 'confirmed' AND u.user_type = 'basic' THEN up.amount ELSE 0 END), 0) as basic_amount
    FROM user_payments up
    JOIN users u ON up.user_id = u.id
    WHERE up.payment_date BETWEEN '$date_from' AND '$date_to'";
$stats = mysqli_fetch_assoc(mysqli_query($conn, $stats_sql));

// Total registered users
$total_users_sql = "SELECT
    COUNT(*) as total,
    SUM(CASE WHEN user_type = 'premium' THEN 1 ELSE 0 END) as premium,
    SUM(CASE WHEN user_type = 'basic' OR user_type IS NULL THEN 1 ELSE 0 END) as basic
    FROM users WHERE role IN ('user', 'broker') AND status = 'approved'";
$total_users = mysqli_fetch_assoc(mysqli_query($conn, $total_users_sql));

$stats['total_users'] = $total_users['total'];
$stats['total_premium'] = $total_users['premium'];
$stats['total_basic'] = $total_users['basic'];
$stats['unpaid_users'] = $total_users['total'] - $stats['total_paid_users'];
$stats['premium_unpaid'] = $total_users['premium'] - $stats['premium_paid'];
$stats['basic_unpaid'] = $total_users['basic'] - $stats['basic_paid'];

// Get paid users list
$paid_where = "WHERE up.payment_date BETWEEN '$date_from' AND '$date_to' AND up.status = 'confirmed'";
if($filter_type) {
    $paid_where .= " AND u.user_type = '$filter_type'";
}

$paid_users_sql = "SELECT u.id, u.full_name, u.username, u.phone, u.email, u.user_type, u.premium_amount,
                   up.amount as paid_amount, up.payment_date, up.payment_method, up.reference_number
                   FROM user_payments up
                   JOIN users u ON up.user_id = u.id
                   $paid_where
                   ORDER BY up.payment_date DESC";
$paid_users = mysqli_query($conn, $paid_users_sql);

// Get unpaid users list
$unpaid_where = "WHERE u.role IN ('user', 'broker') AND u.status = 'approved'";
if($filter_type) {
    $unpaid_where .= " AND u.user_type = '$filter_type'";
}
$unpaid_where .= " AND u.id NOT IN (SELECT user_id FROM user_payments WHERE payment_date BETWEEN '$date_from' AND '$date_to' AND status = 'confirmed')";

$unpaid_users_sql = "SELECT u.id, u.full_name, u.username, u.phone, u.email, u.user_type, u.premium_amount, u.subscription_start_date
                     FROM users u
                     $unpaid_where
                     ORDER BY u.user_type DESC, u.full_name ASC";
$unpaid_users = mysqli_query($conn, $unpaid_users_sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Payment Report - Admin</title>
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="../assets/font-awesome/css/all.min.css">
    <link href="../assets/vendor/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <style>
        :root {
            --primary: #4f46e5;
            --success: #22c55e;
            --warning: #f59e0b;
            --danger: #ef4444;
            --premium: #f59e0b;
        }
        body { font-family: 'Inter', sans-serif; background: #f1f5f9; }
        .sidebar { width: 260px; background: #1e293b; min-height: 100vh; position: fixed; }
        .main-content { margin-left: 260px; padding: 24px; }

        .page-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            border-radius: 16px;
            padding: 24px 30px;
            color: white;
            margin-bottom: 24px;
        }
        .page-header h2 { margin: 0 0 8px 0; font-weight: 700; }
        .page-header p { margin: 0; opacity: 0.9; }

        .filter-card {
            background: white;
            border-radius: 12px;
            padding: 20px;
            margin-bottom: 24px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
        }
        .filter-row {
            display: flex;
            gap: 16px;
            flex-wrap: wrap;
            align-items: flex-end;
        }
        .filter-group { flex: 1; min-width: 150px; }
        .filter-group label { display: block; font-size: 13px; font-weight: 600; color: #334155; margin-bottom: 6px; }
        .filter-group input, .filter-group select {
            width: 100%;
            padding: 10px 14px;
            border: 2px solid #e2e8f0;
            border-radius: 8px;
            font-size: 14px;
        }
        .filter-group input:focus, .filter-group select:focus {
            outline: none;
            border-color: var(--primary);
        }

        .stats-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 20px;
            margin-bottom: 24px;
        }
        .stat-card {
            background: white;
            border-radius: 12px;
            padding: 20px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
        }
        .stat-card.premium {
            background: linear-gradient(135deg, rgba(245, 158, 11, 0.1) 0%, rgba(217, 119, 6, 0.1) 100%);
            border: 1px solid rgba(245, 158, 11, 0.3);
        }
        .stat-card h3 { font-size: 28px; font-weight: 700; margin: 0; color: #1e293b; }
        .stat-card p { color: #64748b; margin: 4px 0 0; font-size: 13px; }
        .stat-icon {
            width: 48px;
            height: 48px;
            border-radius: 10px;
            display: flex;
            align-items: center;
            justify-content: center;
            color: white;
            font-size: 20px;
        }

        .table-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            overflow: hidden;
            margin-bottom: 24px;
        }
        .table-header {
            padding: 20px;
            border-bottom: 1px solid #e2e8f0;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        .table-header h5 { margin: 0; font-weight: 600; }
        .data-table { width: 100%; }
        .data-table th {
            padding: 12px 16px;
            background: #f8fafc;
            font-size: 12px;
            font-weight: 600;
            color: #64748b;
            text-transform: uppercase;
        }
        .data-table td {
            padding: 14px 16px;
            border-bottom: 1px solid #f1f5f9;
            font-size: 14px;
        }
        .badge {
            padding: 4px 10px;
            border-radius: 20px;
            font-size: 11px;
            font-weight: 600;
        }
        .badge-premium {
            background: linear-gradient(135deg, #f59e0b, #d97706);
            color: white;
        }
        .badge-basic {
            background: #e2e8f0;
            color: #64748b;
        }
        .badge-success { background: rgba(34, 197, 94, 0.1); color: var(--success); }
        .badge-danger { background: rgba(239, 68, 68, 0.1); color: var(--danger); }

        .btn {
            padding: 10px 20px;
            border-radius: 8px;
            font-size: 14px;
            font-weight: 500;
            border: none;
            cursor: pointer;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            gap: 8px;
        }
        .btn-primary { background: var(--primary); color: white; }
        .btn-outline { background: transparent; border: 1px solid #e2e8f0; color: #64748b; }
        .btn-success { background: var(--success); color: white; }
        .btn-warning { background: var(--warning); color: white; }

        .export-btns {
            display: flex;
            gap: 10px;
        }

        .tabs {
            display: flex;
            gap: 0;
            margin-bottom: 0;
            border-bottom: 2px solid #e2e8f0;
            padding: 0 20px;
        }
        .tab {
            padding: 14px 24px;
            font-weight: 500;
            color: #64748b;
            cursor: pointer;
            border-bottom: 2px solid transparent;
            margin-bottom: -2px;
            transition: all 0.3s;
        }
        .tab:hover { color: var(--primary); }
        .tab.active {
            color: var(--primary);
            border-bottom-color: var(--primary);
        }
        .tab-content { display: none; }
        .tab-content.active { display: block; }

        .user-type-badge {
            display: inline-flex;
            align-items: center;
            gap: 4px;
        }
        .user-type-badge i { font-size: 12px; }

        .summary-row {
            background: #f8fafc;
            font-weight: 600;
        }

        @media print {
            .sidebar, .filter-card, .tabs, .export-btns, .btn { display: none !important; }
            .main-content { margin-left: 0 !important; }
            .table-card { box-shadow: none; border: 1px solid #e2e8f0; }
        }

        @media (max-width: 768px) {
            .sidebar { display: none; }
            .main-content { margin-left: 0; }
            .stats-grid { grid-template-columns: 1fr 1fr; }
        }
    </style>
</head>
<body>
    <?php include 'includes/sidebar.php'; ?>

    <div class="main-content">
        <div class="page-header">
            <div class="d-flex justify-content-between align-items-center flex-wrap gap-3">
                <div>
                    <h2><i class="fas fa-chart-bar mr-2"></i>Payment Report</h2>
                    <p>View payment statistics and user payment status</p>
                </div>
                <div class="export-btns">
                    <button class="btn btn-outline" onclick="window.print()">
                        <i class="fas fa-print"></i> Print
                    </button>
                    <button class="btn btn-success" onclick="exportToCSV()">
                        <i class="fas fa-file-csv"></i> Export CSV
                    </button>
                </div>
            </div>
        </div>

        <!-- Filters -->
        <div class="filter-card">
            <form method="GET" class="filter-row">
                <div class="filter-group">
                    <label><i class="fas fa-calendar mr-1"></i> From Date</label>
                    <input type="date" name="date_from" value="<?php echo $date_from; ?>">
                </div>
                <div class="filter-group">
                    <label><i class="fas fa-calendar mr-1"></i> To Date</label>
                    <input type="date" name="date_to" value="<?php echo $date_to; ?>">
                </div>
                <div class="filter-group">
                    <label><i class="fas fa-crown mr-1"></i> User Type</label>
                    <select name="user_type">
                        <option value="">All Users</option>
                        <option value="premium" <?php echo $filter_type == 'premium' ? 'selected' : ''; ?>>Premium Only</option>
                        <option value="basic" <?php echo $filter_type == 'basic' ? 'selected' : ''; ?>>Basic Only</option>
                    </select>
                </div>
                <div class="filter-group" style="flex: 0;">
                    <label>&nbsp;</label>
                    <button type="submit" class="btn btn-primary">
                        <i class="fas fa-filter"></i> Apply Filter
                    </button>
                </div>
            </form>
        </div>

        <!-- Statistics -->
        <div class="stats-grid">
            <div class="stat-card">
                <div class="d-flex align-items-center gap-3">
                    <div class="stat-icon" style="background: linear-gradient(135deg, #22c55e, #16a34a);">
                        <i class="fas fa-money-bill-wave"></i>
                    </div>
                    <div>
                        <h3>TZS <?php echo number_format($stats['total_amount']); ?></h3>
                        <p>Total Collected</p>
                    </div>
                </div>
            </div>
            <div class="stat-card premium">
                <div class="d-flex align-items-center gap-3">
                    <div class="stat-icon" style="background: linear-gradient(135deg, #f59e0b, #d97706);">
                        <i class="fas fa-crown"></i>
                    </div>
                    <div>
                        <h3>TZS <?php echo number_format($stats['premium_amount']); ?></h3>
                        <p>Premium Users (<?php echo $stats['premium_paid']; ?> paid)</p>
                    </div>
                </div>
            </div>
            <div class="stat-card">
                <div class="d-flex align-items-center gap-3">
                    <div class="stat-icon" style="background: linear-gradient(135deg, #6366f1, #4f46e5);">
                        <i class="fas fa-user"></i>
                    </div>
                    <div>
                        <h3>TZS <?php echo number_format($stats['basic_amount']); ?></h3>
                        <p>Basic Users (<?php echo $stats['basic_paid']; ?> paid)</p>
                    </div>
                </div>
            </div>
            <div class="stat-card">
                <div class="d-flex align-items-center gap-3">
                    <div class="stat-icon" style="background: linear-gradient(135deg, #3b82f6, #2563eb);">
                        <i class="fas fa-user-check"></i>
                    </div>
                    <div>
                        <h3><?php echo $stats['total_paid_users']; ?> / <?php echo $stats['total_users']; ?></h3>
                        <p>Users Paid</p>
                    </div>
                </div>
            </div>
            <div class="stat-card">
                <div class="d-flex align-items-center gap-3">
                    <div class="stat-icon" style="background: linear-gradient(135deg, #ef4444, #dc2626);">
                        <i class="fas fa-user-times"></i>
                    </div>
                    <div>
                        <h3><?php echo $stats['unpaid_users']; ?></h3>
                        <p>Unpaid Users</p>
                    </div>
                </div>
            </div>
            <div class="stat-card premium">
                <div class="d-flex align-items-center gap-3">
                    <div class="stat-icon" style="background: linear-gradient(135deg, #ef4444, #dc2626);">
                        <i class="fas fa-crown"></i>
                    </div>
                    <div>
                        <h3><?php echo $stats['premium_unpaid']; ?></h3>
                        <p>Premium Unpaid</p>
                    </div>
                </div>
            </div>
        </div>

        <!-- Tabs -->
        <div class="table-card">
            <div class="tabs">
                <div class="tab active" onclick="showTab('paid')">
                    <i class="fas fa-check-circle mr-2"></i>Paid Users (<?php echo mysqli_num_rows($paid_users); ?>)
                </div>
                <div class="tab" onclick="showTab('unpaid')">
                    <i class="fas fa-times-circle mr-2"></i>Unpaid Users (<?php echo mysqli_num_rows($unpaid_users); ?>)
                </div>
            </div>

            <!-- Paid Users Tab -->
            <div class="tab-content active" id="tab-paid">
                <div class="table-responsive">
                    <table class="data-table" id="paidTable">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>User</th>
                                <th>Type</th>
                                <th>Phone</th>
                                <th>Amount Paid</th>
                                <th>Payment Date</th>
                                <th>Method</th>
                                <th>Reference</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $i = 0;
                            mysqli_data_seek($paid_users, 0);
                            while($u = mysqli_fetch_assoc($paid_users)):
                            $i++;
                            ?>
                            <tr>
                                <td><?php echo $i; ?></td>
                                <td>
                                    <strong><?php echo htmlspecialchars($u['full_name'] ?: $u['username']); ?></strong>
                                    <br><small class="text-muted"><?php echo htmlspecialchars($u['email']); ?></small>
                                </td>
                                <td>
                                    <span class="badge <?php echo $u['user_type'] == 'premium' ? 'badge-premium' : 'badge-basic'; ?>">
                                        <?php if($u['user_type'] == 'premium'): ?><i class="fas fa-crown mr-1"></i><?php endif; ?>
                                        <?php echo ucfirst($u['user_type'] ?: 'basic'); ?>
                                    </span>
                                </td>
                                <td><?php echo htmlspecialchars($u['phone']); ?></td>
                                <td><strong>TZS <?php echo number_format($u['paid_amount'], 2); ?></strong></td>
                                <td><?php echo date('d M Y', strtotime($u['payment_date'])); ?></td>
                                <td><?php echo ucfirst(str_replace('_', ' ', $u['payment_method'])); ?></td>
                                <td><?php echo htmlspecialchars($u['reference_number']); ?></td>
                            </tr>
                            <?php endwhile; ?>
                            <?php if($i == 0): ?>
                            <tr><td colspan="8" class="text-center py-4 text-muted">No paid users found in this date range</td></tr>
                            <?php endif; ?>
                        </tbody>
                        <?php if($i > 0): ?>
                        <tfoot>
                            <tr class="summary-row">
                                <td colspan="4" class="text-right"><strong>Total:</strong></td>
                                <td><strong>TZS <?php echo number_format($stats['total_amount'], 2); ?></strong></td>
                                <td colspan="3"></td>
                            </tr>
                        </tfoot>
                        <?php endif; ?>
                    </table>
                </div>
            </div>

            <!-- Unpaid Users Tab -->
            <div class="tab-content" id="tab-unpaid">
                <div class="table-responsive">
                    <table class="data-table" id="unpaidTable">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>User</th>
                                <th>Type</th>
                                <th>Phone</th>
                                <th>Email</th>
                                <th>Expected Amount</th>
                                <th>Registered Since</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $j = 0;
                            $total_expected = 0;
                            mysqli_data_seek($unpaid_users, 0);
                            while($u = mysqli_fetch_assoc($unpaid_users)):
                            $j++;
                            $expected = $u['user_type'] == 'premium'
                                ? ($u['premium_amount'] ?: $premium_yearly_fee)
                                : $yearly_fee;
                            $total_expected += $expected;
                            ?>
                            <tr>
                                <td><?php echo $j; ?></td>
                                <td>
                                    <strong><?php echo htmlspecialchars($u['full_name'] ?: $u['username']); ?></strong>
                                </td>
                                <td>
                                    <span class="badge <?php echo $u['user_type'] == 'premium' ? 'badge-premium' : 'badge-basic'; ?>">
                                        <?php if($u['user_type'] == 'premium'): ?><i class="fas fa-crown mr-1"></i><?php endif; ?>
                                        <?php echo ucfirst($u['user_type'] ?: 'basic'); ?>
                                    </span>
                                </td>
                                <td><?php echo htmlspecialchars($u['phone']); ?></td>
                                <td><?php echo htmlspecialchars($u['email']); ?></td>
                                <td><strong>TZS <?php echo number_format($expected, 2); ?></strong></td>
                                <td><?php echo $u['subscription_start_date'] ? date('d M Y', strtotime($u['subscription_start_date'])) : 'N/A'; ?></td>
                            </tr>
                            <?php endwhile; ?>
                            <?php if($j == 0): ?>
                            <tr><td colspan="7" class="text-center py-4 text-muted">All users have paid!</td></tr>
                            <?php endif; ?>
                        </tbody>
                        <?php if($j > 0): ?>
                        <tfoot>
                            <tr class="summary-row">
                                <td colspan="5" class="text-right"><strong>Total Expected:</strong></td>
                                <td><strong>TZS <?php echo number_format($total_expected, 2); ?></strong></td>
                                <td></td>
                            </tr>
                        </tfoot>
                        <?php endif; ?>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <script src="../assets/vendor/jquery/jquery.min.js"></script>
    <script src="../assets/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
    <script>
        function showTab(tabName) {
            document.querySelectorAll('.tab').forEach(t => t.classList.remove('active'));
            document.querySelectorAll('.tab-content').forEach(c => c.classList.remove('active'));

            event.target.closest('.tab').classList.add('active');
            document.getElementById('tab-' + tabName).classList.add('active');
        }

        function exportToCSV() {
            const activeTab = document.querySelector('.tab-content.active');
            const table = activeTab.querySelector('table');
            const rows = table.querySelectorAll('tr');
            let csv = [];

            rows.forEach(row => {
                const cols = row.querySelectorAll('td, th');
                const rowData = [];
                cols.forEach(col => {
                    let text = col.innerText.replace(/"/g, '""');
                    rowData.push('"' + text + '"');
                });
                csv.push(rowData.join(','));
            });

            const csvContent = csv.join('\n');
            const blob = new Blob([csvContent], { type: 'text/csv;charset=utf-8;' });
            const link = document.createElement('a');
            const url = URL.createObjectURL(blob);
            link.setAttribute('href', url);
            link.setAttribute('download', 'payment_report_<?php echo date('Y-m-d'); ?>.csv');
            link.style.visibility = 'hidden';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    </script>
</body>
</html>
