"""Training app for the "fix the SQL query" lab. Runs on 127.0.0.1 only.
Run:  pip install flask   then   python search_app.py   then open http://127.0.0.1:5000/search?name=Asha
"""
import sqlite3
from flask import Flask, request, jsonify

app = Flask(__name__)
DB = "customers.db"

def setup():
    con = sqlite3.connect(DB)
    con.execute("DROP TABLE IF EXISTS customers")
    con.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT, phone TEXT)")
    con.executemany("INSERT INTO customers (name, city, phone) VALUES (?, ?, ?)", [
        ("Asha", "Pune", "98xxxxxx01"), ("Rahul", "Mumbai", "98xxxxxx02"),
        ("O'Brien", "Bengaluru", "98xxxxxx03"), ("Meera", "Nagpur", "98xxxxxx04")])
    con.commit(); con.close()

@app.route("/search")
def search():
    name = request.args.get("name", "")
    con = sqlite3.connect(DB)
    # BUG: the user's text is pasted straight into the SQL string
    sql = f"SELECT name, city FROM customers WHERE name = '{name}'"
    try:
        rows = con.execute(sql).fetchall()
    except sqlite3.Error as e:
        return jsonify(error=str(e), sql=sql), 500
    finally:
        con.close()
    return jsonify(results=[{"name": r[0], "city": r[1]} for r in rows])

if __name__ == "__main__":
    setup()
    app.run(host="127.0.0.1", port=5000, debug=False)