-- ============================================================
-- Gmind Institute - Practice Exam Fix Migration
-- ============================================================
-- Run this SQL on your database ONCE to fix the attempt tracking.
--
-- This script:
-- 1. Adds user_id column to practice_attempts (if missing)
-- 2. Backfills user_id from users table by matching email
-- 3. Backfills student_id from students table by matching user_id
-- 4. Backfills student_name from users table where empty
-- 5. Adds index for performance
--
-- Run via phpMyAdmin or MySQL CLI:
--   mysql -u username -p database_name < fix_attempt_tracking.sql
-- ============================================================

-- Step 1: Add user_id column if it doesn't exist
-- (Safe to run multiple times)
SET @dbname = DATABASE();
SET @tablename = 'practice_attempts';
SET @columnname = 'user_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      TABLE_SCHEMA = @dbname
      AND TABLE_NAME = @tablename
      AND COLUMN_NAME = @columnname
  ) > 0,
  'SELECT 1',
  'ALTER TABLE `practice_attempts` ADD COLUMN `user_id` INT UNSIGNED DEFAULT NULL AFTER `student_id`'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add index on user_id if not exists
ALTER TABLE `practice_attempts` ADD KEY `idx_user_id` (`user_id`);

-- Step 2: Backfill user_id from users table by matching email
-- This links old attempts (where user_id was NULL) to their actual user accounts
UPDATE `practice_attempts` pa
INNER JOIN `users` u ON pa.student_email = u.email
SET pa.user_id = u.id
WHERE pa.user_id IS NULL
AND pa.student_email IS NOT NULL
AND pa.student_email != '';

-- Step 3: Backfill student_id from students table by matching user_id
-- This ensures attempts have both user_id and student_id set
UPDATE `practice_attempts` pa
INNER JOIN `students` s ON pa.user_id = s.user_id
SET pa.student_id = s.id
WHERE pa.student_id IS NULL
AND pa.user_id IS NOT NULL;

-- Step 4: Backfill student_name from users table where empty
-- This ensures the student name is always available in the attempt record
UPDATE `practice_attempts` pa
INNER JOIN `users` u ON pa.user_id = u.id
SET pa.student_name = u.name
WHERE (pa.student_name IS NULL OR pa.student_name = '')
AND pa.user_id IS NOT NULL
AND u.name IS NOT NULL
AND u.name != '';

-- Done! Verify with:
-- SELECT id, student_name, student_email, student_id, user_id FROM practice_attempts ORDER BY id DESC LIMIT 20;
