<?php
require_once '../config/database.php';
secureSessionStart();
requireLogin();

$conn = getConnection();

$user_id = isset($_GET['user_id']) ? intval($_GET['user_id']) : 0;
$package_filter = isset($_GET['package']) ? intval($_GET['package']) : 0;
$status_filter = isset($_GET['status']) ? intval($_GET['status']) : 0;
$limit = isset($_GET['limit']) ? intval($_GET['limit']) : 100;

if ($user_id <= 0) {
    echo json_encode([
        'success' => false, 
        'message' => 'Invalid user ID'
    ]);
    exit();
}

// Build query with filters using prepared statement for safety
$query = "SELECT p.id, p.pin_value, p.package_id, p.status, p.created_at, 
                 p.used_at, p.used_by, p.transferred_at,
                 u.name as user_name, u.mobile 
          FROM pin p 
          LEFT JOIN users u ON p.used_by = u.user_id 
          WHERE p.allcote_user = ?";

$params = [$user_id];
$types = "i";

// Add status filter
if ($status_filter >= 0) {
    $query .= " AND p.status = ?";
    $params[] = $status_filter;
    $types .= "i";
}

// Add package filter
if ($package_filter > 0 && in_array($package_filter, [2500, 3500])) {
    $query .= " AND p.package_id = ?";
    $params[] = $package_filter;
    $types .= "i";
}

$query .= " ORDER BY p.id DESC LIMIT ?";
$params[] = $limit;
$types .= "i";

// Prepare and execute statement
$stmt = mysqli_prepare($conn, $query);
if ($stmt) {
    mysqli_stmt_bind_param($stmt, $types, ...$params);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    
    $pin_list = [];
    $total_value = 0;
    $package_counts = [2500 => 0, 3500 => 0];
    
    while ($pin = mysqli_fetch_assoc($result)) {
        // Format dates
        $pin['created_at_formatted'] = $pin['created_at'] ? date('d M Y', strtotime($pin['created_at'])) : null;
        $pin['used_at_formatted'] = $pin['used_at'] ? date('d M Y', strtotime($pin['used_at'])) : null;
        $pin['transferred_at_formatted'] = $pin['transferred_at'] && $pin['transferred_at'] != '0000-00-00 00:00:00' 
            ? date('d M Y', strtotime($pin['transferred_at'])) 
            : null;
        
        // Add to package counts
        if (isset($package_counts[$pin['package_id']])) {
            $package_counts[$pin['package_id']]++;
        }
        
        // Calculate total value (only for unused pins)
        if ($pin['status'] == 0) {
            $total_value += $pin['package_id'];
        }
        
        $pin_list[] = $pin;
    }
    
    mysqli_stmt_close($stmt);
    
    // Get user details
    $user_query = mysqli_query($conn, "SELECT name, mobile, email, wallet FROM users WHERE user_id = $user_id");
    $user_details = mysqli_fetch_assoc($user_query);
    
    // Get summary statistics for this user
    $stats_query = "SELECT 
        COUNT(*) as total_pins,
        SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as unused_pins,
        SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as used_pins,
        SUM(CASE WHEN package_id = 2500 AND status = 0 THEN 1 ELSE 0 END) as unused_2500,
        SUM(CASE WHEN package_id = 3500 AND status = 0 THEN 1 ELSE 0 END) as unused_3500
        FROM pin WHERE allcote_user = $user_id";
    
    $stats_result = mysqli_query($conn, $stats_query);
    $stats = mysqli_fetch_assoc($stats_result);
    
    echo json_encode([
        'success' => true,
        'user_id' => $user_id,
        'user_details' => [
            'name' => $user_details['name'] ?? 'Unknown',
            'mobile' => $user_details['mobile'] ?? 'N/A',
            'email' => $user_details['email'] ?? 'N/A',
            'wallet' => floatval($user_details['wallet'] ?? 0)
        ],
        'stats' => [
            'total_pins' => intval($stats['total_pins']),
            'unused_pins' => intval($stats['unused_pins']),
            'used_pins' => intval($stats['used_pins']),
            'unused_2500' => intval($stats['unused_2500']),
            'unused_3500' => intval($stats['unused_3500']),
            'total_value' => $total_value
        ],
        'package_counts' => $package_counts,
        'pins' => $pin_list,
        'count' => count($pin_list),
        'filters' => [
            'package' => $package_filter,
            'status' => $status_filter,
            'limit' => $limit
        ]
    ]);
    
} else {
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . mysqli_error($conn)
    ]);
}

mysqli_close($conn);
?>