-- Releases: a tag-anchored announcement with optional downloadable assets. A release points at
-- a tag (created as a lightweight tag on demand) and keeps its own title and body. Deleting a
-- release keeps the git tag. Drafts have published_at NULL and are visible only to writers.
-- Asset bytes live in the blob store under assets/<asset id>; only metadata is here.

CREATE TABLE releases (
    id           TEXT PRIMARY KEY,
    repo_id      TEXT NOT NULL REFERENCES repos(id),
    tag          TEXT NOT NULL,
    target_oid   TEXT NOT NULL,
    title        TEXT NOT NULL,
    body         TEXT NOT NULL DEFAULT '',
    draft        INTEGER NOT NULL DEFAULT 0,
    prerelease   INTEGER NOT NULL DEFAULT 0,
    author_id    TEXT NOT NULL REFERENCES users(id),
    created_at   TEXT NOT NULL,
    published_at TEXT,
    UNIQUE (repo_id, tag)
);

CREATE TABLE release_assets (
    id           TEXT PRIMARY KEY,
    release_id   TEXT NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    size         INTEGER NOT NULL,
    sha256       TEXT NOT NULL,
    content_type TEXT NOT NULL,
    download_count INTEGER NOT NULL DEFAULT 0,
    uploader_id  TEXT NOT NULL REFERENCES users(id),
    created_at   TEXT NOT NULL,
    UNIQUE (release_id, name)
);