id UUID PRIMARY KEY
name_en TEXT NOT NULL          -- English topic name
name_fi TEXT                   -- Finnish translation
slug TEXT UNIQUE NOT NULL      -- URL slug (English)
slug_fi TEXT                   -- Finnish URL slug
parent_id UUID                 -- Broader topic (hierarchy)
qa_status qa_status_enum       -- 'unchecked', 'ok', 'needs_review'
is_core BOOLEAN                -- Important/featured topic
is_biblical BOOLEAN            -- Biblical vs modern term
semantic_field TEXT            -- Semantic category
semantic_field_fi TEXT         -- Finnish semantic field
semantic_field_en TEXT         -- English semantic field
usage_context TEXT             -- How term is used
usage_context_fi TEXT          -- Finnish context
usage_context_en TEXT          -- English context
nuance_fi TEXT                 -- Finnish nuance
nuance_en TEXT                 -- English nuance

source_topic_id UUID           -- Source topic
target_topic_id UUID           -- Target topic
relation_type TEXT             -- 'related', 'opposite', 'synonym'
is_bidirectional BOOLEAN       -- TRUE for opposite/synonym

topic_id UUID
alias TEXT                     -- Display text
alias_norm TEXT                -- Normalized (lowercase, no diacritics)
lang TEXT                      -- 'fi' or 'en'
alias_type TEXT                -- 'synonym', 'abbrev', 'variant', 'old_term', 'misspelling'
qa_status qa_status_enum       -- 'unchecked', 'ok', 'needs_review'
source TEXT                    -- 'manual', 'ai', 'import'

topic_id UUID
osis_start TEXT                -- Start verse (e.g., 'Gen.1.1')
osis_end TEXT                  -- End verse (for ranges)
relevance_score INTEGER        -- 1-5 relevance

-- Topics with unchecked qa_status
SELECT id, name_en, name_fi, qa_status
FROM bible_schema.topical_topics
WHERE qa_status = 'unchecked'
ORDER BY is_core DESC, name_en
LIMIT 50;

-- Topics without Finnish translation
SELECT id, name_en, slug
FROM bible_schema.topical_topics
WHERE name_fi IS NULL
ORDER BY is_core DESC, name_en;

UPDATE bible_schema.topical_topics
SET
  name_fi = 'suomenkielinen nimi',
  slug_fi = 'suomenkielinen-slug',
  qa_status = 'ok'
WHERE id = 'uuid-here';

-- Related topic (one-way)
INSERT INTO bible_schema.topical_relations
  (source_topic_id, target_topic_id, relation_type, is_bidirectional)
VALUES ('source-uuid', 'target-uuid', 'related', false);

-- Opposite (bidirectional)
INSERT INTO bible_schema.topical_relations
  (source_topic_id, target_topic_id, relation_type, is_bidirectional)
VALUES ('source-uuid', 'target-uuid', 'opposite', true);

-- Set parent (broader term)
UPDATE bible_schema.topical_topics
SET parent_id = (SELECT id FROM bible_schema.topical_topics WHERE slug = 'parent-slug')
WHERE slug = 'child-slug';

INSERT INTO bible_schema.topical_aliases
  (topic_id, alias, alias_norm, lang, alias_type, source, qa_status)
VALUES (
  'topic-uuid',
  'Vaihtoehtoinen nimi',
  'vaihtoehtoinen nimi',
  'fi',
  'synonym',
  'manual',
  'ok'
);

# pip install uralicnlp
from uralicnlp import uralicApi

# Check if word is valid Finnish
def is_valid_finnish(word):
    analyses = uralicApi.analyze(word, "fin")
    return len(analyses) > 0

# Get lemma (base form)
def get_lemma(word):
    analyses = uralicApi.analyze(word, "fin")
    if analyses:
        return analyses[0][0]  # First analysis, lemma
    return word

# pip install libvoikko
import libvoikko

voikko = libvoikko.Voikko("fi")

def is_valid_finnish(word):
    return voikko.spell(word)

def analyze_word(word):
    return voikko.analyze(word)

def get_suggestions(word):
    if not voikko.spell(word):
        return voikko.suggest(word)
    return []

// Use Edge Function to call external API
async function validateFinnish(word: string) {
  const response = await fetch(
    `https://api.kielitoimistonsanakirja.fi/api/search?word=${encodeURIComponent(word)}`
  );
  return response.ok;
}

-- Export untranslated topics to CSV format
SELECT
  slug,
  name_en,
  COALESCE(name_fi, '') as name_fi,
  COALESCE(slug_fi, '') as slug_fi,
  is_core
FROM bible_schema.topical_topics
WHERE name_fi IS NULL OR qa_status = 'unchecked'
ORDER BY is_core DESC, name_en;

-- Create temp table
CREATE TEMP TABLE topic_import (
  slug TEXT,
  name_fi TEXT,
  slug_fi TEXT
);

-- Import (via \copy or Supabase)
-- Update topics
UPDATE bible_schema.topical_topics t
SET
  name_fi = i.name_fi,
  slug_fi = i.slug_fi,
  qa_status = 'ok'
FROM topic_import i
WHERE t.slug = i.slug;

-- Mark single topic
UPDATE bible_schema.topical_topics
SET qa_status = 'ok'
WHERE id = 'uuid';

-- Batch mark by criteria
UPDATE bible_schema.topical_topics
SET qa_status = 'ok'
WHERE qa_status = 'unchecked'
AND name_fi IS NOT NULL
AND LENGTH(name_fi) > 2;