1 files changed, 96 insertions, 2 deletions
diff --git a/src/lib/db/prosodyschema.sql b/src/lib/db/prosodyschema.sql
index c6a04fa..5554a02 100644
--- a/src/lib/db/prosodyschema.sql
+++ b/src/lib/db/prosodyschema.sql
@@ -150,9 +150,103 @@ CREATE TABLE IF NOT EXISTS word_phonetics(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     word_id INTEGER NOT NULL,
     ipa TEXT NOT NULL,
-    syllables INTEGER NOT NULL,
+    syllable_count INTEGER NOT NULL,
+    syllable_sequence TEXT NOT NULL,  -- "家,鄉"
+    tone_sequence TEXT NOT NULL,      -- "rising,rising" 
+    ipa_sequence TEXT NOT NULL,       -- IPA representation
     tag TEXT,
     notes TEXT,
-    CONSTRAINT ipa_unique UNIQUE (ipa, word_id)
+    FOREIGN KEY (word_id) REFERENCES words(id)
 );
 CREATE INDEX IF NOT EXISTS idx_words_ipa ON word_phonetics(ipa, word_id);
+
+-- -- Query 2: Even simpler with pattern table
+-- -- Pattern [{ change: "rising" }, { change: "falling" }] - any 2-syllable word with rising,falling tones
+-- SELECT 
+--     w.spelling,
+--     w.frequency,
+--     wp.syllable_sequence,
+--     wp.tone_sequence
+-- FROM words w
+-- JOIN word_patterns wp ON w.id = wp.word_id
+-- WHERE wp.syllable_count = 2
+--   AND wp.tone_sequence = 'rising,falling'
+-- ORDER BY w.frequency DESC NULLS LAST;
+
+-- -- Query 3: Mixed pattern [{ keep: "家" }, { change: "falling" }, { keep: "人" }]
+-- SELECT DISTINCT
+--     w.spelling,
+--     w.frequency,
+--     wp.syllable_sequence,
+--     wp.tone_sequence
+-- FROM words w
+-- JOIN word_patterns wp ON w.id = wp.word_id
+-- WHERE wp.syllable_count = 3
+--   AND wp.syllable_sequence LIKE '家,%,人'  -- Simple pattern matching
+--   AND EXISTS (
+--       SELECT 1 FROM word_syllable_positions wsp
+--       WHERE wsp.word_id = w.id 
+--         AND wsp.position = 1 
+--         AND wsp.tone_name = 'falling'
+--   )
+-- ORDER BY w.frequency DESC NULLS LAST;
+
+-- -- Query 4: Super fast rhyme finding
+-- -- Find all words that end with same syllable as "家鄉" (end with "鄉")
+-- SELECT 
+--     w.spelling,
+--     w.frequency,
+--     wp.syllable_sequence
+-- FROM words w
+-- JOIN word_patterns wp ON w.id = wp.word_id
+-- WHERE wp.syllable_sequence LIKE '%,鄉'  -- Ends with 鄉
+--   AND wp.syllable_count >= 2
+-- ORDER BY w.frequency DESC NULLS LAST;
+
+
+
+
+-- SELECT
+--     w.id as word_id,
+--     w.spelling,
+--     w.lang,
+--     w.frequency
+-- FROM words w
+-- JOIN word_phonetics wp ON wp.word_id= w.id
+-- WHERE wp.syllable_sequence LIKE '%,ใจ'
+-- AND wp.tone_sequence LIKE 'rising,%'
+-- AND wp.syllable_count = 2
+-- GROUP BY w.id, w.spelling, w.lang, w.frequency
+-- ORDER BY w.frequency DESC NULLS LAST;
+-- 
+-- Indexes for fast pattern matching
+CREATE INDEX IF NOT EXISTS idx_word_patterns_syllables ON word_phonetics(syllable_sequence);
+CREATE INDEX IF NOT EXISTS idx_word_patterns_tones ON word_phonetics(tone_sequence);
+CREATE INDEX IF NOT EXISTS idx_word_patterns_count ON word_phonetics(syllable_count);
+CREATE INDEX IF NOT EXISTS idx_word_patterns_mixed ON word_phonetics(syllable_count, syllable_sequence, tone_sequence);
+
+
+CREATE INDEX IF NOT EXISTS idx_syllables_words_word_idx ON syllables_words(word_id, idx);
+CREATE INDEX IF NOT EXISTS idx_syllables_words_idx_word ON syllables_words(idx, word_id);
+CREATE INDEX IF NOT EXISTS idx_syllables_words_syl ON syllables_words(syl_id);
+
+-- 2. Syllables table indexes for text and language lookups
+CREATE INDEX IF NOT EXISTS idx_syllables_text_lang ON syllables(text, lang);
+CREATE INDEX IF NOT EXISTS idx_syllables_lang_text ON syllables(lang, text);
+CREATE INDEX IF NOT EXISTS idx_syllables_tone ON syllables(tone);
+CREATE INDEX IF NOT EXISTS idx_syllables_text_tone ON syllables(text, tone);
+
+-- 3. Tones table indexes
+CREATE INDEX IF NOT EXISTS idx_tones_name_lang ON tones(name, lang);
+CREATE INDEX IF NOT EXISTS idx_tones_nums_lang ON tones(nums, lang);
+CREATE INDEX IF NOT EXISTS idx_tones_lang_name ON tones(lang, name);
+
+-- 4. Words table indexes
+CREATE INDEX IF NOT EXISTS idx_words_lang_freq ON words(lang, frequency DESC);
+CREATE INDEX IF NOT EXISTS idx_words_id_lang ON words(id, lang);
+
+-- 5. Composite indexes for common query patterns
+CREATE INDEX IF NOT EXISTS idx_syllables_compound ON syllables(lang, text, tone);
+CREATE INDEX IF NOT EXISTS idx_syllables_words_compound ON syllables_words(word_id, idx, syl_id);
+
+