import csv
from datetime import date


# import the Flask web framework so that we can take advantage of
# its libraries for rendering database info for display on web pages
# AND for inserting info into databases from info submitted through
# forms on web pages (dynamic web pages)
from flask import Flask, render_template, request
# import the cs50 library so we can use the connection making abstraction
from cs50 import SQL

app = Flask(__name__)

conn = SQL("sqlite:///data.db")

item_id = 0

@app.route("/")
def index():
    rows = conn.execute("SELECT * FROM items")
    return render_template("index.html", rows=rows)

@app.route('/addItem', methods=['GET', 'POST'])
# addShow function to add fake show (input by user) to a .csv
def addItem():
    if not request.form.get("name") or not request.form.get("price") or not request.form.get("numInStock") or not request.form.get("seller"):
        return render_template("failureToAdd.html")
    id = conn.execute("INSERT INTO items (name, price, seller, datePosted, numInStock) VALUES (?,?,?,?,?)", request.form.get("name"), request.form.get("price"), request.form.get("seller"), date.today(), request.form.get("numInStock"))
    rows = conn.execute("SELECT * FROM items WHERE id = ?", id)
    return render_template("addSuccess.html", rows=rows)

@app.route("/deleteItem", methods=['GET', 'POST'])
def deleteItem():
    if not request.form.get("name") or not request.form.get("seller") or not request.form.get("datePosted"):
        return render_template("failureToDelete.html")
    id = conn.execute("SELECT id FROM items WHERE name = ? and seller = ? and datePosted = ?", request.form.get("name"), request.form.get("seller"), request.form.get("datePosted"))
    rows = conn.execute("SELECT * FROM items WHERE id = ?", id[0].get('id'))
    conn.execute("DELETE FROM items WHERE id = ?", id[0].get('id'))
    return render_template("deleteSuccess.html", rows=rows)

@app.route("/changeNumInStockQuery", methods=['GET', 'POST'])
def changeNumInStockQuery():
    if not request.form.get("name") or not request.form.get("seller") or not request.form.get("datePosted"):
        return render_template("failureToChangeNumInStock.html")
    rows = conn.execute("SELECT * FROM items WHERE name = ? and seller = ? and datePosted = ?", request.form.get("name"), request.form.get("seller"), request.form.get("datePosted"))
    item_id = conn.execute("SELECT id FROM items WHERE name = ? and seller = ? and datePosted = ?", request.form.get("name"), request.form.get("seller"), request.form.get("datePosted"))
    #conn.execute("UPDATE items SET numInStock = ? WHERE id = ?", request.form.get("numInStock"), id[0].get('id'))
    return render_template("changeNumInStock.html", rows=rows)

@app.route("/changeNumInStock", methods=['GET', 'POST'])
def changeNumInStock():
    conn.execute("UPDATE items SET numInStock = ? WHERE id = ?", request.form.get("numInStockChange"), request.form.get("id"))
    rows = conn.execute("SELECT * FROM items WHERE id = ?", request.form.get("id"))
    return render_template("changeNumInStockSuccess.html", rows=rows)

@app.route("/buyItem", methods=['GET', 'POST'])
def buyItem():
    stock = conn.execute("SELECT numInStock FROM items WHERE id = ?", request.form.get("id"))
    if stock[0].get('id') == "None":
        return render_template("failureToBuy.html")
    conn.execute("UPDATE items SET numInStock = ? WHERE id = ?", (stock[0].get('id')-1), request.form.get("id"))
    rows = conn.execute("SELECT * FROM items WHERE id = ?", request.form.get("id"))
    return render_template("itemBought.html", rows=rows)

# route this output to the webpage called queryShows
@app.route("/queryItems", methods=['GET', 'POST'])
def queryItems():
    n = request.form.get("name")
    p = request.form.get("price")
    d = request.form.get("datePosted")
    s = request.form.get("seller")
    i = request.form.get("numInStock")

    if(n == ""):
        n = '%'
    if(p == ""):
        p = '%'
    if(d == ""):
        d = '%'
    if(s == ""):
        s = '%'
    if(i == ""):
        i = '%'

    n = '%' + n + '%'

    rows = conn.execute("SELECT * FROM items WHERE name LIKE ? AND price LIKE ? AND datePosted LIKE ? AND seller like ?", n, p, d, s)
    return render_template("queryItems.html", rows=rows)
