-- ============================================================================
-- GMIND INSTITUTE — Add student_phone column to practice_attempts
-- ============================================================================
-- Run this SQL in phpMyAdmin against the `advaysin_gmind` database.
-- Idempotent — safe to run multiple times.
--
-- Adds the `student_phone` column to the practice_attempts table so the
-- practice test start form can ask for the user's mobile number (instead of
-- or in addition to email).
-- ============================================================================

-- student_phone column
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'practice_attempts'
               AND COLUMN_NAME = 'student_phone');
SET @sql := IF(@col = 0,
    'ALTER TABLE `practice_attempts` ADD COLUMN `student_phone` VARCHAR(20) DEFAULT NULL AFTER `student_email`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Add an index on student_phone for faster attempt-count queries
SET @idx := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'practice_attempts'
               AND INDEX_NAME = 'idx_student_phone');
SET @sql := IF(@idx = 0,
    'ALTER TABLE `practice_attempts` ADD INDEX `idx_student_phone` (`student_phone`)',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Verify with:
-- DESCRIBE practice_attempts;
-- Should now show: student_phone VARCHAR(20) DEFAULT NULL
-- ══════════════════════════════════════════════════════════════════════════════
