Advanced Database Development (SQL DDL)

Custom Domains

-- Check email format
CREATE DOMAIN valid_email AS VARCHAR(255)
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Check rating range from 0 to 5
CREATE DOMAIN story_rating AS DECIMAL(3,2)
CHECK (VALUE >= 0.00 AND VALUE <= 5.00);

-- Status of accepted values
CREATE DOMAIN story_status AS VARCHAR(50)
CHECK (VALUE IN ('draft', 'published', 'archived'));

-- Permission level from 1 to 5
CREATE DOMAIN permission_lvl AS INTEGER
CHECK (VALUE >= 1 AND VALUE <= 5);

-- URL / image path
CREATE DOMAIN valid_url AS VARCHAR(2048)
CHECK (VALUE ~* '^https?://.+');

Views

-- Detailed view for each story with statistic
CREATE OR REPLACE VIEW story_details_view AS
SELECT
    s.story_id,
    s.short_description,
    s.mature_content,
    s.image,
    s.story_created_at,
    u.username AS writer,
    u.user_name,
    u.surname,
    st.status,
    COUNT(DISTINCT ch.chapter_id) AS total_chapters,
    COALESCE(SUM(ch.view_count), 0) AS total_views,
    ROUND(AVG(ch.rating), 2) AS avg_rating,
    COUNT(DISTINCT l.user_id) AS total_likes,
    COUNT(DISTINCT c.comment_id) AS total_comments,
    COUNT(DISTINCT hg.genre_id) AS total_genres,
    COUNT(DISTINCT col.user_id) AS total_collaborators
FROM story s
JOIN writer w   ON s.user_id    = w.user_id
JOIN users u   ON w.user_id    = u.user_id
JOIN "status" st  ON s.story_id   = st.story_id
LEFT JOIN chapter ch  ON s.story_id   = ch.story_id
LEFT JOIN likes l   ON s.story_id   = l.story_id
LEFT JOIN "comment" c   ON s.story_id   = c.story_id
LEFT JOIN has_genre hg  ON s.story_id   = hg.story_id
LEFT JOIN collaboration col ON s.story_id   = col.story_id
GROUP BY
    s.story_id, s.short_description, s.mature_content,
    s.image, s.story_created_at,
    u.username, u.user_name, u.surname, st.status;
-- Top 10 most popular stories by likes count =
CREATE MATERIALIZED VIEW top_stories_by_likes AS
SELECT
    s.story_id,
    s.short_description,
    u.username AS writer,
    st.status,
    COUNT(DISTINCT l.user_id)  AS total_likes,
    COUNT(DISTINCT c.comment_id) AS total_comments,
    ROUND(AVG(ch.rating), 2) AS avg_rating
FROM story s
JOIN writer w   ON s.user_id    = w.user_id
JOIN users u   ON w.user_id    = u.user_id
JOIN "status" st  ON s.story_id   = st.story_id
AND st.status   = 'published'
LEFT JOIN likes l   ON s.story_id   = l.story_id
LEFT JOIN "comment" c   ON s.story_id   = c.story_id
LEFT JOIN chapter  ch  ON s.story_id   = ch.story_id
GROUP BY
    s.story_id, s.short_description,
    u.username, st.status
ORDER BY total_likes DESC
LIMIT 10;

-- View for user profile with all his stories 
CREATE OR REPLACE VIEW writer_profile_view AS
SELECT
    u.user_id,
    u.username,
    u.user_name,
    u.surname,
    u.user_created_at,
    COUNT(DISTINCT s.story_id)AS total_stories,
    COUNT(DISTINCT ch.chapter_id) AS total_chapters,
    COALESCE(SUM(ch.view_count), 0) AS total_views,
    COUNT(DISTINCT l.user_id) AS total_likes,
    COUNT(DISTINCT c.comment_id) AS total_comments,
    ROUND(AVG(ch.rating), 2) AS avg_rating
FROM users u
JOIN writer w   ON u.user_id    = w.user_id
LEFT JOIN story s   ON w.user_id    = s.user_id
LEFT JOIN chapter ch  ON s.story_id   = ch.story_id
LEFT JOIN likes l   ON s.story_id   = l.story_id
LEFT JOIN comment  c   ON s.story_id   = c.story_id
GROUP BY
    u.user_id, u.username, u.user_name,
    u.surname, u.user_created_at;

Stored Procedures / Functions

-- Table for daily top story CREATE TABLE daily_top_stories (

day DATE PRIMARY KEY, story_id INTEGER NULL REFERENCES story(story_id), total_likes BIGINT

);

-- Procedure: saves the most liked story of the previous day CREATE OR REPLACE PROCEDURE get_daily_top_story() LANGUAGE plpgsql AS $$ DECLARE

yesterday TIMESTAMP;

BEGIN

yesterday := CURRENT_DATE - 1;

IF EXISTS (

SELECT 1 FROM likes WHERE like_created_at BETWEEN yesterday AND CURRENT_DATE

) THEN

INSERT INTO daily_top_stories (day, story_id, total_likes) SELECT

CURRENT_DATE - 1, s.story_id, COUNT(l.user_id) AS total_likes

FROM story s JOIN likes l ON s.story_id = l.story_id WHERE l.like_created_at BETWEEN yesterday AND CURRENT_DATE GROUP BY s.story_id ORDER BY total_likes DESC LIMIT 1;

ELSE

INSERT INTO daily_top_stories (day, story_id, total_likes) VALUES (CURRENT_DATE - 1, NULL, 0);

END IF;

END; $$;

-- Procedure: sends a notification to all collaborators of a story CREATE OR REPLACE PROCEDURE notify_collaborators(

p_story_id INTEGER, p_message TEXT, p_type VARCHAR(50), p_link VARCHAR(500)

) LANGUAGE plpgsql AS $$ DECLARE

collab RECORD;

BEGIN

FOR collab IN

SELECT c.user_id FROM collaboration c WHERE c.story_id = p_story_id

LOOP

INSERT INTO notification (

notification_content, is_read, recipient_user_id, type, link, notification_created_at

) VALUES (

p_message, FALSE, collab.user_id, p_type, p_link, CURRENT_TIMESTAMP

);

END LOOP;

END; $$;

-- Function: calculates the engagement score for a given story CREATE OR REPLACE FUNCTION calculate_engagement_score(p_story_id INTEGER) RETURNS DECIMAL LANGUAGE sql AS $$

SELECT ROUND(

(COUNT(DISTINCT l.user_id) + COUNT(DISTINCT c.comment_id))::DECIMAL / NULLIF(COALESCE(SUM(ch.view_count), 0), 0) * 100, 2

) 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 LEFT JOIN chapter ch ON s.story_id = ch.story_id WHERE s.story_id = p_story_id;

$$;