schema.sql

-- =============================================================
-- Section 04 — Exercise 1: YouTube-style video platform
-- =============================================================
-- This file is a REFERENCE schema. It's the "finished" version
-- of the design you'll be asked to build in exercises.md from
-- just the written brief.
--
-- DO NOT load this file until you've tried it yourself.
--
-- When you're done:
--     psql -U postgres -d sql_exercise \
--          -f 03-database-design/04-exercise-1/schema.sql
--
-- It drops the schema first, so it's safe to re-run.
-- =============================================================

DROP SCHEMA IF EXISTS dd_exercise1 CASCADE;
CREATE SCHEMA dd_exercise1;
SET search_path TO dd_exercise1;


CREATE TABLE user_profile (
    id         BIGSERIAL   PRIMARY KEY,
    first_name TEXT        NOT NULL,
    last_name  TEXT        NOT NULL,
    email      TEXT        NOT NULL UNIQUE,
    gender     TEXT        NOT NULL CHECK (gender IN ('MALE', 'FEMALE', 'OTHER')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- youtube_account is 1:1 with user_profile. A user may or may
-- not have a youtube account, but cannot have two.
CREATE TABLE youtube_account (
    id         BIGINT      PRIMARY KEY REFERENCES user_profile(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE youtube_channel (
    id                 BIGSERIAL   PRIMARY KEY,
    youtube_account_id BIGINT      NOT NULL REFERENCES youtube_account(id),
    channel_name       TEXT        NOT NULL UNIQUE,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- channel_subscriber is M:N between accounts and channels.
CREATE TABLE channel_subscriber (
    youtube_account_id BIGINT      NOT NULL REFERENCES youtube_account(id),
    youtube_channel_id BIGINT      NOT NULL REFERENCES youtube_channel(id),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (youtube_account_id, youtube_channel_id)
);

CREATE TABLE video (
    id                 BIGSERIAL   PRIMARY KEY,
    youtube_channel_id BIGINT      NOT NULL REFERENCES youtube_channel(id),
    title              TEXT        NOT NULL,
    url                TEXT        NOT NULL UNIQUE,
    description        TEXT        NOT NULL,
    category           TEXT        NOT NULL,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- A view can be anonymous (youtube_account_id NULL).
-- Multiple views from the same account on the same video are
-- allowed, so we use a synthetic id PK rather than a composite.
CREATE TABLE video_view (
    id                 BIGSERIAL   PRIMARY KEY,
    youtube_account_id BIGINT      REFERENCES youtube_account(id),
    video_id           BIGINT      NOT NULL REFERENCES video(id),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- A like is one per (account, video). Composite PK.
CREATE TABLE video_like (
    youtube_account_id BIGINT      NOT NULL REFERENCES youtube_account(id),
    video_id           BIGINT      NOT NULL REFERENCES video(id),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (youtube_account_id, video_id)
);

CREATE TABLE video_comment (
    id                 BIGSERIAL   PRIMARY KEY,
    youtube_account_id BIGINT      NOT NULL REFERENCES youtube_account(id),
    video_id           BIGINT      NOT NULL REFERENCES video(id),
    comment            TEXT        NOT NULL,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE video_comment_like (
    youtube_account_id BIGINT      NOT NULL REFERENCES youtube_account(id),
    video_comment_id   BIGINT      NOT NULL REFERENCES video_comment(id),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (youtube_account_id, video_comment_id)
);