`lib/yiddish-dictionary/schema.sql`

175 lines
-- Yiddish Dictionary Schema for Dear Bubbe
-- Supports multiple dictionary sources with deduplication
-- Includes Bubbe-safe filtering capabilities

PRAGMA foreign_keys = ON;

-- Dictionary sources (different GitHub repos, community contributions, etc.)
CREATE TABLE IF NOT EXISTS sources (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            TEXT NOT NULL,
    url             TEXT,
    license         TEXT,
    notes           TEXT,
    imported_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Main dictionary entries
CREATE TABLE IF NOT EXISTS entries (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    yiddish         TEXT NOT NULL,              -- Original Yiddish text (Hebrew script)
    yiddish_latin   TEXT,                       -- Latin transliteration
    english         TEXT NOT NULL,              -- English translation
    pos             TEXT,                       -- Part of speech (noun, verb, adj, etc.)
    gender          TEXT,                       -- Grammatical gender (m/f/n)
    plural          TEXT,                       -- Plural form
    tags            TEXT,                       -- Comma-separated tags (slang, archaic, etc.)
    usage_example   TEXT,                       -- Example sentence
    cultural_note   TEXT,                       -- Cultural context or usage notes
    source_id       INTEGER NOT NULL,
    source_entry_id TEXT,                       -- Original ID from source
    bubbe_approved  INTEGER DEFAULT 1,          -- 1=safe for Bubbe, 0=needs review, -1=banned
    notes           TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (source_id) REFERENCES sources(id),
    UNIQUE (yiddish, english, source_id) ON CONFLICT IGNORE
);

-- Indexes for fast lookups
CREATE INDEX IF NOT EXISTS idx_entries_yiddish ON entries(yiddish);
CREATE INDEX IF NOT EXISTS idx_entries_yiddish_latin ON entries(yiddish_latin);
CREATE INDEX IF NOT EXISTS idx_entries_english ON entries(english);
CREATE INDEX IF NOT EXISTS idx_entries_source ON entries(source_id);
CREATE INDEX IF NOT EXISTS idx_entries_bubbe_approved ON entries(bubbe_approved);
CREATE INDEX IF NOT EXISTS idx_entries_pos ON entries(pos);

-- Synonyms and variations
CREATE TABLE IF NOT EXISTS variations (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    entry_id        INTEGER NOT NULL,
    variation       TEXT NOT NULL,
    variation_type  TEXT,                       -- 'spelling', 'regional', 'historical'
    notes           TEXT,
    FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_variations_entry ON variations(entry_id);
CREATE INDEX IF NOT EXISTS idx_variations_text ON variations(variation);

-- Phrases and idioms (multi-word expressions)
CREATE TABLE IF NOT EXISTS phrases (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    yiddish         TEXT NOT NULL,
    yiddish_latin   TEXT,
    english         TEXT NOT NULL,
    literal_meaning TEXT,                       -- Word-for-word translation
    usage_context   TEXT,                       -- When/how to use it
    cultural_note   TEXT,
    source_id       INTEGER,
    bubbe_approved  INTEGER DEFAULT 1,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (source_id) REFERENCES sources(id)
);

CREATE INDEX IF NOT EXISTS idx_phrases_yiddish ON phrases(yiddish);
CREATE INDEX IF NOT EXISTS idx_phrases_english ON phrases(english);

-- Terms that should never be used (supplements banned-terms.json)
CREATE TABLE IF NOT EXISTS banned_terms (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    term        TEXT NOT NULL UNIQUE,
    language    TEXT NOT NULL,              -- 'yi' for Yiddish, 'en' for English
    category    TEXT,                       -- 'ethnic_slur', 'offensive', 'inappropriate'
    severity    TEXT,                       -- 'critical', 'high', 'medium'
    reason      TEXT,
    replacement TEXT,                       -- Suggested alternative
    notes       TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_banned_term ON banned_terms(term);
CREATE INDEX IF NOT EXISTS idx_banned_language ON banned_terms(language);

-- Usage statistics for learning which terms Bubbe uses most
CREATE TABLE IF NOT EXISTS usage_stats (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    entry_id    INTEGER,
    phrase_id   INTEGER,
    term        TEXT NOT NULL,
    usage_count INTEGER DEFAULT 1,
    last_used   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    context     TEXT,                       -- 'greeting', 'insult', 'advice', etc.
    FOREIGN KEY (entry_id) REFERENCES entries(id),
    FOREIGN KEY (phrase_id) REFERENCES phrases(id)
);

CREATE INDEX IF NOT EXISTS idx_usage_stats_entry ON usage_stats(entry_id);
CREATE INDEX IF NOT EXISTS idx_usage_stats_term ON usage_stats(term);

-- Curated Bubbe responses (her favorite expressions)
CREATE TABLE IF NOT EXISTS bubbe_favorites (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    yiddish         TEXT NOT NULL,
    english         TEXT NOT NULL,
    context         TEXT,                   -- 'scolding', 'complaining', 'advice'
    intensity       INTEGER DEFAULT 5,      -- 1-10 scale of harshness
    usage_example   TEXT,
    notes           TEXT
);

-- View for easy access to approved terms with all variations
CREATE VIEW IF NOT EXISTS approved_vocabulary AS
SELECT 
    e.id,
    e.yiddish,
    e.yiddish_latin,
    e.english,
    e.pos,
    e.gender,
    e.tags,
    e.usage_example,
    e.cultural_note,
    s.name as source_name,
    GROUP_CONCAT(v.variation, ', ') as variations
FROM entries e
LEFT JOIN sources s ON e.source_id = s.id
LEFT JOIN variations v ON e.id = v.entry_id
WHERE e.bubbe_approved = 1
GROUP BY e.id;

-- View for Bubbe's active vocabulary (frequently used + favorites)
CREATE VIEW IF NOT EXISTS bubbe_active_vocabulary AS
SELECT 
    'entry' as type,
    e.yiddish,
    e.yiddish_latin,
    e.english,
    e.usage_example,
    us.usage_count,
    us.last_used
FROM entries e
JOIN usage_stats us ON e.id = us.entry_id
WHERE e.bubbe_approved = 1
UNION ALL
SELECT 
    'phrase' as type,
    p.yiddish,
    p.yiddish_latin,
    p.english,
    p.usage_context as usage_example,
    us.usage_count,
    us.last_used
FROM phrases p
JOIN usage_stats us ON p.id = us.phrase_id
WHERE p.bubbe_approved = 1
UNION ALL
SELECT 
    'favorite' as type,
    bf.yiddish,
    NULL as yiddish_latin,
    bf.english,
    bf.usage_example,
    NULL as usage_count,
    NULL as last_used
FROM bubbe_favorites bf
ORDER BY usage_count DESC, last_used DESC;