import sqlite3
import yaml
import pandas as pd
import gradio as gr
import os

DB_FILE = "mydb.sqlite"
YAML_FILE = "data.yaml"

# 🔹 DB init
def init_db():
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name_en TEXT,
        name_bn TEXT,
        description_en TEXT,
        description_bn TEXT,
        photo_path TEXT,
        voice_path TEXT
    )
    """)
    conn.commit()
    conn.close()

init_db()

# 🔹 Load YAML into DB
def load_yaml_to_db():
    if not os.path.exists(YAML_FILE):
        return "❌ data.yaml not found!"
    with open(YAML_FILE, 'r', encoding='utf-8') as f:
        data = yaml.safe_load(f)
    
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    
    for item in data:
        cursor.execute("""
        INSERT INTO items (name_en, name_bn, description_en, description_bn, photo_path, voice_path)
        VALUES (?, ?, ?, ?, ?, ?)
        """, (
            item['name_en'], item['name_bn'],
            item['description_en'], item['description_bn'],
            item['photo'], item['voice']
        ))
    
    conn.commit()
    conn.close()
    return f"✅ YAML loaded: {len(data)} items"

# 🔹 Add Item
def add_item(name_en, name_bn, desc_en, desc_bn, photo, voice):
    if not all([name_en, name_bn, desc_en, desc_bn]):
        return "❌ All text fields are required"
    
    # Save media files
    photo_path, voice_path = None, None
    if photo:
        photo_path = f"media/photo/{os.path.basename(photo.name)}"
        photo.save(photo_path)
    if voice:
        voice_path = f"media/voice/{os.path.basename(voice.name)}"
        voice.save(voice_path)
    
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute("""
    INSERT INTO items (name_en, name_bn, description_en, description_bn, photo_path, voice_path)
    VALUES (?, ?, ?, ?, ?, ?)
    """, (name_en, name_bn, desc_en, desc_bn, photo_path, voice_path))
    conn.commit()
    conn.close()
    return f"✅ Item '{name_en}' added!"

# 🔹 View All Items
def view_items():
    conn = sqlite3.connect(DB_FILE)
    df = pd.read_sql_query("SELECT * FROM items", conn)
    conn.close()
    return df

# 🔹 Search Items
def search_items(keyword):
    conn = sqlite3.connect(DB_FILE)
    query = """
    SELECT * FROM items 
    WHERE name_en LIKE ? OR name_bn LIKE ? OR description_en LIKE ? OR description_bn LIKE ?
    """
    df = pd.read_sql_query(query, conn, params=(f"%{keyword}%", f"%{keyword}%", f"%{keyword}%", f"%{keyword}%"))
    conn.close()
    return df

# 🔹 Get Item by ID
def get_item(item_id):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM items WHERE id=?", (item_id,))
    row = cursor.fetchone()
    conn.close()
    if row:
        return [row[1], row[2], row[3], row[4], row[5], row[6]]
    else:
        return ["","","","","",None,None]

# 🔹 Update Item
def update_item(item_id, name_en, name_bn, desc_en, desc_bn, photo, voice):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    
    # Existing media paths
    cursor.execute("SELECT photo_path, voice_path FROM items WHERE id=?", (item_id,))
    row = cursor.fetchone()
    if not row:
        return "❌ Item not found"
    photo_path, voice_path = row
    
    # Update media if new uploaded
    if photo:
        photo_path = f"media/photo/{os.path.basename(photo.name)}"
        photo.save(photo_path)
    if voice:
        voice_path = f"media/voice/{os.path.basename(voice.name)}"
        voice.save(voice_path)
    
    cursor.execute("""
    UPDATE items SET name_en=?, name_bn=?, description_en=?, description_bn=?, photo_path=?, voice_path=?
    WHERE id=?
    """, (name_en, name_bn, desc_en, desc_bn, photo_path, voice_path, item_id))
    
    conn.commit()
    conn.close()
    return f"✅ Item ID {item_id} updated!"

# 🔹 Delete Item
def delete_item(item_id):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    cursor.execute("DELETE FROM items WHERE id=?", (item_id,))
    conn.commit()
    conn.close()
    return f"❌ Item ID {item_id} deleted!"

# 🔹 Export CSV
def export_csv():
    df = view_items()
    df.to_csv("export_items.csv", index=False)
    return "✅ Exported to export_items.csv"

# 🔹 Gradio UI
with gr.Blocks() as demo:
    gr.Markdown("## 🐄 Advanced Multi-language Media Database (বাংলা + English)")

    with gr.Tab("Load YAML"):
        load_btn = gr.Button("Load YAML")
        load_output = gr.Textbox(label="Status")
        load_btn.click(load_yaml_to_db, inputs=[], outputs=load_output)

    with gr.Tab("View / Search"):
        search_input = gr.Textbox(label="Search (বাংলা/English)")
        search_btn = gr.Button("Search")
        search_output = gr.Dataframe()
        view_btn = gr.Button("View All Items")
        view_output = gr.Dataframe()
        search_btn.click(search_items, inputs=search_input, outputs=search_output)
        view_btn.click(view_items, inputs=[], outputs=view_output)

    with gr.Tab("Media Gallery / CRUD"):
        item_id_input = gr.Number(label="Item ID")
        name_en_input = gr.Textbox(label="Name (EN)")
        name_bn_input = gr.Textbox(label="Name (BN)")
        desc_en_input = gr.Textbox(label="Description (EN)")
        desc_bn_input = gr.Textbox(label="Description (BN)")
        photo_input = gr.File(label="Photo", file_types=[".png",".jpg",".jpeg"])
        voice_input = gr.File(label="Voice", file_types=[".mp3",".wav"])
        
        # Display
        display_btn = gr.Button("Load Item")
        display_name_en = gr.Textbox(label="Name (EN)")
        display_name_bn = gr.Textbox(label="Name (BN)")
        display_desc_en = gr.Textbox(label="Description (EN)")
        display_desc_bn = gr.Textbox(label="Description (BN)")
        display_photo = gr.Image(label="Photo")
        display_voice = gr.Audio(label="Voice")
        display_btn.click(get_item, inputs=item_id_input, outputs=[display_name_en, display_name_bn, display_desc_en, display_desc_bn, display_photo, display_voice])
        
        # CRUD buttons
        add_btn = gr.Button("Add / Save New Item")
        add_output = gr.Textbox(label="Status")
        add_btn.click(add_item, inputs=[name_en_input,name_bn_input,desc_en_input,desc_bn_input,photo_input,voice_input], outputs=add_output)

        update_btn = gr.Button("Update Item")
        update_output = gr.Textbox(label="Status")
        update_btn.click(update_item, inputs=[item_id_input,name_en_input,name_bn_input,desc_en_input,desc_bn_input,photo_input,voice_input], outputs=update_output)

        delete_btn = gr.Button("Delete Item")
        delete_output = gr.Textbox(label="Status")
        delete_btn.click(delete_item, inputs=item_id_input, outputs=delete_output)

        export_btn = gr.Button("Export CSV")
        export_output = gr.Textbox(label="Status")
        export_btn.click(export_csv, inputs=[], outputs=export_output)

demo.launch()