-- Topic Hierarchy Examples
-- This file shows how to use the new hierarchical topic structure

-- Example 1: Web Development Course Structure
-- Course ID: 1 (Web Development)

-- Main Topics (Level 0)
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(1, 'HTML Fundamentals', 'Learn the basics of HTML markup language', NULL, 0, 'HTML Fundamentals', 1),
(1, 'CSS Styling', 'Master CSS for styling web pages', NULL, 0, 'CSS Styling', 2),
(1, 'JavaScript Programming', 'Learn JavaScript for interactive web development', NULL, 0, 'JavaScript Programming', 3),
(1, 'Responsive Design', 'Create mobile-friendly responsive websites', NULL, 0, 'Responsive Design', 4);

-- Sub-topics for HTML Fundamentals (Level 1)
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(1, 'Basic HTML Structure', 'DOCTYPE, html, head, body tags', 1, 1, 'HTML Fundamentals > Basic HTML Structure', 1),
(1, 'HTML Elements', 'Learn about different HTML elements', 1, 1, 'HTML Fundamentals > HTML Elements', 2),
(1, 'HTML Attributes', 'Understanding HTML attributes', 1, 1, 'HTML Fundamentals > HTML Attributes', 3),
(1, 'HTML Forms', 'Creating interactive forms', 1, 1, 'HTML Fundamentals > HTML Forms', 4);

-- Sub-sub-topics for HTML Elements (Level 2)
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(1, 'Text Elements', 'Paragraphs, headings, emphasis', 6, 2, 'HTML Fundamentals > HTML Elements > Text Elements', 1),
(1, 'List Elements', 'Ordered and unordered lists', 6, 2, 'HTML Fundamentals > HTML Elements > List Elements', 2),
(1, 'Link Elements', 'Anchor tags and navigation', 6, 2, 'HTML Fundamentals > HTML Elements > Link Elements', 3),
(1, 'Media Elements', 'Images, videos, audio', 6, 2, 'HTML Fundamentals > HTML Elements > Media Elements', 4);

-- Sub-topics for CSS Styling (Level 1)
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(1, 'CSS Selectors', 'Learn different CSS selector types', 2, 1, 'CSS Styling > CSS Selectors', 1),
(1, 'CSS Properties', 'Understanding CSS properties and values', 2, 1, 'CSS Styling > CSS Properties', 2),
(1, 'CSS Layout', 'Layout techniques and positioning', 2, 1, 'CSS Styling > CSS Layout', 3),
(1, 'CSS Animations', 'Creating smooth animations and transitions', 2, 1, 'CSS Styling > CSS Animations', 4);

-- Sub-sub-topics for CSS Layout (Level 2)
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(1, 'Flexbox', 'Modern flexible box layout', 15, 2, 'CSS Styling > CSS Layout > Flexbox', 1),
(1, 'CSS Grid', 'Two-dimensional grid layout system', 15, 2, 'CSS Styling > CSS Layout > CSS Grid', 2),
(1, 'Positioning', 'Static, relative, absolute, fixed positioning', 15, 2, 'CSS Styling > CSS Layout > Positioning', 3);

-- Example 2: Data Science Course Structure
-- Course ID: 2 (Data Science)

INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(2, 'Python Basics', 'Foundation of Python programming', NULL, 0, 'Python Basics', 1),
(2, 'Data Analysis', 'Working with data using Python libraries', NULL, 0, 'Data Analysis', 2),
(2, 'Machine Learning', 'Introduction to ML algorithms', NULL, 0, 'Machine Learning', 3);

-- Sub-topics for Python Basics
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(2, 'Variables and Data Types', 'Understanding Python data types', 18, 1, 'Python Basics > Variables and Data Types', 1),
(2, 'Control Structures', 'Loops and conditional statements', 18, 1, 'Python Basics > Control Structures', 2),
(2, 'Functions', 'Creating and using functions', 18, 1, 'Python Basics > Functions', 3);

-- Sub-topics for Data Analysis
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(2, 'Pandas Library', 'Data manipulation with Pandas', 19, 1, 'Data Analysis > Pandas Library', 1),
(2, 'NumPy Arrays', 'Numerical computing with NumPy', 19, 1, 'Data Analysis > NumPy Arrays', 2),
(2, 'Data Visualization', 'Creating charts and graphs', 19, 1, 'Data Analysis > Data Visualization', 3);

-- Sub-sub-topics for Data Visualization
INSERT INTO topics (course_id, title, description, parent_topic_id, level, full_path, order_sequence) VALUES
(2, 'Matplotlib', 'Basic plotting with Matplotlib', 25, 2, 'Data Analysis > Data Visualization > Matplotlib', 1),
(2, 'Seaborn', 'Statistical visualization with Seaborn', 25, 2, 'Data Analysis > Data Visualization > Seaborn', 2),
(2, 'Plotly', 'Interactive visualizations', 25, 2, 'Data Analysis > Data Visualization > Plotly', 3);

-- Query Examples to demonstrate the hierarchical structure

-- 1. Get all main topics (level 0) for a course
SELECT * FROM topics 
WHERE course_id = 1 AND level = 0 
ORDER BY order_sequence;

-- 2. Get all topics and their hierarchy for a course
SELECT 
    t.id,
    t.title,
    t.level,
    t.full_path,
    t.parent_topic_id,
    COUNT(q.id) as question_count
FROM topics t
LEFT JOIN questions q ON t.id = q.topic_id
WHERE t.course_id = 1
GROUP BY t.id
ORDER BY t.level, t.parent_topic_id, t.order_sequence;

-- 3. Get all children of a specific topic
SELECT * FROM topics 
WHERE parent_topic_id = 1 
ORDER BY order_sequence;

-- 4. Get the complete path from root to a specific topic
WITH RECURSIVE topic_path AS (
    -- Base case: start with the target topic
    SELECT id, title, parent_topic_id, level, full_path, 0 as depth
    FROM topics 
    WHERE id = 10  -- Replace with target topic ID
    
    UNION ALL
    
    -- Recursive case: get parent topics
    SELECT t.id, t.title, t.parent_topic_id, t.level, t.full_path, tp.depth + 1
    FROM topics t
    JOIN topic_path tp ON t.id = tp.parent_topic_id
)
SELECT * FROM topic_path ORDER BY depth DESC;

-- 5. Get all descendants of a topic
WITH RECURSIVE topic_descendants AS (
    -- Base case: start with the parent topic
    SELECT id, title, parent_topic_id, level, full_path, 0 as depth
    FROM topics 
    WHERE id = 1  -- Replace with parent topic ID
    
    UNION ALL
    
    -- Recursive case: get child topics
    SELECT t.id, t.title, t.parent_topic_id, t.level, t.full_path, td.depth + 1
    FROM topics t
    JOIN topic_descendants td ON t.parent_topic_id = td.id
)
SELECT * FROM topic_descendants WHERE depth > 0 ORDER BY depth, id;

-- 6. Get topic statistics by level for a course
SELECT 
    level,
    COUNT(*) as topic_count,
    AVG(question_count) as avg_questions_per_topic
FROM (
    SELECT 
        t.level,
        COUNT(q.id) as question_count
    FROM topics t
    LEFT JOIN questions q ON t.id = q.topic_id
    WHERE t.course_id = 1
    GROUP BY t.id, t.level
) topic_stats
GROUP BY level
ORDER BY level;
