-- Database schema updates for AI-generated quiz questions
-- Add these columns to the questions table if they don't exist

-- Check if columns exist and add them if missing
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "questions" AND column_name = "is_ai_generated"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE questions ADD COLUMN is_ai_generated BOOLEAN DEFAULT FALSE',
    'SELECT "Column is_ai_generated already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add AI metadata column
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "questions" AND column_name = "ai_metadata"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE questions ADD COLUMN ai_metadata JSON NULL',
    'SELECT "Column ai_metadata already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add created_at column if it doesn't exist
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "questions" AND column_name = "created_at"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE questions ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP',
    'SELECT "Column created_at already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add updated_at column if it doesn't exist
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "questions" AND column_name = "updated_at"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE questions ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
    'SELECT "Column updated_at already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Create index for AI-generated questions
CREATE INDEX IF NOT EXISTS idx_questions_ai_generated ON questions(is_ai_generated);
CREATE INDEX IF NOT EXISTS idx_questions_created_at ON questions(created_at);

-- Update quiz_sessions table to track AI-generated quizzes
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "quiz_sessions" AND column_name = "is_ai_generated"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE quiz_sessions ADD COLUMN is_ai_generated BOOLEAN DEFAULT FALSE',
    'SELECT "Column is_ai_generated already exists in quiz_sessions" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add AI quiz metadata to quiz_sessions
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "quiz_sessions" AND column_name = "ai_quiz_metadata"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE quiz_sessions ADD COLUMN ai_quiz_metadata JSON NULL',
    'SELECT "Column ai_quiz_metadata already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add marks column to questions table if it doesn't exist
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "questions" AND column_name = "marks"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE questions ADD COLUMN marks DECIMAL(5,2) DEFAULT 1.00',
    'SELECT "Column marks already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add negative marking fields to quiz_sessions
SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "quiz_sessions" AND column_name = "negative_marking"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE quiz_sessions ADD COLUMN negative_marking BOOLEAN DEFAULT FALSE',
    'SELECT "Column negative_marking already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = 'SELECT COUNT(*) INTO @col_exists FROM information_schema.columns 
           WHERE table_schema = DATABASE() AND table_name = "quiz_sessions" AND column_name = "negative_marking_percentage"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE quiz_sessions ADD COLUMN negative_marking_percentage DECIMAL(5,2) DEFAULT 0.00',
    'SELECT "Column negative_marking_percentage already exists" as message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Create index for marks-based queries
CREATE INDEX IF NOT EXISTS idx_questions_marks ON questions(marks);
CREATE INDEX IF NOT EXISTS idx_quiz_sessions_negative_marking ON quiz_sessions(negative_marking);
