<?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 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 {
        $stmt = $pdo->prepare("
            INSERT INTO project_selections 
            (project_id, client_id, selection_name, selection_description, estimated_incidence_rate, 
             required_samples, available_samples, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            $project_id,
            $_SESSION['client_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']);
    
    // Build query to select members - NO profiler
    $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) . ")";
        }
    }
    
    // Exclude members already selected in OTHER selections of this project
    $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)) {
        $selectionPlaceholders = str_repeat('?,', count($other_selection_ids) - 1) . '?';
        $dbName = 'u752449863_rrshop';
        $query .= " AND u.id NOT IN (
            SELECT user_id FROM " . $dbName . ".selection_members 
            WHERE selection_id IN ($selectionPlaceholders)
        )";
        $params = array_merge($params, $other_selection_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;
}