-- Issues, comments, labels and milestones (milestone 3). Applied by store/migrate.rs.
-- Numbers come from repos.next_issue_number, allocated in the same transaction as the insert.

ALTER TABLE repos ADD COLUMN next_issue_number INTEGER NOT NULL DEFAULT 1;

CREATE TABLE milestones (
    id TEXT PRIMARY KEY, repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    title TEXT NOT NULL, title_folded TEXT NOT NULL, description TEXT NOT NULL DEFAULT '',
    due_on TEXT,                       -- 'YYYY-MM-DD' or NULL
    state TEXT NOT NULL DEFAULT 'open',-- 'open' | 'closed'
    created_at TEXT NOT NULL, closed_at TEXT,
    UNIQUE (repo_id, title_folded));

CREATE TABLE issues (
    id TEXT PRIMARY KEY, repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    number INTEGER NOT NULL,
    kind TEXT NOT NULL DEFAULT 'issue',          -- 'issue' now; 'pull' in M4 (shared numbering)
    title TEXT NOT NULL, body TEXT NOT NULL DEFAULT '',
    state TEXT NOT NULL DEFAULT 'open',          -- 'open' | 'closed'
    state_reason TEXT,                           -- NULL | 'completed' | 'not_planned'
    author_id TEXT NOT NULL REFERENCES users(id),
    milestone_id TEXT REFERENCES milestones(id) ON DELETE SET NULL,
    comment_count INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL, updated_at TEXT NOT NULL, closed_at TEXT,
    UNIQUE (repo_id, number));
CREATE INDEX issues_repo_state ON issues(repo_id, kind, state, number);

CREATE TABLE comments (
    id TEXT PRIMARY KEY, issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    author_id TEXT NOT NULL REFERENCES users(id),
    body TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
    edited INTEGER NOT NULL DEFAULT 0);
CREATE INDEX comments_issue ON comments(issue_id, created_at);

-- Edit history: the previous version is saved on every edit.
CREATE TABLE edit_history (
    id TEXT PRIMARY KEY, target_kind TEXT NOT NULL,   -- 'issue' | 'comment'
    target_id TEXT NOT NULL, old_title TEXT, old_body TEXT NOT NULL,
    editor_id TEXT NOT NULL REFERENCES users(id), edited_at TEXT NOT NULL);
CREATE INDEX edit_history_target ON edit_history(target_kind, target_id, edited_at);

-- Timeline events (everything except comments).
CREATE TABLE issue_events (
    id TEXT PRIMARY KEY, issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    actor_id TEXT REFERENCES users(id),           -- NULL = system
    kind TEXT NOT NULL,   -- closed reopened labeled unlabeled assigned unassigned milestoned demilestoned renamed referenced
    data TEXT NOT NULL DEFAULT '{}',              -- JSON, e.g. {"label":"bug"} {"from":"a","to":"b"} {"commit":"<oid>"} {"reason":"completed"}
    created_at TEXT NOT NULL);
CREATE INDEX issue_events_issue ON issue_events(issue_id, created_at);

CREATE TABLE labels (
    id TEXT PRIMARY KEY, repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    name TEXT NOT NULL, name_folded TEXT NOT NULL,
    color TEXT NOT NULL,                          -- '#rrggbb' lowercase
    description TEXT NOT NULL DEFAULT '',
    UNIQUE (repo_id, name_folded));

CREATE TABLE issue_labels (issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    label_id TEXT NOT NULL REFERENCES labels(id) ON DELETE CASCADE, PRIMARY KEY (issue_id, label_id));
CREATE TABLE issue_assignees (issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    user_id TEXT NOT NULL REFERENCES users(id), PRIMARY KEY (issue_id, user_id));