

-- @table mcard_embeddings
-- @description Fallback embedding storage when sqlite-vec is unavailable
-- @column id          - Auto-increment primary key
-- @column metadata_id - FK to mcard_vector_metadata.id
-- @column embedding   - Serialized float32 vector as BLOB
CREATE TABLE IF NOT EXISTS mcard_embeddings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    metadata_id INTEGER NOT NULL,
    embedding BLOB NOT NULL,
    UNIQUE(metadata_id),
    FOREIGN KEY (metadata_id) REFERENCES mcard_vector_metadata(id)
);

-- @virtual_table mcard_fts
-- @description Full-text search for hybrid retrieval
-- @note Uses Porter stemming with Unicode support
CREATE VIRTUAL TABLE IF NOT EXISTS mcard_fts USING fts5(
    hash,
    content,
    tokenize='porter unicode61'
);

-- @table version_similarity_cache
-- @description Precomputed pairwise similarities for performance
-- @column id               - Auto-increment primary key
-- @column handle           - Handle these versions belong to
-- @column hash_a           - First version hash
-- @column hash_b           - Second version hash
-- @column similarity_score - Cosine similarity [-1, 1]
-- @column distance_euclidean - L2 distance [0, ∞)
-- @column computed_at      - When similarity was computed
CREATE TABLE IF NOT EXISTS version_similarity_cache (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    handle TEXT NOT NULL,
    hash_a TEXT NOT NULL,
    hash_b TEXT NOT NULL,
    similarity_score REAL NOT NULL,
    distance_euclidean REAL,
    computed_at TEXT NOT NULL,
    UNIQUE(handle, hash_a, hash_b)
);

-- @index idx_vsc_handle
-- @description Efficient lookup of cached similarities by handle
CREATE INDEX IF NOT EXISTS idx_vsc_handle 
ON version_similarity_cache(handle);


-- ═══════════════════════════════════════════════════════════════════════════════
-- LAYER 5: KNOWLEDGE GRAPH (GraphRAG)
-- ═══════════════════════════════════════════════════════════════════════════════
-- Storage for extracted knowledge graph entities and relationships.

-- @table graph_entities
-- @description Nodes in the knowledge graph
-- @column id          - Auto-increment primary key
-- @column name        - Entity name
-- @column type        - Entity type (e.g., "Person", "Organization")
-- @column description - Optional description
-- @column source_hash - FK to card.hash where entity was extracted
-- @column embedding   - Optional entity embedding
-- @column created_at  - When entity was created
CREATE TABLE IF NOT EXISTS graph_entities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    description TEXT,
    source_hash TEXT NOT NULL,
    embedding BLOB,
    created_at TEXT NOT NULL,
    UNIQUE(name, type, source_hash)
);

-- @index idx_entity_name
-- @description Efficient entity lookup by name
CREATE INDEX IF NOT EXISTS idx_entity_name 
ON graph_entities(name);

-- @index idx_entity_type
-- @description Efficient entity lookup by type
CREATE INDEX IF NOT EXISTS idx_entity_type 
ON graph_entities(type);

-- @index idx_entity_source
-- @description Efficient entity lookup by source document
CREATE INDEX IF NOT EXISTS idx_entity_source 
ON graph_entities(source_hash);

-- @table graph_relationships
-- @description Edges in the knowledge graph
-- @column id               - Auto-increment primary key
-- @column source_entity_id - FK to graph_entities.id (from)
-- @column target_entity_id - FK to graph_entities.id (to)
-- @column relationship     - Relationship type/label
-- @column description      - Optional description
-- @column weight           - Relationship strength
-- @column source_hash      - FK to card.hash where relationship was extracted
-- @column created_at       - When relationship was created
CREATE TABLE IF NOT EXISTS graph_relationships (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_entity_id INTEGER NOT NULL,
    target_entity_id INTEGER NOT NULL,
    relationship TEXT NOT NULL,
    description TEXT,
    weight REAL DEFAULT 1.0,
    source_hash TEXT NOT NULL,
    created_at TEXT NOT NULL,
    UNIQUE(source_entity_id, target_entity_id, relationship, source_hash),
    FOREIGN KEY (source_entity_id) REFERENCES graph_entities(id),
    FOREIGN KEY (target_entity_id) REFERENCES graph_entities(id)
);

-- @index idx_rel_source
-- @description Efficient lookup of relationships by source entity
CREATE INDEX IF NOT EXISTS idx_rel_source 
ON graph_relationships(source_entity_id);

-- @index idx_rel_target
-- @description Efficient lookup of relationships by target entity
CREATE INDEX IF NOT EXISTS idx_rel_target 
ON graph_relationships(target_entity_id);

-- @table graph_communities
-- @description Hierarchical community summaries (GraphRAG)
-- @column id                  - Auto-increment primary key
-- @column level               - Hierarchy level (0 = leaf)
-- @column title               - Community title
-- @column summary             - AI-generated summary
-- @column embedding           - Community embedding
-- @column member_entity_ids   - JSON array of entity IDs
-- @column parent_community_id - FK to parent community
-- @column created_at          - When community was created
CREATE TABLE IF NOT EXISTS graph_communities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    level INTEGER NOT NULL DEFAULT 0,
    title TEXT,
    summary TEXT NOT NULL,
    embedding BLOB,
    member_entity_ids TEXT,
    parent_community_id INTEGER,
    created_at TEXT NOT NULL,
    FOREIGN KEY (parent_community_id) REFERENCES graph_communities(id)
);

-- @index idx_community_level
-- @description Efficient lookup of communities by level
CREATE INDEX IF NOT EXISTS idx_community_level 
ON graph_communities(level);

-- ═══════════════════════════════════════════════════════════════════════════════
-- SCHEMA METADATA (Vector DB)
-- ═══════════════════════════════════════════════════════════════════════════════

-- @table schema_version
-- @description Tracks schema version for migrations
CREATE TABLE IF NOT EXISTS vector_schema_version (
    version TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL,
    description TEXT
);

-- Insert current schema version
INSERT OR IGNORE INTO vector_schema_version (version, applied_at, description)
VALUES ('1.0.0', datetime('now'), 'Initial MCard Vector Schema');
