= 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;
$$;
}}}