<?php
/**
 * Feasibility Sample Counter - AJAX Endpoint
 * Location: relevantreflex.com/clients/get-feasibility-samples.php
 * 
 * Returns real-time count of active, verified members matching filter criteria.
 * Supports: age, gender, postcode, STATE, CITY, profiler, past project filters.
 */
require_once 'client-session.php';
require_once 'panel-db-config.php';
requireClientLogin();

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

try {
    $pdo = getClientDBConnection();    // rrshop DB (for survey_urls / past projects)
    $panelPdo = getPanelDBConnection(); // rrpanel DB (for users / profiler)
    
    // Base query: active + email verified members
    $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) . ")";
        }
    }
    
    // ── State filter (NEW) ──
    if (!empty($_POST['filter_state']) && is_array($_POST['filter_state'])) {
        $statePlaceholders = str_repeat('?,', count($_POST['filter_state']) - 1) . '?';
        $query .= " AND u.state IN ($statePlaceholders)";
        $params = array_merge($params, $_POST['filter_state']);
    }
    
    // ── City filter (NEW) ──
    if (!empty($_POST['filter_city']) && is_array($_POST['filter_city'])) {
        $cityPlaceholders = str_repeat('?,', count($_POST['filter_city']) - 1) . '?';
        $query .= " AND u.city IN ($cityPlaceholders)";
        $params = array_merge($params, $_POST['filter_city']);
    }
    
    // Profiler filters
    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'])) {
                $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';
        $projectCodes = $_POST['previous_projects'];
        
        if (!empty($projectCodes)) {
            $codePlaceholders = str_repeat('?,', count($projectCodes) - 1) . '?';
            $statusPlaceholders = str_repeat('?,', count($_POST['previous_statuses']) - 1) . '?';
            
            $prevStmt = $pdo->prepare("
                SELECT DISTINCT sent_to_user_id 
                FROM survey_urls 
                WHERE project_id IN ($codePlaceholders) 
                AND status IN ($statusPlaceholders)
                AND sent_to_user_id IS NOT NULL
            ");
            $prevParams = array_merge($projectCodes, $_POST['previous_statuses']);
            $prevStmt->execute($prevParams);
            $prevUserIds = $prevStmt->fetchAll(PDO::FETCH_COLUMN);
            
            if (!empty($prevUserIds)) {
                $userPlaceholders = str_repeat('?,', count($prevUserIds) - 1) . '?';
                if ($mode === 'exclude') {
                    $query .= " AND u.id NOT IN ($userPlaceholders)";
                } else {
                    $query .= " AND u.id IN ($userPlaceholders)";
                }
                $params = array_merge($params, $prevUserIds);
            } elseif ($mode === 'include') {
                $query .= " AND 1=0";
            }
        }
    }
    
    // Execute on panel DB
    $stmt = $panelPdo->prepare($query);
    $stmt->execute($params);
    $result = $stmt->fetch();
    
    echo json_encode([
        'success' => true,
        'count' => (int)$result['count']
    ]);
    
} catch (Exception $e) {
    error_log("Feasibility sample count error: " . $e->getMessage());
    echo json_encode([
        'success' => false,
        'count' => 0,
        'error' => 'Error counting samples'
    ]);
}