= 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
)
}}}

=== 3.Detailed report on percentage change in collaborator count and invitation validation for a story
=== SQL
{{{
WITH
owned_story AS (
    SELECT s.story_id, s.short_description
    FROM story s
    JOIN writer w ON s.user_id = w.user_id
    WHERE s.story_id = 5
      AND s.user_id = :owner_user_id
),
invited_writer AS (
    SELECT u.user_id, u.username, u.user_name, u.surname
    FROM users u
    JOIN writer w ON u.user_id = w.user_id
    WHERE u.username = :invited_username
),
already_collaborating AS (
    SELECT 1
    FROM collaboration
    WHERE user_id  = (SELECT user_id  FROM invited_writer)
      AND story_id = (SELECT story_id FROM owned_story)
),
current_collaborators AS (
    SELECT
        u.username,
        u.user_name,
        u.surname,
        r.roles,
        p.permission_level,
        c.collab_created_at
    FROM collaboration    c
    JOIN users            u ON c.user_id  = u.user_id
    JOIN roles            r ON c.user_id  = r.user_id AND c.story_id = r.story_id
    JOIN permission_level p ON c.user_id  = p.user_id AND c.story_id = p.story_id
    WHERE c.story_id = (SELECT story_id FROM owned_story)
),
notification_preview AS (
    SELECT
        iw.user_id AS recipient_user_id,
        iw.username AS recipient_username,
        'You have been invited to collaborate on "'
            || os.short_description || '"' AS notification_content,
        'collaboration' AS type,
        '/story/' || os.story_id::VARCHAR   AS link
    FROM invited_writer iw
    CROSS JOIN owned_story os
)
SELECT
    cc.username,
    cc.user_name,
    cc.surname,
    cc.roles,
    cc.permission_level,
    cc.collab_created_at,
    COUNT(*) OVER () AS total_collaborators,
    np.notification_content AS pending_notification,
    CASE
        WHEN NOT EXISTS (SELECT 1 FROM owned_story)          THEN 'Failed: story not found or not owned by you'
        WHEN NOT EXISTS (SELECT 1 FROM invited_writer)       THEN 'Failed: invited user is not a writer'
        WHEN EXISTS     (SELECT 1 FROM already_collaborating) THEN 'User is already a collaborator'
        ELSE                                                       'Ready to add collaborator'
    END   AS status
FROM current_collaborators          cc
LEFT JOIN notification_preview      np ON np.recipient_username = :invited_username
ORDER BY cc.collab_created_at;
}}}
=== Relational Algebra
{{{
OwnedStory ←
π story_id, short_description
(
  σ s.story_id = :story_id ∧ s.user_id = :owner_user_id
  (
    story s
    ⨝ (s.user_id = w.user_id) writer w
  )
)

InvitedWriter ←
π user_id, username, user_name, surname
(
  σ u.username = :invited_username
  (
    users u
    ⨝ (u.user_id = w.user_id) writer w
  )
)

AlreadyCollaborating ←
σ user_id = (π user_id (InvitedWriter))
  ∧ story_id = (π story_id (OwnedStory))
(
  collaboration
)

CurrentCollaborators ←
π
  u.username, u.user_name, u.surname,
  r.roles, p.permission_level,
  c.collab_created_at
(
  (
    (
      σ story_id = (π story_id (OwnedStory)) (collaboration c)
      ⨝ (c.user_id = u.user_id) users u
    )
    ⨝ (c.user_id = r.user_id ∧ c.story_id = r.story_id) roles r
  )
  ⨝ (c.user_id = p.user_id ∧ c.story_id = p.story_id) permission_level p
)

NotificationPreview ←
π
  iw.user_id        → recipient_user_id,
  iw.username       → recipient_username,
  'You have been invited to collaborate on "' || os.short_description || '"'
                    → notification_content,
  'collaboration'   → type,
  '/story/' || os.story_id → link
(
  InvitedWriter iw
  ⨯ OwnedStory os
)

Result ←
π
  cc.username,
  cc.user_name,
  cc.surname,
  cc.roles,
  cc.permission_level,
  cc.collab_created_at,
  COUNT(*) OVER ()  → total_collaborators,
  np.notification_content → pending_notification,
  CASE
    WHEN OwnedStory = ∅           → 'Failed: story not found or not owned by you'
    WHEN InvitedWriter = ∅        → 'Failed: invited user is not a writer'
    WHEN AlreadyCollaborating ≠ ∅ → 'User is already a collaborator'
    ELSE                          → 'Ready to add collaborator'
  END               → status
(
  CurrentCollaborators cc
  ⟕ (cc.username = np.recipient_username) NotificationPreview np
)
}}}