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

Consists of following functions:

postScore          @ /postScore/
getImage           @ /getImage/
createUser         @ /createUser/
userLogin          @ /userLogin/
changeUserPassword @ /changeUserPassword/

@author: nja
"""
import pyodbc
from flask import Flask
from flask import request


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__)
##########################################################################
#                            for testing                                 #
#                                                                        #
#http://127.0.0.1:5000/postScore/?score=9&candidateId=10.jpg&userName=ali#
##########################################################################
'''
Posts new image score for given user
'''
@app.route('/postScore/')
def postScore():
    score = request.args.get('score')
    candidateId = request.args.get('candidateId')
    userName = request.args.get('userName')

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

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

    cursor.execute(
        """
        INSERT INTO [dbo].[Scores]
        (UserId, CandidateId, Score)
        values
        (?,?,?)
        """, ID, candidateId, score)
    cursor.commit()

    return "Success posting score"

##########################################################################
#                            for testing                                 #
#                                                                        #
#            http://127.0.0.1:5000/getImage/?&userName=ali               #
##########################################################################
'''
Returns an image and removes it from the database
'''
@app.route('/getImage/')
def getImage():
    userName = request.args.get('userName')

    cursor.execute(
        """
        WITH alreadyRated AS
        (
        SELECT Scores.UserId, Scores.CandidateId,Scores.Score, Users.UserName
        FROM Scores
        INNER JOIN Users
        ON Scores.userId = users.Id
        WHERE UserName = ?
        )
        SELECT TOP 1 url
        FROM candidates
        LEFT OUTER JOIN alreadyRated
        ON alreadyRated.CandidateId = candidates.URL
        WHERE UserID IS NULL
        ORDER BY NEWID()
        """, userName)

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

    return image


##########################################################################
#                            for testing                                 #
#                                                                        #
#    http://127.0.0.1:5000/createUser/?userName=mathias&password=1234    #
##########################################################################
'''
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()

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

##########################################################################
#                            for testing                                 #
#                                                                        #
#    http://127.0.0.1:5000/userLogin/?userName=nicolai&password=1234     #
##########################################################################
'''
Logs in a user
'''
@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 UserPassword
    from [dbo].[Users]
    where UserName = ?
    """, userName)

    response = cursor.fetchall()

    #check if no response
    if len(response) == 0:
        return "No user found"

    dbPassword = response[0][0]

    #check if password is correct
    if dbPassword == password:
        return "Success. Logged in"
    else:
        return "Error. Wrong password"

####################################################################################
#                            for testing                                           #
#                                                                                  #
#http://127.0.0.1:5000/changeUserPassword/?userName=n&password=1&newPassword=1     #
####################################################################################
'''
Change user password
'''
@app.route('/changeUserPassword/')
def changeUserPassword():
    #Getting user name and password from http request.
    userName = request.args.get('userName')
    password = request.args.get('password')
    newPassword = request.args.get('newPassword')

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

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

    #check if password is correct
    if dbPassword == password:

        cursor.execute(
            """
            UPDATE [dbo].[Users]
            SET UserPassword=?
            WHERE UserName=?;
            """, newPassword, userName)
        cursor.commit()

        return "Success. Password Changed"
    else:
        return "Error. Wrong password"

'''
test connection
'''
@app.route('/test/')
def test():
    return "Hello World"

if __name__ == "__main__":
    app.run(host='0.0.0.0')