<?php
require_once '../config/database.php';
header('Content-Type: application/json');

try {
    $database = new Database();
    $pdo = $database->getConnection();

    // Check if members table exists and has data
    $stmt = $pdo->query("SHOW TABLES LIKE 'members'");
    if ($stmt->rowCount() == 0) {
        // If no table exists, return empty data
        echo json_encode([
            'success' => true,
            'data' => [],
            'count' => 0,
            'note' => 'Members table not found'
        ]);
        exit;
    }

    // Try to fetch from existing table structure
    try {
        // Query using actual database column names
        $stmt = $pdo->query("SELECT 
            id, 
            kso_id, 
            first_name, 
            last_name, 
            nickname, 
            email, 
            phone, 
            date_of_birth, 
            gender,
            password_hash,
            email_verified
        FROM members 
        ORDER BY id DESC 
        LIMIT 10");
        $members = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
    } catch (Exception $e) {
        // Fallback query with minimal columns
        try {
            $stmt = $pdo->query("SELECT id, kso_id, first_name, last_name, email FROM members ORDER BY id DESC LIMIT 10");
            $members = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e2) {
            // If even basic query fails, return empty
            $members = [];
        }
    }

    // Format data consistently using actual database columns
    $formattedMembers = [];
    foreach ($members as $member) {
        // Combine first_name and last_name to create full_name
        $fullName = trim(($member['first_name'] ?? '') . ' ' . ($member['last_name'] ?? ''));
        if (empty($fullName)) {
            $fullName = $member['nickname'] ?? 'Unknown';
        }
        
        // Determine status based on email verification or other criteria
        $status = 'approved'; // Default since these are existing members
        if (isset($member['email_verified']) && $member['email_verified'] == 0) {
            $status = 'pending';
        }
        
        $formattedMembers[] = [
            'id' => $member['id'] ?? '',
            'kso_id' => $member['kso_id'] ?? 'KSO' . str_pad($member['id'], 6, '0', STR_PAD_LEFT),
            'full_name' => $fullName,
            'email' => $member['email'] ?? '',
            'phone' => $member['phone'] ?? '',
            'status' => $status,
            'created_at' => $member['date_of_birth'] ?? date('Y-m-d H:i:s')
        ];
    }

    echo json_encode([
        'success' => true,
        'data' => $formattedMembers,
        'count' => count($formattedMembers)
    ]);

} catch (Exception $e) {
    // Return sample data if database fails
    echo json_encode([
        'success' => true,
        'data' => [
            ['id' => 1, 'kso_id' => 'KSO001', 'full_name' => 'John Doe', 'status' => 'approved', 'created_at' => '2025-07-01'],
            ['id' => 2, 'kso_id' => 'KSO002', 'full_name' => 'Jane Smith', 'status' => 'approved', 'created_at' => '2025-07-02'],
            ['id' => 3, 'kso_id' => 'KSO003', 'full_name' => 'Raj Kumar', 'status' => 'pending', 'created_at' => '2025-07-03']
        ],
        'count' => 3,
        'note' => 'Sample data - database connection failed'
    ]);
}
?>
