-- 0001_init: users, sessions, repos.
--
-- Timestamps are unix epoch seconds (INTEGER) so they map straight onto
-- Go's time.Time with no driver-specific parsing.

-- Single-user host, but the table stays generic so CLI-added accounts
-- remain possible later.
CREATE TABLE users (
    id            INTEGER PRIMARY KEY,
    username      TEXT    NOT NULL UNIQUE,
    password_hash TEXT    NOT NULL,
    created_at    INTEGER NOT NULL DEFAULT (unixepoch())
);

-- Browser login sessions. token is a random opaque string.
CREATE TABLE sessions (
    token      TEXT    PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    expires_at INTEGER NOT NULL
);

CREATE INDEX sessions_user_id_idx ON sessions (user_id);

-- One row per bare repo on disk at data/repos/{username}/{name}.git.
CREATE TABLE repos (
    id             INTEGER PRIMARY KEY,
    owner_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name           TEXT    NOT NULL,
    description    TEXT    NOT NULL DEFAULT '',
    visibility     TEXT    NOT NULL DEFAULT 'private'
                   CHECK (visibility IN ('public', 'private')),
    default_branch TEXT    NOT NULL DEFAULT 'main',
    created_at     INTEGER NOT NULL DEFAULT (unixepoch()),
    pushed_at      INTEGER,
    UNIQUE (owner_id, name)
);

CREATE INDEX repos_visibility_idx ON repos (visibility);