import gradio as gr
import sqlite3
from datetime import datetime
from deep_translator import GoogleTranslator
from gtts import gTTS
import pykakasi
import os
import pandas as pd
from IPython.display import Audio

# 데이터베이스 초기화
def init_db():
    db_path = 'search_history.db'
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS searches
        (id INTEGER PRIMARY KEY AUTOINCREMENT,
        date TEXT,
        keyword TEXT,
        translation TEXT,
        link TEXT)
    ''')
    conn.commit()
    conn.close()

def save_search_history(keyword, translation, link):
    db_path = 'search_history.db'
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    current_date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute('INSERT INTO searches (date, keyword, translation, link) VALUES (?, ?, ?, ?)',
                   (current_date, keyword, translation, link))
    conn.commit()
    conn.close()

def load_search_history(start_date, end_date):
    db_path = 'search_history.db'
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute('SELECT date, keyword, translation, link FROM searches WHERE date BETWEEN ? AND ? ORDER BY date DESC', (start_date, end_date))
    history = cursor.fetchall()
    conn.close()
    return history

def export_history_to_excel(start_date, end_date, file_path):
    history = load_search_history(start_date, end_date)
    df = pd.DataFrame(history, columns=['Date', 'Keyword', 'Translation', 'Link'])
    df.to_excel(file_path, index=False)

# Kakasi 설정
kakasi = pykakasi.kakasi()

# 검색 함수
def search(keyword):
    try:
        translator = GoogleTranslator(source='ko', target='ja')
        japanese_keyword = translator.translate(keyword)
        url = f"https://www.irasutoya.com/search?q={japanese_keyword}"
        romaji = ''.join([item['hepburn'] for item in kakasi.convert(japanese_keyword)])
        result_text = f"**검색어:** {keyword}\n**일본어 번역:** {japanese_keyword} ({romaji})\n[검색 결과 보기]({url})"
        save_search_history(keyword, japanese_keyword, url)
        return result_text, japanese_keyword
    except Exception as e:
        return f"번역 중 오류가 발생했습니다: {e}", None

# 일본어 음성 재생
def play_japanese_audio(japanese_text):
    try:
        tts = gTTS(japanese_text, lang='ja')
        tts.save("japanese_audio.mp3")
        return Audio("japanese_audio.mp3", autoplay=True)
    except Exception as e:
        return f"음성 재생 중 오류가 발생했습니다: {e}"

# Gradio 인터페이스 설정
with gr.Blocks() as demo:
    gr.Markdown("## Irasutoya 한글 검색기")

    with gr.Row():
        keyword_input = gr.Textbox(label="한글 검색어 입력")
        search_button = gr.Button("검색")
        clear_button = gr.Button("입력 지우기")
    
    result_output = gr.Markdown()
    play_button = gr.Button("일본어 음성 재생", visible=False)
    state = gr.State()

    def on_search(keyword):
        result, japanese_keyword = search(keyword)
        if japanese_keyword:
            return result, gr.update(visible=True), japanese_keyword
        else:
            return result, gr.update(visible=False), None
    
    def clear_input():
        return "", "", gr.update(visible=False)

    search_button.click(on_search, inputs=keyword_input, outputs=[result_output, play_button, state])
    keyword_input.submit(on_search, inputs=keyword_input, outputs=[result_output, play_button, state])  # 엔터키로 검색 가능하게 설정
    play_button.click(play_japanese_audio, inputs=state, outputs=None)  # 음성 재생만 실행하고, 결과는 유지
    clear_button.click(clear_input, outputs=[keyword_input, result_output, play_button])

    # 검색 기록을 조회하고 저장하는 인터페이스 추가
    with gr.Row():
        start_date = gr.Textbox(label="시작 날짜 (YYYY-MM-DD)", placeholder="예: 2023-01-01")
        end_date = gr.Textbox(label="종료 날짜 (YYYY-MM-DD)", placeholder="예: 2023-12-31")
        history_button = gr.Button("검색 기록 조회")
        export_button = gr.Button("기록 저장")

    history_output = gr.Markdown()

    def show_history(start_date, end_date):
        history = load_search_history(start_date, end_date)
        if history:
            formatted_history = "\n".join([f"{row[0]} - {row[1]}: {row[2]} [링크]({row[3]})" for row in history])
            return formatted_history
        else:
            return "검색 기록이 없습니다."

    def export_history(start_date, end_date):
        file_path = f"search_history_{start_date}_to_{end_date}.xlsx"
        export_history_to_excel(start_date, end_date, file_path)
        return f"검색 기록이 {file_path}에 저장되었습니다."

    history_button.click(show_history, inputs=[start_date, end_date], outputs=history_output)
    export_button.click(export_history, inputs=[start_date, end_date], outputs=history_output)

# 데이터베이스 초기화
init_db()

# 앱 실행
demo.launch(share=True)