import datetime
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, get_paginated_rows, get_row

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
    pagination = { 'pagesize': 25, '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 pucp_unit_id = %s\n"
        params.append(fc['pucp_unit'])
    if fc['semester'] != 0:
        where_stmt += "    and current_semester = %s\n"
        params.append(fc['semester'])
    if fc['desertion_risk_class'] != 0:
        where_stmt += "    and est_desertion_risk_class_id = %s\n"
        params.append(fc['desertion_risk_class'])

    query = f"""
        select * from view_student
        {where_stmt}
    """

    # output & formatting
    students = get_paginated_rows(query, params=params, pagination=pagination)
    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, pagination=pagination,
        pucp_unit=pucp_unit,
        semester=semester,
        desertion_risk_class=desertion_risks_class,
        fc=fc
    )

@bp.route('/<int:student_id>', methods=('GET', 'POST'))
@login_required
def details(student_id):
    s = get_row("select * from view_student vs where vs.id = %s;", 
        [student_id]
    )

    if s is None:
        abort(404, "El ID del estudiante es inválido.")

    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/details.html',
        date=datetime.datetime(2023, 10, 26),
        student=s
    )