from flask import Flask, request, jsonify
from flaskext.mysql import MySQL
import json
import collections
# from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = '0904'
app.config['MYSQL_DATABASE_DB'] = 'kaidee'
# app.config['SERVER_NAME'] = 'snowywords2.ddns.net:5000'
app.config['SERVER_NAME'] = 'localhost:5000'

mysql = MySQL(app)


@app.route('/')
def index():
    # do sth here
    print('Hello and welcome!')


@app.route('/users/<int:UserID>', methods=['GET'])
def users(UserID):
    cur = mysql.get_db().cursor()
    query_string = "SELECT * FROM kaidee.user WHERE UserID = '{UserID}' AND UserID IS NOT NULL ".format(UserID=UserID)
    cur.execute(query_string)
    columns = [column[0] for column in cur.description]
    results = []
    for row in cur.fetchall():
        results.append(dict(zip(columns, row)))
    if len(results)>0:
        print (results)
        return str(results)
    else:
        print('No data found at the index')
        return ('No data found at the index')


@app.route('/faq/<int:FAQID>', methods=['GET'])
def faq(FAQID):
    cur = mysql.get_db().cursor()
    query_string = "SELECT * FROM kaidee.faq WHERE FAQID = '{FAQID}' AND FAQID IS NOT NULL".format(FAQID=FAQID)
    cur.execute(query_string)
    columns = [column[0] for column in cur.description]
    results = []
    for row in cur.fetchall():
        results.append(dict(zip(columns, row)))
    if len(results)>0:
        print (results)
        return str(results)
    else:
        print('No data found at the index')
        return ('No data found at the index')


@app.route('/browse_product/<int:ItemID>', methods=['GET'])
def browse_product(ItemID):
    cur = mysql.get_db().cursor()
    query_string = "SELECT ItemID, Name, Price, Postcode, LevelID_u, ItemImage \
                    FROM item i, user u, itempicture p \
                    WHERE i.ItemID = '{ItemID}' AND i.UserID_i = u.UserID AND p.ItemID_ip = i.ItemID ".format(ItemID = ItemID)
    cur.execute(query_string)
    columns = [column[0] for column in cur.description]
    results = []
    for row in cur.fetchall():
        results.append(dict(zip(columns, row)))
    if len(results)>0:
        print (results)
        return str(results)
    else:
        print('No data found at the index')
        return ('No data found at the index')


@app.route('/view_product/<int:ItemID>', methods=['GET'])
def view_product(ItemID):
    cur = mysql.get_db().cursor()
    query_string = "SELECT ItemID, Name, Description, Date, Price, ItemImage, Display_name, Postcode, LevelID_u \
                    FROM item i, user u, itempicture p \
                    WHERE i.ItemID = '{ItemID}' AND i.UserID_i = u.UserID AND p.ItemID_ip = i.ItemID".format(ItemID = ItemID)
    cur.execute(query_string)
    columns = [column[0] for column in cur.description]
    results = []
    for row in cur.fetchall():
        results.append(dict(zip(columns, row)))
    if len(results)>0:
        print (results)
        return str(results)
    else:
        print('No data found at the index')
        return ('No data found at the index')


@app.route('/addone/<string:insert>', methods=['GET','POST'])
def add(insert):
    cur = mysql.get_db().cursor()
    cur.execute('''SELECT MAX(UserID) FROM kaidee.user''')
    maxid = cur.fetchone()   #(10,)
    query_string = "INSERT INTO kaidee.user(UserID, Email, Postcode, Firstname, Lastname, Display_name, LevelID_u) VALUES({insert})".format(insert=insert)
    print(query_string)
    cur.execute(query_string)
    db = mysql.get_db()
    db.commit()


@app.errorhandler(404)
def page_not_found(error):
    return 'This page does not exist you fuckin dummy', 404


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)


#     @app.route('/auth')
# def auth():
#     username = request.args.get('UserName')
#     password = request.args.get('Password')
#     cursor = mysql.connect().cursor()
#     cursor.execute("SELECT * from User where Username='" + username + "' and Password='" + password + "'")
#     data = cursor.fetchone()
#     if data is None:
#      return "Username or Password is wrong"
#     else:
#      return "Logged in successfully"