-- Migration: Add hierarchical structure to topics table
-- Date: 2024-01-15
-- Purpose: Allow topics to have parent-child relationships for nested topic structure

-- Add parent_topic_id column to allow hierarchical structure
ALTER TABLE topics 
ADD COLUMN parent_topic_id INT DEFAULT NULL,
ADD COLUMN level TINYINT DEFAULT 0 COMMENT 'Topic hierarchy level (0=main topic, 1=sub-topic, etc)',
ADD COLUMN full_path VARCHAR(500) DEFAULT NULL COMMENT 'Full hierarchical path for easy querying',
ADD CONSTRAINT fk_topics_parent 
    FOREIGN KEY (parent_topic_id) REFERENCES topics(id) 
    ON DELETE CASCADE ON UPDATE CASCADE;

-- Add index for better performance on parent_topic_id queries
CREATE INDEX idx_topics_parent_id ON topics(parent_topic_id);
CREATE INDEX idx_topics_level ON topics(level);
CREATE INDEX idx_topics_course_parent ON topics(course_id, parent_topic_id);

-- Update existing topics to have level 0 (main topics)
UPDATE topics SET level = 0 WHERE level IS NULL;

-- Add a check constraint to prevent circular references (max 5 levels deep)
ALTER TABLE topics ADD CONSTRAINT chk_topic_level CHECK (level >= 0 AND level <= 5);

-- Example data structure after migration:
-- Course: Web Development
--   Topic 1: HTML Basics (level=0, parent_topic_id=NULL)
--     Sub-topic 1.1: HTML Tags (level=1, parent_topic_id=1)
--     Sub-topic 1.2: HTML Attributes (level=1, parent_topic_id=1)
--       Sub-sub-topic 1.2.1: Class Attributes (level=2, parent_topic_id=1.2)
--   Topic 2: CSS Fundamentals (level=0, parent_topic_id=NULL)
--     Sub-topic 2.1: CSS Selectors (level=1, parent_topic_id=2)

-- Sample insert for testing the hierarchical structure
-- INSERT INTO topics (course_id, title, order_sequence, parent_topic_id, level, full_path) 
-- VALUES 
-- (1, 'HTML Basics', 1, NULL, 0, 'HTML Basics'),
-- (1, 'HTML Tags', 1, 1, 1, 'HTML Basics > HTML Tags'),
-- (1, 'HTML Attributes', 2, 1, 1, 'HTML Basics > HTML Attributes'),
-- (1, 'Class Attributes', 1, 3, 2, 'HTML Basics > HTML Attributes > Class Attributes');
