-- SQL script to add time limit functionality to activities
-- This script is idempotent (safe to run multiple times)

-- Add time_limit column to activities table if it doesn't exist
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = 'activities'
AND column_name = 'time_limit';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE activities ADD COLUMN time_limit INT NULL COMMENT "Time limit in minutes, NULL if no limit"',
    'SELECT "Column time_limit already exists" AS message');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Create activity_attempts table for tracking time-limited activities
CREATE TABLE IF NOT EXISTS activity_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    activity_id INT NOT NULL,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
    UNIQUE KEY unique_attempt (student_id, activity_id)
) COMMENT='Tracks when students start time-limited activities';

-- Display success message
SELECT 'Time limit feature installed successfully!' AS message;
SELECT 'You can now set time_limit (in minutes) for activities in the activities table.' AS info;
SELECT 'Example: UPDATE activities SET time_limit = 30 WHERE id = 1; -- Sets 30 minute limit' AS example;
