<?php
header('Content-Type: application/json');
define('SECURE_ACCESS', true);
require_once __DIR__ . '/../../includes/config.php';
require_once __DIR__ . '/../../includes/db.php';
require_once __DIR__ . '/../../includes/auth.php';

$response = [
    'success' => false,
    'total_users' => 0,
    'total_events' => 0,
    'upcoming_events' => [],
    'employee_code' => 'N/A',
    'designation_title' => 'Associate / Standard Rank',
    'stats' => []
];

if (!isLoggedIn()) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized.']);
    exit();
}

$user = getCurrentUser();
checkAndApplyDesignationPromotion($user['id'], $pdo);

try {
    // 1. Fetch upcoming events (filtered by accessible event IDs for the current user)
    $allowedIds = getAccessibleEventIdsForUser($user['id'], $user['role'], $pdo);
    $upcoming_sql = "SELECT e.id, e.title, e.description, e.start_date, e.end_date, e.location, e.capacity,
                           u.username as creator_name, COUNT(r.id) as attendee_count
                    FROM events e
                    LEFT JOIN users u ON e.created_by = u.id
                    LEFT JOIN registrations r ON e.id = r.event_id
                    WHERE e.end_date >= NOW()";
    if ($user['role'] !== 'admin' && $user['role'] !== 'organizer') {
        $upcoming_sql .= " AND e.status = 'published'";
    }
    $upcoming_params = [];
    if ($allowedIds !== null) {
        if (!empty($allowedIds)) {
            $placeholders = implode(',', array_fill(0, count($allowedIds), '?'));
            $upcoming_sql .= " AND e.id IN ($placeholders)";
            $upcoming_params = array_values($allowedIds);
        } else {
            // No accessible events for this user
            $upcoming_sql .= " AND 1=0";
        }
    }
    $upcoming_sql .= " GROUP BY e.id, u.username ORDER BY e.start_date ASC LIMIT 3";
    $stmt = $pdo->prepare($upcoming_sql);
    $stmt->execute($upcoming_params);
    $response['upcoming_events'] = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

    // Basic overall stats
    $response['total_users'] = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
    if ($user['role'] !== 'admin' && $user['role'] !== 'organizer') {
        $response['total_events'] = (int)$pdo->query("SELECT COUNT(*) FROM events WHERE status = 'published'")->fetchColumn();
    } else {
        $response['total_events'] = (int)$pdo->query("SELECT COUNT(*) FROM events")->fetchColumn();
    }

    // User employee code and designation
    $stmt_user_info = $pdo->prepare("
        SELECT u.employee_code, d.title as designation_title 
        FROM users u 
        LEFT JOIN designations d ON u.designation_id = d.id 
        WHERE u.id = ?
    ");
    $stmt_user_info->execute([$user['id']]);
    $db_user = $stmt_user_info->fetch(PDO::FETCH_ASSOC);
    $response['employee_code'] = $db_user['employee_code'] ?? 'N/A';
    $response['designation_title'] = $db_user['designation_title'] ?? 'Associate / Standard Rank';

    // Conditional stats by role
    if ($user['role'] === 'admin') {
        $response['stats'] = [
            'total_revenue' => (float)$pdo->query("SELECT COALESCE(SUM(base_price), 0.00) FROM registrations")->fetchColumn(),
            'total_gst' => (float)$pdo->query("SELECT COALESCE(SUM(gst_amount), 0.00) FROM registrations")->fetchColumn(),
            'pending_kyc' => (int)$pdo->query("SELECT COUNT(*) FROM kyc_records WHERE doc1_status = 'pending' OR doc2_status = 'pending'")->fetchColumn(),
            'pending_payouts' => (int)$pdo->query("SELECT COUNT(*) FROM payouts WHERE status = 'pending'")->fetchColumn(),
            'total_registrations' => (int)$pdo->query("SELECT COUNT(*) FROM registrations")->fetchColumn()
        ];
    } elseif ($user['role'] === 'organizer') {
        $stmt_org = $pdo->prepare("SELECT COUNT(*) FROM events WHERE created_by = ?");
        $stmt_org->execute([$user['id']]);
        $events_count = (int)$stmt_org->fetchColumn();

        $stmt_org_stats = $pdo->prepare("
            SELECT COUNT(r.id) as registrations, COALESCE(SUM(r.base_price), 0.00) as revenue, COALESCE(SUM(e.capacity), 0) as capacity
            FROM events e
            LEFT JOIN registrations r ON e.id = r.event_id
            WHERE e.created_by = ?
        ");
        $stmt_org_stats->execute([$user['id']]);
        $org_stats = $stmt_org_stats->fetch(PDO::FETCH_ASSOC);

        $stmt_org_recent = $pdo->prepare("
            SELECT r.registered_at, u.username, u.email, e.title as event_title, r.total_payable
            FROM registrations r
            JOIN events e ON r.event_id = e.id
            JOIN users u ON r.user_id = u.id
            WHERE e.created_by = ?
            ORDER BY r.registered_at DESC
            LIMIT 5
        ");
        $stmt_org_recent->execute([$user['id']]);

        $response['stats'] = [
            'events_count' => $events_count,
            'registrations_count' => (int)($org_stats['registrations'] ?? 0),
            'estimated_revenue' => (float)($org_stats['revenue'] ?? 0.00),
            'total_capacity' => (int)($org_stats['capacity'] ?? 0),
            'recent_registrants' => $stmt_org_recent->fetchAll(PDO::FETCH_ASSOC) ?: []
        ];
    } elseif ($user['role'] === 'speaker') {
        $stmt_sp = $pdo->prepare("SELECT * FROM speaker_profiles WHERE user_id = ?");
        $stmt_sp->execute([$user['id']]);
        $speaker_profile = $stmt_sp->fetch(PDO::FETCH_ASSOC);

        $sessions_count = 0;
        $reach = 0;
        $upcoming = 0;
        $past = 0;
        $sessions_list = [];

        if ($speaker_profile) {
            $stmt_ag = $pdo->prepare("SELECT COUNT(*) FROM event_agendas WHERE speaker_profile_id = ?");
            $stmt_ag->execute([$speaker_profile['id']]);
            $sessions_count = (int)$stmt_ag->fetchColumn();

            $stmt_aud = $pdo->prepare("
                SELECT COUNT(r.id) 
                FROM registrations r 
                WHERE r.event_id IN (
                    SELECT event_id FROM event_agendas WHERE speaker_profile_id = ?
                )
            ");
            $stmt_aud->execute([$speaker_profile['id']]);
            $reach = (int)$stmt_aud->fetchColumn();

            $stmt_sessions = $pdo->prepare("
                SELECT ea.*, e.title as event_title 
                FROM event_agendas ea
                JOIN events e ON ea.event_id = e.id
                WHERE ea.speaker_profile_id = ?
                ORDER BY ea.agenda_date ASC, ea.start_time ASC
                LIMIT 5
            ");
            $stmt_sessions->execute([$speaker_profile['id']]);
            $sessions_list = $stmt_sessions->fetchAll(PDO::FETCH_ASSOC) ?: [];

            foreach ($sessions_list as $sess) {
                $sess_time = strtotime($sess['agenda_date'] . ' ' . $sess['start_time']);
                if ($sess_time >= time()) {
                    $upcoming++;
                } else {
                    $past++;
                }
            }
        }

        $response['stats'] = [
            'sessions_count' => $sessions_count,
            'audience_reach' => $reach,
            'upcoming_sessions' => $upcoming,
            'past_sessions' => $past,
            'agenda_sessions' => $sessions_list
        ];
    } elseif ($user['role'] === 'sponsor') {
        $stmt_spon = $pdo->prepare("SELECT * FROM sponsor_profiles WHERE user_id = ?");
        $stmt_spon->execute([$user['id']]);
        $sponsor_profile = $stmt_spon->fetch(PDO::FETCH_ASSOC);

        $events_count = 0;
        $reach = 0;
        $gold = 0;
        $silver = 0;
        $bronze = 0;
        $portfolio = [];

        if ($sponsor_profile) {
            $stmt_spon_count = $pdo->prepare("SELECT COUNT(*) FROM event_sponsors WHERE sponsor_profile_id = ?");
            $stmt_spon_count->execute([$sponsor_profile['id']]);
            $events_count = (int)$stmt_spon_count->fetchColumn();

            $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) ?: [];
            $gold = (int)($tiers['Gold'] ?? 0);
            $silver = (int)($tiers['Silver'] ?? 0);
            $bronze = (int)($tiers['Bronze'] ?? 0);

            $stmt_exp = $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_exp->execute([$sponsor_profile['id']]);
            $reach = (int)$stmt_exp->fetchColumn();

            $stmt_port = $pdo->prepare("
                SELECT e.*, es.tier
                FROM event_sponsors es
                JOIN events e ON es.event_id = e.id
                WHERE es.sponsor_profile_id = ?
                ORDER BY e.start_date ASC
                LIMIT 5
            ");
            $stmt_port->execute([$sponsor_profile['id']]);
            $portfolio = $stmt_port->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }

        $response['stats'] = [
            'events_count' => $events_count,
            'audience_reach' => $reach,
            'gold_tier' => $gold,
            'silver_tier' => $silver,
            'bronze_tier' => $bronze,
            'portfolio' => $portfolio
        ];
    } else { // 'user'
        $stmt_matrix = $pdo->prepare("
            SELECT u.id, d.title as designation_title, ref.username as referrer_username, par.username as parent_username 
            FROM users u 
            LEFT JOIN designations d ON u.designation_id = d.id 
            LEFT JOIN users ref ON u.referrer_id = ref.id 
            LEFT JOIN users par ON u.parent_id = par.id 
            WHERE u.id = ?
        ");
        $stmt_matrix->execute([$user['id']]);
        $matrix_info = $stmt_matrix->fetch(PDO::FETCH_ASSOC);

        $stmt_ref_count = $pdo->prepare("SELECT COUNT(*) FROM users WHERE referrer_id = ?");
        $stmt_ref_count->execute([$user['id']]);
        $my_referrals_count = (int)$stmt_ref_count->fetchColumn();

        $stmt_earn_sum = $pdo->prepare("SELECT SUM(amount) FROM earnings WHERE user_id = ?");
        $stmt_earn_sum->execute([$user['id']]);
        $total_earnings = (float)$stmt_earn_sum->fetchColumn();

        $stmt_pay_withdrawn = $pdo->prepare("SELECT SUM(amount) FROM payouts WHERE user_id = ? AND status = 'paid'");
        $stmt_pay_withdrawn->execute([$user['id']]);
        $total_withdrawn = (float)$stmt_pay_withdrawn->fetchColumn();

        $stmt_pay_pending = $pdo->prepare("SELECT SUM(amount) FROM payouts WHERE user_id = ? AND status = 'pending'");
        $stmt_pay_pending->execute([$user['id']]);
        $total_pending = (float)$stmt_pay_pending->fetchColumn();

        $available_balance = max(0.00, $total_earnings - ($total_withdrawn + $total_pending));

        $stmt_levels = $pdo->prepare("
            SELECT level, SUM(amount) as level_sum, COUNT(*) as txn_count 
            FROM earnings 
            WHERE user_id = ? 
            GROUP BY level 
            ORDER BY level ASC
        ");
        $stmt_levels->execute([$user['id']]);
        $mlm_levels = $stmt_levels->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $stmt_earn_logs = $pdo->prepare("
            SELECT e.*, ev.title as event_title, b.username as buyer_name 
            FROM earnings e 
            LEFT JOIN events ev ON e.event_id = ev.id 
            LEFT JOIN users b ON e.buyer_id = b.id 
            WHERE e.user_id = ? 
            ORDER BY e.id DESC 
            LIMIT 10
        ");
        $stmt_earn_logs->execute([$user['id']]);
        $earn_logs = $stmt_earn_logs->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $stmt_reg = $pdo->prepare("SELECT COUNT(*) FROM registrations WHERE user_id = ?");
        $stmt_reg->execute([$user['id']]);
        $my_registrations = (int)$stmt_reg->fetchColumn();

        $response['stats'] = [
            'designation_title' => $matrix_info['designation_title'] ?? 'Associate / Standard Rank',
            'referrer_username' => $matrix_info['referrer_username'] ?? 'N/A',
            'parent_username' => $matrix_info['parent_username'] ?? 'N/A',
            'referrals_count' => $my_referrals_count,
            'total_earnings' => $total_earnings,
            'total_withdrawn' => $total_withdrawn,
            'total_pending' => $total_pending,
            'available_balance' => $available_balance,
            'levels_breakdown' => $mlm_levels,
            'earnings_logs' => $earn_logs,
            'my_registrations' => $my_registrations
        ];
    }
    
    $response['success'] = true;
} catch (PDOException $e) {
    $response['message'] = "Database error: " . $e->getMessage();
}

echo json_encode($response);
