<?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,
    'message' => '',
    'ledger' => [],
    'incentives' => [],
    'gst' => [],
    'mlm_levels' => [],
    'referrals' => [],
    'total_pages' => 1,
    'page' => 1
];

if (!isLoggedIn()) {
    $response['message'] = "Unauthorized.";
    echo json_encode($response);
    exit();
}

$currentUser = getCurrentUser();
$role = $currentUser['role'];
$tab = $_GET['tab'] ?? 'ledger';
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$limit = 10;
$offset = ($page - 1) * $limit;

try {
    if ($role === 'admin' || $role === 'organizer') {
        if ($tab === 'ledger' && $role === 'admin') {
            // 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;

            $count_query = "SELECT COUNT(*) FROM earnings WHERE 1=1";
            $data_query = "
                SELECT e.*, ev.title as event_title, u.username as recipient_name, b.username as buyer_name 
                FROM earnings e 
                LEFT JOIN events ev ON e.event_id = ev.id 
                LEFT JOIN users u ON e.user_id = u.id 
                LEFT JOIN users b ON e.buyer_id = b.id
                WHERE 1=1
            ";
            $params = [];

            if ($filter_user) {
                $count_query .= " AND user_id = ?";
                $data_query .= " AND e.user_id = ?";
                $params[] = $filter_user;
            }
            if ($filter_type) {
                $count_query .= " AND type = ?";
                $data_query .= " AND e.type = ?";
                $params[] = $filter_type;
            }

            $stmt_c = $pdo->prepare($count_query);
            $stmt_c->execute($params);
            $total = (int)$stmt_c->fetchColumn();

            $data_query .= " ORDER BY e.id DESC LIMIT ? OFFSET ?";
            
            $stmt_d = $pdo->prepare($data_query);
            foreach ($params as $idx => $val) {
                $stmt_d->bindValue($idx + 1, $val);
            }
            $stmt_d->bindValue(count($params) + 1, $limit, PDO::PARAM_INT);
            $stmt_d->bindValue(count($params) + 2, $offset, PDO::PARAM_INT);
            $stmt_d->execute();

            $response['ledger'] = $stmt_d->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $response['total_pages'] = max(1, ceil($total / $limit));
            $response['page'] = $page;
            $response['success'] = true;
        } elseif ($tab === 'incentives') {
            // Incentives report
            $count_query = "
                SELECT COUNT(DISTINCT earn.event_id) 
                FROM earnings earn
                JOIN events e ON earn.event_id = e.id
            ";
            $data_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 || ' (L' || earn.level || ': ' || earn.percentage || '% = ₹' || earn.amount || ')'), ' -> ') 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
            ";
            $params = [];
            if ($role === 'organizer') {
                $count_query .= " WHERE e.created_by = ?";
                $data_query .= " WHERE e.created_by = ?";
                $params[] = $currentUser['id'];
            }

            $stmt_c = $pdo->prepare($count_query);
            $stmt_c->execute($params);
            $total = (int)$stmt_c->fetchColumn();

            $data_query .= " GROUP BY e.id, e.title, e.price, earn.buyer_id, b.username ORDER BY sale_date DESC LIMIT ? OFFSET ?";
            $stmt_d = $pdo->prepare($data_query);
            if ($role === 'organizer') {
                $stmt_d->bindValue(1, $currentUser['id'], PDO::PARAM_INT);
                $stmt_d->bindValue(2, $limit, PDO::PARAM_INT);
                $stmt_d->bindValue(3, $offset, PDO::PARAM_INT);
            } else {
                $stmt_d->bindValue(1, $limit, PDO::PARAM_INT);
                $stmt_d->bindValue(2, $offset, PDO::PARAM_INT);
            }
            $stmt_d->execute();

            $response['incentives'] = $stmt_d->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $response['total_pages'] = max(1, ceil($total / $limit));
            $response['page'] = $page;
            $response['success'] = true;
        } elseif ($tab === 'gst') {
            // GST Report
            $count_query = "
                SELECT COUNT(*) FROM registrations r
                JOIN events e ON r.event_id = e.id
            ";
            $data_query = "
                SELECT r.*, e.title as event_title, u.username as attendee_name, u.email as attendee_email 
                FROM registrations r 
                JOIN events e ON r.event_id = e.id 
                JOIN users u ON r.user_id = u.id
            ";
            $params = [];
            if ($role === 'organizer') {
                $count_query .= " WHERE e.created_by = ?";
                $data_query .= " WHERE e.created_by = ?";
                $params[] = $currentUser['id'];
            }

            $stmt_c = $pdo->prepare($count_query);
            $stmt_c->execute($params);
            $total = (int)$stmt_c->fetchColumn();

            $data_query .= " ORDER BY r.registered_at DESC LIMIT ? OFFSET ?";
            $stmt_d = $pdo->prepare($data_query);
            if ($role === 'organizer') {
                $stmt_d->bindValue(1, $currentUser['id'], PDO::PARAM_INT);
                $stmt_d->bindValue(2, $limit, PDO::PARAM_INT);
                $stmt_d->bindValue(3, $offset, PDO::PARAM_INT);
            } else {
                $stmt_d->bindValue(1, $limit, PDO::PARAM_INT);
                $stmt_d->bindValue(2, $offset, PDO::PARAM_INT);
            }
            $stmt_d->execute();

            $response['gst'] = $stmt_d->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $response['total_pages'] = max(1, ceil($total / $limit));
            $response['page'] = $page;
            $response['success'] = true;
        }
    } else { // standard 'user'
        if ($tab === 'mlm') {
            // Fetch MLM Level Breakdown
            $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([$currentUser['id']]);
            $response['mlm_levels'] = $stmt_levels->fetchAll(PDO::FETCH_ASSOC) ?: [];

            // Fetch Direct referrals list
            $stmt_ref_count = $pdo->prepare("SELECT COUNT(*) FROM users WHERE referrer_id = ?");
            $stmt_ref_count->execute([$currentUser['id']]);
            $total = (int)$stmt_ref_count->fetchColumn();

            $stmt_ref = $pdo->prepare("
                SELECT u.id, u.username, u.email, u.employee_code, u.created_at, d.title as designation_title
                FROM users u 
                LEFT JOIN designations d ON u.designation_id = d.id
                WHERE u.referrer_id = ?
                ORDER BY u.created_at DESC
                LIMIT ? OFFSET ?
            ");
            $stmt_ref->bindValue(1, $currentUser['id'], PDO::PARAM_INT);
            $stmt_ref->bindValue(2, $limit, PDO::PARAM_INT);
            $stmt_ref->bindValue(3, $offset, PDO::PARAM_INT);
            $stmt_ref->execute();

            $response['referrals'] = $stmt_ref->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $response['total_pages'] = max(1, ceil($total / $limit));
            $response['page'] = $page;
            $response['success'] = true;
        }
    }
} catch (PDOException $e) {
    $response['message'] = "Database error: " . $e->getMessage();
}

echo json_encode($response);
