<?php
require_once 'client-session.php';
require_once 'panel-db-config.php';
requireClientLogin();

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

try {
    $pdo = getClientDBConnection();
    $panelPdo = getPanelDBConnection();
    
    $project_id = isset($_POST['project_id']) ? (int)$_POST['project_id'] : 0;
    $selection_id = isset($_POST['selection_id']) ? (int)$_POST['selection_id'] : 0;
    
    if (!$project_id) {
        throw new Exception('Invalid project ID');
    }
    
    // Verify project belongs to this client
    $stmt = $pdo->prepare("SELECT id FROM projects WHERE id = ? AND client_id = ?");
    $stmt->execute([$project_id, $_SESSION['client_id']]);
    if (!$stmt->fetch()) {
        throw new Exception('Unauthorized');
    }
    
    // Build the query - NO joins initially
    $query = "SELECT COUNT(DISTINCT u.id) as count FROM users u 
              WHERE u.status = 'active'
              AND u.email_verified = 1";
    
    $params = [];
    
    // Age filter
    if (!empty($_POST['filter_age_min']) || !empty($_POST['filter_age_max'])) {
        $currentYear = date('Y');
        
        if (!empty($_POST['filter_age_min'])) {
            $maxBirthYear = $currentYear - (int)$_POST['filter_age_min'];
            $query .= " AND YEAR(u.date_of_birth) <= ?";
            $params[] = $maxBirthYear;
        }
        
        if (!empty($_POST['filter_age_max'])) {
            $minBirthYear = $currentYear - (int)$_POST['filter_age_max'];
            $query .= " AND YEAR(u.date_of_birth) >= ?";
            $params[] = $minBirthYear;
        }
    }
    
    // Gender filter
    if (!empty($_POST['filter_gender']) && is_array($_POST['filter_gender'])) {
        $genderPlaceholders = str_repeat('?,', count($_POST['filter_gender']) - 1) . '?';
        $query .= " AND u.gender IN ($genderPlaceholders)";
        $params = array_merge($params, $_POST['filter_gender']);
    }
    
    // Postcode filter
    if (!empty($_POST['filter_postcode'])) {
        $postcodes = array_map('trim', explode(',', $_POST['filter_postcode']));
        $postcodeConditions = [];
        foreach ($postcodes as $postcode) {
            if (!empty($postcode)) {
                $postcodeConditions[] = "u.postcode LIKE ?";
                $params[] = $postcode . '%';
            }
        }
        if (!empty($postcodeConditions)) {
            $query .= " AND (" . implode(' OR ', $postcodeConditions) . ")";
        }
    }
    
    // Profiler filters (using existing user_profiler table)
    if (!empty($_POST['profiler_filters']) && is_array($_POST['profiler_filters'])) {
        foreach ($_POST['profiler_filters'] as $filter) {
            if (!empty($filter['section']) && !empty($filter['question_id']) && !empty($filter['responses']) && is_array($filter['responses'])) {
                // Build OR conditions for multiple response values
                $responseConditions = [];
                $responseParams = [];
                foreach ($filter['responses'] as $response) {
                    $responseConditions[] = "up.response LIKE ?";
                    $responseParams[] = '%' . $response . '%';
                }
                
                if (!empty($responseConditions)) {
                    $query .= " AND EXISTS (
                        SELECT 1 FROM user_profiler up 
                        WHERE up.user_id = u.id 
                        AND up.section = ? 
                        AND up.question_id = ?
                        AND (" . implode(' OR ', $responseConditions) . ")
                    )";
                    $params[] = $filter['section'];
                    $params[] = $filter['question_id'];
                    $params = array_merge($params, $responseParams);
                }
            }
        }
    }
    
    // Previous project participation filter
    if (!empty($_POST['previous_projects']) && is_array($_POST['previous_projects']) && 
        !empty($_POST['previous_statuses']) && is_array($_POST['previous_statuses'])) {
        
        $mode = $_POST['previous_mode'] ?? 'exclude';
        
        // Project IDs in POST are project_id codes (like PRJ-001), use directly
        $projectCodes = $_POST['previous_projects'];
        
        if (!empty($projectCodes)) {
            // Get user IDs from panel database survey_responses
            $codePlaceholders = str_repeat('?,', count($projectCodes) - 1) . '?';
            $statusPlaceholders = str_repeat('?,', count($_POST['previous_statuses']) - 1) . '?';
            
            $subQuery = "SELECT DISTINCT user_id FROM survey_responses 
                         WHERE project_id IN ($codePlaceholders) 
                         AND status IN ($statusPlaceholders)";
            
            $subParams = array_merge($projectCodes, $_POST['previous_statuses']);
            
            if ($mode === 'exclude') {
                $query .= " AND u.id NOT IN ($subQuery)";
            } else {
                $query .= " AND u.id IN ($subQuery)";
            }
            
            $params = array_merge($params, $subParams);
        }
    }
    
    // Exclude members already selected in OTHER selections of this project
    // Query shop DB first to get excluded user IDs, then pass to panel DB query
    try {
        $stmt = $pdo->prepare("
            SELECT id FROM project_selections 
            WHERE project_id = ? AND id != ?
        ");
        $stmt->execute([$project_id, $selection_id]);
        $other_selection_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        if (!empty($other_selection_ids)) {
            // Get excluded user IDs from shop database
            $selectionPlaceholders = str_repeat('?,', count($other_selection_ids) - 1) . '?';
            $stmt = $pdo->prepare("
                SELECT DISTINCT user_id FROM selection_members 
                WHERE selection_id IN ($selectionPlaceholders)
            ");
            $stmt->execute($other_selection_ids);
            $excluded_user_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
            
            // Now add to panel database query
            if (!empty($excluded_user_ids)) {
                $userPlaceholders = str_repeat('?,', count($excluded_user_ids) - 1) . '?';
                $query .= " AND u.id NOT IN ($userPlaceholders)";
                $params = array_merge($params, $excluded_user_ids);
            }
        }
    } catch (Exception $e) {
        // Table might not exist yet - continue without exclusion
        error_log("Selection exclusion query failed: " . $e->getMessage());
    }
    
    // Execute the count query on panel database
    $stmt = $panelPdo->prepare($query);
    $stmt->execute($params);
    $result = $stmt->fetch();
    
    echo json_encode([
        'success' => true,
        'count' => (int)$result['count']
    ]);
    
} catch (Exception $e) {
    error_log("Available samples error: " . $e->getMessage());
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage(),
        'count' => 0
    ]);
}