-- ============================================================
-- Research Survey Management System - Stored Procedures
-- ============================================================

USE research_survey_system;

DELIMITER //

-- ============================================================
-- Session Management
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS CleanupExpiredSessions()
BEGIN
    UPDATE user_sessions 
    SET is_active = 0 
    WHERE expires_at < NOW() AND is_active = 1;
    
    DELETE FROM user_sessions 
    WHERE expires_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
    
    INSERT INTO system_events (event_name, event_type, status, completed_at, result_data)
    VALUES ('CleanupExpiredSessions', 'scheduled', 'completed', NOW(), 
            JSON_OBJECT('rows_affected', ROW_COUNT()));
END//

-- ============================================================
-- Survey Analytics Calculations
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS CalculateSurveyAnalytics(IN p_survey_id INT)
BEGIN
    DECLARE v_total INT;
    DECLARE v_completed INT;
    DECLARE v_completion_rate DECIMAL(5,2);
    DECLARE v_avg_time INT;
    DECLARE v_last_response DATETIME;
    
    SELECT COUNT(*) INTO v_total 
    FROM survey_responses 
    WHERE survey_id = p_survey_id;
    
    SELECT COUNT(*) INTO v_completed 
    FROM survey_responses 
    WHERE survey_id = p_survey_id AND status = 'completed';
    
    SET v_completion_rate = IF(v_total > 0, (v_completed / v_total) * 100, 0);
    
    SELECT COALESCE(AVG(time_spent_seconds), 0) INTO v_avg_time
    FROM survey_responses 
    WHERE survey_id = p_survey_id AND status = 'completed';
    
    SELECT MAX(completed_at) INTO v_last_response
    FROM survey_responses 
    WHERE survey_id = p_survey_id AND status = 'completed';
    
    INSERT INTO survey_analytics (survey_id, total_responses, completion_rate, 
                                  average_time_spent, last_response_date, analytics_data)
    VALUES (p_survey_id, v_total, v_completion_rate, v_avg_time, v_last_response,
            JSON_OBJECT('completed', v_completed, 'in_progress', 
                       (SELECT COUNT(*) FROM survey_responses WHERE survey_id = p_survey_id AND status = 'in_progress'),
                       'disqualified', 
                       (SELECT COUNT(*) FROM survey_responses WHERE survey_id = p_survey_id AND status = 'disqualified')))
    ON DUPLICATE KEY UPDATE
        total_responses = v_total,
        completion_rate = v_completion_rate,
        average_time_spent = v_avg_time,
        last_response_date = v_last_response,
        calculated_at = NOW();
END//

-- ============================================================
-- Question Analytics - Descriptive Statistics
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS CalculateQuestionAnalytics(IN p_question_id INT)
BEGIN
    DECLARE v_count INT;
    DECLARE v_mean DECIMAL(15,5);
    DECLARE v_median DECIMAL(15,5);
    DECLARE v_std DECIMAL(15,5);
    DECLARE v_min DECIMAL(15,5);
    DECLARE v_max DECIMAL(15,5);
    DECLARE v_qtype VARCHAR(50);
    
    SELECT question_type INTO v_qtype 
    FROM survey_questions 
    WHERE question_id = p_question_id;
    
    -- Frequency distribution for all types
    INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
    SELECT p_question_id, 'frequency', 
           JSON_OBJECT('distribution', JSON_ARRAYAGG(
               JSON_OBJECT('value', COALESCE(response_numeric, response_value), 
                          'count', cnt)
           )),
           (SELECT COUNT(*) FROM question_responses WHERE question_id = p_question_id)
    FROM (
        SELECT COALESCE(response_numeric, response_value) as val, COUNT(*) as cnt
        FROM question_responses 
        WHERE question_id = p_question_id AND response_numeric IS NOT NULL
        GROUP BY COALESCE(response_numeric, response_value)
    ) t
    ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                            sample_size = VALUES(sample_size),
                            calculated_at = NOW();
    
    -- Numeric statistics for quantitative questions
    IF v_qtype IN ('rating_scale', 'likert_scale', 'numerical', 'yes_no') THEN
        SELECT COUNT(*), AVG(response_numeric), 
               STDDEV(response_numeric), MIN(response_numeric), MAX(response_numeric)
        INTO v_count, v_mean, v_std, v_min, v_max
        FROM question_responses 
        WHERE question_id = p_question_id AND response_numeric IS NOT NULL;
        
        -- Calculate median using variables
        SET @rownum := 0;
        SET @total := (SELECT COUNT(*) FROM question_responses 
                       WHERE question_id = p_question_id AND response_numeric IS NOT NULL);
        
        SELECT AVG(response_numeric) INTO v_median
        FROM (
            SELECT response_numeric, @rownum := @rownum + 1 AS rn
            FROM question_responses 
            WHERE question_id = p_question_id AND response_numeric IS NOT NULL
            ORDER BY response_numeric
        ) t
        WHERE rn IN (FLOOR((@total + 1) / 2), CEIL((@total + 1) / 2));
        
        -- Mean
        INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
        VALUES (p_question_id, 'mean', 
                JSON_OBJECT('mean', v_mean, 'sum', v_mean * v_count), v_count)
        ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                                sample_size = VALUES(sample_size),
                                calculated_at = NOW();
        
        -- Median
        INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
        VALUES (p_question_id, 'median', 
                JSON_OBJECT('median', v_median), v_count)
        ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                                sample_size = VALUES(sample_size),
                                calculated_at = NOW();
        
        -- Standard Deviation
        INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
        VALUES (p_question_id, 'std_deviation', 
                JSON_OBJECT('std_deviation', v_std, 'variance', v_std * v_std), v_count)
        ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                                sample_size = VALUES(sample_size),
                                calculated_at = NOW();
        
        -- Range
        INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
        VALUES (p_question_id, 'range', 
                JSON_OBJECT('min', v_min, 'max', v_max, 'range', v_max - v_min), v_count)
        ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                                sample_size = VALUES(sample_size),
                                calculated_at = NOW();
    END IF;
END//

-- ============================================================
-- NPS Calculation
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS CalculateNPS(IN p_survey_id INT, IN p_question_id INT)
BEGIN
    DECLARE v_promoters INT;
    DECLARE v_passives INT;
    DECLARE v_detractors INT;
    DECLARE v_total INT;
    DECLARE v_nps DECIMAL(5,2);
    
    SELECT 
        SUM(CASE WHEN response_numeric >= 9 THEN 1 ELSE 0 END),
        SUM(CASE WHEN response_numeric BETWEEN 7 AND 8 THEN 1 ELSE 0 END),
        SUM(CASE WHEN response_numeric <= 6 THEN 1 ELSE 0 END),
        COUNT(*)
    INTO v_promoters, v_passives, v_detractors, v_total
    FROM question_responses qr
    JOIN survey_responses sr ON qr.response_id = sr.response_id
    WHERE qr.question_id = p_question_id 
      AND sr.survey_id = p_survey_id
      AND sr.status = 'completed';
    
    SET v_nps = IF(v_total > 0, 
                   ((v_promoters - v_detractors) / v_total) * 100, 
                   0);
    
    INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
    VALUES (p_question_id, 'nps',
            JSON_OBJECT('nps_score', v_nps, 'promoters', v_promoters, 
                       'passives', v_passives, 'detractors', v_detractors,
                       'total', v_total),
            v_total)
    ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                            sample_size = VALUES(sample_size),
                            calculated_at = NOW();
END//

-- ============================================================
-- CSAT Calculation
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS CalculateCSAT(IN p_survey_id INT, IN p_question_id INT)
BEGIN
    DECLARE v_satisfied INT;
    DECLARE v_total INT;
    DECLARE v_csat DECIMAL(5,2);
    
    SELECT 
        SUM(CASE WHEN response_numeric >= 4 THEN 1 ELSE 0 END),
        COUNT(*)
    INTO v_satisfied, v_total
    FROM question_responses qr
    JOIN survey_responses sr ON qr.response_id = sr.response_id
    WHERE qr.question_id = p_question_id 
      AND sr.survey_id = p_survey_id
      AND sr.status = 'completed';
    
    SET v_csat = IF(v_total > 0, (v_satisfied / v_total) * 100, 0);
    
    INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
    VALUES (p_question_id, 'csat',
            JSON_OBJECT('csat_score', v_csat, 'satisfied', v_satisfied, 'total', v_total),
            v_total)
    ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                            sample_size = VALUES(sample_size),
                            calculated_at = NOW();
END//

-- ============================================================
-- Sentiment Analysis (Basic keyword-based)
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS CalculateSentiment(IN p_question_id INT)
BEGIN
    DECLARE v_positive INT;
    DECLARE v_neutral INT;
    DECLARE v_negative INT;
    DECLARE v_total INT;
    
    SELECT 
        SUM(CASE WHEN sentiment_score > 0.3 THEN 1 ELSE 0 END),
        SUM(CASE WHEN sentiment_score BETWEEN -0.3 AND 0.3 THEN 1 ELSE 0 END),
        SUM(CASE WHEN sentiment_score < -0.3 THEN 1 ELSE 0 END),
        COUNT(*)
    INTO v_positive, v_neutral, v_negative, v_total
    FROM question_responses 
    WHERE question_id = p_question_id AND sentiment_score IS NOT NULL;
    
    INSERT INTO response_analytics (question_id, analytics_type, analytics_data, sample_size)
    VALUES (p_question_id, 'sentiment',
            JSON_OBJECT('positive', v_positive, 'neutral', v_neutral, 
                       'negative', v_negative, 'total', v_total,
                       'avg_sentiment', (SELECT AVG(sentiment_score) 
                                        FROM question_responses 
                                        WHERE question_id = p_question_id 
                                        AND sentiment_score IS NOT NULL)),
            v_total)
    ON DUPLICATE KEY UPDATE analytics_data = VALUES(analytics_data), 
                            sample_size = VALUES(sample_size),
                            calculated_at = NOW();
END//

-- ============================================================
-- Archive Completed Surveys
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS ArchiveCompletedSurveys(IN p_days_threshold INT)
BEGIN
    UPDATE surveys 
    SET status = 'archived' 
    WHERE status = 'closed' 
      AND updated_at < DATE_SUB(NOW(), INTERVAL p_days_threshold DAY);
    
    INSERT INTO system_events (event_name, event_type, status, completed_at, result_data)
    VALUES ('ArchiveCompletedSurveys', 'scheduled', 'completed', NOW(),
            JSON_OBJECT('surveys_archived', ROW_COUNT(), 'threshold_days', p_days_threshold));
END//

-- ============================================================
-- Generate Survey Report
-- ============================================================

CREATE PROCEDURE IF NOT EXISTS GenerateSurveyReport(IN p_survey_id INT)
BEGIN
    DECLARE v_survey_title VARCHAR(255);
    DECLARE v_total_responses INT;
    DECLARE v_completion_rate DECIMAL(5,2);
    DECLARE v_avg_time INT;
    
    SELECT title INTO v_survey_title FROM surveys WHERE survey_id = p_survey_id;
    
    SELECT total_responses, completion_rate, average_time_spent 
    INTO v_total_responses, v_completion_rate, v_avg_time
    FROM survey_analytics WHERE survey_id = p_survey_id;
    
    -- Return comprehensive report data
    SELECT 
        p_survey_id AS survey_id,
        v_survey_title AS survey_title,
        v_total_responses AS total_responses,
        v_completion_rate AS completion_rate,
        v_avg_time AS avg_time_seconds,
        (SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'question_id', q.question_id,
                'question_text', q.question_text,
                'question_type', q.question_type,
                'analytics', (
                    SELECT JSON_ARRAYAGG(
                        JSON_OBJECT(
                            'type', ra.analytics_type,
                            'data', ra.analytics_data,
                            'sample_size', ra.sample_size
                        )
                    )
                    FROM response_analytics ra
                    WHERE ra.question_id = q.question_id
                )
            )
        )
        FROM survey_questions q
        WHERE q.survey_id = p_survey_id
        ORDER BY q.question_order) AS question_analytics;
END//

DELIMITER ;