-- Course Package Refactoring - Add new fields
-- Date: 2026-02-28

-- Add new fields to courses table for course package refactoring

-- Core content
ALTER TABLE courses ADD COLUMN IF NOT EXISTS core_content TEXT COMMENT 'Core content summary';

-- Course introduction fields (8 fields)
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_summary TEXT COMMENT 'Course summary';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_highlights TEXT COMMENT 'Course highlights';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_goals TEXT COMMENT 'Course goals';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_schedule TEXT COMMENT 'Content schedule';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_key_points TEXT COMMENT 'Key points and difficulties';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_methods TEXT COMMENT 'Teaching methods';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_evaluation TEXT COMMENT 'Evaluation methods';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS intro_notes TEXT COMMENT 'Notes and precautions';

-- Schedule reference data
ALTER TABLE courses ADD COLUMN IF NOT EXISTS schedule_ref_data TEXT COMMENT 'Schedule reference data (JSON)';

-- Environment construction (Step 7)
ALTER TABLE courses ADD COLUMN IF NOT EXISTS environment_construction TEXT COMMENT 'Environment construction content';

-- Theme and picture book relation
ALTER TABLE courses ADD COLUMN IF NOT EXISTS theme_id BIGINT COMMENT 'Theme ID';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS picture_book_name VARCHAR(200) COMMENT 'Picture book name';

-- Cover image path
ALTER TABLE courses ADD COLUMN IF NOT EXISTS cover_image_path VARCHAR(500) COMMENT 'Cover image file path';

-- Digital resources (JSON arrays)
ALTER TABLE courses ADD COLUMN IF NOT EXISTS ebook_paths TEXT COMMENT 'Ebook paths (JSON array)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS audio_paths TEXT COMMENT 'Audio paths (JSON array)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS video_paths TEXT COMMENT 'Video paths (JSON array)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS other_resources TEXT COMMENT 'Other resources (JSON array)';

-- Teaching materials
ALTER TABLE courses ADD COLUMN IF NOT EXISTS ppt_path VARCHAR(500) COMMENT 'PPT file path';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS ppt_name VARCHAR(200) COMMENT 'PPT file name';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS poster_paths TEXT COMMENT 'Poster paths (JSON array)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS tools TEXT COMMENT 'Teaching tools (JSON array)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS student_materials TEXT COMMENT 'Student materials';

-- Lesson plan and activities
ALTER TABLE courses ADD COLUMN IF NOT EXISTS lesson_plan_data TEXT COMMENT 'Lesson plan data (JSON)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS activities_data TEXT COMMENT 'Activities data (JSON)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS assessment_data TEXT COMMENT 'Assessment data (JSON)';

-- Grade and domain tags
ALTER TABLE courses ADD COLUMN IF NOT EXISTS grade_tags TEXT COMMENT 'Grade tags (JSON array)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS domain_tags TEXT COMMENT 'Domain tags (JSON array)';

-- Collective lesson flag
ALTER TABLE courses ADD COLUMN IF NOT EXISTS has_collective_lesson TINYINT DEFAULT 0 COMMENT 'Has collective lesson';

-- Version and review fields
ALTER TABLE courses ADD COLUMN IF NOT EXISTS version VARCHAR(20) DEFAULT '1.0' COMMENT 'Version number';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS parent_id BIGINT COMMENT 'Parent course ID for versions';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS is_latest TINYINT DEFAULT 1 COMMENT 'Is latest version';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS submitted_at DATETIME COMMENT 'Submitted for review at';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS submitted_by BIGINT COMMENT 'Submitted by user ID';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS reviewed_at DATETIME COMMENT 'Reviewed at';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS reviewed_by BIGINT COMMENT 'Reviewed by user ID';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS review_comment TEXT COMMENT 'Review comment';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS review_checklist TEXT COMMENT 'Review checklist (JSON)';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS published_at DATETIME COMMENT 'Published at';

-- Usage statistics
ALTER TABLE courses ADD COLUMN IF NOT EXISTS usage_count INT DEFAULT 0 COMMENT 'Usage count';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS teacher_count INT DEFAULT 0 COMMENT 'Teacher count';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS avg_rating DECIMAL(3,2) DEFAULT 0 COMMENT 'Average rating';
ALTER TABLE courses ADD COLUMN IF NOT EXISTS created_by BIGINT COMMENT 'Created by user ID';

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_courses_theme ON courses(theme_id);
CREATE INDEX IF NOT EXISTS idx_courses_status ON courses(status);
CREATE INDEX IF NOT EXISTS idx_courses_parent ON courses(parent_id);
