ALTER TABLE repositories ADD COLUMN enable_issues INTEGER NOT NULL DEFAULT 1;
ALTER TABLE repositories ADD COLUMN enable_pulls INTEGER NOT NULL DEFAULT 1;
ALTER TABLE repositories ADD COLUMN enable_releases INTEGER NOT NULL DEFAULT 1;
ALTER TABLE repositories ADD COLUMN enable_activity INTEGER NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS pull_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    number INTEGER NOT NULL,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    base_branch TEXT NOT NULL,
    head_branch TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    merged_by_id INTEGER REFERENCES users(id),
    merged_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(repo_id, number)
);

CREATE INDEX IF NOT EXISTS idx_pulls_repo_id ON pull_requests(repo_id, status, number DESC);

CREATE TABLE IF NOT EXISTS pull_request_comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pull_id INTEGER NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    body TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_pull_comments_pull_id ON pull_request_comments(pull_id, created_at ASC);

CREATE TABLE IF NOT EXISTS releases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    tag_name TEXT NOT NULL,
    title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    is_draft INTEGER NOT NULL DEFAULT 0,
    is_prerelease INTEGER NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(repo_id, tag_name)
);

CREATE INDEX IF NOT EXISTS idx_releases_repo_id ON releases(repo_id, published_at DESC);