<?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');
    }
    
    // ========================================
    // MANUAL UPLOAD MODE
    // ========================================
    $selection_mode = $_POST['selection_mode'] ?? 'filter';
    
    if ($selection_mode === 'manual') {
        $selection_name = trim($_POST['selection_name'] ?? '');
        $selection_description = trim($_POST['selection_description'] ?? '');
        $estimated_incidence_rate = (float)($_POST['estimated_incidence_rate'] ?? 100);
        $manual_user_ids_json = $_POST['manual_user_ids'] ?? '[]';
        $manual_user_ids = json_decode($manual_user_ids_json, true);
        
        if (empty($selection_name)) {
            throw new Exception('Selection name is required');
        }
        if (empty($manual_user_ids) || !is_array($manual_user_ids)) {
            throw new Exception('No valid user IDs provided');
        }
        
        // Sanitize IDs
        $manual_user_ids = array_map('intval', $manual_user_ids);
        $manual_user_ids = array_unique(array_filter($manual_user_ids, function($id) { return $id > 0; }));
        $member_count = count($manual_user_ids);
        
        if ($member_count === 0) {
            throw new Exception('No valid user IDs after sanitization');
        }
        
        // Re-verify IDs exist and are active (server-side security)
        $verified_ids = [];
        $batches = array_chunk($manual_user_ids, 1000);
        foreach ($batches as $batch) {
            $ph = str_repeat('?,', count($batch) - 1) . '?';
            $stmt = $panelPdo->prepare("SELECT id FROM users WHERE id IN ($ph) AND status = 'active' AND email_verified = 1");
            $stmt->execute($batch);
            $verified_ids = array_merge($verified_ids, $stmt->fetchAll(PDO::FETCH_COLUMN));
        }
        
        // Re-check for duplicates in existing selections
        $stmt = $pdo->prepare("SELECT id FROM project_selections WHERE project_id = ?");
        $stmt->execute([$project_id]);
        $existing_sel_ids = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        $already_assigned = [];
        if (!empty($existing_sel_ids) && !empty($verified_ids)) {
            $selPh = str_repeat('?,', count($existing_sel_ids) - 1) . '?';
            $vBatches = array_chunk($verified_ids, 1000);
            foreach ($vBatches as $batch) {
                $uPh = str_repeat('?,', count($batch) - 1) . '?';
                $stmt = $pdo->prepare("SELECT DISTINCT user_id FROM selection_members WHERE selection_id IN ($selPh) AND user_id IN ($uPh)");
                $stmt->execute(array_merge($existing_sel_ids, $batch));
                $already_assigned = array_merge($already_assigned, $stmt->fetchAll(PDO::FETCH_COLUMN));
            }
        }
        
        $final_ids = array_values(array_diff($verified_ids, $already_assigned));
        $final_count = count($final_ids);
        
        if ($final_count === 0) {
            throw new Exception('No eligible members to add after verification');
        }
        
        // Generate 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}";
        
        // Create selection record
        $stmt = $pdo->prepare("
            INSERT INTO project_selections 
            (project_id, client_id, selection_id, selection_name, selection_description, estimated_incidence_rate, 
             required_samples, available_samples, selected_samples, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            $project_id,
            $_SESSION['client_id'],
            $generated_selection_id,
            $selection_name,
            $selection_description,
            $estimated_incidence_rate,
            $final_count,
            $final_count,
            $final_count,
            $_SESSION['client_id']
        ]);
        $new_selection_id = $pdo->lastInsertId();
        
        // Save criteria record to mark this as manual upload
        $criteria_stmt = $pdo->prepare("
            INSERT INTO selection_criteria (selection_id, filter_type, filter_field, filter_operator, filter_value)
            VALUES (?, ?, ?, ?, ?)
        ");
        $criteria_stmt->execute([$new_selection_id, 'manual_upload', 'user_ids', 'in', json_encode($final_ids)]);
        $criteria_stmt->execute([$new_selection_id, 'verification', 'email_verified', 'equals', '1']);
        
        // Insert members
        $member_stmt = $pdo->prepare("
            INSERT INTO selection_members (selection_id, user_id, sample_status)
            VALUES (?, ?, 'pending')
        ");
        foreach ($final_ids as $uid) {
            $member_stmt->execute([$new_selection_id, $uid]);
        }
        
        // Log activity
        $skipped = count($manual_user_ids) - $final_count;
        $log_desc = "Manual CSV selection created with {$final_count} members";
        if ($skipped > 0) {
            $log_desc .= " ({$skipped} skipped: not found, inactive, or already in other selections)";
        }
        
        $stmt = $pdo->prepare("
            INSERT INTO selection_activity_log (selection_id, action, description, performed_by)
            VALUES (?, 'created', ?, ?)
        ");
        $stmt->execute([$new_selection_id, $log_desc, $_SESSION['client_id']]);
        
        $pdo->commit();
        
        header('Location: view-project.php?id=' . $project_id . '&success=1');
        exit;
    }
    
    // ========================================
    // FILTER-BASED MODE (existing logic)
    // ========================================
    
    $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'], 
                        'like', 
                        $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',
            'in',
            json_encode($_POST['previous_projects'])
        ]);
        
        // Save statuses
        $criteria_stmt->execute([
            $selection_id,
            'previous_project',
            'statuses',
            'in',
            json_encode($_POST['previous_statuses'])
        ]);
        
        // Save mode
        $criteria_stmt->execute([
            $selection_id,
            'previous_project',
            'mode',
            'equals',
            $_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
    // survey_urls is in the SHOP DB, so we pre-fetch user IDs then pass to panel query
    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) . '?';
            
            // Query SHOP DB (survey_urls) for user IDs matching project + status
            $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') {
                // Include mode but no matching users found - result is 0
                $query .= " AND 1=0";
            }
        }
    }
    
    // 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 " . max(1, (int)$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: create-selection.php?project_id=' . ($project_id ?? 0) . '&error=' . urlencode($e->getMessage()));
    exit;
}