def sendThatEmail(eRecipient, eSubject, eMessage):
    #this is where we send the welcome email
    message = Mail(
        from_email = DEVELOPER_EMAIL,
        to_emails = eRecipient,
        subject = eSubject,
        html_content = eMessage)

    sg = SendGridAPIClient(SENDGRID_API_KEY)
    response = sg.send(message)
    return True

@app.route('/')
def home():
    return render_template('home.html')

@app.route('/resetpassword', methods = ['GET','POST'])
def resetPassword():
    if request.method == 'POST':
        e = request.form['resetemail']
        t = (e)
        cursor = db.cursor()
        query = "SELECT u_password FROM users WHERE u_email = %s"
        cursor.execute(query, t)
        results = cursor.fetchall()
        totalCount = len(results)

        if totalCount == 1:
            #we generate a new password here
            np = str(random.random())
            np = np[10:]

            t = (np, e)
            cursor = db.cursor()
            query = "UPDATE users SET u_password = %s WHERE u_email = %s"
            cursor.execute(query, t)
            db.commit()
            #p = results[0][0] #we get the password from the result set
            #we send the email here
            sendThatEmail(e, 'Your Requested Password', 'Your password is: ' + np)
            flash('Check your inbox for your password.')
        else:
            flash('Your email address does not exist.')

    return redirect('/login')

@app.route('/login', methods = ['POST','GET'])
def login():
    if request.method == 'POST':
        #We process login username and password
        u = request.form['username']
        p = request.form['password']

        #t = (u, p)
        t = (u, u, p)

        cursor = db.cursor()
        #query = "SELECT * FROM users WHERE u_username = %s AND u_password = %s"
        query = "SELECT * FROM users WHERE (u_username = %s OR u_email = %s) AND u_password = %s"
        cursor.execute(query, t)
        results = cursor.fetchall()

        totalCount = len(results)   #if total results returned is 1, successful login

        if totalCount == 1:
            session['username'] = u
            flash('Login was successful. Welcome ' + u + '! ')
            return redirect('/dashboard')
        else:
            flash('Bad credentials. Please try again.')
            return redirect('/login')

    return render_template('login.html')

@app.route('/dashboard', methods = ['GET','POST'])
def dashboard():
    if 'username' in session:
        '''
        if request.method == 'GET':
            sdDate = request['gamedate']
        else:
            sdDate = 'No Date Selected'
        '''
        #we connect to the sportsdata.io API here to get data for the dashboard
        URL = 'https://api.sportsdata.io/v3/nba/scores/json/GamesByDate/2019-FEB-1?key=ee023fa1e20045ed854d1abfdb5214a5'
        response = requests.get(URL)
        sd = json.loads(response.text)
        #end connection to sportsdata.io
        return render_template('dashboard.html', **locals())
    else:
        return redirect('/login')

    return redirect('/login')

@app.route('/logout')
def logout():
    session.pop('username', None)
    flash('You were logged out successfully.')
    return redirect('/')

@app.route('/signup')
def signup():
    if 'username' in session:
        redirect('/dashboard')

    return render_template('signup.html')

@app.route('/register', methods = ['POST','GET'])
def register():
    #we do form processing here
    if request.method == 'POST':
        e = request.form['email']
        u = request.form['username']
        p = request.form['password']
        #we will save the info above into the DB
        t = (e, u)
        cursor = db.cursor()
        query = "SELECT * FROM users WHERE u_email = %s OR u_username = %s"
        cursor.execute(query, t)
        results = cursor.fetchall()

        verifyCount = len(results)

        if verifyCount == 0:
            t = (e, u, p)
            cursor = db.cursor()
            query = "INSERT INTO users (u_email, u_username, u_password ) VALUES (%s, %s, %s)"
            cursor.execute(query, t)
            db.commit() #this function makes the change permanent to the table
            flash('You were registered successfully. Please login.')

            sendThatEmail(e, 'Welcome to The Oracle', 'Welcome! <br/> Your registration was successful.')
            flash('Check your email inbox for a welcome email.')
            #end of sending the welcome email

            return redirect('/login')
        else:
            flash('There was a duplicate email or username. Please try again.')

    else:
        flash('Your registration failed.')

    return redirect('/signup')

if __name__ == '__main__':
    app.run(debug=True)