import sqlite3
import os
from vocab import get_word_info
from ai_sentence import generate_sentence
from tqdm import tqdm

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,
        phonetic TEXT,
        sentence TEXT,
        source TEXT,
        model TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (word, source, model)
    )
    ''')
    conn.commit()
    conn.close()


def get_sentences_by_word(word):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('SELECT word, phonetic, sentence, source, model FROM sentences WHERE word=?', (word,))
    results = c.fetchall()
    conn.close()
    return results

def get_sentence(word):
    result = get_sentences_by_word(word)
    if result:
        return result[0]  # 取第一筆資料
    return None


def save_sentence(word, phonetic, sentence, source, model):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''
    INSERT INTO sentences (word, phonetic, sentence, source, model)
    VALUES (?, ?, ?, ?, ?)
    ON CONFLICT(word, source, model) DO UPDATE SET sentence=excluded.sentence, phonetic=excluded.phonetic
    ''', (word, phonetic, sentence, source, model))
    conn.commit()
    conn.close()


def generate_sentences(words, source, use_ai, model_name):
    result_display = ""
    current_status = ""

    for i, word in enumerate(words):
        current_status = f"⏳ 處理中：{i + 1}/{len(words)} - [{word}] 正在查詢句子..."

        word_info = get_word_info(source, word)
        phonetic = word_info['phonetic'] if word_info else "無"

        sentence_records = get_sentences_by_word(word)

        if use_ai or not sentence_records:
            try:
                current_status = f"🤖 使用 AI 生成 [{word}] 的句子中... (模型：{model_name})"
                sentence = generate_sentence(word, model_name)
                save_sentence(word, phonetic, sentence, 'ai', model_name)
                source_used = 'ai'
                model_used = model_name
            except Exception as e:
                sentence = f"[AI生成失敗：{e}]"
                source_used = "error"
                model_used = None
        else:
            sentence = sentence_records[0][2]
            source_used = sentence_records[0][3]
            model_used = sentence_records[0][4]

        result_display += f"""
            <div style="margin-bottom: 10px; padding: 8px; border-left: 4px solid #4CAF50; background-color: transparent;">
                <strong>單字：</strong> {word} <br>
                <strong>音標：</strong> {phonetic} <br>
                <strong>句子：</strong> {sentence} <br>
                <strong>來源：</strong> {source_used} {f"({model_used})" if model_used else ""}
            </div>
        """

       

    current_status = "✅ 所有單字處理完成！"

    return result_display, current_status