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

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    header('Location: projects-list.php');
    exit;
}

try {
    $pdo = getClientDBConnection();
    $panelPdo = getPanelDBConnection();
    
    $pdo->beginTransaction();
    
    $project_id = (int)$_POST['project_id'];
    $selection_id = !empty($_POST['selection_id']) ? (int)$_POST['selection_id'] : 0;
    
    // Verify project belongs to this client
    $stmt = $pdo->prepare("SELECT id, client_id, project_id FROM projects WHERE id = ? AND client_id = ?");
    $stmt->execute([$project_id, $_SESSION['client_id']]);
    $project = $stmt->fetch();
    
    if (!$project) {
        throw new Exception('Unauthorized');
    }
    
    $selection_name = trim($_POST['selection_name']);
    $selection_description = trim($_POST['selection_description'] ?? '');
    $estimated_incidence_rate = (float)$_POST['estimated_incidence_rate'];
    $required_samples = (int)$_POST['required_samples'];
    $available_samples = (int)$_POST['available_samples'];
    
    // Validate
    if (empty($selection_name) || $required_samples <= 0 || $required_samples > $available_samples) {
        throw new Exception('Invalid selection data');
    }
    
    // Create or update selection
    if ($selection_id) {
        $stmt = $pdo->prepare("
            UPDATE project_selections 
            SET selection_name = ?,
                selection_description = ?,
                estimated_incidence_rate = ?,
                required_samples = ?,
                available_samples = ?,
                updated_at = NOW()
            WHERE id = ? AND status = 'draft' AND client_id = ?
        ");
        $stmt->execute([
            $selection_name,
            $selection_description,
            $estimated_incidence_rate,
            $required_samples,
            $available_samples,
            $selection_id,
            $_SESSION['client_id']
        ]);
        
        $stmt = $pdo->prepare("DELETE FROM selection_criteria WHERE selection_id = ?");
        $stmt->execute([$selection_id]);
        
        $stmt = $pdo->prepare("DELETE FROM selection_members WHERE selection_id = ?");
        $stmt->execute([$selection_id]);
        
    } else {
        // Generate unique selection_id
        $date_part = date('Ymd');
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM project_selections WHERE selection_id LIKE ?");
        $stmt->execute(["SEL-{$date_part}-%"]);
        $count = $stmt->fetchColumn();
        $sequence = str_pad($count + 1, 4, '0', STR_PAD_LEFT);
        $generated_selection_id = "SEL-{$date_part}-{$sequence}";
        
        $stmt = $pdo->prepare("
            INSERT INTO project_selections 
            (project_id, client_id, selection_id, selection_name, selection_description, estimated_incidence_rate, 
             required_samples, available_samples, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            $project_id,
            $_SESSION['client_id'],
            $generated_selection_id,
            $selection_name,
            $selection_description,
            $estimated_incidence_rate,
            $required_samples,
            $available_samples,
            $_SESSION['client_id']
        ]);
        
        $selection_id = $pdo->lastInsertId();
    }
    
    // Save filter criteria
    $criteria_stmt = $pdo->prepare("
        INSERT INTO selection_criteria (selection_id, filter_type, filter_field, filter_operator, filter_value)
        VALUES (?, ?, ?, ?, ?)
    ");
    
    // Age filters
    if (!empty($_POST['filter_age_min'])) {
        $criteria_stmt->execute([$selection_id, 'demographic', 'age', 'greater_than', $_POST['filter_age_min']]);
    }
    if (!empty($_POST['filter_age_max'])) {
        $criteria_stmt->execute([$selection_id, 'demographic', 'age', 'less_than', $_POST['filter_age_max']]);
    }
    
    // Gender filter
    if (!empty($_POST['filter_gender']) && is_array($_POST['filter_gender'])) {
        $criteria_stmt->execute([$selection_id, 'demographic', 'gender', 'in', json_encode($_POST['filter_gender'])]);
    }
    
    // Postcode filter
    if (!empty($_POST['filter_postcode'])) {
        $criteria_stmt->execute([$selection_id, 'demographic', 'postcode', 'like', $_POST['filter_postcode']]);
    }
    
    // Email verified (always applied)
    $criteria_stmt->execute([$selection_id, 'verification', 'email_verified', 'equals', '1']);
    
    // 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'])) {
                // Save each response as a separate criterion for easier editing later
                foreach ($filter['responses'] as $response) {
                    // Store as "section:question_id" in filter_field
                    $criteria_stmt->execute([
                        $selection_id, 
                        'profiler', 
                        $filter['section'] . ':' . $filter['question_id'], 
                        'contains', 
                        $response
                    ]);
                }
            }
        }
    }
    
    // Previous project participation filter
    if (!empty($_POST['previous_projects']) && is_array($_POST['previous_projects']) &&
        !empty($_POST['previous_statuses']) && is_array($_POST['previous_statuses'])) {
        
        // Save project IDs
        $criteria_stmt->execute([
            $selection_id,
            'previous_project',
            'project_ids',
            'filter',
            json_encode($_POST['previous_projects'])
        ]);
        
        // Save statuses
        $criteria_stmt->execute([
            $selection_id,
            'previous_project',
            'statuses',
            'filter',
            json_encode($_POST['previous_statuses'])
        ]);
        
        // Save mode
        $criteria_stmt->execute([
            $selection_id,
            'previous_project',
            'mode',
            'filter',
            $_POST['previous_mode'] ?? 'exclude'
        ]);
    }
    
    // Build query to select members
    $query = "SELECT DISTINCT u.id 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, use them directly
        $projectCodes = $_POST['previous_projects'];
        
        if (!empty($projectCodes)) {
            // Get user IDs from panel database
            $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
    $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);
        }
    }
    
    // Add random ordering and limit
    $query .= " ORDER BY RAND() LIMIT ?";
    $params[] = $required_samples;
    
    // Execute query on panel database
    $stmt = $panelPdo->prepare($query);
    $stmt->execute($params);
    $selected_users = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    // Insert selected members
    if (!empty($selected_users)) {
        $member_stmt = $pdo->prepare("
            INSERT INTO selection_members (selection_id, user_id, sample_status)
            VALUES (?, ?, 'pending')
        ");
        
        foreach ($selected_users as $user_id) {
            $member_stmt->execute([$selection_id, $user_id]);
        }
    }
    
    // Update selected_samples count
    $stmt = $pdo->prepare("
        UPDATE project_selections 
        SET selected_samples = ?
        WHERE id = ?
    ");
    $stmt->execute([count($selected_users), $selection_id]);
    
    // Log activity
    $stmt = $pdo->prepare("
        INSERT INTO selection_activity_log (selection_id, action, description, performed_by)
        VALUES (?, ?, ?, ?)
    ");
    $stmt->execute([
        $selection_id,
        $selection_id ? 'updated' : 'created',
        $selection_id ? 'Selection updated' : 'Selection created with ' . count($selected_users) . ' members',
        $_SESSION['client_id']
    ]);
    
    $pdo->commit();
    
    header('Location: view-project.php?id=' . $project_id . '&success=1');
    exit;
    
} catch (Exception $e) {
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    
    error_log("Save selection error: " . $e->getMessage());
    header('Location: view-project.php?id=' . ($project_id ?? 0) . '&error=' . urlencode($e->getMessage()));
    exit;
}