# -*- coding: utf-8 -*-
"""
Created on Thu Jan 26 16:30:19 2017

@author: nja
"""
import pyodbc
from flask import Flask
from flask import request
import glob
import os
import random


connection = pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
                               'Server=tcp:nja-training.database.windows.net,1433;'
                               'Database=RateMe;Uid=nja-admin@nja-training;'
                               'Pwd={Porsche911};'
                               'Encrypt=yes;'
                               'TrustServerCertificate=no;'
                               'Connection Timeout=30;')

cursor = connection.cursor()

app = Flask(__name__)

@app.route('/postScore/')
def postScore():
    direc = "D:\\DTU\\FlotGrim\\images\\exploretagsselfiegirl\\"
    os.chdir(direc)

    score = request.args.get('score')
    imageId = request.args.get('imageId')
    userName = request.args.get('userName')

    cursor.execute(
    """
    select userID
    from [dbo].[Users]
    where UserName = ?
    """, userName)

    ID = cursor.fetchall()[0][0]

    cursor.execute(
        """
        UPDATE [dbo].[Scores]
        SET [Score] = ?
        WHERE UserID = ? AND ImageId = ?
        """, score, ID, imageId)

    cursor.commit()

    return "Success posting score"
'''
Returns an image and removes it from the database
'''
@app.route('/getImage/')
def getImage():
    userName = request.args.get('userName')

    cursor.execute(
        """
        SELECT *
        FROM [dbo].[Scores]
        WHERE Score IS NULL AND UserName = ?
        """, userName)

    ID = cursor.fetchall()

    i = random.randrange(len(ID)) # get random index
    image = ID[i][2]

    return image

'''
Creates a user
'''
@app.route('/createUser/')
def createUser():
    #Getting user name and password from http request.
    userName = request.args.get('userName')
    password = request.args.get('passWord')

    #Checking if user exists
    cursor.execute(
    """
    select count(1)
    from [dbo].[Users]
    where UserName = ?
    """, userName)

    row = cursor.fetchall()[0][0]

    if row == 0:
        #creating user
        cursor.execute("INSERT INTO [dbo].[Users](UserName, UserPassWord) values (?, ?)", userName, password)
        cursor.commit()

        #fetching user ID
        cursor.execute(
            """
            select userID
            from [dbo].[Users]
            where UserName = ?
            """, userName)


        userID = cursor.fetchall()[0][0]
        #setting dir
        direc = "D:\\DTU\\FlotGrim\\images\\exploretagsselfiegirl\\"
        os.chdir(direc)

        #fetching all image names
        userList = glob.glob("*")

        inputList = []
        temp = []
        #looping over image names and creating lists
        for i in range(len(userList)):
            temp.append([userID, userName, userList[i]])

            #making new lists at 500 to limit size of call
            if i % 500 == 0:
                inputList.append(temp)
                temp = []

        #inserting to database
        for inputs in inputList:
            cursor.executemany("""
                      INSERT INTO [dbo].[Scores]
                      (UserID, UserName, ImageID)
                      VALUES (?,?,?)
                      """, inputs)
            cursor.commit()

        return 'Success creating user'
    else:
        return 'User already exists'

@app.route('/userLogin/')
def userLogin():
    #Getting user name and password from http request.
    userName = request.args.get('userName')
    password = request.args.get('passWord')

    #Checking if user exists
    cursor.execute(
    """
    select passWord
    from [dbo].[Users]
    where UserName = ?
    """, userName)

    dbPassword = cursor.fetchall()[0][0]

    if len(dbPassword) == 0:
        return "No user found"
    if dbPassword == password:
        return "Success. Loged in"
    else:
        return "Error. Wrong password"

if __name__ == "__main__":
    app.run()