<?php
define('SECURE_ACCESS', true);

$pageTitle = "Reporting & Audit Ledgers";
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/auth.php';

requireLogin();

$user = getCurrentUser();

$error = '';
$success = '';

// Filter params for Audit Ledger (Admin only)
$filter_user = isset($_GET['filter_user']) && $_GET['filter_user'] !== '' ? (int)$_GET['filter_user'] : null;
$filter_type = isset($_GET['filter_type']) && $_GET['filter_type'] !== '' ? trim($_GET['filter_type']) : null;

$activeTab = $_GET['tab'] ?? 'ledger';

// Initialize metrics variables
$metrics = [];
$designation_stats = [];
$created_events = [];
$hosted_events = [];
$referrals_list = [];
$sales_incentives = [];
$sponsored_events = [];

try {
    // Fetch Detailed Incentive Sales Records
    $show_incentive_tab = ($user['role'] === 'admin' || $user['role'] === 'organizer');
    if ($show_incentive_tab && $activeTab === 'incentives') {
        $inc_query = "
            SELECT 
                e.title as event_title,
                e.price as ticket_price,
                b.username as buyer_name,
                MIN(earn.created_at) as sale_date,
                SUM(earn.amount) as total_incentive,
                ARRAY_TO_STRING(ARRAY_AGG(u.username || ' (' || earn.percentage || '% = ₹' || earn.amount || ')'), ' &rarr; ') as breakdown
            FROM earnings earn
            JOIN events e ON earn.event_id = e.id
            JOIN users b ON earn.buyer_id = b.id
            JOIN users u ON earn.user_id = u.id
        ";
        
        $inc_params = [];
        if ($user['role'] === 'organizer') {
            $inc_query .= " WHERE e.created_by = :org_user_id ";
            $inc_params['org_user_id'] = $user['id'];
        }
        
        $inc_query .= " GROUP BY e.title, e.price, b.username ORDER BY sale_date DESC ";
        $stmt_inc = $pdo->prepare($inc_query);
        $stmt_inc->execute($inc_params);
        $sales_incentives = $stmt_inc->fetchAll();
    }
    // 1. Fetch Role-Specific Dashboard Metrics
    if ($user['role'] === 'admin') {
        // Total commissions distributed
        $stmt = $pdo->query("SELECT COALESCE(SUM(amount), 0.00) FROM earnings");
        $metrics['total_commissions'] = $stmt->fetchColumn();

        // Pending payouts volume
        $stmt = $pdo->query("SELECT COALESCE(SUM(amount), 0.00) FROM payouts WHERE status = 'pending'");
        $metrics['pending_payouts'] = $stmt->fetchColumn();

        // Verified KYC count
        $stmt = $pdo->query("SELECT COUNT(*) FROM kyc_records WHERE doc1_status = 'approved' AND doc2_status = 'approved' AND bank_status = 'verified'");
        $metrics['verified_kyc'] = $stmt->fetchColumn();

        // Total tickets revenue
        $stmt = $pdo->query("
            SELECT COALESCE(SUM(e.price), 0.00) 
            FROM registrations r 
            JOIN events e ON r.event_id = e.id 
            WHERE e.payment_type = 'paid'
        ");
        $metrics['tickets_revenue'] = $stmt->fetchColumn();

        // Total platform fees collected
        $stmt = $pdo->query("SELECT COALESCE(SUM(platform_fee), 0.00) FROM payouts WHERE status = 'paid'");
        $metrics['total_platform_fees'] = $stmt->fetchColumn();

        // Total TDS collected
        $stmt = $pdo->query("SELECT COALESCE(SUM(tds_amount), 0.00) FROM payouts WHERE status = 'paid'");
        $metrics['total_tds_collected'] = $stmt->fetchColumn();

        // Users by designation
        $stmt = $pdo->query("
            SELECT d.title, COUNT(u.id) as user_count 
            FROM designations d 
            LEFT JOIN users u ON u.designation_id = d.id 
            GROUP BY d.title 
            ORDER BY MIN(d.id) ASC
        ");
        $designation_stats = $stmt->fetchAll();

    } elseif ($user['role'] === 'organizer') {
        // Created events count
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM events WHERE created_by = ?");
        $stmt->execute([$user['id']]);
        $metrics['events_created'] = $stmt->fetchColumn();

        // Total sales revenue from created events
        $stmt = $pdo->prepare("
            SELECT COALESCE(SUM(e.price), 0.00) 
            FROM registrations r 
            JOIN events e ON r.event_id = e.id 
            WHERE e.created_by = ? AND e.payment_type = 'paid'
        ");
        $stmt->execute([$user['id']]);
        $metrics['organizer_revenue'] = $stmt->fetchColumn();

        // Total RSVPs across created events
        $stmt = $pdo->prepare("
            SELECT COUNT(r.id) 
            FROM registrations r 
            JOIN events e ON r.event_id = e.id 
            WHERE e.created_by = ?
        ");
        $stmt->execute([$user['id']]);
        $metrics['organizer_rsvps'] = $stmt->fetchColumn();

        // List events created
        $stmt = $pdo->prepare("
            SELECT e.*, COUNT(r.id) as attendee_count 
            FROM events e 
            LEFT JOIN registrations r ON e.id = r.event_id 
            WHERE e.created_by = ? 
            GROUP BY e.id 
            ORDER BY e.start_date DESC
        ");
        $stmt->execute([$user['id']]);
        $created_events = $stmt->fetchAll();

    } elseif ($user['role'] === 'speaker') {
        // Fetch speaker profile id
        $stmt_prof = $pdo->prepare("SELECT id FROM speaker_profiles WHERE user_id = ?");
        $stmt_prof->execute([$user['id']]);
        $speaker_profile_id = $stmt_prof->fetchColumn() ?: 0;

        // Hosted events count
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM events WHERE speaker_profile_id = ?");
        $stmt->execute([$speaker_profile_id]);
        $metrics['events_hosted'] = $stmt->fetchColumn();

        // Total attendees registered
        $stmt = $pdo->prepare("
            SELECT COUNT(r.id) 
            FROM registrations r 
            JOIN events e ON r.event_id = e.id 
            WHERE e.speaker_profile_id = ?
        ");
        $stmt->execute([$speaker_profile_id]);
        $metrics['speaker_attendees'] = $stmt->fetchColumn();

        // List events hosted
        $stmt = $pdo->prepare("
            SELECT e.*, COUNT(r.id) as attendee_count 
            FROM events e 
            LEFT JOIN registrations r ON e.id = r.event_id 
            WHERE e.speaker_profile_id = ? 
            GROUP BY e.id 
            ORDER BY e.start_date DESC
        ");
        $stmt->execute([$speaker_profile_id]);
        $hosted_events = $stmt->fetchAll();
    } elseif ($user['role'] === 'sponsor') {
        // Fetch Sponsor Profile
        $stmt_spon = $pdo->prepare("SELECT * FROM sponsor_profiles WHERE user_id = ?");
        $stmt_spon->execute([$user['id']]);
        $sponsor_profile = $stmt_spon->fetch(PDO::FETCH_ASSOC);
        $sponsor_profile_id = $sponsor_profile ? $sponsor_profile['id'] : 0;

        // Sponsored events count
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM event_sponsors WHERE sponsor_profile_id = ?");
        $stmt->execute([$sponsor_profile_id]);
        $metrics['sponsor_events'] = $stmt->fetchColumn() ?: 0;

        // Total Reach
        $stmt = $pdo->prepare("
            SELECT COUNT(r.id) 
            FROM registrations r 
            WHERE r.event_id IN (
                SELECT event_id FROM event_sponsors WHERE sponsor_profile_id = ?
            )
        ");
        $stmt->execute([$sponsor_profile_id]);
        $metrics['sponsor_reach'] = $stmt->fetchColumn() ?: 0;

        // Tiers
        $stmt_tiers = $pdo->prepare("SELECT tier, COUNT(*) as count FROM event_sponsors WHERE sponsor_profile_id = ? GROUP BY tier");
        $stmt_tiers->execute([$sponsor_profile_id]);
        $tiers = $stmt_tiers->fetchAll(PDO::FETCH_KEY_PAIR) ?: [];
        $metrics['sponsor_gold'] = (int)($tiers['Gold'] ?? 0);
        $metrics['sponsor_silver'] = (int)($tiers['Silver'] ?? 0);
        $metrics['sponsor_bronze'] = (int)($tiers['Bronze'] ?? 0);

        // List sponsored events
        $stmt = $pdo->prepare("
            SELECT e.*, es.tier, COUNT(r.id) as attendee_count 
            FROM events e 
            JOIN event_sponsors es ON e.id = es.event_id 
            LEFT JOIN registrations r ON e.id = r.event_id 
            WHERE es.sponsor_profile_id = ? 
            GROUP BY e.id, es.tier 
            ORDER BY e.start_date DESC
        ");
        $stmt->execute([$sponsor_profile_id]);
        $sponsored_events = $stmt->fetchAll();
    } else {
        // Attendee / User View
        // Total commissions earned
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(amount), 0.00) FROM earnings WHERE user_id = ?");
        $stmt->execute([$user['id']]);
        $metrics['personal_earnings'] = $stmt->fetchColumn();

        // Paid payouts
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(amount), 0.00) FROM payouts WHERE user_id = ? AND status = 'paid'");
        $stmt->execute([$user['id']]);
        $metrics['personal_withdrawn'] = $stmt->fetchColumn();

        // Pending payouts
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(amount), 0.00) FROM payouts WHERE user_id = ? AND status = 'pending'");
        $stmt->execute([$user['id']]);
        $metrics['personal_pending'] = $stmt->fetchColumn();

        // Direct referrals count
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE referrer_id = ?");
        $stmt->execute([$user['id']]);
        $metrics['direct_referrals'] = $stmt->fetchColumn();

        // Current designation title
        $stmt = $pdo->prepare("
            SELECT d.title 
            FROM designations d 
            JOIN users u ON u.designation_id = d.id 
            WHERE u.id = ?
        ");
        $stmt->execute([$user['id']]);
        $metrics['rank_title'] = $stmt->fetchColumn() ?: 'No Rank Assigned';


    }

    $ledger_query = "
        SELECT 
            item.type, 
            item.timestamp, 
            item.details, 
            item.amount, 
            item.action_type,
            item.user_id,
            item.event_id,
            u.username,
            u.email
        FROM (
            SELECT 
                type AS type, 
                created_at AS timestamp, 
                CASE 
                    WHEN type = 'signup_bonus' THEN 'Signup Bonus'
                    WHEN type = 'referral_bonus' THEN 'Referral Bonus'
                    ELSE 'Commission Credit'
                END AS details, 
                amount AS amount, 
                'credit' AS action_type,
                user_id,
                NULL::INTEGER AS event_id
            FROM earnings
            UNION ALL
            SELECT 
                'payout' AS type, 
                created_at AS timestamp, 
                'Withdrawal Request (' || status || ')' AS details, 
                amount AS amount, 
                CASE WHEN status = 'paid' THEN 'debit' ELSE 'pending_debit' END AS action_type,
                user_id,
                NULL::INTEGER AS event_id
            FROM payouts
            UNION ALL
            SELECT 
                'kyc' AS type, 
                updated_at AS timestamp, 
                'KYC Update (Photo ID: ' || doc1_status || ', Address: ' || doc2_status || ')' AS details, 
                0.00 AS amount, 
                'compliance' AS action_type,
                user_id,
                NULL::INTEGER AS event_id
            FROM kyc_records
            UNION ALL
            SELECT 
                'rsvp' AS type, 
                r.registered_at AS timestamp, 
                'RSVP Registered: ' || e.title AS details, 
                r.total_payable AS amount, 
                'event' AS action_type,
                r.user_id,
                r.event_id
            FROM registrations r
            JOIN events e ON r.event_id = e.id
        ) item
        JOIN users u ON item.user_id = u.id
    ";

    $params = [];
    $where_clauses = [];

    // Filter by user role permissions
    if ($user['role'] !== 'admin') {
        // Standard user or other roles are forced to inspect their own ledger entries
        $where_clauses[] = "item.user_id = :current_user_id";
        $params['current_user_id'] = $user['id'];
    } else {
        // Admin can filter by any user
        if ($filter_user !== null) {
            $where_clauses[] = "item.user_id = :filter_user";
            $params['filter_user'] = $filter_user;
        }
    }

    // Filter by Event Type
    if ($filter_type !== null) {
        $where_clauses[] = "item.type = :filter_type";
        $params['filter_type'] = $filter_type;
    }

    // Pagination variables for ledger
    $limit = 15;
    $page = isset($_GET['page']) && $activeTab === 'ledger' ? max(1, (int)$_GET['page']) : 1;
    $offset = ($page - 1) * $limit;
    $total_records = 0;
    $total_pages = 0;

    $count_query = "
        SELECT COUNT(*) FROM (
            SELECT type, user_id FROM earnings
            UNION ALL
            SELECT 'payout' AS type, user_id FROM payouts
            UNION ALL
            SELECT 'kyc' AS type, user_id FROM kyc_records
            UNION ALL
            SELECT 'rsvp' AS type, user_id FROM registrations
        ) item
    ";

    if (!empty($where_clauses)) {
        $count_query .= " WHERE " . implode(" AND ", $where_clauses);
        $ledger_query .= " WHERE " . implode(" AND ", $where_clauses);
    }

    $stmt_count = $pdo->prepare($count_query);
    $stmt_count->execute($params);
    $total_records = (int)$stmt_count->fetchColumn() ?: 0;
    $total_pages = ceil($total_records / $limit);

    $ledger_query .= " ORDER BY item.timestamp DESC LIMIT $limit OFFSET $offset";

    $stmt_led = $pdo->prepare($ledger_query);
    $stmt_led->execute($params);
    $ledger_items = $stmt_led->fetchAll();

    // Fetch users list for admin filter dropdown
    $users_list = [];
    if ($user['role'] === 'admin') {
        $stmt_ul = $pdo->query("SELECT id, username FROM users ORDER BY username ASC");
        $users_list = $stmt_ul->fetchAll();
    }

} catch (PDOException $e) {
    $error = "Reporting query failed: " . $e->getMessage();
}

include __DIR__ . '/../includes/header.php';
?>

<div class="d-flex flex-column flex-md-row justify-content-between align-items-md-center gap-3 mb-4">
    <div>
        <h1 class="h2 fw-bold text-dark mb-1">Reports</h1>
    </div>
</div>

<?php if (!empty($error)): ?>
    <div class="alert alert-danger shadow-sm border-0 mb-4" role="alert">
        <i class="bi bi-x-circle-fill me-2"></i>
        <?php echo htmlspecialchars($error); ?>
    </div>
<?php endif; ?>

<!-- Section 1: Role-Based Dashboard Cards -->
<div class="row g-4 mb-5">
    <?php if ($user['role'] === 'admin'): ?>
        <!-- Admin Dashboard Cards -->
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-success bg-opacity-10 text-success rounded-3 me-3">
                        <i class="bi bi-wallet2 fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Hierarchy Commissions</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['total_commissions'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-warning bg-opacity-10 text-warning rounded-3 me-3">
                        <i class="bi bi-cash-coin fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Pending Payouts</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['pending_payouts'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-info bg-opacity-10 text-info rounded-3 me-3">
                        <i class="bi bi-shield-check fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Verified KYC</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['verified_kyc']; ?> Members</h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-primary bg-opacity-10 text-primary rounded-3 me-3">
                        <i class="bi bi-ticket-perforated fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Ticket Revenue</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['tickets_revenue'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>

        <!-- Platform Fees Card -->
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-secondary bg-opacity-10 text-secondary rounded-3 me-3">
                        <i class="bi bi-percent fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Platform Revenue</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['total_platform_fees'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>

        <!-- TDS Liabilities Card -->
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-danger bg-opacity-10 text-danger rounded-3 me-3">
                        <i class="bi bi-file-earmark-text fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">TDS Liabilities</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['total_tds_collected'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>

    <?php elseif ($user['role'] === 'organizer'): ?>
        <!-- Organizer Dashboard Cards -->
        <div class="col-12 col-sm-6 col-lg-4">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-primary bg-opacity-10 text-primary rounded-3 me-3">
                        <i class="bi bi-calendar-check fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Events Created</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['events_created']; ?> Events</h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-4">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-success bg-opacity-10 text-success rounded-3 me-3">
                        <i class="bi bi-cash-stack fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Total Sales Revenue</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['organizer_revenue'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-4">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-info bg-opacity-10 text-info rounded-3 me-3">
                        <i class="bi bi-people-fill fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Attendee RSVPs</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['organizer_rsvps']; ?> Registered</h4>
                    </div>
                </div>
            </div>
        </div>

    <?php elseif ($user['role'] === 'speaker'): ?>
        <!-- Speaker Dashboard Cards -->
        <div class="col-12 col-sm-6 col-lg-6">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-warning bg-opacity-10 text-warning rounded-3 me-3">
                        <i class="bi bi-mic fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Events Hosted</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['events_hosted']; ?> Events</h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-6">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-info bg-opacity-10 text-info rounded-3 me-3">
                        <i class="bi bi-people-fill fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Total RSVP Roster</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['speaker_attendees']; ?> Attendees</h4>
                    </div>
                </div>
            </div>
        </div>

    <?php elseif ($user['role'] === 'sponsor'): ?>
        <!-- Sponsor Dashboard Cards -->
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-primary bg-opacity-10 text-primary rounded-3 me-3">
                        <i class="bi bi-briefcase fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Sponsored Events</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['sponsor_events']; ?> Events</h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-info bg-opacity-10 text-info rounded-3 me-3">
                        <i class="bi bi-megaphone fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Audience Reach</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['sponsor_reach']; ?> Attendees</h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-warning bg-opacity-10 text-warning rounded-3 me-3">
                        <i class="bi bi-award fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Gold Tiers</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['sponsor_gold']; ?> Sponsors</h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-secondary bg-opacity-10 text-secondary rounded-3 me-3">
                        <i class="bi bi-shield-check fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Silver / Bronze</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['sponsor_silver'] + $metrics['sponsor_bronze']; ?> Sponsors</h4>
                    </div>
                </div>
            </div>
        </div>

    <?php else: ?>
        <!-- Standard Attendee / Associate Dashboard Cards -->
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-success bg-opacity-10 text-success rounded-3 me-3">
                        <i class="bi bi-wallet fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Commissions Earned</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['personal_earnings'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-primary bg-opacity-10 text-primary rounded-3 me-3">
                        <i class="bi bi-reply-all fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Total Withdrawn</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['personal_withdrawn'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-warning bg-opacity-10 text-warning rounded-3 me-3">
                        <i class="bi bi-clock-history fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Pending Review</h6>
                        <h4 class="fw-bold mb-0 text-dark">₹<?php echo number_format($metrics['personal_pending'], 2); ?></h4>
                    </div>
                </div>
            </div>
        </div>
        <div class="col-12 col-sm-6 col-lg-3">
            <div class="card border-0 shadow-sm h-100 bg-white">
                <div class="card-body p-4 d-flex align-items-center">
                    <div class="p-3 bg-info bg-opacity-10 text-info rounded-3 me-3">
                        <i class="bi bi-people-fill fs-3"></i>
                    </div>
                    <div>
                        <h6 class="text-muted mb-1 small uppercase font-monospace">Direct Team</h6>
                        <h4 class="fw-bold mb-0 text-dark"><?php echo $metrics['direct_referrals']; ?> Members</h4>
                    </div>
                </div>
            </div>
        </div>
    <?php endif; ?>
</div>

<div class="row g-4">
    <!-- Section 2: Role Based Tables & Unified Audit Ledger -->
    <div class="col-12">
        
        <?php if ($show_incentive_tab): ?>
            <!-- Tabs Menu -->
            <ul class="nav nav-tabs mb-3" id="reportsTab" role="tablist">
                <li class="nav-item">
                    <a class="nav-link <?php echo $activeTab === 'ledger' ? 'active fw-bold text-primary' : 'text-muted'; ?>" href="reports?tab=ledger">
                        <i class="bi bi-journal-text me-1"></i> Audit Ledger
                    </a>
                </li>
                <li class="nav-item">
                    <a class="nav-link <?php echo $activeTab === 'incentives' ? 'active fw-bold text-primary' : 'text-muted'; ?>" href="reports?tab=incentives">
                        <i class="bi bi-diagram-3-fill me-1"></i> Ticket Sales Incentive Details
                    </a>
                </li>
            </ul>
        <?php endif; ?>

        <?php if ($activeTab === 'incentives' && $show_incentive_tab): ?>
            <!-- Ticket Sales Incentive Details Card -->
            <div class="card border-0 shadow-sm mb-4">
                <div class="card-header bg-white border-bottom py-3">
                    <h5 class="mb-0 fw-bold text-dark"><i class="bi bi-diagram-3-fill me-2 text-primary"></i>Ticket Sales & Incentive Payout Details</h5>
                </div>
                <div class="card-body p-0">
                    <div class="table-responsive">
                        <table class="table table-hover align-middle mb-0" style="font-size: 0.9rem;">
                            <thead class="table-light text-muted uppercase font-monospace" style="font-size: 0.75rem;">
                                <tr>
                                    <th class="ps-4">Sale Date</th>
                                    <th>Event Title</th>
                                    <th>Buyer</th>
                                    <th>Ticket Price</th>
                                    <th>Commissions Paid</th>
                                    <th class="pe-4">Hierarchy Distribution Path</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php if (empty($sales_incentives)): ?>
                                    <tr>
                                        <td colspan="6" class="text-center py-5 text-muted">
                                            <i class="bi bi-diagram-3 fs-1 mb-2"></i>
                                            <p class="mb-0">No paid ticket sales recorded.</p>
                                        </td>
                                    </tr>
                                <?php else: ?>
                                    <?php foreach ($sales_incentives as $sale): 
                                        $payout_pct = $sale['ticket_price'] > 0 ? ($sale['total_incentive'] / $sale['ticket_price']) * 100 : 0;
                                    ?>
                                        <tr>
                                            <!-- Sale Date -->
                                            <td class="ps-4 font-monospace small">
                                                <?php echo date('Y-m-d H:i', strtotime($sale['sale_date'])); ?>
                                            </td>
                                            <!-- Event Title -->
                                            <td>
                                                <strong class="text-dark"><?php echo htmlspecialchars($sale['event_title']); ?></strong>
                                            </td>
                                            <!-- Buyer -->
                                            <td>
                                                <span class="text-secondary fw-semibold"><?php echo htmlspecialchars($sale['buyer_name']); ?></span>
                                            </td>
                                            <!-- Ticket Price -->
                                            <td class="font-monospace fw-bold text-dark">
                                                ₹<?php echo number_format($sale['ticket_price'], 2); ?>
                                            </td>
                                            <!-- Total Incentive -->
                                            <td>
                                                <strong class="text-success font-monospace">₹<?php echo number_format($sale['total_incentive'], 2); ?></strong>
                                                <span class="text-muted small d-block font-monospace" style="font-size: 0.7rem;"><?php echo number_format($payout_pct, 1); ?>% distributed</span>
                                            </td>
                                            <!-- Breakdown Path -->
                                            <td class="pe-4">
                                                <div class="d-flex align-items-center flex-wrap gap-1 font-monospace" style="font-size: 0.8rem;">
                                                    <?php 
                                                    $nodes = explode(' &rarr; ', $sale['breakdown']);
                                                    foreach ($nodes as $index => $node) {
                                                        if ($index > 0) {
                                                            echo '<i class="bi bi-arrow-right text-muted mx-1"></i>';
                                                        }
                                                        echo '<span class="badge bg-light text-dark border py-1 px-2">' . htmlspecialchars($node) . '</span>';
                                                    }
                                                    ?>
                                                </div>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        <?php else: ?>
            <!-- Unified Audit Ledger Card -->
            <div class="card border-0 shadow-sm mb-4">
                <div class="card-header bg-white border-bottom py-3 d-flex flex-column flex-md-row justify-content-between align-items-md-center gap-2">
                    <h5 class="mb-0 fw-bold text-dark"><i class="bi bi-journal-text me-2 text-primary"></i>Audit Ledger</h5>
                    
                    <!-- Ledger Filters Form -->
                    <form method="GET" action="reports" class="d-flex flex-wrap gap-2 align-items-center">
                        <input type="hidden" name="tab" value="ledger">
                        <!-- Event Type Filter -->
                        <select name="filter_type" class="form-select form-select-sm" style="width: auto;">
                            <option value="">All Types</option>
                            <option value="commission" <?php echo $filter_type === 'commission' ? 'selected' : ''; ?>>Commissions</option>
                            <option value="payout" <?php echo $filter_type === 'payout' ? 'selected' : ''; ?>>Payouts</option>
                            <option value="kyc" <?php echo $filter_type === 'kyc' ? 'selected' : ''; ?>>KYC Updates</option>
                            <option value="rsvp" <?php echo $filter_type === 'rsvp' ? 'selected' : ''; ?>>RSVPs</option>
                        </select>

                        <!-- User Filter (Admin Only) -->
                        <?php if ($user['role'] === 'admin'): ?>
                            <select name="filter_user" class="form-select form-select-sm" style="width: auto;">
                                <option value="">All Users</option>
                                <?php foreach ($users_list as $u_item): ?>
                                    <option value="<?php echo $u_item['id']; ?>" <?php echo $filter_user === $u_item['id'] ? 'selected' : ''; ?>>
                                        <?php echo htmlspecialchars($u_item['username']); ?>
                                    </option>
                                <?php endforeach; ?>
                            </select>
                        <?php endif; ?>

                        <button type="submit" class="btn btn-primary btn-sm px-3 fw-bold"><i class="bi bi-funnel me-1"></i>Filter</button>
                        <a href="reports?tab=ledger" class="btn btn-outline-secondary btn-sm"><i class="bi bi-x-lg"></i></a>
                    </form>
                </div>
                <div class="card-body p-0">
                    <div class="table-responsive">
                        <table class="table table-hover align-middle mb-0" style="font-size: 0.9rem;">
                            <thead class="table-light text-muted uppercase font-monospace" style="font-size: 0.75rem;">
                                <tr>
                                    <th class="ps-4">Timestamp</th>
                                    <?php if ($user['role'] === 'admin'): ?>
                                        <th>User</th>
                                    <?php endif; ?>
                                    <th>Ledger Action Details</th>
                                    <th>Type</th>
                                    <th class="text-end pe-4">Amount</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php if (empty($ledger_items)): ?>
                                    <tr>
                                        <td colspan="5" class="text-center py-5 text-muted">
                                            <i class="bi bi-file-earmark-bar-graph fs-1 mb-2"></i>
                                            <p class="mb-0">No ledger entries found.</p>
                                        </td>
                                    </tr>
                                <?php else: ?>
                                    <?php foreach ($ledger_items as $item): ?>
                                        <tr>
                                            <!-- Timestamp -->
                                            <td class="ps-4">
                                                <span class="text-muted font-monospace small"><?php echo date('Y-m-d H:i:s', strtotime($item['timestamp'])); ?></span>
                                            </td>
                                            
                                            <!-- User (Admin only) -->
                                            <?php if ($user['role'] === 'admin'): ?>
                                                <td>
                                                    <strong class="text-dark d-block"><?php echo htmlspecialchars($item['username']); ?></strong>
                                                    <span class="text-muted small" style="font-size: 0.75rem;"><?php echo htmlspecialchars($item['email']); ?></span>
                                                </td>
                                            <?php endif; ?>

                                            <!-- Details -->
                                            <td>
                                                <span class="text-secondary fw-semibold"><?php echo htmlspecialchars($item['details']); ?></span>
                                                <?php if ($item['action_type'] === 'event' && (float)$item['amount'] > 0.00 && !empty($item['event_id'])): ?>
                                                    <a href="invoice/<?php echo $item['event_id']; ?>&user_id=<?php echo $item['user_id']; ?>" target="_blank" class="btn btn-sm btn-outline-primary py-0 px-2 ms-2 fw-semibold" style="font-size: 0.75rem;">
                                                        <i class="bi bi-file-earmark-pdf me-1"></i>Tax Invoice
                                                    </a>
                                                <?php endif; ?>
                                            </td>

                                            <!-- Badge type -->
                                            <td>
                                                <?php if ($item['action_type'] === 'credit'): ?>
                                                    <span class="badge bg-success-subtle text-success border border-success-subtle font-monospace uppercase px-2 py-1" style="font-size: 0.7rem;">credit</span>
                                                <?php elseif ($item['action_type'] === 'debit'): ?>
                                                    <span class="badge bg-danger-subtle text-danger border border-danger-subtle font-monospace uppercase px-2 py-1" style="font-size: 0.7rem;">debit</span>
                                                <?php elseif ($item['action_type'] === 'pending_debit'): ?>
                                                    <span class="badge bg-warning-subtle text-warning-emphasis border border-warning-subtle font-monospace uppercase px-2 py-1" style="font-size: 0.7rem;">pending</span>
                                                <?php elseif ($item['action_type'] === 'compliance'): ?>
                                                    <span class="badge bg-info-subtle text-info border border-info-subtle font-monospace uppercase px-2 py-1" style="font-size: 0.7rem;">compliance</span>
                                                <?php else: ?>
                                                    <span class="badge bg-secondary-subtle text-secondary-emphasis border border-secondary-subtle font-monospace uppercase px-2 py-1" style="font-size: 0.7rem;">rsvp</span>
                                                <?php endif; ?>
                                            </td>

                                            <!-- Amount -->
                                            <td class="text-end pe-4 font-monospace fw-bold">
                                                <?php if ($item['action_type'] === 'credit'): ?>
                                                    <span class="text-success">+ ₹<?php echo number_format($item['amount'], 2); ?></span>
                                                <?php elseif ($item['action_type'] === 'debit'): ?>
                                                    <span class="text-danger">- ₹<?php echo number_format($item['amount'], 2); ?></span>
                                                <?php elseif ($item['action_type'] === 'pending_debit'): ?>
                                                    <span class="text-warning-emphasis">₹<?php echo number_format($item['amount'], 2); ?></span>
                                                <?php elseif ($item['action_type'] === 'event' && (float)$item['amount'] > 0.00): ?>
                                                    <span class="text-danger">- ₹<?php echo number_format($item['amount'], 2); ?></span>
                                                <?php else: ?>
                                                    <span class="text-muted">-</span>
                                                <?php endif; ?>
                                            </td>
                                        </tr>
                                    <?php endforeach; ?>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
                <?php if ($total_pages > 1): ?>
                    <div class="card-footer bg-light border-top py-3 d-flex flex-column flex-md-row justify-content-between align-items-center gap-3">
                        <div class="small text-secondary font-monospace">
                            Showing <?php echo $offset + 1; ?> to <?php echo min($offset + $limit, $total_records); ?> of <?php echo $total_records; ?> ledger entries
                        </div>
                        <nav aria-label="Page navigation">
                            <ul class="pagination pagination-sm mb-0 align-items-center gap-1">
                                <li class="page-item <?php echo $page <= 1 ? 'disabled' : ''; ?>">
                                    <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => 1])); ?>">First</a>
                                </li>
                                <li class="page-item <?php echo $page <= 1 ? 'disabled' : ''; ?>">
                                    <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $page - 1])); ?>">Previous</a>
                                </li>
                                <li class="page-item disabled mx-1">
                                    <span class="text-secondary small fw-bold" style="font-size: 0.75rem;">Page <?php echo $page; ?> of <?php echo $total_pages; ?></span>
                                </li>
                                <li class="page-item <?php echo $page >= $total_pages ? 'disabled' : ''; ?>">
                                    <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $page + 1])); ?>">Next</a>
                                </li>
                                <li class="page-item <?php echo $page >= $total_pages ? 'disabled' : ''; ?>">
                                    <a class="page-link" href="?<?php echo http_build_query(array_merge($_GET, ['page' => $total_pages])); ?>">Last</a>
                                </li>
                            </ul>
                        </nav>
                    </div>
                <?php endif; ?>
            </div>
        <?php endif; ?>
    </div>

    <div class="col-12">
        <?php if ($user['role'] === 'admin'): ?>
            <!-- Admin Role-Based Sidebar: Designation Metrics -->
            <div class="card border-0 shadow-sm">
                <div class="card-header bg-white border-bottom py-3">
                    <h5 class="mb-0 fw-bold text-dark"><i class="bi bi-award me-2 text-primary"></i>Member Distribution</h5>
                </div>
                <div class="card-body p-0">
                    <ul class="list-group list-group-flush">
                        <?php foreach ($designation_stats as $stat): ?>
                            <li class="list-group-item d-flex justify-content-between align-items-center p-3">
                                <span class="fw-semibold text-secondary"><?php echo htmlspecialchars($stat['title']); ?></span>
                                <span class="badge bg-primary rounded-pill"><?php echo $stat['user_count']; ?> Members</span>
                            </li>
                        <?php endforeach; ?>
                    </ul>
                </div>
            </div>

        <?php elseif ($user['role'] === 'organizer'): ?>
            <!-- Organizer Role-Based Sidebar: Created Events -->
            <div class="card border-0 shadow-sm">
                <div class="card-header bg-white border-bottom py-3">
                    <h5 class="mb-0 fw-bold text-dark"><i class="bi bi-calendar3 me-2 text-primary"></i>My Managed Events</h5>
                </div>
                <div class="card-body p-0" style="max-height: 400px; overflow-y: auto;">
                    <?php if (empty($created_events)): ?>
                        <div class="text-center py-4 text-muted">
                            <p class="mb-0 small">No events created.</p>
                        </div>
                    <?php else: ?>
                        <div class="list-group list-group-flush">
                            <?php foreach ($created_events as $ev): ?>
                                <div class="list-group-item p-3">
                                    <strong class="text-dark small d-block"><?php echo htmlspecialchars($ev['title']); ?></strong>
                                    <div class="d-flex justify-content-between align-items-center mt-2">
                                        <span class="small text-muted font-monospace"><i class="bi bi-clock me-1"></i><?php echo date('Y-m-d', strtotime($ev['start_date'])); ?></span>
                                        <span class="badge bg-light text-dark border small"><?php echo $ev['attendee_count']; ?> RSVPs</span>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>

        <?php elseif ($user['role'] === 'speaker'): ?>
            <!-- Speaker Role-Based Sidebar: Hosted Events -->
            <div class="card border-0 shadow-sm">
                <div class="card-header bg-white border-bottom py-3">
                    <h5 class="mb-0 fw-bold text-dark"><i class="bi bi-mic me-2 text-primary"></i>My Hosting Agenda</h5>
                </div>
                <div class="card-body p-0" style="max-height: 400px; overflow-y: auto;">
                    <?php if (empty($hosted_events)): ?>
                        <div class="text-center py-4 text-muted">
                            <p class="mb-0 small">No hosting events linked.</p>
                        </div>
                    <?php else: ?>
                        <div class="list-group list-group-flush">
                            <?php foreach ($hosted_events as $ev): ?>
                                <div class="list-group-item p-3">
                                    <strong class="text-dark small d-block"><?php echo htmlspecialchars($ev['title']); ?></strong>
                                    <div class="d-flex justify-content-between align-items-center mt-2">
                                        <span class="small text-muted font-monospace"><i class="bi bi-calendar me-1"></i><?php echo date('Y-m-d', strtotime($ev['start_date'])); ?></span>
                                        <span class="badge bg-light text-dark border small"><?php echo $ev['attendee_count']; ?> Registered</span>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>

        <?php elseif ($user['role'] === 'sponsor'): ?>
            <!-- Sponsor Role-Based Sidebar: Sponsored Events Portfolio -->
            <div class="card border-0 shadow-sm">
                <div class="card-header bg-white border-bottom py-3">
                    <h5 class="mb-0 fw-bold text-dark"><i class="bi bi-award-fill me-2 text-primary"></i>Sponsor Portfolio</h5>
                </div>
                <div class="card-body p-0" style="max-height: 400px; overflow-y: auto;">
                    <?php if (empty($sponsored_events)): ?>
                        <div class="text-center py-4 text-muted">
                            <p class="mb-0 small">No sponsored events.</p>
                        </div>
                    <?php else: ?>
                        <div class="list-group list-group-flush">
                            <?php foreach ($sponsored_events as $ev): ?>
                                <div class="list-group-item p-3">
                                    <strong class="text-dark small d-block"><?php echo htmlspecialchars($ev['title']); ?></strong>
                                    <div class="d-flex justify-content-between align-items-center mt-2">
                                        <span class="small text-muted font-monospace"><i class="bi bi-tags me-1"></i><?php echo htmlspecialchars($ev['tier']); ?> Tier</span>
                                        <span class="badge bg-light text-dark border small"><?php echo $ev['attendee_count']; ?> Reach</span>
                                    </div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>

        <?php endif; ?>
    </div>
</div>

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