CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    forge TEXT NOT NULL,
    owner TEXT NOT NULL,
    repo TEXT NOT NULL,
    UNIQUE(forge, owner, repo)
);

CREATE TABLE project_memory_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    project_id INTEGER NOT NULL,
    key TEXT NOT NULL,
    value_json TEXT NOT NULL,
    source TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, key)
);

CREATE TABLE project_memory_summaries (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    project_id INTEGER NOT NULL,
    summary_type TEXT NOT NULL,
    content TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, summary_type)
);

CREATE TABLE review_threads (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    project_id INTEGER NOT NULL,
    file TEXT NOT NULL,
    line INTEGER NOT NULL,
    initial_comment TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE review_thread_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    thread_id INTEGER NOT NULL,
    author TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(thread_id) REFERENCES review_threads(id) ON DELETE CASCADE
);

CREATE INDEX idx_project_memory_entries_project_id
    ON project_memory_entries(project_id);
CREATE INDEX idx_project_memory_summaries_project_id
    ON project_memory_summaries(project_id);
CREATE INDEX idx_review_threads_project_id
    ON review_threads(project_id);
CREATE INDEX idx_review_thread_messages_thread_id
    ON review_thread_messages(thread_id);