CREATE TABLE IF NOT EXISTS contributors (
    login TEXT PRIMARY KEY,
    public_pr_count INTEGER DEFAULT 0,
    public_issue_count INTEGER DEFAULT 0,
    account_age_days INTEGER DEFAULT 0,
    hidden_reputation REAL DEFAULT 0.5,
    hidden_is_spammer INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS issues (
    issue_id INTEGER PRIMARY KEY,
    number INTEGER NOT NULL,
    repo TEXT NOT NULL,
    title TEXT NOT NULL,
    body TEXT,
    author_login TEXT NOT NULL,
    created_at TEXT NOT NULL,
    closed_at TEXT,
    state TEXT NOT NULL,
    is_pr INTEGER DEFAULT 0,
    tier INTEGER DEFAULT 1,
    is_synthetic_spam INTEGER DEFAULT 0,
    spam_pattern TEXT,
    truth_action TEXT NOT NULL,
    truth_labels TEXT NOT NULL,
    truth_close_reason TEXT,
    truth_assignee TEXT,
    FOREIGN KEY (author_login) REFERENCES contributors(login)
);

CREATE TABLE IF NOT EXISTS comments (
    comment_id INTEGER PRIMARY KEY,
    issue_id INTEGER NOT NULL,
    author_login TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TEXT NOT NULL,
    FOREIGN KEY (issue_id) REFERENCES issues(issue_id),
    FOREIGN KEY (author_login) REFERENCES contributors(login)
);

CREATE TABLE IF NOT EXISTS labels (
    repo TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    PRIMARY KEY (repo, name)
);

CREATE INDEX IF NOT EXISTS idx_issues_repo_created ON issues(repo, created_at);
CREATE INDEX IF NOT EXISTS idx_issues_tier ON issues(tier);
CREATE INDEX IF NOT EXISTS idx_comments_issue ON comments(issue_id);