import sqlite3
from config import DB_PATH

def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS users
                 (user_id INTEGER PRIMARY KEY, name TEXT, downloads INTEGER DEFAULT 0)""")
    c.execute("""CREATE TABLE IF NOT EXISTS menu_buttons
                 (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT)""")
    conn.commit()
    conn.close()

def add_user(user_id, name=None):
    conn = sqlite3.connect(DB_PATH)
    conn.execute("INSERT OR IGNORE INTO users (user_id, name) VALUES (?, ?)", (user_id, name))
    if name:
        conn.execute("UPDATE users SET name=? WHERE user_id=?", (name, user_id))
    conn.commit()
    conn.close()

def increment_download(user_id):
    conn = sqlite3.connect(DB_PATH)
    conn.execute("UPDATE users SET downloads = downloads + 1 WHERE user_id=?", (user_id,))
    conn.commit()
    conn.close()

def get_all_users():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT user_id FROM users")
    rows = [r[0] for r in c.fetchall()]
    conn.close()
    return rows

def get_stats():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT COUNT(*) FROM users")
    total = c.fetchone()[0]
    c.execute("SELECT user_id, name, downloads FROM users ORDER BY downloads DESC")
    logs = c.fetchall()
    conn.close()
    return total, logs

def get_menu_buttons():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT label FROM menu_buttons")
    rows = [r[0] for r in c.fetchall()]
    conn.close()
    return rows or ["Help", "About"]

def add_menu_button(label):
    conn = sqlite3.connect(DB_PATH)
    conn.execute("INSERT INTO menu_buttons (label) VALUES (?)", (label,))
    conn.commit()
    conn.close()

def clear_menu_buttons():
    conn = sqlite3.connect(DB_PATH)
    conn.execute("DELETE FROM menu_buttons")
    conn.commit()
    conn.close()
