CREATE TABLE IF NOT EXISTS channel_article_like (
    article UUID NOT NULL REFERENCES channel_article(id) ON DELETE CASCADE,
    "user" UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (article, "user")
);

CREATE INDEX IF NOT EXISTS idx_article_like_user
    ON channel_article_like ("user", created_at DESC);

CREATE TABLE IF NOT EXISTS channel_article_comment (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    article UUID NOT NULL REFERENCES channel_article(id) ON DELETE CASCADE,
    author UUID NOT NULL,
    parent UUID REFERENCES channel_article_comment(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_article_comment_article
    ON channel_article_comment (article, created_at ASC)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_article_comment_parent
    ON channel_article_comment (parent, created_at ASC)
    WHERE deleted_at IS NULL;
