-- Migration: Add multiple language support for audio content
-- Description: Adds language field to course_audio_content table and creates indexes for efficient querying

-- Add language field to course_audio_content table
ALTER TABLE course_audio_content 
ADD COLUMN language VARCHAR(10) NOT NULL DEFAULT 'English' COMMENT 'Language of the audio content (English, Hindi, Nepali, etc.)';

-- Add index for efficient language-based queries
CREATE INDEX idx_course_audio_content_topic_language ON course_audio_content(topic_id, language);

-- Add index for position and language ordering
CREATE INDEX idx_course_audio_content_topic_lang_pos ON course_audio_content(topic_id, language, position);

-- Update existing records to have default language
UPDATE course_audio_content SET language = 'English' WHERE language IS NULL OR language = '';

-- Add constraint to ensure language is one of supported languages
ALTER TABLE course_audio_content 
ADD CONSTRAINT chk_audio_language 
CHECK (language IN ('English', 'Hindi', 'Nepali', 'Spanish', 'French', 'German', 'Arabic', 'Chinese', 'Japanese', 'Korean'));

-- Optional: Create a separate table for supported languages (for future extensibility)
CREATE TABLE IF NOT EXISTS supported_audio_languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    language_code VARCHAR(10) NOT NULL UNIQUE,
    language_name VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default supported languages
INSERT INTO supported_audio_languages (language_code, language_name) VALUES
('en', 'English'),
('hi', 'Hindi'), 
('ne', 'Nepali'),
('es', 'Spanish'),
('fr', 'French'),
('de', 'German'),
('ar', 'Arabic'),
('zh', 'Chinese'),
('ja', 'Japanese'),
('ko', 'Korean')
ON DUPLICATE KEY UPDATE language_name = VALUES(language_name);

-- Add user preference table for default audio language
CREATE TABLE IF NOT EXISTS user_audio_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    preferred_language VARCHAR(10) DEFAULT 'English',
    auto_switch_language BOOLEAN DEFAULT FALSE COMMENT 'Auto switch to available language if preferred not available',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_preference (user_id)
);
