import sqlite3
import os
from supabase import create_client, Client

def create_table():
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS code (
        id INTEGER PRIMARY KEY,
        titre TEXT,
        niveau INTEGER,
        mots_cle TEXT,
        enonce TEXT NOT NULL,
        test TEXT
    )
    """)
    cursor.execute("DELETE FROM code;")
    if not os.path.exists('code.txt'):
        print("CRITICAL: code.txt not found! Exercises will not be loaded.")
        conn.close()
        return
    with open('code.txt', 'r') as f:
        c = 0
        txt = ""
        id = 1
        for l in f :
            txt += l
            if l == ";;;\n":
                c += 1
            if c == 5 :
                tab = txt.split(";;;\n")
                txt = ""
                c = 0
                cursor.execute("INSERT INTO code (id, titre, niveau, mots_cle, enonce, test) VALUES (?, ?, ?, ?, ?, ?)", (id, tab[0], tab[1], tab[2], tab[3], tab[4]))
                id += 1
        conn.commit()
        conn.close()

def check_user(username, password):
    supabase_url = os.getenv("SUPABASE_URL")
    supabase_key = os.getenv("SUPABASE_KEY")
    
    if not supabase_url or not supabase_key:
        print("Erreur: SUPABASE_URL ou SUPABASE_KEY non défini")
        return False
        
    try:
        supabase: Client = create_client(supabase_url, supabase_key)
        response = supabase.rpc("check_login", {"p_user": username, "p_password": password}).execute()
        
        # Log for debugging
        print(f"DEBUG: Supabase response for {username}: {response.data}")
        
        # Ensure we return a boolean
        if response.data is True:
            return True
        elif response.data is False:
            return False
        return bool(response.data)
    except Exception as e:
        print(f"Erreur lors de la connexion à Supabase: {e}")
        import traceback
        traceback.print_exc()
        return False

# La fonction add_user n'est plus nécessaire car la gestion des utilisateurs se fait via Supabase

def return_title():
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("SELECT id, titre, niveau FROM code")
    rows = cursor.fetchall()
    conn.close()
    return [{"id" : v[0], "title" : v[1].replace("\n",""), "niveau":v[2]} for v in rows]

def return_exercise(id):
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM code WHERE id = ?", (id,))
    rows = cursor.fetchall()
    conn.close()
    return rows[0]

def return_mot_cle():
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("SELECT id, mots_cle FROM code")
    rows = cursor.fetchall()
    conn.close()
    return [{"id" : v[0], "mot_cle" : v[1].replace("\n","")} for v in rows]

def return_niveau():
    conn = sqlite3.connect("code.db")
    cursor = conn.cursor()
    cursor.execute("SELECT id, niveau FROM code")
    rows = cursor.fetchall()
    conn.close()
    return [{"id" : v[0], "niveau" : v[1]} for v in rows]