from flask import (
    Blueprint, flash, g, redirect, render_template, request, url_for
)
from werkzeug.exceptions import abort

from ustayml.views.auth import login_required
from ustayml.db import get_db

bp = Blueprint('students', __name__, url_prefix='/students')


@bp.route('/', methods=('GET', 'POST'))
@login_required
def index():
    db = get_db()
    pucp_units = db.execute("select * from pucp_unit;").fetchall()
    semesters = list(range(20, 0, -1))
    dessertion_risks = ['H', 'M', 'L']

    # filters
    filters = {}
    where_clause = "1=1\n"
    if request.method == 'POST':
        filters['pucp_unit'] = request.form['pucp_unit']
        filters['semester'] = request.form['semester']
        filters['dessertion_risk'] = request.form['dessertion_risk']

        if filters['pucp_unit'] in pucp_units:
            where_clause += f"and stu.pucp_unit_id = {filters['pucp_unit']}\n"
        if filters['semester'] in semesters:
            where_clause += f"and stu.current_semester  = {filters['semester']}"
        if filters['dessertion_risk'] in dessertion_risks:
            where_clause += f"and stu.est_dessertion_risk  = {filters['dessertion_risk']}"

        error = None
        if error is not None:
            flash(error)
        else:
            pass
    
    query = f"""
        select stu.*, adm.subdivision3 as "district" 
        from student stu left join administrative_division adm 
            on stu.administrative_division_id = adm.id
        ;
    """
    students = db.execute(query).fetchall()

    # formatting
    for s in students:
        s['fullname'] = f"{s['first_name']} {s['last_name']}"
        s['current_attendance'] = f"{s['current_attendance']*100:.2f}%"
        s['current_merit'] = f"{s['current_merit']*100:.2f}%"

    return render_template('students/index.html', students=students)