-- Releases: tag-based release notes with optional uploaded assets. Toggleable
-- per repository (releases_enabled), like issues and pull requests.
ALTER TABLE repos ADD COLUMN releases_enabled BOOLEAN NOT NULL DEFAULT TRUE;

CREATE TABLE releases (
    id            TEXT PRIMARY KEY,
    repo_id       TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    tag           TEXT NOT NULL,
    name          TEXT NOT NULL,
    body          TEXT,
    is_prerelease BOOLEAN NOT NULL DEFAULT FALSE,
    is_draft      BOOLEAN NOT NULL DEFAULT FALSE,
    author_id     TEXT REFERENCES users(id) ON DELETE SET NULL,
    created_at    BIGINT NOT NULL,
    updated_at    BIGINT NOT NULL
);
CREATE UNIQUE INDEX idx_releases_repo_tag ON releases (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           BIGINT NOT NULL,
    content_type   TEXT NOT NULL,
    download_count BIGINT NOT NULL DEFAULT 0,
    created_at     BIGINT NOT NULL
);
CREATE INDEX idx_release_assets_release ON release_assets (release_id);