<?php
// Token Management API
include '../../includes/auth.php';
include '../../includes/db.php';
include '../../includes/TokenSystem.php';

// Check if user has permission
$allowed_roles = ['super_admin', 'admin'];
$user_role = $_SESSION['role'] ?? $_SESSION['user_type'] ?? '';

if (!in_array($user_role, $allowed_roles)) {
    http_response_code(403);
    echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
    exit;
}

$action = $_GET['action'] ?? $_POST['action'] ?? '';

switch ($action) {
    case 'get_user_budget':
        echo json_encode(getUserBudget($pdo, $_GET['user_id']));
        break;
    case 'get_transactions':
        echo json_encode(getUserTransactions($pdo, $_GET['user_id']));
        break;
    case 'create_budget':
        echo json_encode(createBudgetForUser($pdo, $_POST['user_id']));
        break;
    case 'export_transactions':
        exportTransactionsCSV($pdo, $_GET['user_id']);
        break;
    case 'get_budget_transactions':
        echo json_encode(getBudgetTransactions($pdo, $_GET['budget_id']));
        break;
    case 'export_all_transactions':
        exportAllTransactions($pdo);
        break;
    case 'export_all_budgets':
        exportAllBudgets($pdo);
        break;
    case 'export_action_usage':
        exportActionUsage($pdo);
        break;
    case 'export_user_activity':
        exportUserActivity($pdo);
        break;
    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
}

function getUserBudget($pdo, $user_id) {
    try {
        // Get user information
        $user_stmt = $pdo->prepare("
            SELECT u.id, u.username, u.role, p.full_name, p.email, p.is_active
            FROM users u
            LEFT JOIN user_profiles p ON u.id = p.user_id
            WHERE u.id = ?
        ");
        $user_stmt->execute([$user_id]);
        $user_info = $user_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$user_info) {
            return ['success' => false, 'message' => 'User not found'];
        }

        // Try to get budget information
        $budget = null;
        $group_info = null;
        
        try {
            // First try to get budget through group membership - use correct table names
            $budget_stmt = $pdo->prepare("
                SELECT b.*, g.name as group_name, g.color as group_color
                FROM budgets b
                LEFT JOIN user_groups g ON b.group_id = g.id
                LEFT JOIN user_group_memberships ugm ON g.id = ugm.group_id
                WHERE ugm.user_id = ? AND b.is_active = 1
                LIMIT 1
            ");
            $budget_stmt->execute([$user_id]);
            $budget_result = $budget_stmt->fetch(PDO::FETCH_ASSOC);
            
            if ($budget_result) {
                $budget = $budget_result;
                $group_info = [
                    'name' => $budget_result['group_name'],
                    'color' => $budget_result['group_color']
                ];
            } else {
                // Try to get budget directly linked to user (if such system exists)
                $direct_budget_stmt = $pdo->prepare("
                    SELECT * FROM budgets WHERE user_id = ? AND is_active = 1 LIMIT 1
                ");
                $direct_budget_stmt->execute([$user_id]);
                $budget = $direct_budget_stmt->fetch(PDO::FETCH_ASSOC);
            }
        } catch (Exception $e) {
            // Tables might not exist, try alternative approach
            try {
                // Check if budgets table exists at all
                $check_stmt = $pdo->query("SHOW TABLES LIKE 'budgets'");
                if ($check_stmt->rowCount() > 0) {
                    // Try simple budget query without joins
                    $simple_budget_stmt = $pdo->prepare("SELECT * FROM budgets WHERE user_id = ? LIMIT 1");
                    $simple_budget_stmt->execute([$user_id]);
                    $budget = $simple_budget_stmt->fetch(PDO::FETCH_ASSOC);
                }
            } catch (Exception $e2) {
                // Budgets table doesn't exist, that's ok
                $budget = null;
            }
        }

        return [
            'success' => true,
            'budget' => $budget,
            'user_info' => $user_info,
            'group_info' => $group_info,
            'message' => $budget ? null : 'No budget found for user group'
        ];
    } catch (Exception $e) {
        return ['success' => false, 'message' => $e->getMessage()];
    }
}

function getUserTransactions($pdo, $user_id) {
    try {
        // Get user information
        $user_stmt = $pdo->prepare("
            SELECT u.id, u.username, u.role, p.full_name, p.email
            FROM users u
            LEFT JOIN user_profiles p ON u.id = p.user_id
            WHERE u.id = ?
        ");
        $user_stmt->execute([$user_id]);
        $user_info = $user_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$user_info) {
            return ['success' => false, 'message' => 'User not found'];
        }

        // Get current balance
        $balance = 0;
        try {
            $balance = TokenSystem::getBalance($user_id);
        } catch (Exception $e) {
            // TokenSystem might not be properly configured
        }
        $user_info['balance'] = $balance;

        // Get transactions
        $transactions = [];
        try {
            // Try to get transactions from token_transactions table
            $trans_stmt = $pdo->prepare("
                SELECT tt.*, ta.action_name
                FROM token_transactions tt
                LEFT JOIN token_actions ta ON tt.action_id = ta.id
                WHERE tt.budget_id IN (
                    SELECT b.id FROM budgets b
                    LEFT JOIN user_group_memberships ugm ON b.group_id = ugm.group_id
                    WHERE ugm.user_id = ? OR b.user_id = ?
                )
                ORDER BY tt.created_at DESC
                LIMIT 100
            ");
            $trans_stmt->execute([$user_id, $user_id]);
            $transactions = $trans_stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            // Table might not exist, return empty array
            $transactions = [];
        }

        return [
            'success' => true,
            'transactions' => $transactions,
            'user_info' => $user_info
        ];
    } catch (Exception $e) {
        return ['success' => false, 'message' => $e->getMessage()];
    }
}

function createBudgetForUser($pdo, $user_id) {
    try {
        // Check if user exists
        $user_stmt = $pdo->prepare("SELECT id, username FROM users WHERE id = ?");
        $user_stmt->execute([$user_id]);
        $user = $user_stmt->fetch();
        
        if (!$user) {
            return ['success' => false, 'message' => 'User not found'];
        }

        // Try to find or create a default group for the user
        $group_id = null;
        
        try {
            // Check if user is already in a group
            $group_stmt = $pdo->prepare("
                SELECT group_id FROM user_group_memberships WHERE user_id = ? LIMIT 1
            ");
            $group_stmt->execute([$user_id]);
            $group_result = $group_stmt->fetch();
            
            if ($group_result) {
                $group_id = $group_result['group_id'];
            } else {
                // Create a default group for the user
                $create_group_stmt = $pdo->prepare("
                    INSERT INTO user_groups (name, description, color, created_by) 
                    VALUES (?, ?, ?, ?)
                ");
                $group_name = "Budget Group - " . $user['username'];
                $create_group_stmt->execute([
                    $group_name,
                    "Default budget group for " . $user['username'],
                    "#007bff",
                    $_SESSION['user_id']
                ]);
                $group_id = $pdo->lastInsertId();
                
                // Add user to the group
                $add_member_stmt = $pdo->prepare("
                    INSERT INTO user_group_memberships (user_id, group_id, role_in_group) 
                    VALUES (?, ?, 'owner')
                ");
                $add_member_stmt->execute([$user_id, $group_id]);
            }
        } catch (Exception $e) {
            // If group system doesn't exist, try direct budget creation
        }

        // Create budget
        if ($group_id) {
            // Group-based budget
            $budget_stmt = $pdo->prepare("
                INSERT INTO budgets (group_id, balance, total_spent, is_active, created_by) 
                VALUES (?, 0, 0, 1, ?)
            ");
            $budget_stmt->execute([$group_id, $_SESSION['user_id']]);
        } else {
            // Direct user budget (if table supports it)
            try {
                $budget_stmt = $pdo->prepare("
                    INSERT INTO budgets (user_id, balance, total_spent, is_active, created_by) 
                    VALUES (?, 0, 0, 1, ?)
                ");
                $budget_stmt->execute([$user_id, $_SESSION['user_id']]);
            } catch (Exception $e) {
                return ['success' => false, 'message' => 'Unable to create budget: ' . $e->getMessage()];
            }
        }

        return ['success' => true, 'message' => 'Budget created successfully'];
    } catch (Exception $e) {
        return ['success' => false, 'message' => $e->getMessage()];
    }
}

function exportTransactionsCSV($pdo, $user_id) {
    try {
        // Get user info
        $user_stmt = $pdo->prepare("SELECT username FROM users WHERE id = ?");
        $user_stmt->execute([$user_id]);
        $user = $user_stmt->fetch();
        
        if (!$user) {
            echo "User not found";
            return;
        }

        // Get transactions
        $trans_stmt = $pdo->prepare("
            SELECT tt.*, ta.action_name
            FROM token_transactions tt
            LEFT JOIN token_actions ta ON tt.action_id = ta.id
            WHERE tt.budget_id IN (
                SELECT b.id FROM budgets b
                LEFT JOIN user_group_memberships ugm ON b.group_id = ugm.group_id
                WHERE ugm.user_id = ? OR b.user_id = ?
            )
            ORDER BY tt.created_at DESC
        ");
        $trans_stmt->execute([$user_id, $user_id]);
        $transactions = $trans_stmt->fetchAll(PDO::FETCH_ASSOC);

        // Set headers for CSV download
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="transactions_' . $user['username'] . '_' . date('Y-m-d') . '.csv"');

        // Output CSV
        $output = fopen('php://output', 'w');
        
        // Headers
        fputcsv($output, [
            'Date', 'Type', 'Amount', 'Description', 'Action', 
            'Reference Type', 'Reference ID', 'Balance After'
        ]);

        // Data
        foreach ($transactions as $tx) {
            fputcsv($output, [
                $tx['created_at'],
                $tx['transaction_type'],
                $tx['amount'],
                $tx['description'],
                $tx['action_name'] ?? '',
                $tx['reference_type'] ?? '',
                $tx['reference_id'] ?? '',
                $tx['balance_after'] ?? ''
            ]);
        }

        fclose($output);
        exit;
    } catch (Exception $e) {
        echo "Error exporting transactions: " . $e->getMessage();
    }
}

function getBudgetTransactions($pdo, $budget_id) {
    try {
        if (!$budget_id) {
            return ['success' => false, 'message' => 'Budget ID is required'];
        }
        
        // Get budget info
        $budget_stmt = $pdo->prepare("
            SELECT 
                b.*,
                CASE 
                    WHEN b.group_id IS NOT NULL THEN 'Group'
                    ELSE 'Individual'
                END as type,
                COALESCE(g.name, CONCAT(u.username, ' (', p.full_name, ')')) as owner
            FROM budgets b
            LEFT JOIN user_groups g ON b.group_id = g.id
            LEFT JOIN users u ON b.user_id = u.id
            LEFT JOIN user_profiles p ON u.id = p.user_id
            WHERE b.id = ?
        ");
        $budget_stmt->execute([$budget_id]);
        $budget_info = $budget_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$budget_info) {
            return ['success' => false, 'message' => 'Budget not found'];
        }
        
        // Get transactions for this budget
        $transactions_stmt = $pdo->prepare("
            SELECT 
                tt.*,
                ta.action_name,
                u.username as created_by_name
            FROM token_transactions tt
            LEFT JOIN token_actions ta ON tt.action_id = ta.id
            LEFT JOIN users u ON tt.created_by = u.id
            WHERE tt.budget_id = ?
            ORDER BY tt.created_at DESC
        ");
        $transactions_stmt->execute([$budget_id]);
        $transactions = $transactions_stmt->fetchAll(PDO::FETCH_ASSOC);
        
        return [
            'success' => true,
            'transactions' => $transactions,
            'budget_info' => $budget_info
        ];
    } catch (Exception $e) {
        return ['success' => false, 'message' => $e->getMessage()];
    }
}

function exportAllTransactions($pdo) {
    try {
        // Check if required tables exist
        $tables_check = $pdo->query("SHOW TABLES LIKE 'token_transactions'");
        if ($tables_check->rowCount() == 0) {
            echo "Error: Token transactions table does not exist";
            return;
        }
        
        // Set headers for CSV download
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="all_transactions_' . date('Y-m-d') . '.csv"');
        
        $output = fopen('php://output', 'w');
        
        // CSV headers
        fputcsv($output, [
            'Transaction ID',
            'Date',
            'Type',
            'Amount',
            'Budget ID',
            'Budget Owner',
            'Description',
            'Action',
            'Balance After',
            'Created By'
        ]);
        
        // Get all transactions with error handling for missing tables
        try {
            $stmt = $pdo->query("
                SELECT 
                    tt.*,
                    ta.action_name,
                    COALESCE(gu.username, bu.username, 'Unknown') as budget_owner,
                    cb.username as created_by_name
                FROM token_transactions tt
                LEFT JOIN token_actions ta ON tt.action_id = ta.id
                LEFT JOIN budgets b ON tt.budget_id = b.id
                LEFT JOIN user_groups g ON b.group_id = g.id
                LEFT JOIN user_group_memberships ugm ON g.id = ugm.group_id
                LEFT JOIN users gu ON ugm.user_id = gu.id
                LEFT JOIN users bu ON b.user_id = bu.id
                LEFT JOIN users cb ON tt.created_by = cb.id
                ORDER BY tt.created_at DESC
            ");
        } catch (Exception $e) {
            // Fallback to simpler query if complex joins fail
            $stmt = $pdo->query("
                SELECT 
                    tt.*,
                    'Unknown' as budget_owner,
                    'Unknown' as created_by_name,
                    NULL as action_name
                FROM token_transactions tt
                ORDER BY tt.created_at DESC
            ");
        }
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($output, [
                $row['id'] ?? '',
                $row['created_at'] ?? '',
                $row['transaction_type'] ?? '',
                $row['amount'] ?? '',
                $row['budget_id'] ?? '',
                $row['budget_owner'] ?? 'Unknown',
                $row['description'] ?? '',
                $row['action_name'] ?? '',
                $row['balance_after'] ?? '',
                $row['created_by_name'] ?? 'Unknown'
            ]);
        }
        
        fclose($output);
        exit;
    } catch (Exception $e) {
        echo "Error exporting all transactions: " . $e->getMessage();
    }
}

function exportAllBudgets($pdo) {
    try {
        // Check if required tables exist
        $tables_check = $pdo->query("SHOW TABLES LIKE 'budgets'");
        if ($tables_check->rowCount() == 0) {
            echo "Error: Budgets table does not exist";
            return;
        }
        
        // Set headers for CSV download
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="all_budgets_' . date('Y-m-d') . '.csv"');
        
        $output = fopen('php://output', 'w');
        
        // CSV headers
        fputcsv($output, [
            'Budget ID',
            'Type',
            'Owner',
            'Group Name',
            'Balance',
            'Total Spent',
            'Status',
            'Created',
            'Created By'
        ]);
        
        // Get all budgets with error handling
        try {
            $stmt = $pdo->query("
                SELECT 
                    b.*,
                    g.name as group_name,
                    COALESCE(g.name, CONCAT(u.username, ' (', p.full_name, ')'), 'Unknown') as owner,
                    cb.username as created_by_name
                FROM budgets b
                LEFT JOIN user_groups g ON b.group_id = g.id
                LEFT JOIN users u ON b.user_id = u.id
                LEFT JOIN user_profiles p ON u.id = p.user_id
                LEFT JOIN users cb ON b.created_by = cb.id
                ORDER BY b.created_at DESC
            ");
        } catch (Exception $e) {
            // Fallback to simpler query if complex joins fail
            $stmt = $pdo->query("
                SELECT 
                    b.*,
                    NULL as group_name,
                    'Unknown' as owner,
                    'Unknown' as created_by_name
                FROM budgets b
                ORDER BY b.created_at DESC
            ");
        }
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($output, [
                $row['id'] ?? '',
                $row['group_id'] ? 'Group' : 'Individual',
                $row['owner'] ?? 'Unknown',
                $row['group_name'] ?? '',
                $row['balance'] ?? '0',
                $row['total_spent'] ?? '0',
                ($row['is_active'] ?? 1) ? 'Active' : 'Inactive',
                $row['created_at'] ?? '',
                $row['created_by_name'] ?? 'Unknown'
            ]);
        }
        
        fclose($output);
        exit;
    } catch (Exception $e) {
        echo "Error exporting all budgets: " . $e->getMessage();
    }
}

function exportActionUsage($pdo) {
    try {
        // Check if required tables exist
        $tables_check = $pdo->query("SHOW TABLES LIKE 'token_actions'");
        if ($tables_check->rowCount() == 0) {
            echo "Error: Token actions table does not exist";
            return;
        }
        
        // Set headers for CSV download
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="action_usage_' . date('Y-m-d') . '.csv"');
        
        $output = fopen('php://output', 'w');
        
        // CSV headers
        fputcsv($output, [
            'Action Name',
            'Category',
            'Token Cost',
            'Usage Count',
            'Total Tokens Used',
            'Average Cost'
        ]);
        
        // Get action usage with error handling
        try {
            $stmt = $pdo->query("
                SELECT 
                    ta.action_name,
                    ta.category,
                    ta.token_cost,
                    COUNT(tt.id) as usage_count,
                    SUM(tt.amount) as total_tokens,
                    AVG(tt.amount) as avg_cost
                FROM token_actions ta
                LEFT JOIN token_transactions tt ON ta.id = tt.action_id
                WHERE tt.transaction_type = 'debit' OR tt.transaction_type IS NULL
                GROUP BY ta.id
                HAVING usage_count > 0
                ORDER BY usage_count DESC, total_tokens DESC
            ");
        } catch (Exception $e) {
            // Fallback to just token_actions if joins fail
            $stmt = $pdo->query("
                SELECT 
                    action_name,
                    category,
                    token_cost,
                    0 as usage_count,
                    0 as total_tokens,
                    0 as avg_cost
                FROM token_actions
                ORDER BY action_name
            ");
        }
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($output, [
                $row['action_name'] ?? '',
                $row['category'] ?? '',
                $row['token_cost'] ?? '',
                $row['usage_count'] ?? '0',
                $row['total_tokens'] ?? '0',
                number_format($row['avg_cost'] ?? 0, 2)
            ]);
        }
        
        fclose($output);
        exit;
    } catch (Exception $e) {
        echo "Error exporting action usage: " . $e->getMessage();
    }
}

function exportUserActivity($pdo) {
    try {
        // Check if required tables exist
        $tables_check = $pdo->query("SHOW TABLES LIKE 'users'");
        if ($tables_check->rowCount() == 0) {
            echo "Error: Users table does not exist";
            return;
        }
        
        // Set headers for CSV download
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="user_activity_' . date('Y-m-d') . '.csv"');
        
        $output = fopen('php://output', 'w');
        
        // CSV headers
        fputcsv($output, [
            'Username',
            'Full Name',
            'Transaction Count',
            'Total Spent',
            'Current Balance'
        ]);
        
        // Get user activity with error handling
        try {
            $stmt = $pdo->query("
                SELECT 
                    u.username,
                    p.full_name,
                    COUNT(tt.id) as transaction_count,
                    SUM(CASE WHEN tt.transaction_type = 'debit' THEN tt.amount ELSE 0 END) as total_spent,
                    COALESCE(b.balance, 0) as current_balance
                FROM users u
                LEFT JOIN user_profiles p ON u.id = p.user_id
                LEFT JOIN user_group_memberships ugm ON u.id = ugm.user_id
                LEFT JOIN budgets b ON (ugm.group_id = b.group_id OR u.id = b.user_id)
                LEFT JOIN token_transactions tt ON b.id = tt.budget_id
                WHERE u.role != 'participant' OR u.role IS NULL
                GROUP BY u.id
                HAVING transaction_count > 0
                ORDER BY total_spent DESC, transaction_count DESC
            ");
        } catch (Exception $e) {
            // Fallback to simpler query if complex joins fail
            $stmt = $pdo->query("
                SELECT 
                    u.username,
                    p.full_name,
                    0 as transaction_count,
                    0 as total_spent,
                    0 as current_balance
                FROM users u
                LEFT JOIN user_profiles p ON u.id = p.user_id
                WHERE u.role != 'participant' OR u.role IS NULL
                ORDER BY u.username
            ");
        }
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($output, [
                $row['username'] ?? '',
                $row['full_name'] ?? '',
                $row['transaction_count'] ?? '0',
                number_format($row['total_spent'] ?? 0, 2),
                number_format($row['current_balance'] ?? 0, 2)
            ]);
        }
        
        fclose($output);
        exit;
    } catch (Exception $e) {
        echo "Error exporting user activity: " . $e->getMessage();
    }
}
?>
