<?php
/**
 * Helper Functions
 * Research Survey Management System
 */

require_once __DIR__ . '/auth.php';

/**
 * Sanitize input string
 */
function sanitize($input) {
    if (is_array($input)) {
        return array_map('sanitize', $input);
    }
    return htmlspecialchars(strip_tags(trim($input)), ENT_QUOTES, 'UTF-8');
}

/**
 * Validate email
 */
function validateEmail($email) {
    return filter_var($email, FILTER_VALIDATE_EMAIL) !== false;
}

/**
 * Generate CSRF token
 */
function generateCSRFToken() {
    if (!isset($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    return $_SESSION['csrf_token'];
}

/**
 * Verify CSRF token
 */
function verifyCSRFToken($token) {
    if (!isset($_SESSION['csrf_token']) || empty($token)) {
        return false;
    }
    return hash_equals($_SESSION['csrf_token'], $token);
}

/**
 * Get surveys with pagination
 */
function getSurveys($page = 1, $perPage = ITEMS_PER_PAGE, $filters = []) {
    $pdo = getDBConnection();
    $offset = ($page - 1) * $perPage;
    
    $where = [];
    $params = [];
    
    if (!empty($filters['status'])) {
        $where[] = "s.status = :status";
        $params['status'] = $filters['status'];
    }
    
    if (!empty($filters['type'])) {
        $where[] = "s.survey_type = :type";
        $params['type'] = $filters['type'];
    }
    
    if (!empty($filters['search'])) {
        $where[] = "(s.title LIKE :search1 OR s.description LIKE :search2)";
        $params['search1'] = '%' . $filters['search'] . '%';
        $params['search2'] = '%' . $filters['search'] . '%';
    }
    
    if (!empty($filters['project_id'])) {
        $where[] = "s.project_id = :project_id";
        $params['project_id'] = $filters['project_id'];
    }
    
    if (isset($_SESSION['user_role']) && $_SESSION['user_role'] !== 'admin') {
        $where[] = "s.created_by = :user_id";
        $params['user_id'] = $_SESSION['user_id'];
    }
    
    $whereClause = !empty($where) ? "WHERE " . implode(" AND ", $where) : "";
    
    // Get total count
    $countStmt = $pdo->prepare("SELECT COUNT(*) FROM surveys s $whereClause");
    $countStmt->execute($params);
    $total = $countStmt->fetchColumn();
    
    // Get surveys
    $stmt = $pdo->prepare("
        SELECT s.*, u.full_name as creator_name, 
               COUNT(DISTINCT sq.question_id) as question_count,
               COUNT(DISTINCT sr.response_id) as response_count
        FROM surveys s
        JOIN users u ON s.created_by = u.user_id
        LEFT JOIN survey_questions sq ON s.survey_id = sq.survey_id
        LEFT JOIN survey_responses sr ON s.survey_id = sr.survey_id
        $whereClause
        GROUP BY s.survey_id
        ORDER BY s.created_at DESC
        LIMIT :limit OFFSET :offset
    ");
    
    foreach ($params as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    
    return [
        'data' => $stmt->fetchAll(),
        'total' => $total,
        'page' => $page,
        'perPage' => $perPage,
        'totalPages' => ceil($total / $perPage)
    ];
}

/**
 * Get survey with all its questions
 */
function getSurveyWithQuestions($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("SELECT * FROM surveys WHERE survey_id = :id");
    $stmt->execute(['id' => $surveyId]);
    $survey = $stmt->fetch();
    
    if (!$survey) {
        return null;
    }
    
    $stmt = $pdo->prepare("
        SELECT q.*, 
               GROUP_CONCAT(
                   JSON_OBJECT('option_id', o.option_id, 'option_text', o.option_text, 
                              'option_value', o.option_value, 'option_order', o.option_order,
                              'score_value', o.score_value)
                   ORDER BY o.option_order
               ) as options
        FROM survey_questions q
        LEFT JOIN question_options o ON q.question_id = o.question_id
        WHERE q.survey_id = :survey_id
        GROUP BY q.question_id
        ORDER BY q.question_order
    ");
    $stmt->execute(['survey_id' => $surveyId]);
    $questions = $stmt->fetchAll();
    
    // Parse options JSON for each question
    foreach ($questions as &$question) {
        if ($question['options']) {
            $question['options'] = json_decode('[' . str_replace('}{', '},{', $question['options']) . ']', true);
        } else {
            $question['options'] = [];
        }
    }
    
    $survey['questions'] = $questions;
    return $survey;
}

/**
 * Save survey response
 */
function saveSurveyResponse($surveyId, $answers) {
    $pdo = getDBConnection();
    
    try {
        $pdo->beginTransaction();
        
        // Create or get participant
        $participantId = null;
        if (isset($_SESSION['user_id'])) {
            $stmt = $pdo->prepare("SELECT participant_id FROM survey_participants 
                                   WHERE survey_id = :sid AND user_id = :uid AND status = 'invited'");
            $stmt->execute(['sid' => $surveyId, 'uid' => $_SESSION['user_id']]);
            $participantId = $stmt->fetchColumn();
        }
        
        if (!$participantId) {
            $stmt = $pdo->prepare("INSERT INTO survey_participants (survey_id, user_id, email, status, ip_address, user_agent) 
                                   VALUES (:sid, :uid, :email, 'started', :ip, :ua)");
            $stmt->execute([
                'sid' => $surveyId,
                'uid' => $_SESSION['user_id'] ?? null,
                'email' => $_POST['email'] ?? null,
                'ip' => $_SERVER['REMOTE_ADDR'] ?? '',
                'ua' => $_SERVER['HTTP_USER_AGENT'] ?? ''
            ]);
            $participantId = $pdo->lastInsertId();
        }
        
        // Insert response
        $token = bin2hex(random_bytes(32));
        $stmt = $pdo->prepare("INSERT INTO survey_responses (survey_id, participant_id, session_token, status, ip_address, user_agent) 
                               VALUES (:sid, :pid, :token, 'in_progress', :ip, :ua)");
        $stmt->execute([
            'sid' => $surveyId,
            'pid' => $participantId,
            'token' => $token,
            'ip' => $_SERVER['REMOTE_ADDR'] ?? '',
            'ua' => $_SERVER['HTTP_USER_AGENT'] ?? ''
        ]);
        $responseId = $pdo->lastInsertId();
        
        // Save answers
        foreach ($answers as $questionId => $answer) {
            $numericVal = null;
            $optionId = null;
            $value = null;
            
            if (is_array($answer)) {
                // Multiple choice or matrix
                $value = json_encode($answer);
                if (isset($answer['option_id'])) {
                    $optionId = $answer['option_id'];
                    $value = $answer['value'] ?? null;
                }
            } else {
                $value = $answer;
                if (is_numeric($answer)) {
                    $numericVal = $answer;
                }
            }
            
            $stmt = $pdo->prepare("INSERT INTO question_responses (response_id, question_id, option_id, response_value, response_numeric) 
                                   VALUES (:rid, :qid, :oid, :val, :num)");
            $stmt->execute([
                'rid' => $responseId,
                'qid' => $questionId,
                'oid' => $optionId,
                'val' => $value,
                'num' => $numericVal
            ]);
        }
        
        $pdo->commit();
        return $responseId;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        error_log("Failed to save survey response: " . $e->getMessage());
        return false;
    }
}

/**
 * Get analytics data for a survey
 */
function getSurveyAnalytics($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT 
            sa.total_responses,
            sa.completion_rate,
            sa.average_time_spent,
            sa.total_views,
            sa.last_response_date,
            sa.analytics_data,
            (SELECT AVG(qr.response_numeric) 
             FROM question_responses qr 
             JOIN survey_questions sq ON qr.question_id = sq.question_id 
             WHERE sq.survey_id = :sid1 AND qr.response_numeric IS NOT NULL) as overall_mean,
            (SELECT STDDEV(qr.response_numeric) 
             FROM question_responses qr 
             JOIN survey_questions sq ON qr.question_id = sq.question_id 
             WHERE sq.survey_id = :sid2 AND qr.response_numeric IS NOT NULL) as overall_std,
            sa.calculated_at
        FROM survey_analytics sa
        WHERE sa.survey_id = :sid3
    ");
    $stmt->execute(['sid1' => $surveyId, 'sid2' => $surveyId, 'sid3' => $surveyId]);
    return $stmt->fetch();
}

/**
 * Get all projects
 */
function getProjects($status = null) {
    $pdo = getDBConnection();
    
    $sql = "SELECT p.*, u.full_name as creator_name,
                   COUNT(DISTINCT s.survey_id) as survey_count
            FROM research_projects p
            JOIN users u ON p.created_by = u.user_id
            LEFT JOIN surveys s ON p.project_id = s.project_id";
    
    $params = [];
    if ($status) {
        $sql .= " WHERE p.status = :status";
        $params['status'] = $status;
    }
    
    if (isset($_SESSION['user_role']) && $_SESSION['user_role'] !== 'admin') {
        $sql .= $status ? " AND" : " WHERE";
        $sql .= " p.created_by = :user_id";
        $params['user_id'] = $_SESSION['user_id'];
    }
    
    $sql .= " GROUP BY p.project_id ORDER BY p.created_at DESC";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

/**
 * Check if user has access to a project
 */
function hasProjectAccess($projectId, $requiredRole = null) {
    $pdo = getDBConnection();
    $userId = $_SESSION['user_id'] ?? null;
    $userRole = $_SESSION['user_role'] ?? null;
    
    if (!$userId) return false;
    if ($userRole === 'admin') return true;
    
    $stmt = $pdo->prepare("SELECT role_in_project FROM project_members 
                           WHERE project_id = :pid AND user_id = :uid");
    $stmt->execute(['pid' => $projectId, 'uid' => $userId]);
    $memberRole = $stmt->fetchColumn();
    
    if (!$memberRole) return false;
    if ($requiredRole && $memberRole !== 'owner' && $memberRole !== $requiredRole) return false;
    
    return true;
}

/**
 * Get project members
 */
function getProjectMembers($projectId) {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("SELECT pm.*, u.username, u.email, u.full_name, u.role as system_role 
                           FROM project_members pm 
                           JOIN users u ON pm.user_id = u.user_id 
                           WHERE pm.project_id = :pid 
                           ORDER BY FIELD(pm.role_in_project, 'owner', 'researcher', 'analyst', 'viewer')");
    $stmt->execute(['pid' => $projectId]);
    return $stmt->fetchAll();
}

/**
 * Add project member
 */
function addProjectMember($projectId, $userId, $role = 'viewer') {
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("INSERT INTO project_members (project_id, user_id, role_in_project, joined_at) 
                           VALUES (:pid, :uid, :role, NOW()) 
                           ON DUPLICATE KEY UPDATE role_in_project = :role");
    $stmt->execute(['pid' => $projectId, 'uid' => $userId, 'role' => $role]);
}

/**
 * Calculate NPS for a survey question
 */
function calculateNPS($surveyId, $questionId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("CALL CalculateNPS(:sid, :qid)");
    $stmt->execute(['sid' => $surveyId, 'qid' => $questionId]);
    
    $stmt = $pdo->prepare("SELECT analytics_data FROM response_analytics 
                           WHERE question_id = :qid AND analytics_type = 'nps'");
    $stmt->execute(['qid' => $questionId]);
    return json_decode($stmt->fetchColumn(), true);
}

/**
 * Calculate CSAT for a survey question
 */
function calculateCSAT($surveyId, $questionId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("CALL CalculateCSAT(:sid, :qid)");
    $stmt->execute(['sid' => $surveyId, 'qid' => $questionId]);
    
    $stmt = $pdo->prepare("SELECT analytics_data FROM response_analytics 
                           WHERE question_id = :qid AND analytics_type = 'csat'");
    $stmt->execute(['qid' => $questionId]);
    return json_decode($stmt->fetchColumn(), true);
}

/**
 * Calculate Pearson correlation between two questions
 */
function calculateCorrelation($questionId1, $questionId2) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as n,
            AVG(qr1.response_numeric) as mean1,
            AVG(qr2.response_numeric) as mean2,
            STDDEV(qr1.response_numeric) as std1,
            STDDEV(qr2.response_numeric) as std2,
            SUM((qr1.response_numeric - AVG(qr1.response_numeric)) * 
                (qr2.response_numeric - AVG(qr2.response_numeric))) as covar
        FROM question_responses qr1
        JOIN survey_responses sr1 ON qr1.response_id = sr1.response_id
        JOIN question_responses qr2 ON sr1.response_id = qr2.response_id
        WHERE qr1.question_id = :qid1 
          AND qr2.question_id = :qid2
          AND qr1.response_numeric IS NOT NULL
          AND qr2.response_numeric IS NOT NULL
          AND sr1.status = 'completed'
    ");
    $stmt->execute(['qid1' => $questionId1, 'qid2' => $questionId2]);
    $result = $stmt->fetch();
    
    if (!$result || $result['n'] < 3) return null;
    
    $n = $result['n'];
    $covar = $result['covar'];
    $std1 = $result['std1'];
    $std2 = $result['std2'];
    
    if ($std1 == 0 || $std2 == 0) return null;
    
    $r = $covar / ($n * $std1 * $std2);
    return [
        'pearson_r' => $r,
        'r_squared' => $r * $r,
        'n' => $n,
        'interpretation' => abs($r) > 0.7 ? 'Strong' : (abs($r) > 0.4 ? 'Moderate' : 'Weak')
    ];
}

/**
 * Calculate simple linear regression coefficients
 */
function calculateLinearRegression($questionIdX, $questionIdY) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as n,
            AVG(qr1.response_numeric) as mean_x,
            AVG(qr2.response_numeric) as mean_y,
            STDDEV(qr1.response_numeric) as std_x,
            SUM((qr1.response_numeric - AVG(qr1.response_numeric)) * 
                (qr2.response_numeric - AVG(qr2.response_numeric))) as ss_xy,
            SUM(POWER(qr1.response_numeric - AVG(qr1.response_numeric), 2)) as ss_xx
        FROM question_responses qr1
        JOIN survey_responses sr1 ON qr1.response_id = sr1.response_id
        JOIN question_responses qr2 ON sr1.response_id = qr2.response_id
        WHERE qr1.question_id = :qx 
          AND qr2.question_id = :qy
          AND qr1.response_numeric IS NOT NULL
          AND qr2.response_numeric IS NOT NULL
          AND sr1.status = 'completed'
    ");
    $stmt->execute(['qx' => $questionIdX, 'qy' => $questionIdY]);
    $result = $stmt->fetch();
    
    if (!$result || $result['n'] < 3 || $result['ss_xx'] == 0) return null;
    
    $slope = $result['ss_xy'] / $result['ss_xx'];
    $intercept = $result['mean_y'] - $slope * $result['mean_x'];
    
    return [
        'slope' => $slope,
        'intercept' => $intercept,
        'equation' => "y = " . round($slope, 4) . "x + " . round($intercept, 4),
        'n' => $result['n']
    ];
}

/**
 * Calculate cross-tabulation between two questions
 */
function calculateCrossTabulation($questionId1, $questionId2) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT qr1.response_value as val1, qr2.response_value as val2, COUNT(*) as count
        FROM question_responses qr1
        JOIN survey_responses sr ON qr1.response_id = sr.response_id
        JOIN question_responses qr2 ON sr.response_id = qr2.response_id
        WHERE qr1.question_id = :qid1 
          AND qr2.question_id = :qid2
          AND sr.status = 'completed'
          AND qr1.response_value IS NOT NULL
          AND qr2.response_value IS NOT NULL
        GROUP BY qr1.response_value, qr2.response_value
        ORDER BY count DESC
    ");
    $stmt->execute(['qid1' => $questionId1, 'qid2' => $questionId2]);
    return $stmt->fetchAll();
}

/**
 * Calculate Chi-square for cross-tabulation
 */
function calculateChiSquare($crossTabData, $totalResponses) {
    $chiSquare = 0;
    $rowTotals = [];
    $colTotals = [];
    $table = [];
    
    foreach ($crossTabData as $row) {
        $table[$row['val1']][$row['val2']] = (int)$row['count'];
        $rowTotals[$row['val1']] = ($rowTotals[$row['val1']] ?? 0) + (int)$row['count'];
        $colTotals[$row['val2']] = ($colTotals[$row['val2']] ?? 0) + (int)$row['count'];
    }
    
    foreach ($table as $r => $cols) {
        foreach ($cols as $c => $observed) {
            $expected = ($rowTotals[$r] * $colTotals[$c]) / $totalResponses;
            if ($expected > 0) {
                $chiSquare += pow($observed - $expected, 2) / $expected;
            }
        }
    }
    
    return $chiSquare;
}

/**
 * Quantitize qualitative data (convert text to numerical scales)
 */
function quantitizeResponses($questionId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("SELECT response_id, response_value, sentiment_score, file_url 
                           FROM question_responses 
                           WHERE question_id = :qid AND response_value IS NOT NULL");
    $stmt->execute(['qid' => $questionId]);
    $responses = $stmt->fetchAll();
    
    $quantitized = [];
    foreach ($responses as $resp) {
        $score = 0;
        $metrics = [];
        
        if ($resp['sentiment_score'] !== null) {
            $score += ($resp['sentiment_score'] + 1) * 2.5;
            $metrics['sentiment_score'] = $resp['sentiment_score'];
        }
        
        $wordCount = str_word_count($resp['response_value']);
        $metrics['word_count'] = $wordCount;
        $metrics['engagement_score'] = min($wordCount / 50, 5);
        $score += min($wordCount / 50, 5);
        
        $quantitized[] = [
            'response_id' => $resp['response_id'],
            'original_text' => $resp['response_value'],
            'composite_score' => round($score, 2),
            'metrics' => $metrics
        ];
    }
    
    return $quantitized;
}

/**
 * Generate mixed methods joint display
 */
function generateJointDisplay($surveyId, $themeCodeId = null) {
    $pdo = getDBConnection();
    
    $sql = "
        SELECT 
            q.question_id,
            q.question_text,
            q.question_type,
            COUNT(DISTINCT sr.response_id) as response_count,
            AVG(qr.response_numeric) as avg_score,
            COUNT(DISTINCT ca.coding_assignment_id) as theme_count
        FROM survey_questions q
        LEFT JOIN question_responses qr ON q.question_id = qr.question_id
        LEFT JOIN survey_responses sr ON qr.response_id = sr.response_id AND sr.status = 'completed'
        LEFT JOIN coding_assignments ca ON qr.question_response_id = ca.question_response_id
        WHERE q.survey_id = :sid
    ";
    $params = ['sid' => $surveyId];
    
    if ($themeCodeId) {
        $sql .= " AND ca.code_id = :cid";
        $params['cid'] = $themeCodeId;
    }
    
    $sql .= " GROUP BY q.question_id ORDER BY q.question_order";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

/**
 * Calculate inter-rater reliability (Cohen's Kappa)
 */
function calculateCohensKappa($coder1Id, $coder2Id, $themeCodeId = null) {
    $pdo = getDBConnection();
    
    $sql = "
        SELECT 
            ca1.response_id,
            MAX(CASE WHEN ca1.coder_id = :c1 THEN 1 ELSE 0 END) as coder1_coded,
            MAX(CASE WHEN ca2.coder_id = :c2 THEN 1 ELSE 0 END) as coder2_coded
        FROM coding_assignments ca1
        JOIN coding_assignments ca2 ON ca1.response_id = ca2.response_id 
            AND ca1.coding_assignment_id != ca2.coding_assignment_id
        WHERE ca1.coder_id = :c1 AND ca2.coder_id = :c2
    ";
    $params = ['c1' => $coder1Id, 'c2' => $coder2Id];
    
    if ($themeCodeId) {
        $sql .= " AND ca1.code_id = :cid";
        $params['cid'] = $themeCodeId;
    }
    
    $sql .= " GROUP BY ca1.response_id";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $pairs = $stmt->fetchAll();
    
    $bothCoded = 0;
    $agree = 0;
    
    foreach ($pairs as $pair) {
        if ($pair['coder1_coded'] && $pair['coder2_coded']) {
            $bothCoded++;
            if ($pair['coder1_coded'] == $pair['coder2_coded']) {
                $agree++;
            }
        }
    }
    
    if ($bothCoded == 0) return null;
    
    $p_e = ($agree / $bothCoded);
    $kappa = ($p_e > 0) ? ($agree - ($bothCoded * $p_e * $p_e)) / (1 - ($bothCoded * $p_e * $p_e)) : 0;
    
    return [
        'kappa' => $kappa,
        'agreements' => $agree,
        'total_pairs' => $bothCoded,
        'interpretation' => $kappa > 0.75 ? 'Excellent' : ($kappa > 0.6 ? 'Good' : ($kappa > 0.4 ? 'Fair' : 'Poor'))
    ];
}

/**
 * Calculate T-test between two groups
 */
function calculateTTest($questionId, $group1Field, $group1Value, $group2Field, $group2Value) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as n,
            AVG(qr.response_numeric) as mean,
            STDDEV(qr.response_numeric) as std
        FROM question_responses qr
        JOIN survey_responses sr ON qr.response_id = sr.response_id
        JOIN survey_participants sp ON sr.participant_id = sp.participant_id
        WHERE qr.question_id = :qid 
          AND sp.{$group1Field} = :gval
          AND sr.status = 'completed'
          AND qr.response_numeric IS NOT NULL
    ");
    $stmt->execute(['qid' => $questionId, 'gval' => $group1Value]);
    $group1 = $stmt->fetch();
    
    $stmt->execute(['qid' => $questionId, 'gval' => $group2Value]);
    $group2 = $stmt->fetch();
    
    if (!$group1 || !$group2 || $group1['n'] < 2 || $group2['n'] < 2) return null;
    
    $n1 = $group1['n'];
    $n2 = $group2['n'];
    $mean1 = $group1['mean'];
    $mean2 = $group2['mean'];
    $std1 = $group1['std'] ?: 0.001;
    $std2 = $group2['std'] ?: 0.001;
    
    $pooledStd = sqrt((($n1 - 1) * $std1 * $std1 + ($n2 - 1) * $std2 * $std2) / ($n1 + $n2 - 2));
    $se = $pooledStd * sqrt(1/$n1 + 1/$n2);
    $t = $se > 0 ? ($mean1 - $mean2) / $se : 0;
    
    return [
        't_statistic' => $t,
        'group1_mean' => $mean1,
        'group2_mean' => $mean2,
        'group1_n' => $n1,
        'group2_n' => $n2,
        'pooled_std' => $pooledStd
    ];
}

/**
 * Get sentiment analysis results for a survey
 */
function getSentimentAnalysis($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT 
            AVG(qr.sentiment_score) as avg_sentiment,
            COUNT(*) as total_with_sentiment,
            SUM(CASE WHEN qr.sentiment_score > 0.3 THEN 1 ELSE 0 END) as positive,
            SUM(CASE WHEN qr.sentiment_score BETWEEN -0.3 AND 0.3 THEN 1 ELSE 0 END) as neutral,
            SUM(CASE WHEN qr.sentiment_score < -0.3 THEN 1 ELSE 0 END) as negative
        FROM question_responses qr
        JOIN survey_questions q ON qr.question_id = q.question_id
        JOIN survey_responses sr ON qr.response_id = sr.response_id
        WHERE sr.survey_id = :sid 
          AND q.question_type IN ('text_short', 'text_long', 'rich_text')
          AND qr.sentiment_score IS NOT NULL
          AND sr.status = 'completed'
    ");
    $stmt->execute(['sid' => $surveyId]);
    return $stmt->fetch();
}

/**
 * Get keyword frequency for text analysis
 */
function getKeywordFrequency($questionId, $limit = 20) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT response_value FROM question_responses 
        WHERE question_id = :qid AND response_value IS NOT NULL AND response_value != ''
    ");
    $stmt->execute(['qid' => $questionId]);
    $texts = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (empty($texts)) return [];
    
    $allText = implode(' ', $texts);
    $words = preg_split('/\s+/', strtolower($allText));
    
    $stopWords = ['the', 'is', 'at', 'which', 'on', 'and', 'a', 'an', 'as', 'for', 'to', 'of', 'in', 'it', 'that', 'this', 'with', 'but', 'are', 'was', 'be', 'have', 'has', 'had', 'not', 'they', 'their', 'them', 'so', 'or', 'if', 'can', 'will', 'would', 'should', 'could', 'may', 'might', 'must', 'shall', 'do', 'did', 'does', 'i', 'you', 'he', 'she', 'we', 'they', 'me', 'him', 'her', 'us', 'my', 'your', 'his', 'its', 'our', 'what', 'when', 'where', 'who', 'why', 'how', 'all', 'each', 'every', 'both', 'few', 'more', 'most', 'other', 'some', 'such', 'no', 'only', 'own', 'same', 'than', 'too', 'very', 'just', 'because', 'but', 'and', 'or', 'so', 'then'];
    
    $filtered = array_filter($words, function($w) use ($stopWords) {
        return strlen($w) > 2 && !in_array($w, $stopWords) && !is_numeric($w);
    });
    
    $freq = array_count_values($filtered);
    arsort($freq);
    
    return array_slice($freq, 0, $limit, true);
}

/**
 * Calculate survey analytics using stored procedure
 */
function recalculateSurveyAnalytics($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("CALL CalculateSurveyAnalytics(:sid)");
    $stmt->execute(['sid' => $surveyId]);
    
    $stmt = $pdo->prepare("SELECT survey_id FROM survey_questions WHERE survey_id = :sid");
    $stmt->execute(['sid' => $surveyId]);
    $questions = $stmt->fetchAll();
    
    foreach ($questions as $q) {
        $stmt = $pdo->prepare("CALL CalculateQuestionAnalytics(:qid)");
        $stmt->execute(['qid' => $q['survey_id'] > 0 ? $q['question_id'] : 0]);
        
        $qTypes = ['rating_scale', 'likert_scale', 'numerical', 'yes_no'];
        $stmt = $pdo->prepare("SELECT question_type FROM survey_questions WHERE question_id = :qid");
        $stmt->execute(['qid' => $q['question_id']]);
        $type = $stmt->fetchColumn();
        
        if (in_array($type, $qTypes)) {
            $stmt = $pdo->prepare("CALL CalculateNPS(:sid, :qid)");
            $stmt->execute(['sid' => $surveyId, 'qid' => $q['question_id']]);
        }
    }
}

/**
 * Check if user can view survey (project access or creator)
 */
function canViewSurvey($surveyId) {
    $pdo = getDBConnection();
    $userId = $_SESSION['user_id'] ?? null;
    $userRole = $_SESSION['user_role'] ?? null;
    
    if (!$userId) return false;
    if ($userRole === 'admin') return true;
    
    $stmt = $pdo->prepare("SELECT created_by, project_id FROM surveys WHERE survey_id = :sid");
    $stmt->execute(['sid' => $surveyId]);
    $survey = $stmt->fetch();
    
    if (!$survey) return false;
    
    if ($survey['created_by'] == $userId) return true;
    if ($survey['project_id'] && hasProjectAccess($survey['project_id'])) return true;
    
    return false;
}

/**
 * Check if user can edit survey
 */
function canEditSurvey($surveyId) {
    $pdo = getDBConnection();
    $userId = $_SESSION['user_id'] ?? null;
    $userRole = $_SESSION['user_role'] ?? null;
    
    if (!$userId) return false;
    if ($userRole === 'admin') return true;
    
    $stmt = $pdo->prepare("SELECT created_by, project_id FROM surveys WHERE survey_id = :sid");
    $stmt->execute(['sid' => $surveyId]);
    $survey = $stmt->fetch();
    
    if (!$survey) return false;
    
    if ($survey['created_by'] == $userId) return true;
    if ($survey['project_id']) {
        $stmt = $pdo->prepare("SELECT role_in_project FROM project_members 
                               WHERE project_id = :pid AND user_id = :uid 
                               AND role_in_project IN ('owner', 'researcher')");
        $stmt->execute(['pid' => $survey['project_id'], 'uid' => $userId]);
        if ($stmt->fetch()) return true;
    }
    
    return false;
}

/**
 * Get surveys accessible to current user
 */
function getAccessibleSurveys($page = 1, $perPage = ITEMS_PER_PAGE, $filters = []) {
    $pdo = getDBConnection();
    $userId = $_SESSION['user_id'] ?? null;
    $userRole = $_SESSION['user_role'] ?? 'viewer';
    $offset = ($page - 1) * $perPage;
    
    $where = ["1 = 1"];
    $params = [];
    
    if ($userRole !== 'admin') {
        $where[] = "(s.created_by = :user_id OR s.project_id IN (
            SELECT DISTINCT pm.project_id FROM project_members pm WHERE pm.user_id = :user_id2
        ))";
        $params['user_id'] = $userId;
        $params['user_id2'] = $userId;
    }
    
    if (!empty($filters['status'])) {
        $where[] = "s.status = :status";
        $params['status'] = $filters['status'];
    }
    if (!empty($filters['type'])) {
        $where[] = "s.survey_type = :type";
        $params['type'] = $filters['type'];
    }
    if (!empty($filters['project_id'])) {
        $where[] = "s.project_id = :project_id";
        $params['project_id'] = $filters['project_id'];
    }
    if (!empty($filters['search'])) {
        $where[] = "(s.title LIKE :search1 OR s.description LIKE :search2)";
        $params['search1'] = '%' . $filters['search'] . '%';
        $params['search2'] = '%' . $filters['search'] . '%';
    }
    
    $whereClause = implode(" AND ", $where);
    
    $countStmt = $pdo->prepare("SELECT COUNT(*) FROM surveys s WHERE $whereClause");
    $countStmt->execute($params);
    $total = $countStmt->fetchColumn();
    
    $stmt = $pdo->prepare("
        SELECT s.*, u.full_name as creator_name, 
               COUNT(DISTINCT sq.question_id) as question_count,
               COUNT(DISTINCT sr.response_id) as response_count,
               p.project_name
        FROM surveys s
        JOIN users u ON s.created_by = u.user_id
        LEFT JOIN survey_questions sq ON s.survey_id = sq.survey_id
        LEFT JOIN survey_responses sr ON s.survey_id = sr.survey_id
        LEFT JOIN research_projects p ON s.project_id = p.project_id
        WHERE $whereClause
        GROUP BY s.survey_id
        ORDER BY s.created_at DESC
        LIMIT :limit OFFSET :offset
    ");
    
    foreach ($params as $key => $value) {
        $stmt->bindValue(":$key", $value);
    }
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    
    return [
        'data' => $stmt->fetchAll(),
        'total' => $total,
        'page' => $page,
        'perPage' => $perPage,
        'totalPages' => ceil($total / $perPage)
    ];
}

/**
 * Calculate advanced metrics (NPS, CSAT, CES) for a survey
 */
function getAdvancedMetrics($surveyId) {
    $pdo = getDBConnection();
    $metrics = [
        'nps' => null,
        'csat' => null,
        'ces' => null,
        'overall' => []
    ];
    
    $stmt = $pdo->prepare("SELECT question_id, question_type FROM survey_questions 
                           WHERE survey_id = :sid AND question_type IN ('rating_scale', 'likert_scale', 'numerical', 'yes_no')");
    $stmt->execute(['sid' => $surveyId]);
    $numericQuestions = $stmt->fetchAll();
    
    foreach ($numericQuestions as $q) {
        $nps = calculateNPS($surveyId, $q['question_id']);
        if ($nps) $metrics['nps'] = $nps;
        
        $csat = calculateCSAT($surveyId, $q['question_id']);
        if ($csat) $metrics['csat'] = $csat;
    }
    
    $stmt = $pdo->prepare("
        SELECT 
            AVG(qr.response_numeric) as avg_score,
            COUNT(DISTINCT sr.response_id) as responses
        FROM question_responses qr
        JOIN survey_questions q ON qr.question_id = q.question_id
        JOIN survey_responses sr ON qr.response_id = sr.response_id
        WHERE sr.survey_id = :sid 
          AND q.question_type IN ('rating_scale', 'likert_scale', 'numerical')
          AND qr.response_numeric IS NOT NULL
          AND sr.status = 'completed'
    ");
    $stmt->execute(['sid' => $surveyId]);
    $overall = $stmt->fetch();
    
    if ($overall) {
        $metrics['overall'] = $overall;
    }
    
    return $metrics;
}

/**
 * Calculate Cronbach's Alpha for reliability
 */
function calculateCronbachAlpha($questionIds) {
    if (count($questionIds) < 2) return null;
    
    $pdo = getDBConnection();
    
    $placeholders = implode(',', array_fill(0, count($questionIds), '?'));
    $stmt = $pdo->prepare("
        SELECT sr.response_id,
               SUM(CASE WHEN qr.question_id = :qid THEN qr.response_numeric ELSE 0 END) as total
        FROM survey_responses sr
        JOIN question_responses qr ON sr.response_id = qr.response_id
        WHERE sr.status = 'completed'
          AND qr.question_id IN ($placeholders)
        GROUP BY sr.response_id
        HAVING COUNT(DISTINCT qr.question_id) = :count
    ");
    
    $params = array_merge([count($questionIds)], $questionIds, [count($questionIds)]);
    $stmt->execute($params);
    
    $totals = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (count($totals) < 3) return null;
    
    $n = count($totals);
    $sum = array_sum($totals);
    $mean = $sum / $n;
    
    $varianceSum = 0;
    foreach ($totals as $total) {
        $varianceSum += pow($total - $mean, 2);
    }
    
    $k = count($questionIds);
    $alpha = ($k / ($k - 1)) * (1 - ($varianceSum / ($n * pow(array_sum(array_map(function($x) use ($mean) { return pow($x - $mean, 2); }, $totals)) / $n * $n, 2))));
    
    return [
        'alpha' => max(0, $alpha),
        'n_responses' => $n,
        'n_items' => $k
    ];
}

/**
 * Format percentage
 */
function formatPercentage($value, $decimals = 1) {
    return number_format($value, $decimals) . '%';
}

/**
 * Generate random response token
 */
function generateResponseToken() {
    return bin2hex(random_bytes(32));
}

/**
 * Validate survey access token
 */
function validateSurveyToken($surveyId, $token) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT sp.participant_id, sp.status, sp.survey_id, s.status as survey_status
        FROM survey_participants sp
        JOIN surveys s ON sp.survey_id = s.survey_id
        WHERE sp.survey_id = :sid AND sp.token = :token
    ");
    $stmt->execute(['sid' => $surveyId, 'token' => $token]);
    return $stmt->fetch();
}

/**
 * Export survey to different formats
 */
function exportSurveyData($surveyId, $format = 'csv') {
    $survey = getSurveyWithQuestions($surveyId);
    if (!$survey) return null;
    
    $pdo = getDBConnection();
    $stmt = $pdo->prepare("
        SELECT sr.response_id, sr.completed_at, sr.time_spent_seconds,
               qr.question_id, qr.response_value, qr.response_numeric,
               q.question_text, q.question_type
        FROM survey_responses sr
        JOIN question_responses qr ON sr.response_id = qr.response_id
        JOIN survey_questions q ON qr.question_id = q.question_id
        WHERE sr.survey_id = :sid AND sr.status = 'completed'
        ORDER BY sr.response_id, q.question_order
    ");
    $stmt->execute(['sid' => $surveyId]);
    $rows = $stmt->fetchAll();
    
    $responses = [];
    foreach ($rows as $r) {
        $responses[$r['response_id']]['info'] = [
            'completed_at' => $r['completed_at'],
            'time_spent' => $r['time_spent_seconds']
        ];
        $responses[$r['response_id']]['answers'][$r['question_id']] = $r['response_numeric'] ?? $r['response_value'] ?? '';
    }
    
    return [
        'survey' => $survey,
        'responses' => $responses,
        'format' => $format
    ];
}

/**
 * Calculate mean from array
 */
function calculateMean($values) {
    if (empty($values)) return 0;
    return array_sum($values) / count($values);
}

/**
 * Calculate median from array
 */
function calculateMedian($values) {
    if (empty($values)) return 0;
    sort($values);
    $n = count($values);
    $mid = floor($n / 2);
    return ($n % 2) ? $values[$mid] : ($values[$mid - 1] + $values[$mid]) / 2;
}

/**
 * Calculate mode from array
 */
function calculateMode($values) {
    if (empty($values)) return 0;
    $counts = array_count_values($values);
    arsort($counts);
    return key($counts);
}

/**
 * Calculate variance from array
 */
function calculateVariance($values) {
    if (count($values) < 2) return 0;
    $mean = calculateMean($values);
    $variance = 0;
    foreach ($values as $v) {
        $variance += pow($v - $mean, 2);
    }
    return $variance / (count($values) - 1);
}

/**
 * Calculate standard deviation from array
 */
function calculateStdDev($values) {
    return sqrt(calculateVariance($values));
}

/**
 * Get frequency distribution
 */
function getFrequencyDistribution($values) {
    if (empty($values)) return [];
    return array_count_values($values);
}

/**
 * Normalize data to 0-100 scale
 */
function normalizeData($values, $min = null, $max = null) {
    if (empty($values)) return [];
    
    $min = $min ?? min($values);
    $max = $max ?? max($values);
    $range = $max - $min;
    
    if ($range == 0) return array_fill_keys(array_keys($values), 50);
    
    $normalized = [];
    foreach ($values as $key => $val) {
        $normalized[$key] = (($val - $min) / $range) * 100;
    }
    
    return $normalized;
}

/**
 * Calculate weighted mean
 */
function calculateWeightedMean($values, $weights) {
    if (empty($values) || empty($weights) || count($values) != count($weights)) return 0;
    
    $sumWeighted = 0;
    $sumWeights = 0;
    
    foreach ($values as $i => $val) {
        $sumWeighted += $val * $weights[$i];
        $sumWeights += $weights[$i];
    }
    
    return $sumWeights > 0 ? $sumWeighted / $sumWeights : 0;
}

/**
 * Send email notification (stub for future implementation)
 */
function sendEmailNotification($to, $subject, $body, $from = null) {
    return true;
}

/**
 * Generate PDF report (stub - requires external library)
 */
function generatePDFReport($surveyId) {
    return ['error' => 'PDF generation requires external library integration'];
}

/**
 * Validate survey completion
 */
function validateSurveyCompletion($surveyId, $answers) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("SELECT question_id, is_required FROM survey_questions 
                           WHERE survey_id = :sid AND is_required = 1");
    $stmt->execute(['sid' => $surveyId]);
    $requiredQuestions = $stmt->fetchAll();
    
    $missing = [];
    foreach ($requiredQuestions as $q) {
        if (!isset($answers[$q['question_id']]) || empty($answers[$q['question_id']])) {
            $missing[] = $q['question_id'];
        }
    }
    
    return [
        'valid' => empty($missing),
        'missing_questions' => $missing
    ];
}

/**
 * Log system event
 */
function logSystemEvent($eventName, $eventType = 'scheduled', $status = 'pending', $resultData = null, $errorMessage = null) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("INSERT INTO system_events (event_name, event_type, status, result_data, error_message) 
                           VALUES (:name, :type, :status, :data, :error)");
    $stmt->execute([
        'name' => $eventName,
        'type' => $eventType,
        'status' => $status,
        'data' => $resultData ? json_encode($resultData) : null,
        'error' => $errorMessage
    ]);
}

/**
 * Get survey completion statistics
 */
function getSurveyCompletionStats($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
            SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress,
            SUM(CASE WHEN status = 'disqualified' THEN 1 ELSE 0 END) as disqualified,
            AVG(CASE WHEN status = 'completed' THEN time_spent_seconds END) as avg_time,
            MIN(CASE WHEN status = 'completed' THEN time_spent_seconds END) as min_time,
            MAX(CASE WHEN status = 'completed' THEN time_spent_seconds END) as max_time
        FROM survey_responses
        WHERE survey_id = :sid
    ");
    $stmt->execute(['sid' => $surveyId]);
    return $stmt->fetch();
}

/**
 * Get response time statistics
 */
function getResponseTimeStats($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT 
            DATE(started_at) as date,
            COUNT(*) as count,
            AVG(time_spent_seconds) as avg_time
        FROM survey_responses
        WHERE survey_id = :sid AND status = 'completed'
        GROUP BY DATE(started_at)
        ORDER BY date
    ");
    $stmt->execute(['sid' => $surveyId]);
    return $stmt->fetchAll();
}

/**
 * Calculate dropout rate per question
 */
function getDropoutRate($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("
        SELECT q.question_id, q.question_text, q.question_order,
               COUNT(DISTINCT qr.response_id) as answered_count,
               (
                   SELECT COUNT(DISTINCT sr2.response_id)
                   FROM survey_responses sr2
                   WHERE sr2.survey_id = :sid 
                     AND sr2.status = 'in_progress'
                     AND sr2.started_at >= (
                         SELECT MIN(sr3.started_at)
                         FROM survey_responses sr3
                         WHERE sr3.survey_id = :sid
                     )
               ) as total_started
        FROM survey_questions q
        LEFT JOIN question_responses qr ON q.question_id = qr.question_id
        WHERE q.survey_id = :sid
        GROUP BY q.question_id
        ORDER BY q.question_order
    ");
    $stmt->execute(['sid' => $surveyId]);
    $results = $stmt->fetchAll();
    
    foreach ($results as &$r) {
        $r['dropout_rate'] = $r['total_started'] > 0 ? 
            (1 - ($r['answered_count'] / $r['total_started'])) * 100 : 0;
    }
    
    return $results;
}

/**
 * Calculate survey reliability metrics
 */
function getSurveyReliability($surveyId) {
    $pdo = getDBConnection();
    
    $stmt = $pdo->prepare("SELECT question_id FROM survey_questions 
                           WHERE survey_id = :sid AND question_type IN ('rating_scale', 'likert_scale', 'numerical')");
    $stmt->execute(['sid' => $surveyId]);
    $questionIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (count($questionIds) < 2) return null;
    
    return calculateCronbachAlpha($questionIds);
}

/**
 * Format time duration
 */
function formatDuration($seconds) {
    if ($seconds < 60) {
        return $seconds . ' seconds';
    } elseif ($seconds < 3600) {
        return floor($seconds / 60) . ' min ' . ($seconds % 60) . ' sec';
    } else {
        return floor($seconds / 3600) . 'h ' . floor(($seconds % 3600) / 60) . 'm';
    }
}

/**
 * Format date
 */
function formatDate($date, $format = 'M d, Y H:i') {
    if (empty($date)) return 'N/A';
    return date($format, strtotime($date));
}

/**
 * Get status badge HTML
 */
function getStatusBadge($status) {
    $colors = [
        'active' => 'success',
        'completed' => 'info',
        'draft' => 'secondary',
        'archived' => 'dark',
        'closed' => 'warning',
        'in_progress' => 'primary',
        'invited' => 'info',
        'partial' => 'warning',
        'disqualified' => 'danger',
        'expired' => 'secondary'
    ];
    
    $color = $colors[$status] ?? 'secondary';
    return "<span class='badge bg-{$color}'>{$status}</span>";
}

/**
 * Send JSON response
 */
function jsonResponse($data, $statusCode = 200) {
    http_response_code($statusCode);
    header('Content-Type: application/json');
    echo json_encode($data);
    exit;
}

/**
 * Handle file upload
 */
function handleFileUpload($file, $subfolder = '') {
    if ($file['error'] !== UPLOAD_ERR_OK) {
        return ['error' => 'Upload failed with error code: ' . $file['error']];
    }
    
    if ($file['size'] > MAX_UPLOAD_SIZE) {
        return ['error' => 'File size exceeds maximum allowed size of ' . (MAX_UPLOAD_SIZE / 1024 / 1024) . 'MB'];
    }
    
    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($ext, ALLOWED_FILE_TYPES)) {
        return ['error' => 'File type not allowed. Allowed types: ' . implode(', ', ALLOWED_FILE_TYPES)];
    }
    
    $uploadDir = UPLOAD_DIR . ($subfolder ? $subfolder . '/' : '');
    if (!is_dir($uploadDir)) {
        mkdir($uploadDir, 0755, true);
    }
    
    $filename = uniqid() . '_' . bin2hex(random_bytes(8)) . '.' . $ext;
    $filepath = $uploadDir . $filename;
    
    if (move_uploaded_file($file['tmp_name'], $filepath)) {
        return ['success' => true, 'filename' => $filename, 'path' => 'assets/uploads/' . ($subfolder ? $subfolder . '/' : '') . $filename];
    }
    
    return ['error' => 'Failed to move uploaded file'];
}