= Complex DB Reports (SQL, Stored Procedures, Relational Algebra)


=== 1. Story Statistics 

==== SQL
{{{

WITH likeCount AS (
    SELECT
        story_id,
        COUNT(user_id) AS total_likes
    FROM likes
    GROUP BY story_id
),
commentCount AS (
    SELECT
        story_id,
        COUNT(comment_id) AS total_comments
    FROM comment
    GROUP BY story_id
),
averageRating AS (
    SELECT
        story_id,
        ROUND(AVG(rating), 2) AS avg_rating
    FROM chapter
    WHERE rating IS NOT NULL
    GROUP BY story_id
)
SELECT
    u.username AS writer,
    s.story_id,
    s.short_description,
    st.status,
    COALESCE(lk.total_likes, 0) AS total_likes,
    COALESCE(cm.total_comments, 0)         AS total_comments,
    COALESCE(CAST(ar.avg_rating AS VARCHAR), 'no ratings')    AS avg_rating
FROM story              s
JOIN status             st ON s.story_id = st.story_id
JOIN writer             w  ON s.user_id  = w.user_id
JOIN users              u  ON w.user_id  = u.user_id
LEFT JOIN likeCount     lk ON s.story_id = lk.story_id
LEFT JOIN commentCount  cm ON s.story_id = cm.story_id
LEFT JOIN averageRating ar ON s.story_id = ar.story_id
ORDER BY total_likes DESC, total_comments DESC;
}}}

==== Relational Algebra
{{{
likeCount <-
γ story_id;
  total_likes := COUNT(user_id)
(
  likes
)

commentCount <-
γ story_id;
  total_comments := COUNT(comment_id)
(
  comment
)

averageRating <-
γ story_id;
  avg_rating := ROUND(AVG(rating), 2)
(
  σ rating ≠ NULL (chapter)
)

Base <-
story s
⨝ (s.story_id = st.story_id) status st
⨝ (s.user_id = w.user_id)   writer w
⨝ (w.user_id = u.user_id)   users u

WithLikes <-
Base
⟕ (s.story_id = lk.story_id) likeCount lk

WithComments <-
WithLikes
⟕ (s.story_id = cm.story_id) commentCount cm

WithRatings <-
WithComments
⟕ (s.story_id = ar.story_id) averageRating ar

Result <-
π
  u.username → writer,
  s.story_id,
  s.short_description,
  st.status,
  COALESCE(lk.total_likes, 0)             → total_likes,
  COALESCE(cm.total_comments, 0)          → total_comments,
  COALESCE(ar.avg_rating, 'no ratings')   → avg_rating
(
  WithRatings
)
}}}

=== 2. Validate and insert story into user reading list with confirmation
==== SQL
{{{ 
WITH
published_story AS (
    SELECT s.story_id, s.short_description
    FROM story s
    JOIN status st ON s.story_id = st.story_id
    WHERE s.story_id = 1
      AND st.status = 'published'
),
user_list AS (
    SELECT list_id, list_name, is_public
    FROM reading_list
    WHERE list_id = 1
      AND user_id = 4
),
already_added AS (
    SELECT 1
    FROM reading_list_items
    WHERE list_id = 1
      AND story_id = 1
),
list_items AS (
    SELECT
        rli.list_id,
        COUNT(rli.story_id) AS total_items,
        MAX(rli.added_at)   AS last_added_at
    FROM reading_list_items rli
    WHERE rli.list_id = 1
    GROUP BY rli.list_id
)
SELECT
    rl.list_id,
    rl.list_name,
    rl.is_public,
    u.username                              AS owner,
    ps.story_id                             AS story_to_add,
    ps.short_description                    AS story_description,
    COALESCE(li.total_items, 0)             AS total_items,
    li.last_added_at,
    CASE
        WHEN NOT EXISTS (SELECT 1 FROM published_story) THEN 'Failed: story not published or not found'
        WHEN NOT EXISTS (SELECT 1 FROM user_list)       THEN 'Failed: reading list not found or not owned by you'
        WHEN EXISTS     (SELECT 1 FROM already_added)   THEN 'Story already in list'
        ELSE                                                 'Ready to add story'
    END                                     AS status
FROM reading_list               rl
JOIN users                      u   ON rl.user_id  = u.user_id
LEFT JOIN published_story       ps  ON ps.story_id = 1
LEFT JOIN list_items            li  ON li.list_id  = rl.list_id
WHERE rl.list_id = 1;
}}}

=== Relational Algebra
{{{
ValidateAndInsertStoryIntoUserReadingListWithConfirmation


PublishedStory ←
π story_id, short_description
(
  σ s.story_id = 1 ∧ st.status = 'published'
  (
    story s
    ⨝ (s.story_id = st.story_id) status st
  )
)

UserList ←
π list_id, list_name, is_public
(
  σ list_id = 1 ∧ user_id = 4
  (
    reading_list
  )
)

AlreadyAdded ←
σ list_id = 1 ∧ story_id = 1
(
  reading_list_items
)

ListItems ←
γ list_id;
  total_items  := COUNT(story_id),
  last_added_at := MAX(added_at)
(
  σ list_id = 1
  (
    reading_list_items
  )
)

Result ←
π
  rl.list_id,
  rl.list_name,
  rl.is_public,
  u.username                                        → owner,
  ps.story_id                                       → story_to_add,
  ps.short_description                              → story_description,
  COALESCE(li.total_items, 0)                       → total_items,
  li.last_added_at,
  CASE
    WHEN PublishedStory = ∅   → 'Failed: story not published or not found'
    WHEN UserList = ∅         → 'Failed: reading list not found or not owned by you'
    WHEN AlreadyAdded ≠ ∅     → 'Story already in list'
    ELSE                      → 'Ready to add story'
  END                                               → status
(
  (
    (
      σ rl.list_id = 1 (reading_list rl)
      ⨝ (rl.user_id = u.user_id) users u
    )
    ⟕ (ps.story_id = 1) PublishedStory ps
  )
  ⟕ (li.list_id = rl.list_id) ListItems li
)
}}}