from flask import Flask, request, render_template, session, redirect, url_for, flash
from TwitterAPI import TwitterAPI
from api_keys import *
import database_connector
import hashlib

conn = database_connector.conn
cur = database_connector.cur

app = Flask(__name__)
app.secret_key = b'890hj3h5gh0i8n5h0g8ni3hg50n8i'

def func_check_login(uname, pword):
    hashedpass = hashlib.sha256(pword.encode('UTF-8') + 'saltandpepper'.encode('UTF-8')).hexdigest()
    cur.execute("SELECT * FROM moderators WHERE naam = (%s);", [uname])
    sqlreturn = cur.fetchone()
    if sqlreturn == None:
        flash('Gebruiker niet gevonden')
        return False
    elif hashedpass != sqlreturn[2]:
        flash('Uw wachtwoord is fout')
        return False
    elif sqlreturn[3] != True:
        flash('Dit account mag niet meer inloggen')
        return False
    else:
        return True

def func_listmoderators(limit, page):
    cur.execute('SELECT * FROM moderators LIMIT (%s) OFFSET (%s)', (limit, page))
    allmoderators = cur.fetchall()
    cur.execute('SELECT COUNT(*) FROM moderators')
    countmoderators = cur.fetchone()
    return (allmoderators, countmoderators)

def func_addmoderator(uname, pword, pword2):
    username = uname.lower()
    password = pword
    password2 = pword2

    if len(password) < 4:
        flash('Wachtwoord lengte is te weinig, gebruik minimaal 4 tekens!')
        return False
    elif password != password2:
        flash('Wachtwoorden zijn niet hetzelfde!')
        return False

    if len(username) > 64 or len(username) < 3:
        flash('Gebruikersnaam lengte is ongeldig, gebruik minimaal 3 en maximaal 64 tekens!')
        return False
    elif ' ' in username:
        flash('Gebruikersnaam bevat spatie, dat mag niet')
        return False

    cur.execute("SELECT naam FROM moderators WHERE naam = (%s);", (username,))
    sqlreturn = cur.fetchone()

    if sqlreturn == None:
        hashedpass = hashlib.sha256(password.encode('UTF-8') + 'saltandpepper'.encode('UTF-8')).hexdigest()
        cur.execute("INSERT INTO moderators(naam, hash, maylogin) VALUES (%s, %s, 'true')", (username, hashedpass))
        conn.commit()
        flash('Account voor ' + username + ' aangemaakt!')
        return True
    else:
        flash('Probleem met de database, bestaat de gebruiker al?')
        return False

def func_removemoderator(moderatorid):
    try:
        cur.execute('DELETE FROM moderators WHERE moderatorid = (%s)', [moderatorid])
        conn.commit()
        flash('Moderator account verwijdert')
        return True
    except:
        conn.rollback()
        try:
            cur.execute('UPDATE moderators SET maylogin = false WHERE moderatorid = (%s)', [moderatorid])
            conn.commit()
            flash('Account kan niet verwijdert worden, account mag niet meer inloggen')
            return True
        except:
            conn.rollback()
            flash('Probleem opgetreden bij het aanpassen van moderator account')
            return False


def func_listberichten(limit, page):
    cur.execute('SELECT berichten.berichtid, berichten.bericht, berichten.datumontvangen, berichten.naamposter, '
                'berichten.datumgekeurd, locaties.naam, moderators.naam, statuses.status, berichten.twitterid '
                'FROM berichten '
                'LEFT OUTER JOIN statuses ON berichten.statusid = statuses.statusid '
                'LEFT OUTER JOIN locaties ON berichten.locatieid = locaties.locatieid '
                'LEFT OUTER JOIN moderators ON berichten.moderatorid = moderators.moderatorid '
                'ORDER BY datumontvangen DESC LIMIT (%s) OFFSET (%s)', (limit, page))
    allberichten = cur.fetchall()
    cur.execute('SELECT COUNT(*) FROM berichten')
    countberichten = cur.fetchone()
    return (allberichten, countberichten)

def func_querybericht(berichtid):
    cur.execute('SELECT berichten.berichtid, berichten.bericht, berichten.datumontvangen, berichten.naamposter, '
                'berichten.datumgekeurd, locaties.naam, moderators.naam, statuses.status, berichten.twitterid '
                'FROM berichten '
                'LEFT OUTER JOIN statuses ON berichten.statusid = statuses.statusid '
                'LEFT OUTER JOIN locaties ON berichten.locatieid = locaties.locatieid '
                'LEFT OUTER JOIN moderators ON berichten.moderatorid = moderators.moderatorid '
                'WHERE berichtid = (%s)', [berichtid])
    return cur.fetchone()

def func_lockbericht(berichtid, moderatorid):
    cur.execute('UPDATE berichten SET statusid = 2, moderatorid = (%s) WHERE berichtid = (%s)', (moderatorid, berichtid))
    conn.commit()
    return True



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

@app.route('/login', methods=['POST', 'GET'])
def login():
    if request.method == 'POST':
        if func_check_login(request.form['uname'], request.form['pword']):
            session['username'] = request.form['uname']
            return redirect(url_for('home'))
        else:
            return render_template('login.html')
    else:
        if 'username' in session:
            session.pop('username', None)
            flash('You logged out!')
        return render_template('login.html')

@app.route('/home')
def home():
    if 'username' in session:
        return render_template('home.html', username = session['username'])
    else:
        flash('You are not logged in!')
        return redirect(url_for('login'))

@app.route('/moderators')
def moderators():
    if 'username' in session:
        if request.args.get('maxitems'):
            maxitems = int(request.args.get('maxitems'))
        else:
            maxitems = 10
        if request.args.get('pagenum'):
            pagenum = int(request.args.get('pagenum')) - 1
        else:
            pagenum = 0

        page = maxitems * pagenum

        listreturn = func_listmoderators(maxitems, page)
        moderators = listreturn[0]
        moderatorcount = listreturn[1][0]

        return render_template('moderators.html', moderators=moderators, moderatorcount=moderatorcount)
    else:
        flash('You are not logged in!')
        return redirect(url_for('login'))

@app.route('/moderators/addmoderator', methods=['POST'])
def addmoderator():
    if 'username' in session:
        if func_addmoderator(request.form['uname'], request.form['pword'], request.form['pword2']):
            return redirect(url_for('moderators'))
        else:
            return redirect(url_for('moderators'))
    else:
        flash('You are not logged in!')
        return redirect(url_for('login'))

@app.route('/moderators/removemoderator', methods=['POST'])
def listmoderators():
    if 'username' in session:
        if func_removemoderator(request.form['delete']):
            return redirect(url_for('moderators'))
        else:
            return redirect(url_for('moderators'))
    else:
        flash('You are not logged in!')
        return redirect(url_for('login'))

@app.route('/berichten')
def berichten():
    if 'username' in session:
        if request.args.get('maxitems'):
            maxitems = int(request.args.get('maxitems'))
        else:
            maxitems = 10
        if request.args.get('pagenum'):
            pagenum = int(request.args.get('pagenum')) - 1
        else:
            pagenum = 0

        page = maxitems * pagenum

        listreturn = func_listberichten(maxitems, page)
        berichten = listreturn[0]
        berichtcount = listreturn[1][0]

        return render_template('berichten.html', berichten=berichten, berichtcount=berichtcount)
    else:
        flash('You are not logged in!')
        return redirect(url_for('login'))

@app.route('/bericht/<berichtid>')
def bericht(berichtid):
    if 'username' in session:
        bericht = func_querybericht(berichtid)
        return render_template('bericht.html', bericht=bericht)
    else:
        flash('You are not logged in!')
    return redirect(url_for('login'))



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