"""SQLite schema for the Roam index."""

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL UNIQUE,
    language TEXT,
    file_role TEXT DEFAULT 'source',
    hash TEXT,
    mtime REAL,
    line_count INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS symbols (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    qualified_name TEXT,
    kind TEXT NOT NULL,
    signature TEXT,
    line_start INTEGER,
    line_end INTEGER,
    docstring TEXT,
    visibility TEXT DEFAULT 'public',
    is_exported INTEGER DEFAULT 1,
    parent_id INTEGER REFERENCES symbols(id) ON DELETE SET NULL,
    default_value TEXT,
    -- Python pivot v12.4: is_async marks ``async def`` functions/methods.
    -- Lets agents distinguish coroutines from sync calls without
    -- regex'ing the source. Used by retrieve, context, search.
    is_async INTEGER DEFAULT 0,
    -- Decorators applied to the symbol, comma-joined ("@property,@cached").
    -- Empty when none. Captured by the Python extractor; other languages
    -- may populate when the concept maps (TypeScript decorators).
    decorators TEXT DEFAULT '',
    -- W81 / ROADMAP A6: extractor VERSION at the time this row was written
    -- (see ``LanguageExtractor.VERSION`` in src/roam/languages/base.py).
    -- Consumers comparing two indexes can spot rows produced under an
    -- older extractor shape. NULL = pre-A6 row, treat as 1.0.0-compatible.
    extractor_version TEXT
);

CREATE TABLE IF NOT EXISTS edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_id INTEGER NOT NULL REFERENCES symbols(id) ON DELETE CASCADE,
    target_id INTEGER NOT NULL REFERENCES symbols(id) ON DELETE CASCADE,
    kind TEXT NOT NULL,
    line INTEGER,
    bridge TEXT,
    confidence REAL,
    source_file_id INTEGER REFERENCES files(id) ON DELETE CASCADE,
    -- W81 / ROADMAP A6: bridge VERSION at the time this edge was emitted
    -- (see ``LanguageBridge.VERSION`` in src/roam/bridges/base.py). Only
    -- populated for bridge-emitted edges (``bridge`` IS NOT NULL); plain
    -- static-call edges leave this NULL.
    bridge_version TEXT
);

CREATE TABLE IF NOT EXISTS file_edges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    target_file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    kind TEXT NOT NULL DEFAULT 'imports',
    symbol_count INTEGER DEFAULT 1
);

CREATE TABLE IF NOT EXISTS git_commits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hash TEXT NOT NULL UNIQUE,
    author TEXT,
    timestamp INTEGER,
    message TEXT
);

CREATE TABLE IF NOT EXISTS git_file_changes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    commit_id INTEGER NOT NULL REFERENCES git_commits(id) ON DELETE CASCADE,
    file_id INTEGER REFERENCES files(id) ON DELETE SET NULL,
    path TEXT NOT NULL,
    lines_added INTEGER DEFAULT 0,
    lines_removed INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS git_cochange (
    file_id_a INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    file_id_b INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    cochange_count INTEGER DEFAULT 0,
    PRIMARY KEY (file_id_a, file_id_b)
);

CREATE TABLE IF NOT EXISTS file_stats (
    file_id INTEGER PRIMARY KEY REFERENCES files(id) ON DELETE CASCADE,
    commit_count INTEGER DEFAULT 0,
    total_churn INTEGER DEFAULT 0,
    distinct_authors INTEGER DEFAULT 0,
    complexity REAL DEFAULT 0,
    health_score REAL DEFAULT NULL,
    cochange_entropy REAL DEFAULT NULL,
    cognitive_load REAL DEFAULT NULL,
    coverage_pct REAL DEFAULT NULL,
    covered_lines INTEGER DEFAULT NULL,
    coverable_lines INTEGER DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS graph_metrics (
    symbol_id INTEGER PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    pagerank REAL DEFAULT 0,
    in_degree INTEGER DEFAULT 0,
    out_degree INTEGER DEFAULT 0,
    betweenness REAL DEFAULT 0,
    closeness REAL DEFAULT 0,
    eigenvector REAL DEFAULT 0,
    clustering_coefficient REAL DEFAULT 0,
    debt_score REAL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS clusters (
    symbol_id INTEGER PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    cluster_id INTEGER NOT NULL,
    cluster_label TEXT
);

CREATE INDEX IF NOT EXISTS idx_symbols_file ON symbols(file_id);
CREATE INDEX IF NOT EXISTS idx_symbols_name ON symbols(name);
CREATE INDEX IF NOT EXISTS idx_symbols_qualified ON symbols(qualified_name);
CREATE INDEX IF NOT EXISTS idx_symbols_kind ON symbols(kind);
CREATE INDEX IF NOT EXISTS idx_edges_source ON edges(source_id);
CREATE INDEX IF NOT EXISTS idx_edges_target ON edges(target_id);
CREATE INDEX IF NOT EXISTS idx_file_edges_source ON file_edges(source_file_id);
CREATE INDEX IF NOT EXISTS idx_file_edges_target ON file_edges(target_file_id);
CREATE INDEX IF NOT EXISTS idx_git_changes_file ON git_file_changes(file_id);
CREATE INDEX IF NOT EXISTS idx_git_changes_commit ON git_file_changes(commit_id);
CREATE INDEX IF NOT EXISTS idx_files_path ON files(path);
CREATE INDEX IF NOT EXISTS idx_graph_metrics_pagerank ON graph_metrics(pagerank DESC);
CREATE INDEX IF NOT EXISTS idx_symbols_parent ON symbols(parent_id);
CREATE INDEX IF NOT EXISTS idx_edges_kind_target ON edges(kind, target_id);
CREATE INDEX IF NOT EXISTS idx_file_stats_churn ON file_stats(total_churn DESC);

-- v11: composite indexes for hot query paths
CREATE INDEX IF NOT EXISTS idx_edges_source_target ON edges(source_id, target_id);
-- NOTE: idx_edges_source_file moved to connection.py ensure_schema() to run AFTER column migration
CREATE INDEX IF NOT EXISTS idx_symbols_file_kind ON symbols(file_id, kind);
CREATE INDEX IF NOT EXISTS idx_symbols_file_exported ON symbols(file_id, is_exported);
CREATE INDEX IF NOT EXISTS idx_file_edges_source_target ON file_edges(source_file_id, target_file_id);
CREATE INDEX IF NOT EXISTS idx_files_language ON files(language);
CREATE INDEX IF NOT EXISTS idx_clusters_cluster ON clusters(cluster_id);

-- Hypergraph: n-ary commit patterns (beyond pairwise co-change)
CREATE TABLE IF NOT EXISTS git_hyperedges (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    commit_id INTEGER NOT NULL REFERENCES git_commits(id) ON DELETE CASCADE,
    file_count INTEGER NOT NULL,
    sig_hash TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS git_hyperedge_members (
    hyperedge_id INTEGER NOT NULL REFERENCES git_hyperedges(id) ON DELETE CASCADE,
    file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    ordinal INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_hyperedge_commit ON git_hyperedges(commit_id);
CREATE INDEX IF NOT EXISTS idx_hyperedge_sig ON git_hyperedges(sig_hash);
CREATE INDEX IF NOT EXISTS idx_hyperedge_members_edge ON git_hyperedge_members(hyperedge_id);
CREATE INDEX IF NOT EXISTS idx_hyperedge_members_file ON git_hyperedge_members(file_id);

-- Per-symbol complexity metrics (cognitive complexity, nesting, params)
CREATE TABLE IF NOT EXISTS symbol_metrics (
    symbol_id INTEGER PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    cognitive_complexity REAL DEFAULT 0,
    nesting_depth INTEGER DEFAULT 0,
    param_count INTEGER DEFAULT 0,
    line_count INTEGER DEFAULT 0,
    return_count INTEGER DEFAULT 0,
    bool_op_count INTEGER DEFAULT 0,
    callback_depth INTEGER DEFAULT 0,
    cyclomatic_density REAL DEFAULT 0,
    halstead_volume REAL DEFAULT 0,
    halstead_difficulty REAL DEFAULT 0,
    halstead_effort REAL DEFAULT 0,
    halstead_bugs REAL DEFAULT 0,
    coverage_pct REAL DEFAULT NULL,
    covered_lines INTEGER DEFAULT NULL,
    coverable_lines INTEGER DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS idx_symbol_metrics_complexity
    ON symbol_metrics(cognitive_complexity DESC);

-- Math signals: AST-derived signals for algorithm detection
CREATE TABLE IF NOT EXISTS math_signals (
    symbol_id INTEGER PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    loop_depth INTEGER DEFAULT 0,
    has_nested_loops INTEGER DEFAULT 0,
    calls_in_loops TEXT,
    calls_in_loops_qualified TEXT,
    subscript_in_loops INTEGER DEFAULT 0,
    has_self_call INTEGER DEFAULT 0,
    loop_with_compare INTEGER DEFAULT 0,
    loop_with_accumulator INTEGER DEFAULT 0,
    loop_with_multiplication INTEGER DEFAULT 0,
    loop_with_modulo INTEGER DEFAULT 0,
    self_call_count INTEGER DEFAULT 0,
    str_concat_in_loop INTEGER DEFAULT 0,
    loop_invariant_calls TEXT,
    loop_lookup_calls TEXT,
    front_ops_in_loop INTEGER DEFAULT 0,
    loop_bound_small INTEGER DEFAULT 0,
    loop_eq_with_dependent_write INTEGER DEFAULT 0
);

-- Agentic memory: persistent annotations on symbols and files
CREATE TABLE IF NOT EXISTS annotations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    symbol_id INTEGER REFERENCES symbols(id) ON DELETE SET NULL,
    qualified_name TEXT,
    file_path TEXT,
    tag TEXT,
    content TEXT NOT NULL,
    author TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    expires_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_annotations_symbol ON annotations(symbol_id);
CREATE INDEX IF NOT EXISTS idx_annotations_qname ON annotations(qualified_name);
CREATE INDEX IF NOT EXISTS idx_annotations_file ON annotations(file_path);
CREATE INDEX IF NOT EXISTS idx_annotations_tag ON annotations(tag);

-- Symbol effects: what functions DO (side-effect classification)
CREATE TABLE IF NOT EXISTS symbol_effects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    symbol_id INTEGER NOT NULL REFERENCES symbols(id) ON DELETE CASCADE,
    effect_type TEXT NOT NULL,
    source TEXT NOT NULL DEFAULT 'direct'
);
CREATE INDEX IF NOT EXISTS idx_symbol_effects_symbol ON symbol_effects(symbol_id);
CREATE INDEX IF NOT EXISTS idx_symbol_effects_type ON symbol_effects(effect_type);

-- Snapshots: health metrics over time
CREATE TABLE IF NOT EXISTS snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp INTEGER NOT NULL,
    tag TEXT,
    source TEXT NOT NULL,
    git_branch TEXT,
    git_commit TEXT,
    files INTEGER,
    symbols INTEGER,
    edges INTEGER,
    cycles INTEGER,
    god_components INTEGER,
    bottlenecks INTEGER,
    dead_exports INTEGER,
    layer_violations INTEGER,
    health_score INTEGER,
    tangle_ratio REAL,
    avg_complexity REAL,
    brain_methods INTEGER,
    spectral_gap REAL
);

-- Runtime trace statistics: ingested from OpenTelemetry/Jaeger/Zipkin/generic traces
CREATE TABLE IF NOT EXISTS runtime_stats (
    id INTEGER PRIMARY KEY,
    symbol_id INTEGER REFERENCES symbols(id) ON DELETE SET NULL,
    symbol_name TEXT,
    file_path TEXT,
    trace_source TEXT,
    call_count INTEGER DEFAULT 0,
    p50_latency_ms REAL,
    p99_latency_ms REAL,
    error_rate REAL DEFAULT 0.0,
    last_seen TEXT,
    otel_db_system TEXT,
    otel_db_operation TEXT,
    otel_db_statement_type TEXT,
    ingested_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_runtime_stats_symbol ON runtime_stats(symbol_id);
CREATE INDEX IF NOT EXISTS idx_runtime_stats_name ON runtime_stats(symbol_name);

-- Security: vulnerability tracking and reachability
CREATE TABLE IF NOT EXISTS vulnerabilities (
    id INTEGER PRIMARY KEY,
    cve_id TEXT,
    package_name TEXT NOT NULL,
    severity TEXT,
    title TEXT,
    source TEXT,
    matched_symbol_id INTEGER REFERENCES symbols(id) ON DELETE SET NULL,
    matched_file TEXT,
    reachable INTEGER DEFAULT 0,
    shortest_path TEXT,
    hop_count INTEGER,
    ingested_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_vuln_cve ON vulnerabilities(cve_id);
CREATE INDEX IF NOT EXISTS idx_vuln_package ON vulnerabilities(package_name);
CREATE INDEX IF NOT EXISTS idx_vuln_symbol ON vulnerabilities(matched_symbol_id);

-- TF-IDF vectors for semantic search
CREATE TABLE IF NOT EXISTS symbol_tfidf (
    symbol_id INTEGER PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    terms TEXT NOT NULL,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Optional dense vectors for local ONNX semantic search
CREATE TABLE IF NOT EXISTS symbol_embeddings (
    symbol_id INTEGER PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    vector TEXT NOT NULL,
    dims INTEGER NOT NULL,
    provider TEXT NOT NULL DEFAULT 'onnx',
    model_id TEXT,
    updated_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_symbol_embeddings_provider
    ON symbol_embeddings(provider);

-- Metric trends: fine-grained metric snapshots for sparkline tracking
CREATE TABLE IF NOT EXISTS metric_snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp TEXT NOT NULL,
    metric_name TEXT NOT NULL,
    metric_value REAL NOT NULL,
    details TEXT
);
CREATE INDEX IF NOT EXISTS idx_metric_snapshots_name_ts
    ON metric_snapshots(metric_name, timestamp);

-- Taint analysis: per-function summaries and cross-function findings
CREATE TABLE IF NOT EXISTS taint_summaries (
    symbol_id INTEGER PRIMARY KEY REFERENCES symbols(id) ON DELETE CASCADE,
    param_taints_return TEXT,
    param_to_sink TEXT,
    return_from_source INTEGER DEFAULT 0,
    direct_sources TEXT,
    direct_sinks TEXT,
    is_sanitizer INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS taint_findings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_symbol_id INTEGER REFERENCES symbols(id) ON DELETE CASCADE,
    sink_symbol_id INTEGER REFERENCES symbols(id) ON DELETE CASCADE,
    source_type TEXT NOT NULL,
    sink_type TEXT NOT NULL,
    call_chain TEXT,
    chain_length INTEGER DEFAULT 1,
    sanitized INTEGER DEFAULT 0,
    confidence REAL DEFAULT 0.8
);
CREATE INDEX IF NOT EXISTS idx_taint_findings_source ON taint_findings(source_symbol_id);
CREATE INDEX IF NOT EXISTS idx_taint_findings_sink ON taint_findings(sink_symbol_id);

-- v12 (A.0): Persisted AST clone detection results.
-- Populated by graph.clone_detect.store_clones(); queried by `roam clones`,
-- `roam critique` (clone-not-edited check), and the retrieve reranker
-- (clone-canonical-boost). Without this, every consumer re-runs detection
-- (~5s on roam itself per query).
CREATE TABLE IF NOT EXISTS clone_clusters (
    id INTEGER PRIMARY KEY,
    canonical_qname TEXT,
    canonical_file TEXT,
    canonical_func TEXT,
    canonical_line INTEGER,
    member_count INTEGER NOT NULL,
    avg_similarity REAL NOT NULL,
    pattern TEXT,
    suggestion TEXT,
    detected_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS clone_pairs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    qname_a TEXT NOT NULL,
    qname_b TEXT NOT NULL,
    file_a TEXT NOT NULL,
    file_b TEXT NOT NULL,
    func_a TEXT NOT NULL,
    func_b TEXT NOT NULL,
    line_a INTEGER NOT NULL,
    line_end_a INTEGER,
    line_b INTEGER NOT NULL,
    line_end_b INTEGER,
    similarity REAL NOT NULL,
    cluster_id INTEGER REFERENCES clone_clusters(id) ON DELETE SET NULL,
    detected_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_clone_pairs_qname_a ON clone_pairs(qname_a);
CREATE INDEX IF NOT EXISTS idx_clone_pairs_qname_b ON clone_pairs(qname_b);
CREATE INDEX IF NOT EXISTS idx_clone_pairs_cluster ON clone_pairs(cluster_id);
CREATE INDEX IF NOT EXISTS idx_clone_pairs_file_a ON clone_pairs(file_a);
CREATE INDEX IF NOT EXISTS idx_clone_pairs_file_b ON clone_pairs(file_b);

-- Index manifest: one row per index run capturing the environment +
-- project state at indexing time. Used by `roam doctor` to flag stale
-- indexes (parser/grammar drift, schema bumps, version mismatches) and
-- by drift checks on the agent contract surface.
CREATE TABLE IF NOT EXISTS index_manifest (
    id INTEGER PRIMARY KEY,
    indexed_at INTEGER NOT NULL,
    roam_version TEXT NOT NULL,
    schema_version INTEGER NOT NULL,
    parser_versions TEXT NOT NULL,
    grammar_versions TEXT,
    config_hash TEXT NOT NULL,
    git_head TEXT,
    git_dirty_hash TEXT,
    enabled_extras TEXT NOT NULL,
    index_profile TEXT DEFAULT 'all',
    notes TEXT,
    -- W82 / ROADMAP A8: per-sub-step completion status for the run that
    -- wrote this row. JSON map of {step_name: {status, error_excerpt,
    -- duration_ms}}. Lets `roam doctor` surface "your index is missing
    -- taint analysis because that step failed" rather than the generic
    -- stale-manifest signal. Older rows leave this NULL.
    steps_status TEXT,
    -- W81 / ROADMAP A6: JSON map of {"bridges": {name: ver},
    -- "detectors": {task_id: ver}, "extractors": {language: ver}}
    -- capturing every component's VERSION at index time. Compare row N
    -- to row N-1 to spot a bump; any drift here means rows stamped
    -- under the previous run carry a stale shape and consumers may want
    -- to invalidate (cf. ``edges.bridge_version``, ``symbols.extractor_version``).
    component_versions TEXT
);
CREATE INDEX IF NOT EXISTS idx_index_manifest_at ON index_manifest(indexed_at);

-- W90 / ROADMAP A4: Finding Registry hybrid table.
--
-- Every detector has its own emit shape (math_signals, taint_findings,
-- clone_pairs, vuln rows, smells, etc.) so cross-detector dedup is
-- impossible and suppression management has no audit trail. This table
-- is the denormalised cross-detector surface: each detector continues
-- to write to its detector-specific table AND emits one row here. That
-- gives ``roam findings --filter ...`` a single index to query against
-- + a central SARIF emit point + the substrate the queued WorkflowRun
-- work will hang off.
--
-- ``finding_id_str`` is the deterministic stable key (detector + subject
-- + a content hash); UNIQUE on it lets re-emits upsert evidence without
-- duplicating rows. ``source_version`` was reserved by W81 (ROADMAP A6)
-- specifically for this table — it stamps the detector VERSION that
-- produced the row, so a consumer can spot rows produced under a stale
-- detector shape without a full re-index.
CREATE TABLE IF NOT EXISTS findings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    finding_id_str TEXT NOT NULL UNIQUE,
    subject_kind TEXT NOT NULL,
    subject_id INTEGER,
    claim TEXT NOT NULL,
    evidence_json TEXT,
    confidence TEXT,
    source_detector TEXT NOT NULL,
    source_version TEXT,
    supersedes_id INTEGER REFERENCES findings(id) ON DELETE SET NULL,
    suppressions_json TEXT DEFAULT '[]',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_findings_subject ON findings(subject_kind, subject_id);
CREATE INDEX IF NOT EXISTS idx_findings_detector ON findings(source_detector);
CREATE INDEX IF NOT EXISTS idx_findings_created ON findings(created_at);
"""
