import sqlite3
import os

DATA_DIR = "./data"
DB_PATH = os.path.join(DATA_DIR, "sentences.db")


# 建立資料表（若不存在）
def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
    CREATE TABLE IF NOT EXISTS sentences (
        word TEXT PRIMARY KEY,
        phonetic TEXT,
        sentence TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    conn.commit()
    conn.close()


# 查句庫，傳回 (word, phonetic, sentence) 或 None
def get_sentence(word):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('SELECT word, phonetic, sentence FROM sentences WHERE word=?', (word,))
    result = c.fetchone()
    conn.close()
    return result


# 保存句子到 SQLite
def save_sentence(word, phonetic, sentence):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
    INSERT INTO sentences (word, phonetic, sentence)
    VALUES (?, ?, ?)
    ON CONFLICT(word) DO UPDATE SET sentence=excluded.sentence, phonetic=excluded.phonetic
    ''', (word, phonetic, sentence))
    conn.commit()
    conn.close()


# 初始化資料表
init_db()