Code_files/algorithm_registry_schema.sql

-- ============================================================
-- ALGORITHM_REGISTRY — The Multi-Root Algorithm Layer
-- ============================================================
-- Level 2.5 in the computational fabric.
-- An ALGORITHM is a named root-set that fires together across
-- one or more ayat to execute a single operational pattern.
--
-- Q28:4 is ONE algorithm (Pharaoh extraction).
-- Q63:1-11 is ONE algorithm (munafiq deception).
-- Q12:57-66 is ONE algorithm (Yusuf kayl distribution).
-- The Radhanite operation runs MULTIPLE algorithms simultaneously.
--
-- This replaces the root-at-a-time fabric scan with a
-- multi-root fabric scan keyed on named algorithms.
-- ============================================================

-- ============================================================
-- 1. algorithm_registry — master metadata
-- ============================================================
CREATE TABLE IF NOT EXISTS algorithm_registry (
    algo_id           TEXT PRIMARY KEY,       -- ALG-PHARAOH-EXTRACTION
    algo_name         TEXT NOT NULL,          -- human-readable name
    algo_class        TEXT NOT NULL,          -- OPERATOR | NARRATIVE | BINARY_PAIR | REFRAIN | DESCRIPTION | ECONOMIC | ESCHATOLOGICAL
    description       TEXT,                   -- structural description (no etymologies)
    primary_ayah      TEXT,                   -- canonical source (Q28:4)
    root_count        INTEGER DEFAULT 0,      -- auto-populated from algorithm_root_map
    ayah_count        INTEGER DEFAULT 0,      -- auto-populated from algorithm_ayah_map
    surah_count       INTEGER DEFAULT 0,      -- auto-populated from algorithm_ayah_map
    cross_refs        TEXT,                   -- JSON array of related algo_ids and table refs
    status            TEXT DEFAULT 'DRAFT',   -- DRAFT | VERIFIED | SEALED
    created_date      TEXT DEFAULT (datetime('now')),
    notes             TEXT,
    quf_q             TEXT,
    quf_u             TEXT,
    quf_f             TEXT,
    quf_pass          TEXT,
    quf_date          TEXT,
    quf_token         TEXT,
    CHECK (algo_id LIKE 'ALG-%'),
    CHECK (algo_class IN ('OPERATOR','NARRATIVE','BINARY_PAIR','REFRAIN','DESCRIPTION','ECONOMIC','ESCHATOLOGICAL','MORPHOLOGICAL'))
);

CREATE INDEX IF NOT EXISTS idx_algo_class ON algorithm_registry(algo_class);
CREATE INDEX IF NOT EXISTS idx_algo_status ON algorithm_registry(status);

-- ============================================================
-- 2. algorithm_root_map — N:M link (algo ↔ roots)
-- ============================================================
CREATE TABLE IF NOT EXISTS algorithm_root_map (
    map_id            INTEGER PRIMARY KEY AUTOINCREMENT,
    algo_id           TEXT NOT NULL,
    root_letters      TEXT NOT NULL,          -- must exist in roots table
    role              TEXT DEFAULT 'PRIMARY', -- PRIMARY | SUPPORT | BINARY_A | BINARY_B | INVERSION
    token_count       INTEGER,                -- auto-filled from quran_word_roots at insert time
    notes             TEXT,
    created_date      TEXT DEFAULT (datetime('now')),
    UNIQUE (algo_id, root_letters),
    FOREIGN KEY (algo_id) REFERENCES algorithm_registry(algo_id) ON DELETE CASCADE,
    CHECK (role IN ('PRIMARY','SUPPORT','BINARY_A','BINARY_B','INVERSION','OBJECT','INSTRUMENT'))
);

CREATE INDEX IF NOT EXISTS idx_arm_algo ON algorithm_root_map(algo_id);
CREATE INDEX IF NOT EXISTS idx_arm_root ON algorithm_root_map(root_letters);

-- ============================================================
-- 3. algorithm_ayah_map — N:M link (algo ↔ ayat)
-- ============================================================
CREATE TABLE IF NOT EXISTS algorithm_ayah_map (
    map_id            INTEGER PRIMARY KEY AUTOINCREMENT,
    algo_id           TEXT NOT NULL,
    surah             INTEGER NOT NULL,
    ayah_start        INTEGER NOT NULL,
    ayah_end          INTEGER,                -- NULL = single ayah
    is_primary        INTEGER DEFAULT 0,      -- 1 = canonical source
    instance_note     TEXT,                   -- e.g. "Pharaoh instance" vs "Rome instance"
    created_date      TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (algo_id) REFERENCES algorithm_registry(algo_id) ON DELETE CASCADE,
    CHECK (surah BETWEEN 1 AND 114),
    CHECK (ayah_start > 0),
    CHECK (ayah_end IS NULL OR ayah_end >= ayah_start)
);

CREATE INDEX IF NOT EXISTS idx_aam_algo ON algorithm_ayah_map(algo_id);
CREATE INDEX IF NOT EXISTS idx_aam_surah ON algorithm_ayah_map(surah);
CREATE INDEX IF NOT EXISTS idx_aam_primary ON algorithm_ayah_map(is_primary) WHERE is_primary = 1;

-- ============================================================
-- TRIGGERS — auto-populate counts + contamination shield
-- ============================================================

-- Update root_count on algorithm_registry when algorithm_root_map changes
CREATE TRIGGER IF NOT EXISTS trg_arm_insert_count
AFTER INSERT ON algorithm_root_map
BEGIN
    UPDATE algorithm_registry
    SET root_count = (SELECT COUNT(*) FROM algorithm_root_map WHERE algo_id = NEW.algo_id)
    WHERE algo_id = NEW.algo_id;
END;

CREATE TRIGGER IF NOT EXISTS trg_arm_delete_count
AFTER DELETE ON algorithm_root_map
BEGIN
    UPDATE algorithm_registry
    SET root_count = (SELECT COUNT(*) FROM algorithm_root_map WHERE algo_id = OLD.algo_id)
    WHERE algo_id = OLD.algo_id;
END;

-- Update ayah_count + surah_count on algorithm_registry when algorithm_ayah_map changes
CREATE TRIGGER IF NOT EXISTS trg_aam_insert_count
AFTER INSERT ON algorithm_ayah_map
BEGIN
    UPDATE algorithm_registry
    SET
        ayah_count = (
            SELECT COALESCE(SUM(COALESCE(ayah_end, ayah_start) - ayah_start + 1), 0)
            FROM algorithm_ayah_map WHERE algo_id = NEW.algo_id
        ),
        surah_count = (
            SELECT COUNT(DISTINCT surah) FROM algorithm_ayah_map WHERE algo_id = NEW.algo_id
        )
    WHERE algo_id = NEW.algo_id;
END;

CREATE TRIGGER IF NOT EXISTS trg_aam_delete_count
AFTER DELETE ON algorithm_ayah_map
BEGIN
    UPDATE algorithm_registry
    SET
        ayah_count = (
            SELECT COALESCE(SUM(COALESCE(ayah_end, ayah_start) - ayah_start + 1), 0)
            FROM algorithm_ayah_map WHERE algo_id = OLD.algo_id
        ),
        surah_count = (
            SELECT COUNT(DISTINCT surah) FROM algorithm_ayah_map WHERE algo_id = OLD.algo_id
        )
    WHERE algo_id = OLD.algo_id;
END;

-- Auto-fill token_count on algorithm_root_map insert (from quran_word_roots)
CREATE TRIGGER IF NOT EXISTS trg_arm_fill_tokens
AFTER INSERT ON algorithm_root_map
FOR EACH ROW
WHEN NEW.token_count IS NULL
BEGIN
    UPDATE algorithm_root_map
    SET token_count = (SELECT COUNT(*) FROM quran_word_roots WHERE root = NEW.root_letters)
    WHERE map_id = NEW.map_id;
END;

-- Contamination shield — block banned terms in description/notes
CREATE TRIGGER IF NOT EXISTS trg_algo_contamination_desc
BEFORE INSERT ON algorithm_registry
FOR EACH ROW
WHEN NEW.description IS NOT NULL AND (
    LOWER(NEW.description) LIKE '%borrowed from%' OR
    LOWER(NEW.description) LIKE '%loan from%' OR
    LOWER(NEW.description) LIKE '%loanword%' OR
    LOWER(NEW.description) LIKE '%cognate with%' OR
    LOWER(NEW.description) LIKE '%semitic%' OR
    LOWER(NEW.description) LIKE '%proto-indo-european%' OR
    LOWER(NEW.description) LIKE '%from greek%' OR
    LOWER(NEW.description) LIKE '%from latin%' OR
    LOWER(NEW.description) LIKE '%from sanskrit%' OR
    LOWER(NEW.description) LIKE '%from persian%' OR
    LOWER(NEW.description) LIKE '%farsi%'
)
BEGIN
    SELECT RAISE(ABORT, 'CONTAMINATION: banned term in algorithm description');
END;

CREATE TRIGGER IF NOT EXISTS trg_algo_contamination_notes
BEFORE INSERT ON algorithm_registry
FOR EACH ROW
WHEN NEW.notes IS NOT NULL AND (
    LOWER(NEW.notes) LIKE '%borrowed from%' OR
    LOWER(NEW.notes) LIKE '%loan from%' OR
    LOWER(NEW.notes) LIKE '%loanword%' OR
    LOWER(NEW.notes) LIKE '%cognate with%' OR
    LOWER(NEW.notes) LIKE '%semitic%' OR
    LOWER(NEW.notes) LIKE '%proto-indo-european%' OR
    LOWER(NEW.notes) LIKE '%from greek%' OR
    LOWER(NEW.notes) LIKE '%from latin%' OR
    LOWER(NEW.notes) LIKE '%from sanskrit%' OR
    LOWER(NEW.notes) LIKE '%from persian%' OR
    LOWER(NEW.notes) LIKE '%farsi%'
)
BEGIN
    SELECT RAISE(ABORT, 'CONTAMINATION: banned term in algorithm notes');
END;