Changes between Initial Version and Version 1 of UserBrowsesStoriesByGenre

Timestamp:

03/05/26 15:28:38 (2 weeks ago)

Author:

211099

Comment:

--

UserBrowsesStoriesByGenre

@@ +1 @@
+==  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.
+{{{#!sql
+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.
+{{{#!sql
+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.