-- Database schema updates for user-specific AI questions
-- Add user_id column to questions table for user-specific AI generated questions

-- Add user_id column to questions table
ALTER TABLE questions 
ADD COLUMN user_id INT DEFAULT NULL COMMENT 'User who generated this AI question (NULL for system/topic questions)';

-- Add foreign key constraint for user_id
ALTER TABLE questions 
ADD CONSTRAINT fk_questions_user_id 
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

-- Add index for faster queries on user-specific questions
CREATE INDEX idx_questions_user_id ON questions(user_id);

-- Add composite index for user + topic queries
CREATE INDEX idx_questions_user_topic ON questions(user_id, topic_id);

-- Add index for AI generated questions by user
CREATE INDEX idx_questions_ai_user ON questions(is_ai_generated, user_id);

-- Add AI model information column
ALTER TABLE questions 
ADD COLUMN ai_model VARCHAR(50) DEFAULT NULL COMMENT 'AI model used to generate this question';

-- Update existing AI metadata structure (optional)
-- This adds model info to existing ai_metadata JSON
UPDATE questions 
SET ai_metadata = JSON_SET(
    COALESCE(ai_metadata, '{}'), 
    '$.model', 'gpt_4o'
) 
WHERE is_ai_generated = 1 AND ai_metadata IS NOT NULL;

-- Add index for AI model queries
CREATE INDEX idx_questions_ai_model ON questions(ai_model);
