import sqlite3
# ---------------------------------------------------------
#                     Huiles Essentiels
# ---------------------------------------------------------

def HE_Data():
    con = sqlite3.connect("HE.db")
    cur = con.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS HE (id INTEGER PRIMARY KEY, HE_Nom text, HE_Prop text, HE_Dosage text)")
    con.commit()
    con.close()

def addRec(HE_Nom, HE_Prop, HE_Dosage):
    con = sqlite3.connect("HE.db")
    cur = con.cursor()
    cur.execute("INSERT INTO HE VALUES (NULL,?,?,?)", (HE_Nom, HE_Prop, HE_Dosage))
    con.commit()
    con.close()

# Charge le table par ordre alphabétic sur le champ [HE_Nom]
def loadRecord():
    con = sqlite3.connect("HE.db")
    cur = con.cursor()
    cur.execute("SELECT id, HE_Nom, HE_Prop, HE_Dosage FROM HE ORDER BY HE_Nom ASC")
    mData = cur.fetchall()
    print("Nombre d'enregistrement _ lecture Table", len(mData))
    # print(mData)                                          # Affiche tous les enregistrements
    # print(mData[0])                                         # Affiche l'enregistrement ligne 0
    # for item in mData:                                      # Affiche tous les enregistrements en colonne HE_Nom
    #       print(item[1])

    con.close()
    return mData

def deleteRec(id):
    con = sqlite3.connect("HE.db")
    cur = con.cursor()
    cur.execute("DELETE FROM HE WHERE id=?", (id,))
    print("id", id)
    con.commit()
    con.close()

# def searchDataTable(HE_Nom="", HE_Prop="", HE_Dosage=""):
# https://www.developpez.net/forums/d2061412/autres-langages/python/general-python/select-from/#post11455408
# https://www.python.org/dev/peps/pep-0249/#id41
def searchDataTable(HE_Prop="", HE_Dosage="",HE_Nom=""):

    print("HE_Nom", HE_Nom, type(HE_Nom))
    print("HE_Prop", HE_Prop, type(HE_Prop))
    print("HE_Dosage", HE_Dosage, type(HE_Dosage))

    con = sqlite3.connect("HE.db")
    cur = con.cursor()
    # cur.execute("SELECT * FROM HE WHERE HE_Nom = ? OR HE_Dosage = ?", ('%' + str(var_HENom) + '%', '%' + str(var_HEDos) + '%',))
    cur.execute("SELECT * FROM HE WHERE HE_Prop=? OR HE_Dosage=? OR HE_Nom=?", (HE_Prop, HE_Dosage, HE_Nom))
    rows = cur.fetchall()
    print("Liste_DB".format(rows))
    print("Nombre d'enregistrement(s) recherché(s)",len(rows))                                        # Nombre d'enregistrement
    for ii in rows:
        print("--------",ii)

    #
    # print(rows)                                          # Affiche tous les enregistrements
    # print(rows[0])                                         # Affiche l'enregistrement ligne 0
    # for item in rows:                                      # Affiche tous les enregistrements en colonne HE_Nom
    #        print(item[1])
    con.close()
    return rows

def dataUpdate(id, HE_Nom="", HE_Prop="", HE_Dosage=""):
    con = sqlite3.connect("HE.db")
    cur = con.cursor()
    cur.execute("UPDATE HE SET HE_Nom=?, HE_Pro=?, HE_Dosage=?, WHERE id=?", (HE_Nom, HE_Prop, HE_Dosage, id))
    con.commit()
    con.close()

HE_Data()