-- ═══════════════════════════════════════════════════════════════════════════════
-- MCard Unified Database Schema (Monadic Core)
-- ═══════════════════════════════════════════════════════════════════════════════
-- 
-- STABILITY WARNING: This schema is the invariant "seeding meta-language" for all
-- Domain Specific Languages (DSLs). It must remain as stable as possible.
--
-- THE UNIFICATION THESIS:
-- This design unites three distinct namespaces into a single relational network
-- by leveraging the power of cryptographic hash functions:
-- 1. CONTENT SPACE (Intrinsic Identity): Content-Addressable Storage (CAS).
-- 2. HANDLE SPACE (Reserved Words): Mapping human logic to content hashes.
-- 3. VERSION SPACE (Temporal Evolution): Tracking state transitions via hash history.
--
-- TURING MACHINE ANALOGY (The Infinitely Long Tape):
-- The `card` table stores content blobs with unique identities, emulating the 
-- "Infinitely Long Tape" of the Turing Machine formalism. By using relational 
-- queries, this tape can be dynamically constructed and traversed for different 
-- DSLs, providing a flexible substrate for practically all computable languages.
--
-- By using hash values as the universal primitives across content, handles, and 
-- time, we create a "Wordless Book" — a minimal substrate capable of seeding any 
-- domain-specific knowledge system without requiring schema changes.
--
-- Monadic Mapping (See: Monadic Justification for Schema Design.md):
-- 1. Card    = Monad (Perception/State/Exponent) - Intrinsic Identity
-- 2. Handle  = Registry (Appetition/Reader/Sum) - Mutable Reference
-- 3. Version = History (Harmony/Writer/Product) - Coordinated Evolution
--
-- Version: 3.0.2 (Turing Tape Refinement)
-- Last Updated: 2025-12-20
-- ═══════════════════════════════════════════════════════════════════════════════


-- ═══════════════════════════════════════════════════════════════════════════════
-- LAYER 1: CORE CONTENT-ADDRESSABLE STORAGE (The Monad / Exponent)
-- ═══════════════════════════════════════════════════════════════════════════════
-- The fundamental MCard storage: content-addressed by cryptographic hash.
-- "Each Monad ... mirrors the universe"

-- @table card
-- @description Core content-addressable storage table (Monad/State)
-- @column hash    - SHA-256 hash of content (primary key)
-- @column content - The actual content (BLOB for binary safety)
-- @column g_time  - Generation timestamp (ISO 8601)
CREATE TABLE IF NOT EXISTS card (
    hash TEXT PRIMARY KEY,
    content BLOB NOT NULL,
    g_time TEXT NOT NULL
);


-- ═══════════════════════════════════════════════════════════════════════════════
-- 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)');
