-- Milestone 1 schema. Applied by store/migrate.rs, which records the version in
-- schema_migrations. Timestamps are RFC 3339 UTC strings (see time::now_stamp).

CREATE TABLE users (
    id              TEXT PRIMARY KEY,
    username        TEXT NOT NULL,
    username_folded TEXT NOT NULL UNIQUE,
    email           TEXT,
    password_hash   TEXT NOT NULL,
    is_admin        INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL
);

CREATE TABLE sessions (
    id          TEXT PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  TEXT NOT NULL UNIQUE,
    csrf_token  TEXT NOT NULL,
    created_at  TEXT NOT NULL,
    expires_at  TEXT NOT NULL,
    last_seen   TEXT NOT NULL,
    user_agent  TEXT
);
CREATE INDEX sessions_user_id ON sessions(user_id);
CREATE INDEX sessions_expires_at ON sessions(expires_at);

CREATE TABLE ssh_keys (
    id           TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title        TEXT NOT NULL,
    algorithm    TEXT NOT NULL,
    key_blob     BLOB NOT NULL UNIQUE,
    fingerprint  TEXT NOT NULL,
    created_at   TEXT NOT NULL,
    last_used_at TEXT
);
CREATE INDEX ssh_keys_user_id ON ssh_keys(user_id);

CREATE TABLE access_tokens (
    id           TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    token_hash   TEXT NOT NULL UNIQUE,
    scopes       TEXT NOT NULL DEFAULT '[]',
    expires_at   TEXT,
    created_at   TEXT NOT NULL,
    last_used_at TEXT
);
CREATE INDEX access_tokens_user_id ON access_tokens(user_id);

CREATE TABLE repos (
    id             TEXT PRIMARY KEY,
    owner_id       TEXT NOT NULL REFERENCES users(id),
    name           TEXT NOT NULL,
    name_folded    TEXT NOT NULL,
    description    TEXT NOT NULL DEFAULT '',
    visibility     TEXT NOT NULL,
    default_branch TEXT NOT NULL,
    archived       INTEGER NOT NULL DEFAULT 0,
    created_at     TEXT NOT NULL,
    UNIQUE(owner_id, name_folded)
);

-- Old owner/name pairs (folded) that now point at a repo, e.g. after a rename.
CREATE TABLE repo_redirects (
    owner_folded TEXT NOT NULL,
    name_folded  TEXT NOT NULL,
    repo_id      TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    created_at   TEXT NOT NULL,
    PRIMARY KEY (owner_folded, name_folded)
);
CREATE INDEX repo_redirects_repo_id ON repo_redirects(repo_id);

CREATE TABLE collaborators (
    repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role    TEXT NOT NULL,
    PRIMARY KEY (repo_id, user_id)
);
CREATE INDEX collaborators_user_id ON collaborators(user_id);

CREATE TABLE push_events (
    id         TEXT PRIMARY KEY,
    repo_id    TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    actor_id   TEXT NOT NULL REFERENCES users(id),
    ref_name   TEXT NOT NULL,
    old_oid    TEXT NOT NULL,
    new_oid    TEXT NOT NULL,
    protocol   TEXT NOT NULL,
    created_at TEXT NOT NULL
);
CREATE INDEX push_events_repo_created ON push_events(repo_id, created_at);