-- Collaborators: per-repository access grants for other users.
CREATE TABLE IF NOT EXISTS collaborators (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role TEXT NOT NULL DEFAULT 'write',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(repo_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_collaborators_user ON collaborators(user_id);

-- Forks.
ALTER TABLE repositories ADD COLUMN forked_from_repo_id INTEGER REFERENCES repositories(id) ON DELETE SET NULL;

-- Fork-based (cross-repository) pull requests.
ALTER TABLE pull_requests ADD COLUMN head_repo_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE;

-- Pull request reviews (approve / request changes).
CREATE TABLE IF NOT EXISTS pull_request_reviews (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pull_id INTEGER NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
    reviewer_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    state TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(pull_id, reviewer_id)
);

-- Device authorization grants (RFC 8628) used by the git credential helper.
CREATE TABLE IF NOT EXISTS device_codes (
    device_code TEXT PRIMARY KEY,
    user_code TEXT NOT NULL UNIQUE,
    client_id TEXT NOT NULL DEFAULT '',
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    last_polled_at DATETIME
);
CREATE INDEX IF NOT EXISTS idx_device_codes_expires ON device_codes(expires_at);