-- 0005_releases: releases attached to existing tags, with binary assets.
--
-- Unlike issues/pulls, releases are owner-only publish artifacts: there is
-- no guest author and no moderation queue. A release names one tag that
-- already exists in the repo (UNIQUE per repo). release_assets are files
-- uploaded to the release; stored_name is the opaque on-disk name under
-- uploads/releases/<release_id>/, while filename is what the user sees.
CREATE TABLE releases (
    id         INTEGER PRIMARY KEY,
    repo_id    INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    tag        TEXT    NOT NULL,
    title      TEXT    NOT NULL,
    notes      TEXT    NOT NULL DEFAULT '',
    author_id  INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    UNIQUE (repo_id, tag)
);

CREATE INDEX releases_repo_id_idx ON releases (repo_id);

CREATE TABLE release_assets (
    id          INTEGER PRIMARY KEY,
    release_id  INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
    filename    TEXT    NOT NULL,
    stored_name TEXT    NOT NULL,
    size        INTEGER NOT NULL,
    created_at  INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE INDEX release_assets_release_id_idx ON release_assets (release_id);