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

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

// Check authentication
if (!isAuthenticated()) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
    exit;
}

try {
    $database = new Database();
    $conn = $database->getConnection();
    
    // Get filter parameters
    $status = isset($_GET['status']) ? $_GET['status'] : '';
    $type = isset($_GET['type']) ? $_GET['type'] : '';
    $session = isset($_GET['session']) ? $_GET['session'] : '';
    
    // Build the query
    $query = "SELECT m.* FROM members m WHERE 1=1";
    $params = [];
    
    // Add membership type filter
    if (!empty($type)) {
        $query .= " AND m.membership_type = ?";
        $params[] = $type;
    }
    
    // Add session year filter
    if (!empty($session)) {
        $query .= " AND m.session_from_year = ?";
        $params[] = $session;
    }
    
    // Add status filter
    if (!empty($status)) {
        $today = date('Y-m-d');
        
        switch ($status) {
            case 'active':
                $query .= " AND (m.membership_expiry_date IS NULL OR m.membership_expiry_date > DATE_ADD(?, INTERVAL 30 DAY))";
                $params[] = $today;
                break;
            case 'expiring_soon':
                $query .= " AND m.membership_expiry_date BETWEEN ? AND DATE_ADD(?, INTERVAL 30 DAY)";
                $params[] = $today;
                $params[] = $today;
                break;
            case 'expired':
                $query .= " AND m.membership_expiry_date < ?";
                $params[] = $today;
                break;
        }
    }
    
    // Add sorting by expiry date (expired first, then expiring soon, then active)
    $query .= " ORDER BY 
                CASE 
                    WHEN m.membership_expiry_date < CURDATE() THEN 1
                    WHEN m.membership_expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 2
                    ELSE 3
                END,
                m.membership_expiry_date ASC";
    
    // Prepare and execute the query
    $stmt = $conn->prepare($query);
    
    if (!empty($params)) {
        $stmt->execute($params);
    } else {
        $stmt->execute();
    }
    
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get counts for different statuses
    $countQuery = "SELECT 
                     SUM(CASE WHEN membership_expiry_date > DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as active,
                     SUM(CASE WHEN membership_expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as expiring_soon,
                     SUM(CASE WHEN membership_expiry_date < CURDATE() THEN 1 ELSE 0 END) as expired,
                     SUM(CASE WHEN updated_at BETWEEN DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND LAST_DAY(CURDATE()) THEN 1 ELSE 0 END) as renewed_this_month
                   FROM members";
    
    $countStmt = $conn->prepare($countQuery);
    $countStmt->execute();
    $counts = $countStmt->fetch(PDO::FETCH_ASSOC);
    
    echo json_encode([
        'success' => true,
        'data' => $results,
        'counts' => $counts,
        'message' => 'Member renewal data fetched successfully'
    ]);
    
} catch (PDOException $e) {
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . $e->getMessage()
    ]);
}
