import math
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_unit = db.execute("select * from pucp_unit;").fetchall()
    semester = [{'id': x, 'name': x} for x in range(1, 13)]
    desertion_risks_class = db.execute("select * from desertion_risk_class;").fetchall()

    pagination = { 'pagesize': 100, 'page': None , 'n_pages': None, 'rowcount': None}
    pagination['page'] = int(request.args.get('page', 0))

    # filter criteria    | field choices
    fc = {
        'pucp_unit': int(request.args.get('pucp_unit', 0)),
        'semester': int(request.args.get('semester', 0)),
        'desertion_risk_class': int(request.args.get('desertion_risk_class', 0)),
    }
    where_stmt = "where 1=1\n"
    params = []
    if fc['pucp_unit'] != 0:
        where_stmt += "    and stu.pucp_unit_id = %s\n"
        params.append(fc['pucp_unit'])
    if fc['semester'] != 0:
        where_stmt += "    and stu.current_semester = %s\n"
        params.append(fc['semester'])
    if fc['desertion_risk_class'] != 0:
        where_stmt += "    and stu.est_desertion_risk_class_id = %s\n"
        params.append(fc['desertion_risk_class'])

    query = f"""
        select stu.*, adm.subdivision3 as "district", puc.name as "pucp_unit",
            des.name as "est_desertion_risk_class"
        from student stu 
            left join administrative_division adm 
                on stu.administrative_division_id = adm.id
            left join pucp_unit as puc
                on stu.pucp_unit_id = puc.id
            left join desertion_risk_class as des
                on stu.est_desertion_risk_class_id = des.id
        {where_stmt}
    """
    students = get_paginated_items(query, params=params, pagination=pagination)

    # 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}%"

    print(query, params)

    return render_template(
        'students/index.html', 
        students=students, pagination=pagination,
        pucp_unit=pucp_unit,
        semester=semester,
        desertion_risk_class=desertion_risks_class,
        fc=fc
    )


# Helper functions

def get_paginated_items(query: str, params: list=[], pagination: dict={}):
    limit = pagination.get('pagesize', 100)
    offset = pagination.get('page', 0) * limit
    with get_db().cursor() as cursor:
        cursor.execute(f"{query} LIMIT %s OFFSET %s;", params + [limit, offset])
        pagination['n_pages'] = math.ceil(cursor.rowcount / limit)
        pagination['rowcount'] = cursor.rowcount
        items = cursor.fetchall()
    return items