


-- ═══════════════════════════════════════════════════════════════════════════════
-- LAYER 2: HANDLE SYSTEM (Appetition / Sum)
-- ═══════════════════════════════════════════════════════════════════════════════
-- The mutable pointer that "desires" to reference new content.
-- "Appetition: the tendency to move to new states"

-- @table handle_registry (Monadic Role: Handle)
-- @description Maps human-readable handles to current content hashes
-- @column handle       - UTF-8 handle name (primary key)
-- @column current_hash - FK to card.hash of current version
-- @column created_at   - When handle was first created
-- @column updated_at   - When handle was last updated
CREATE TABLE IF NOT EXISTS handle_registry (
    handle TEXT PRIMARY KEY,
    current_hash TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (current_hash) REFERENCES card(hash)
);

-- @index idx_handle_current_hash
-- @description Efficient reverse lookup from hash to handles
CREATE INDEX IF NOT EXISTS idx_handle_current_hash 
ON handle_registry(current_hash);

-- @table handle_history (Monadic Role: Version / Product)
-- @description Audit trail for handle pointer changes (Pre-Established Harmony)
-- @column id            - Auto-increment primary key
-- @column handle        - The handle that was updated
-- @column previous_hash - Hash it pointed to before update
-- @column changed_at    - When the change occurred
CREATE TABLE IF NOT EXISTS handle_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    handle TEXT NOT NULL,
    previous_hash TEXT NOT NULL,
    changed_at TEXT NOT NULL,
    FOREIGN KEY (handle) REFERENCES handle_registry(handle),
    FOREIGN KEY (previous_hash) REFERENCES card(hash)
);

-- @index idx_handle_history_handle
-- @description Efficient lookup of history by handle
CREATE INDEX IF NOT EXISTS idx_handle_history_handle 
ON handle_history(handle);


-- ═══════════════════════════════════════════════════════════════════════════════
-- LEGACY SUPPORT: FTS5 Documents Table
-- ═══════════════════════════════════════════════════════════════════════════════
-- KEPT FOR BACKWARD COMPATIBILITY with existing codebase.
-- New implementations should use mcard_vector_schema.sql -> mcard_fts

-- @virtual_table documents
-- @description Legacy FTS table for backward compatibility
-- @note Synced with card table via triggers
CREATE VIRTUAL TABLE IF NOT EXISTS documents USING fts5(content);


-- ═══════════════════════════════════════════════════════════════════════════════
-- SCHEMA METADATA
-- ═══════════════════════════════════════════════════════════════════════════════

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

-- Insert current schema version
INSERT OR IGNORE INTO schema_version (version, applied_at, description)
VALUES ('3.0.0', datetime('now'), 'Monadic Core Schema (split vectors to mcard_vector_schema.sql)');
