User Views Story Details and Chapters

Actor: Guest User / Regular User / Writer

A user opens a published story to view its full details, genres, and list of available chapters before starting to read.

1. The user clicks on a story from the browse page or reading list.

2. The system retrieves the full story details.

SELECT s.story_id, s.short_description, s.content, s.mature_content, 
       s.image, s.created_at, u.username AS author,
       st.status
FROM STORY s
JOIN USERS u ON s.user_id = u.user_id
JOIN STATUS st ON s.story_id = st.story_id
WHERE s.story_id = 1 AND st.status = 'published';

3. The system retrieves all genres assigned to the story.

SELECT g.name
FROM GENRE g
JOIN HAS_GENRE hg ON g.genre_id = hg.genre_id
WHERE hg.story_id = 1;

4. The system retrieves all chapters of the story ordered by chapter number.

SELECT chapter_id, chapter_number, chapter_name, title, 
       word_count, rating, published_at, view_count
FROM CHAPTER
WHERE story_id = 1
ORDER BY chapter_number ASC;

5. The system displays the story page with all details, genres, like and comment counts, and the chapter list.

SELECT COUNT(DISTINCT l.user_id) AS total_likes,
       COUNT(DISTINCT c.comment_id) AS total_comments
FROM STORY s
LEFT JOIN LIKES l ON s.story_id = l.story_id
LEFT JOIN COMMENT c ON s.story_id = c.story_id
WHERE s.story_id = 1
GROUP BY s.story_id;

6. The user selects a chapter to start reading.