-- Enable foreign key support
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA cache_size = -2000;
PRAGMA mmap_size = 30000000000;

-- a semantic entity
CREATE TABLE IF NOT EXISTS senses(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id INTEGER NOT NULL,
    spelling TEXT NOT NULL,
    pos TEXT,
    etymology TEXT,
    senses JSONB,
    forms JSONB,
    related JSONB,
    confidence INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (parent_id) REFERENCES expressions(id)
);
CREATE INDEX IF NOT EXISTS idx_words_pos ON senses(pos);
CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);

CREATE TABLE IF NOT EXISTS subsenses(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sid INTEGER NOT NULL
    gloss TEXT NOT NULL,
    examples JSONB,    
    FOREIGN KEY (sid) REFERENCES senses(id)
);

CREATE TABLE IF NOT EXISTS derivation(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sid INTEGER NOT NULL
    type TEXT NOT NULL,
    text TEXT NOT NULL,
    tags JSONB,    
    FOREIGN KEY (sid) REFERENCES senses(id)
);

-- Categories table (for noun and verb categories)
CREATE TABLE IF NOT EXISTS categories (
    name TEXT PRIMARY KEY
);

-- Word Categories junction table
CREATE TABLE IF NOT EXISTS word_categories (
    word_id INTEGER NOT NULL,
    category INTEGER NOT NULL,
    PRIMARY KEY (word_id, category),
    FOREIGN KEY (word_id) REFERENCES expressions(id),
    FOREIGN KEY (category) REFERENCES categories(name)
);
CREATE INDEX IF NOT EXISTS idx_word_categories_category_id ON word_categories(category);