Guest/User Browses Stories by Genre

Actor: Regular User / Guest User

A user browses the platform filtering published stories by a specific genre to discover new content.

1. The user navigates to the "Browse by Genre" section of the platform.

2. The system retrieves and displays all available genres.

SELECT g.genre_id, g.name, COUNT(hg.story_id) AS total_stories
FROM GENRE g
LEFT JOIN HAS_GENRE hg ON g.genre_id = hg.genre_id
GROUP BY g.genre_id
ORDER BY total_stories DESC;

3. The user selects a genre, for example "Fantasy".

4. The system retrieves all published stories that belong to that genre.

SELECT s.story_id, s.short_description, s.mature_content, s.image,
       u.username AS author,
       COUNT(DISTINCT l.user_id) AS total_likes,
       COUNT(DISTINCT c.comment_id) AS total_comments
FROM STORY s
JOIN HAS_GENRE hg ON s.story_id = hg.story_id
JOIN GENRE g ON hg.genre_id = g.genre_id
JOIN USERS u ON s.user_id = u.user_id
JOIN STATUS st ON s.story_id = st.story_id
LEFT JOIN LIKES l ON s.story_id = l.story_id
LEFT JOIN COMMENT c ON s.story_id = c.story_id
WHERE g.name = 'Fantasy' AND st.status = 'published'
GROUP BY s.story_id, u.username
ORDER BY total_likes DESC;

5. The system displays the filtered list of stories with their cover image, author, likes, and comment count.

6. The user clicks on a story to view its details.