from bottle import route, run, request, template
import sqlite3

# Connexion à la base de données
db_conn = sqlite3.connect('data.db')
db_conn.execute('PRAGMA foreign_keys = ON')

# Préparation de la base de données
db_conn.execute('''
CREATE TABLE IF NOT EXISTS ingredients (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);''')
try:
    with db_conn:
        db_conn.execute('''
        INSERT INTO ingredients (name)
        VALUES ('Mozzarella'),('Tomate'),('Basilic'),
               ('Jambon'),('Olive'),
                ('Oignon'),('Oeuf');
        ''')
except sqlite3.IntegrityError:
    # Si la table a déjà été remplie, on ne fait rien
    pass

@route('/ingredients', method=['GET', 'POST'])
def ingredients():
    # utilisation de la référence à la connexion globale
    cursor = db_conn.cursor()
    q = request.query.get('q', '')
    if request.method == 'POST':
        name = request.forms.get('name')
        try:
            with db_conn:
                cursor.execute('INSERT INTO ingredients (name) VALUES (?)',
                               (name,))
        except sqlite3.IntegrityError:
            cursor.execute(
                'SELECT id, name FROM ingredients WHERE UPPER(name) LIKE ?',
                (f'%{q.upper()}%',))
            rows = cursor.fetchall()
            return template('ingredients.html',
                            error="L'ingrédient existe déjà",
                            rows=rows, q=q)
    cursor.execute('SELECT id, name FROM ingredients')
    rows = cursor.fetchall()
    return template('ingredients.html', rows=rows, q=q)

run(host='localhost', port=38083, debug=True, reloader=True)