<?php
require_once 'includes/config.php';
require_once 'includes/functions.php';
require_once 'includes/auth.php';
require_once 'includes/db.php';

$auth = new Auth();

// Check if user is logged in
if (!$auth->isLoggedIn()) {
    $_SESSION['error_message'] = 'Unauthorized';
    redirectTo('projects.php');
    exit;
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    $_SESSION['error_message'] = 'Invalid request method';
    redirectTo('projects.php');
    exit;
}

$db = Database::getInstance();
$project_id = isset($_POST['project_id']) ? (int)$_POST['project_id'] : 0;
$selection_name = isset($_POST['selection_name']) ? sanitizeInput($_POST['selection_name']) : '';
$required_size = isset($_POST['required_size']) ? (int)$_POST['required_size'] : 0;

if (!$project_id || empty($selection_name) || $required_size <= 0) {
    $_SESSION['error_message'] = 'Required fields are missing';
    redirectTo("manage_project.php?id=$project_id");
    exit;
}

// Verify user has access to this project
$stmt = $db->prepare("SELECT id FROM projects WHERE id = ? AND created_by = ?");
$stmt->bind_param('ii', $project_id, $_SESSION['user_id']);
$stmt->execute();
if ($stmt->get_result()->num_rows === 0) {
    $_SESSION['error_message'] = 'Access denied';
    redirectTo('projects.php');
    exit;
}

// Get all previously used panelist IDs in this project's selections
$stmt = $db->prepare("
    SELECT DISTINCT panelist_id 
    FROM selection_members sm
    JOIN selections s ON sm.selection_id = s.id
    WHERE s.project_id = ?
");
$stmt->bind_param('i', $project_id);
$stmt->execute();
$used_panelists_result = $stmt->get_result();
$used_panelists = [];
while ($row = $used_panelists_result->fetch_assoc()) {
    $used_panelists[] = $row['panelist_id'];
}

// Build WHERE clause based on filter criteria
$where_conditions = [];
$filter_descriptions = [];

// If we have used panelists, exclude them
if (!empty($used_panelists)) {
    $used_panelists_str = "'" . implode("','", $used_panelists) . "'";
    $where_conditions[] = "panelist_id NOT IN ($used_panelists_str)";
}

// Process filter criteria from attributes
$attributes = $db->query("SELECT id, name, choice_type FROM attributes ORDER BY created_at ASC");
while ($attr = $attributes->fetch_assoc()) {
    $attr_id = $attr['id'];
    
    if ($attr['choice_type'] === 'single') {
        // Single choice attributes
        if (isset($_POST["attr_{$attr_id}"]) && !empty($_POST["attr_{$attr_id}"])) {
            $value = $db->escape($_POST["attr_{$attr_id}"]);
            $where_conditions[] = "JSON_UNQUOTE(JSON_EXTRACT(attribute_values, '$.{$attr_id}')) = '{$value}'";
            $filter_descriptions[] = "{$attr['name']}: {$value}";
        }
    } else if ($attr['choice_type'] === 'multiple') {
        // Multiple choice attributes
        if (isset($_POST["attr_{$attr_id}"]) && is_array($_POST["attr_{$attr_id}"]) && !empty($_POST["attr_{$attr_id}"])) {
            $values = array_map(function($value) use ($db) {
                return $db->escape($value);
            }, $_POST["attr_{$attr_id}"]);
            
            $or_conditions = [];
            foreach ($values as $value) {
                // Use JSON_CONTAINS to check if array contains the value
                $or_conditions[] = "JSON_CONTAINS(attribute_values, '\"{$value}\"', '$.{$attr_id}')";
            }
            
            $where_conditions[] = '(' . implode(' OR ', $or_conditions) . ')';
            $filter_descriptions[] = "{$attr['name']}: " . implode(' OR ', $values);
        }
    }
}

// Log the SQL query for debugging
error_log("Selection filter conditions: " . implode(' AND ', $where_conditions));

// Build the SQL query to get eligible panelists
$sql = "SELECT panelist_id FROM panel_data";
if (!empty($where_conditions)) {
    $sql .= " WHERE " . implode(' AND ', $where_conditions);
}
$sql .= " ORDER BY RAND() LIMIT $required_size";
error_log("Selection SQL query: " . $sql);

$result = $db->query($sql);
if (!$result) {
    $_SESSION['error_message'] = 'Error querying panel data: ' . $db->getLastError();
    redirectTo("manage_project.php?id=$project_id");
    exit;
}

// Check if we have enough panelists
$panelists = [];
while ($row = $result->fetch_assoc()) {
    $panelists[] = $row['panelist_id'];
}

if (count($panelists) < $required_size) {
    $_SESSION['error_message'] = 'Not enough eligible panelists found. Only ' . count($panelists) . ' available.';
    redirectTo("manage_project.php?id=$project_id");
    exit;
}

// Begin transaction
$db->query("START TRANSACTION");

try {
    // Create the selection record
    $filters_text = implode(', ', $filter_descriptions);
    $stmt = $db->prepare("
        INSERT INTO selections (
            project_id, name, sample_size, filters, created_by
        ) VALUES (?, ?, ?, ?, ?)
    ");
    $stmt->bind_param('isisi', $project_id, $selection_name, $required_size, $filters_text, $_SESSION['user_id']);
    
    if (!$stmt->execute()) {
        throw new Exception("Failed to create selection: " . $db->getLastError());
    }
    
    $selection_id = $db->getLastInsertId();
    
    // Insert selection members
    $stmt = $db->prepare("
        INSERT INTO selection_members (selection_id, panelist_id) VALUES (?, ?)
    ");
    
    foreach ($panelists as $panelist_id) {
        $stmt->bind_param('is', $selection_id, $panelist_id);
        if (!$stmt->execute()) {
            throw new Exception("Failed to add selection member: " . $db->getLastError());
        }
    }
    
    // Commit the transaction
    $db->query("COMMIT");
    
    $_SESSION['success_message'] = "Selection '$selection_name' created successfully with $required_size panelists";
    redirectTo("manage_project.php?id=$project_id");
    
} catch (Exception $e) {
    // Rollback on error
    $db->query("ROLLBACK");
    $_SESSION['error_message'] = $e->getMessage();
    redirectTo("manage_project.php?id=$project_id");
}
?>