<?php
require_once '../config/database.php';
require_once '../config/auth_enhanced.php';

if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized access']);
    exit;
}

header('Content-Type: application/json');

try {
    $database = new Database();
    $pdo = $database->getConnection();
    
    $start_date = $_GET['start_date'] ?? date('Y-m-01');
    $end_date = $_GET['end_date'] ?? date('Y-m-t');
    
    // Member statistics overview
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as total_members,
            COUNT(CASE WHEN created_at BETWEEN ? AND ? THEN 1 END) as new_members,
            COUNT(CASE WHEN status = 'approved' THEN 1 END) as active_members,
            COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_members,
            COUNT(CASE WHEN membership_type = 'individual' THEN 1 END) as individual_members,
            COUNT(CASE WHEN membership_type = 'family' THEN 1 END) as family_members
        FROM members
    ");
    $stmt->execute([$start_date, $end_date]);
    $member_stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // New members by month
    $stmt = $pdo->prepare("
        SELECT 
            DATE_FORMAT(created_at, '%Y-%m') as month,
            COUNT(*) as count
        FROM members 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY DATE_FORMAT(created_at, '%Y-%m')
        ORDER BY month ASC
    ");
    $stmt->execute([$start_date, $end_date]);
    $monthly_registrations = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Top referring members (if referral system exists)
    $stmt = $pdo->prepare("
        SELECT 
            m.name,
            m.email,
            COUNT(r.id) as referrals
        FROM members m
        LEFT JOIN members r ON r.referred_by = m.id
        WHERE m.created_at BETWEEN ? AND ?
        GROUP BY m.id, m.name, m.email
        HAVING referrals > 0
        ORDER BY referrals DESC
        LIMIT 10
    ");
    $stmt->execute([$start_date, $end_date]);
    $top_referrers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Member status distribution
    $stmt = $pdo->prepare("
        SELECT 
            status,
            COUNT(*) as count
        FROM members 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY status
        ORDER BY count DESC
    ");
    $stmt->execute([$start_date, $end_date]);
    $status_distribution = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Membership type distribution
    $stmt = $pdo->prepare("
        SELECT 
            membership_type,
            COUNT(*) as count
        FROM members 
        WHERE created_at BETWEEN ? AND ?
        GROUP BY membership_type
        ORDER BY count DESC
    ");
    $stmt->execute([$start_date, $end_date]);
    $type_distribution = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Recent member activities (last 30 days)
    $stmt = $pdo->prepare("
        SELECT 
            m.name,
            m.email,
            m.created_at,
            m.status,
            m.membership_type
        FROM members m
        WHERE m.created_at BETWEEN ? AND ?
        ORDER BY m.created_at DESC
        LIMIT 20
    ");
    $stmt->execute([$start_date, $end_date]);
    $recent_members = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Format recent members
    foreach ($recent_members as &$member) {
        $member['created_at'] = date('M d, Y', strtotime($member['created_at']));
        $member['membership_type'] = ucfirst($member['membership_type']);
        $member['status'] = ucfirst($member['status']);
    }
    
    // Format monthly data
    foreach ($monthly_registrations as &$month) {
        $month['month_name'] = date('M Y', strtotime($month['month'] . '-01'));
    }
    
    // Format for frontend table display
    $table_data = [];
    $total_in_period = $member_stats['new_members'];
    
    // Add status distribution for the period
    foreach ($status_distribution as $status) {
        $percentage = $total_in_period > 0 ? round(($status['count'] / $total_in_period) * 100, 2) : 0;
        $table_data[] = [
            'status' => ucfirst($status['status']),
            'count' => $status['count'],
            'percentage' => $percentage
        ];
    }

    echo json_encode([
        'success' => true,
        'data' => $table_data,
        'period' => [
            'start_date' => $start_date,
            'end_date' => $end_date
        ],
        'overview' => $member_stats,
        'monthly_registrations' => $monthly_registrations,
        'status_distribution' => $status_distribution,
        'type_distribution' => $type_distribution,
        'top_referrers' => $top_referrers,
        'recent_members' => $recent_members
    ]);
    
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>
