<?php
/**
 * admin_get_all_withdrawal_pins.php
 * 
 * Admin endpoint to view and manage all withdrawal PINs in the system
 * Includes filtering by user, status, date range, and pagination
 */

header('Content-Type: application/json');
// Critical: Add cache control headers
header('Cache-Control: no-cache, no-store, must-revalidate');
header('Pragma: no-cache');
header('Expires: 0');
// CRITICAL: Allow origin with credentials
$allowed_origin = isset($_SERVER['HTTP_ORIGIN']) ? $_SERVER['HTTP_ORIGIN'] : '*';
header("Access-Control-Allow-Origin: $allowed_origin");
header('Access-Control-Allow-Methods: GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization');
header('Access-Control-Allow-Credentials: true');
header('X-Content-Type-Options: nosniff');
header('X-Frame-Options: DENY');
header('X-XSS-Protection: 1; mode=block');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}

// Helper to check if a table exists
function tonsui_table_exists($pdo, $table) {
    try {
        $stmt = $pdo->prepare("SHOW TABLES LIKE ?");
        $stmt->execute([$table]);
        return $stmt->rowCount() > 0;
    } catch (Exception $e) {
        return false;
    }
}

// Define TONSUI_LOADED constant to prevent direct access to session_init.php
define('TONSUI_LOADED', true);

// Include centralized session initialization
require_once __DIR__ . '/session_init.php';

// Require admin authentication
requireAdmin(true);

require_once 'db.php';

// Helper to check if a column exists on a table
function tonsui_has_column($pdo, $table, $column) {
    try {
        $stmt = $pdo->prepare("SHOW COLUMNS FROM `{$table}` LIKE ?");
        $stmt->execute([$column]);
        return $stmt->rowCount() > 0;
    } catch (Exception $e) {
        return false;
    }
}

try {
    // Parse query parameters
    $user_id = isset($_GET['user_id']) ? intval($_GET['user_id']) : null;
    $status = isset($_GET['status']) ? trim($_GET['status']) : null;
    $from_date = isset($_GET['from_date']) ? trim($_GET['from_date']) : null;
    $to_date = isset($_GET['to_date']) ? trim($_GET['to_date']) : null;
    $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
    $limit = isset($_GET['limit']) ? min(100, max(1, intval($_GET['limit']))) : 20;
    $offset = ($page - 1) * $limit;
    
    // Detect table existence and schema
    try {
        $tcheck = $pdo->query("SHOW TABLES LIKE 'withdrawal_pins'");
        if (!$tcheck || $tcheck->rowCount() === 0) {
            echo json_encode([
                'success' => true,
                'pins' => [],
                'pagination' => [
                    'total_pins' => 0,
                    'total_pages' => 0,
                    'current_page' => 1,
                    'limit' => $limit
                ]
            ]);
            return;
        }
    } catch (Exception $e) {
        echo json_encode([
            'success' => true,
            'pins' => [],
            'pagination' => [
                'total_pins' => 0,
                'total_pages' => 0,
                'current_page' => 1,
                'limit' => $limit
            ]
        ]);
        return;
    }

    // Introspect columns once (more reliable on some hosts than LIKE per call)
    $hasStatus = false; $hasUsed = false; $hasCreatedAt = false; $hasUpdatedAt = false;
    $hasUserIdCol = false; $hasUsernameCol = false; $hasName = false; $hasExpiry = false;
    try {
        $cols = $pdo->query("SHOW COLUMNS FROM withdrawal_pins");
        while ($c = $cols->fetch(PDO::FETCH_ASSOC)) {
            $f = strtolower($c['Field'] ?? '');
            if ($f === 'status') $hasStatus = true;
            if ($f === 'used') $hasUsed = true;
            if ($f === 'created_at') $hasCreatedAt = true;
            if ($f === 'updated_at') $hasUpdatedAt = true;
            if ($f === 'user_id') $hasUserIdCol = true;
            if ($f === 'username') $hasUsernameCol = true;
            if ($f === 'name') $hasName = true;
            if ($f === 'expiry_date') $hasExpiry = true;
        }
    } catch (Exception $e) { /* ignore */ }

    // Build the query
    $selectStatus = $hasStatus
        ? "wp.status"
        : ($hasUsed ? "CASE WHEN wp.used = 1 THEN 'used' ELSE 'active' END" : "'active'");
    $selectCreated = $hasCreatedAt ? 'wp.created_at' : 'NULL';
    $selectUpdated = $hasUpdatedAt ? 'wp.updated_at' : 'NULL';
    $selectName = $hasName ? 'wp.name' : 'NULL';
    $selectExpiry = $hasExpiry ? 'wp.expiry_date' : 'NULL';

    // Determine how to join users table
    $join = $hasUserIdCol
        ? 'LEFT JOIN users u ON wp.user_id = u.id'
        : ($hasUsernameCol ? 'LEFT JOIN users u ON u.username = wp.username' : 'LEFT JOIN users u ON 1=0');

    // Check for checkpoint_percentage column
    $hasCheckpoint = tonsui_has_column($pdo, 'withdrawal_pins', 'checkpoint_percentage');
    $selectCheckpoint = $hasCheckpoint ? 'wp.checkpoint_percentage' : '92';
    
    $query = "SELECT 
                wp.id, 
                COALESCE(u.id, wp.user_id) AS user_id, 
                wp.pin, 
                {$selectName} AS name,
                {$selectCheckpoint} AS checkpoint_percentage,
                {$selectStatus} AS status, 
                {$selectExpiry} AS expiry_date,
                {$selectCreated} AS created_at, 
                {$selectUpdated} AS updated_at,
                u.username,
                u.email
            FROM withdrawal_pins wp
            {$join}";
    
    $where_clauses = [];
    $params = [];
    
    if ($user_id) {
        $userField = $hasUserIdCol ? 'wp.user_id' : 'u.id';
        $where_clauses[] = $userField . " = ?";
        $params[] = $user_id;
    }
    
    if ($status !== null) {
        // Apply filter only if underlying column exists
        if ($hasStatus && in_array($status, ['used', 'active', 'cancelled'], true)) {
            $where_clauses[] = "wp.status = ?";
            $params[] = $status;
        } elseif ($hasUsed && in_array($status, ['used', 'active'], true)) {
            $where_clauses[] = ($status === 'used') ? "wp.used = 1" : "wp.used = 0";
        }
    } else {
        // Default to only active (and non-expired) pins when no status filter provided
        if ($hasStatus) {
            $where_clauses[] = "wp.status = 'active'";
        } elseif ($hasUsed) {
            $where_clauses[] = "(wp.used = 0 OR wp.used IS NULL)";
        }
        if ($hasExpiry) {
            $where_clauses[] = "(wp.expiry_date IS NULL OR wp.expiry_date > NOW())";
        }
    }
    
    if ($hasCreatedAt) {
        if ($from_date) {
            $where_clauses[] = "wp.created_at >= ?";
            $params[] = $from_date . ' 00:00:00';
        }
        
        if ($to_date) {
            $where_clauses[] = "wp.created_at <= ?";
            $params[] = $to_date . ' 23:59:59';
        }
    }
    
    if (!empty($where_clauses)) {
        $query .= " WHERE " . implode(" AND ", $where_clauses);
    }
    
    // Count total matching pins for pagination
    $count_query = "SELECT COUNT(*) FROM withdrawal_pins wp {$join}";
    if (!empty($where_clauses)) {
        $count_query .= " WHERE " . implode(" AND ", $where_clauses);
    }
    
    $count_stmt = $pdo->prepare($count_query);
    $count_stmt->execute($params);
    $total_pins = $count_stmt->fetchColumn();
    $total_pages = ceil($total_pins / $limit);
    
    // Add pagination and ordering (fallback to id when created_at missing)
    $orderField = $hasCreatedAt ? 'wp.created_at' : 'wp.id';
    $safeLimit = max(1, min(100, (int)$limit));
    $safeOffset = max(0, (int)$offset);
    $query .= " ORDER BY {$orderField} DESC LIMIT {$safeLimit} OFFSET {$safeOffset}";
    
    // Execute the query
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    $pins = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get pin settings for users if we're filtering by user_id
    $user_settings = null;
    if ($user_id) {
        // Optional settings table
        if (tonsui_table_exists($pdo, 'user_pin_settings')) {
            try {
                $settings_stmt = $pdo->prepare("SELECT * FROM user_pin_settings WHERE user_id = ? LIMIT 1");
                $settings_stmt->execute([$user_id]);
                $user_settings = $settings_stmt->fetch(PDO::FETCH_ASSOC);
            } catch (Exception $e) { /* ignore */ }
        }

        // If no explicit settings, get the basic user setting if column exists
        if (!$user_settings) {
            $has_wpr = tonsui_has_column($pdo, 'users', 'withdrawal_pin_required');
            if ($has_wpr) {
                try {
                    $user_stmt = $pdo->prepare("SELECT withdrawal_pin_required FROM users WHERE id = ? LIMIT 1");
                    $user_stmt->execute([$user_id]);
                    $user_data = $user_stmt->fetch(PDO::FETCH_ASSOC);
                    if ($user_data) {
                        $user_settings = [
                            'user_id' => $user_id,
                            'pin_enabled' => (bool)($user_data['withdrawal_pin_required'] ?? 1),
                            'max_pins' => 5,
                            'pin_expiry_days' => 30
                        ];
                    }
                } catch (Exception $e) { /* ignore */ }
            }
        }
    }
    
    // Prepare the response
    $response = [
        'success' => true,
        'pins' => $pins,
        'pagination' => [
            'total_pins' => $total_pins,
            'total_pages' => $total_pages,
            'current_page' => $page,
            'limit' => $limit
        ]
    ];
    
    if ($user_settings) {
        $response['user_settings'] = $user_settings;
    }
    
    echo json_encode($response);
    
} catch (PDOException $e) {
    error_log('Database error in admin_get_all_withdrawal_pins: ' . $e->getMessage());
    
    http_response_code(500);
    echo json_encode([
        'success' => false, 
        'message' => 'Database error fetching withdrawal PINs'
    ]);
    
} catch (Exception $e) {
    http_response_code(400);
    echo json_encode([
        'success' => false,
        'message' => 'Failed to fetch withdrawal pins: ' . $e->getMessage()
    ]);
}
