<?php
$pageTitle = 'Financial Reports';
include 'includes/header.php';

// --- Admin Check ---
$current_role = $_SESSION['user_role'] ?? $_SESSION['admin_user_role'] ?? '';
if (!in_array($current_role, ['admin', 'super_admin'])) {
    header('Location: dashboard.php');
    exit();
}

$walletModel = new Wallet();
$settingModel = new Setting();

// Get currency symbol
$settings = $settingModel->getAll();
$currency_symbol = ($settings['system_currency'] ?? 'USD') === 'ZMW' ? 'K' : '$';

// --- Fetch Financial Data ---
$db = new Database();

// 1. Total Site Commission (from 'site' wallet transactions)
$db->query("
    SELECT SUM(amount) as total 
    FROM wallet_transactions wt
    JOIN wallets w ON wt.wallet_id = w.id
    WHERE w.entity_type = 'site' AND wt.type = 'deposit'
");
$total_site_commission = $db->single()->total ?? 0;

// 1b. Current Site Wallet Balance
$db->query("SELECT balance FROM wallets WHERE entity_type = 'site' LIMIT 1");
$site_wallet_res = $db->single();
$site_wallet_balance = $site_wallet_res->balance ?? 0;

// 2. Total Board Commission (from 'board' wallet transactions)
$db->query("
    SELECT SUM(amount) as total 
    FROM wallet_transactions wt
    JOIN wallets w ON wt.wallet_id = w.id
    WHERE w.entity_type = 'board' AND wt.type = 'deposit'
");
$total_board_commission = $db->single()->total ?? 0;

// 2b. Current Board Wallet Balance
$db->query("SELECT balance FROM wallets WHERE entity_type = 'board' LIMIT 1");
$board_wallet_res = $db->single();
$board_wallet_balance = $board_wallet_res->balance ?? 0;

// 3. Total Club Revenue (sum of all club wallet deposits)
$db->query("
    SELECT SUM(amount) as total 
    FROM wallet_transactions wt
    JOIN wallets w ON wt.wallet_id = w.id
    WHERE w.entity_type = 'club' AND wt.type = 'deposit'
");
$total_club_revenue = $db->single()->total ?? 0;

// 4. Recent Commission Transactions
$db->query("
    SELECT wt.*, w.entity_type
    FROM wallet_transactions wt
    JOIN wallets w ON wt.wallet_id = w.id
    WHERE w.entity_type IN ('site', 'board') AND wt.type = 'deposit'
    ORDER BY wt.created_at DESC
    LIMIT 50
");
$recent_commissions = $db->resultSet();

// 5. Commission Breakdown by Source
$db->query("
    SELECT 
        CASE 
            WHEN description LIKE 'Membership:%' THEN 'Membership'
            WHEN description LIKE 'Fine Payment%' THEN 'Fines'
            WHEN description LIKE 'Referee Fee:%' THEN 'Referee Fees'
            WHEN description LIKE 'Registration Fee%' THEN 'Registration Fees'
            ELSE 'Other'
        END as source,
        SUM(amount) as total
    FROM wallet_transactions wt
    JOIN wallets w ON wt.wallet_id = w.id
    WHERE w.entity_type = 'site' AND wt.type = 'deposit'
    GROUP BY source
    ORDER BY total DESC
");
$commission_breakdown = $db->resultSet();

include 'includes/sidebar.php';
?>

<div class="page-content">
    <div class="matches-header">
        <h1>Financial Reports</h1>
    </div>

    <div class="dashboard-section">
        <h2 class="dashboard-title">Revenue Breakdown</h2>
        <div class="stats-card-grid">
            <div class="stat-card">
                <div class="stat-value" style="color: #2ecc71;"><?php echo $currency_symbol . number_format($total_site_commission, 2); ?></div>
                <div class="stat-label">Total Site Commission (Lifetime)</div>
            </div>
            <div class="stat-card">
                <div class="stat-value" style="color: #27ae60;"><?php echo $currency_symbol . number_format($site_wallet_balance, 2); ?></div>
                <div class="stat-label">Current Site Balance</div>
            </div>
            <div class="stat-card">
                <div class="stat-value" style="color: #3498db;"><?php echo $currency_symbol . number_format($total_board_commission, 2); ?></div>
                <div class="stat-label">Total Board Commission (Lifetime)</div>
            </div>
            <div class="stat-card">
                <div class="stat-value" style="color: #2980b9;"><?php echo $currency_symbol . number_format($board_wallet_balance, 2); ?></div>
                <div class="stat-label">Current Board Balance</div>
            </div>
            <div class="stat-card">
                <div class="stat-value" style="color: #9b59b6;"><?php echo $currency_symbol . number_format($total_club_revenue, 2); ?></div>
                <div class="stat-label">Total Club Revenue</div>
            </div>
        </div>
    </div>

    <div class="dashboard-section">
        <h2 class="dashboard-title">Site Commission Breakdown</h2>
        <div class="table-wrapper">
            <table class="styled-table">
                <thead>
                    <tr>
                        <th>Source</th>
                        <th>Total Commission</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($commission_breakdown)): ?>
                        <tr><td colspan="2" style="text-align:center;">No commission data available.</td></tr>
                    <?php else: ?>
                        <?php foreach ($commission_breakdown as $row): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($row->source); ?></td>
                                <td><strong><?php echo $currency_symbol . number_format($row->total, 2); ?></strong></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <div class="dashboard-section">
        <h2 class="dashboard-title">Recent Commission Logs</h2>
        <div class="table-wrapper">
            <table class="styled-table">
                <thead>
                    <tr>
                        <th>Date</th>
                        <th>Beneficiary</th>
                        <th>Amount</th>
                        <th>Description</th>
                        <th>Reference</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($recent_commissions)): ?>
                        <tr><td colspan="5" style="text-align:center;">No commission records found.</td></tr>
                    <?php else: ?>
                        <?php foreach ($recent_commissions as $t): ?>
                            <tr>
                                <td><?php echo date('d M Y, H:i', strtotime($t->created_at)); ?></td>
                                <td>
                                    <span class="badge badge-<?php echo $t->entity_type === 'site' ? 'success' : 'info'; ?>">
                                        <?php echo ucfirst($t->entity_type); ?> Wallet
                                    </span>
                                </td>
                                <td><strong><?php echo $currency_symbol . number_format($t->amount, 2); ?></strong></td>
                                <td><?php echo htmlspecialchars($t->description); ?></td>
                                <td><small><?php echo htmlspecialchars($t->reference); ?></small></td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<style>
.stats-card-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
    gap: 20px;
    margin-bottom: 20px;
}
.stat-card { background: #fff; padding: 20px; border-radius: 8px; box-shadow: 0 2px 5px rgba(0,0,0,0.05); text-align: center; border: 1px solid #eee; }
.stat-value { font-size: 2rem; font-weight: 800; margin-bottom: 5px; }
.stat-label { color: #7f8c8d; font-size: 0.9rem; text-transform: uppercase; letter-spacing: 0.5px; }
.badge-success { background-color: #d4edda; color: #155724; padding: 4px 8px; border-radius: 4px; font-size: 0.8rem; }
.badge-info { background-color: #d1ecf1; color: #0c5460; padding: 4px 8px; border-radius: 4px; font-size: 0.8rem; }
</style>

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