SCHEMA_VERSION = "shared-agent-memory-v2"

SCHEMA_SQL = f"""
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS repos (
    id TEXT PRIMARY KEY,
    project_id TEXT,
    name TEXT NOT NULL,
    path TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY(project_id) REFERENCES projects(id)
);

CREATE TABLE IF NOT EXISTS sources (
    id TEXT PRIMARY KEY,
    source_kind TEXT NOT NULL,
    title TEXT,
    content TEXT NOT NULL,
    source_ref TEXT,
    metadata_json TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS ingestion_events (
    id TEXT PRIMARY KEY,
    trigger_type TEXT NOT NULL,
    source_id TEXT NOT NULL,
    requested_scope TEXT NOT NULL,
    job_state TEXT NOT NULL,
    processor_outcome TEXT,
    retry_count INTEGER NOT NULL DEFAULT 0,
    error_message TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    processed_at TEXT,
    FOREIGN KEY(source_id) REFERENCES sources(id)
);

CREATE TABLE IF NOT EXISTS memories (
    id TEXT PRIMARY KEY,
    schema_version TEXT NOT NULL DEFAULT '{SCHEMA_VERSION}',
    type TEXT NOT NULL,
    subtype TEXT,
    scope TEXT NOT NULL,
    status TEXT NOT NULL,
    project_id TEXT,
    repo_id TEXT,
    agent_id TEXT,
    origin_agent TEXT,
    run_id TEXT,
    task_id TEXT,
    url TEXT,
    domain TEXT,
    source_kind TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    summary TEXT NOT NULL,
    confidence REAL NOT NULL,
    freshness REAL NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    observed_at TEXT NOT NULL,
    source_ref TEXT,
    evidence_ref TEXT,
    embedding_json TEXT,
    metadata_json TEXT NOT NULL,
    FOREIGN KEY(project_id) REFERENCES projects(id),
    FOREIGN KEY(repo_id) REFERENCES repos(id)
);

CREATE TABLE IF NOT EXISTS memory_links (
    id TEXT PRIMARY KEY,
    from_memory_id TEXT NOT NULL,
    to_memory_id TEXT NOT NULL,
    relation TEXT NOT NULL,
    created_at TEXT NOT NULL,
    metadata_json TEXT NOT NULL,
    FOREIGN KEY(from_memory_id) REFERENCES memories(id),
    FOREIGN KEY(to_memory_id) REFERENCES memories(id)
);

CREATE TABLE IF NOT EXISTS retrieval_logs (
    id TEXT PRIMARY KEY,
    query_text TEXT NOT NULL,
    filters_json TEXT NOT NULL,
    results_json TEXT NOT NULL,
    created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS maintenance_jobs (
    id TEXT PRIMARY KEY,
    job_type TEXT NOT NULL,
    cadence TEXT NOT NULL,
    interval_minutes INTEGER,
    window_start TEXT,
    window_end TEXT,
    next_due_at TEXT NOT NULL,
    last_run_at TEXT,
    last_status TEXT,
    last_summary TEXT,
    enabled INTEGER NOT NULL DEFAULT 1,
    metadata_json TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS maintenance_runs (
    id TEXT PRIMARY KEY,
    job_id TEXT NOT NULL,
    status TEXT NOT NULL,
    started_at TEXT NOT NULL,
    completed_at TEXT,
    summary TEXT,
    stats_json TEXT NOT NULL,
    error_message TEXT,
    FOREIGN KEY(job_id) REFERENCES maintenance_jobs(id)
);

CREATE TABLE IF NOT EXISTS memory_conflicts (
    id TEXT PRIMARY KEY,
    memory_id TEXT NOT NULL,
    conflicting_memory_id TEXT NOT NULL,
    reason TEXT NOT NULL,
    created_at TEXT NOT NULL,
    metadata_json TEXT NOT NULL,
    FOREIGN KEY(memory_id) REFERENCES memories(id),
    FOREIGN KEY(conflicting_memory_id) REFERENCES memories(id)
);

CREATE TABLE IF NOT EXISTS profiles (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    profile_json TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS tasks (
    id TEXT PRIMARY KEY,
    schema_version TEXT NOT NULL DEFAULT '{SCHEMA_VERSION}',
    run_id TEXT,
    title TEXT NOT NULL,
    intent TEXT NOT NULL,
    kind TEXT NOT NULL,
    status TEXT NOT NULL,
    priority INTEGER NOT NULL DEFAULT 3,
    project_id TEXT,
    repo_id TEXT,
    parent_task_id TEXT,
    origin TEXT,
    owner_agent TEXT,
    blocked_reason TEXT,
    requires_human_input INTEGER NOT NULL DEFAULT 0,
    due_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    metadata_json TEXT NOT NULL,
    FOREIGN KEY(project_id) REFERENCES projects(id),
    FOREIGN KEY(repo_id) REFERENCES repos(id),
    FOREIGN KEY(parent_task_id) REFERENCES tasks(id)
);

CREATE TABLE IF NOT EXISTS task_runs (
    id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    status TEXT NOT NULL,
    input_payload_json TEXT NOT NULL,
    result_summary TEXT,
    error_message TEXT,
    started_at TEXT NOT NULL,
    completed_at TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    metadata_json TEXT NOT NULL,
    FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS artifacts (
    id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL,
    artifact_type TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    format TEXT NOT NULL,
    status TEXT NOT NULL,
    source_ref TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    metadata_json TEXT NOT NULL,
    FOREIGN KEY(task_id) REFERENCES tasks(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_memories_scope ON memories(scope);
CREATE INDEX IF NOT EXISTS idx_memories_status ON memories(status);
CREATE INDEX IF NOT EXISTS idx_memories_project ON memories(project_id);
CREATE INDEX IF NOT EXISTS idx_memories_repo ON memories(repo_id);
CREATE INDEX IF NOT EXISTS idx_memories_type ON memories(type);
CREATE INDEX IF NOT EXISTS idx_memories_subtype ON memories(subtype);
CREATE INDEX IF NOT EXISTS idx_memories_run ON memories(run_id);
CREATE INDEX IF NOT EXISTS idx_memories_task ON memories(task_id);
CREATE INDEX IF NOT EXISTS idx_memories_url ON memories(url);
CREATE INDEX IF NOT EXISTS idx_memories_domain ON memories(domain);
CREATE INDEX IF NOT EXISTS idx_memories_origin_agent ON memories(origin_agent);
CREATE INDEX IF NOT EXISTS idx_ingestion_state ON ingestion_events(job_state);
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks(status);
CREATE INDEX IF NOT EXISTS idx_tasks_owner ON tasks(owner_agent);
CREATE INDEX IF NOT EXISTS idx_tasks_run ON tasks(run_id);
CREATE INDEX IF NOT EXISTS idx_task_runs_task ON task_runs(task_id);
CREATE INDEX IF NOT EXISTS idx_task_runs_status ON task_runs(status);
CREATE INDEX IF NOT EXISTS idx_artifacts_task ON artifacts(task_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_jobs_due ON maintenance_jobs(next_due_at);
CREATE INDEX IF NOT EXISTS idx_maintenance_runs_job ON maintenance_runs(job_id);
CREATE INDEX IF NOT EXISTS idx_memory_conflicts_mem ON memory_conflicts(memory_id);

CREATE VIRTUAL TABLE IF NOT EXISTS memories_fts USING fts5(
    memory_id UNINDEXED,
    title,
    summary,
    content
);
"""