from flask_restful import Api, Resource, reqparse
import mysql.connector

from api.DatabaseHandler import DatabaseHandler

'''

Pomocny endpoint na vypocet celkove ucasti (pritomni + castecne pritomni)
na kazdem zasedani

'''

class AttendanceStats(Resource):
  def get(self):
    
    # navazani spojeni s db
    dbHandler = DatabaseHandler()

    # ziskani potrebnych dat
    orderZMB = 8
    data = []

    # data pro 'zasedani'
    sql = "SELECT cislo_zasedani FROM zasedani_zmb WHERE ck_poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))
    totalSessions = dbHandler.cursor.rowcount

    # ziskani cisel zasedani
    sessions = []
    for cislo_zasedani in dbHandler.cursor:
        sessions.append(cislo_zasedani[0])
    
    updated = 0
    added = 0
    # pro kazde zasedani
    for session in sessions:
        # pocet platnych hlasovani
        sql = "SELECT * FROM hlasovani WHERE hlasovani.ck_poradi_ZMB=%s AND hlasovani.cislo_zasedani=%s AND hlasovani.validni_hlasovani=1"
        dbHandler.cursor.execute(sql, (orderZMB, session))

        validVotes = dbHandler.cursor.rowcount

        sql = "SELECT hlasovani_clena.ck_clen_ZMB, vysledek_volice.text, COUNT(vysledek_volice.text) AS pocet FROM zasedani_zmb JOIN zmb ON zasedani_zmb.ck_poradi_ZMB=zmb.poradi_ZMB JOIN hlasovani ON zmb.poradi_ZMB=hlasovani.ck_poradi_ZMB JOIN vysledek_hlasovani ON hlasovani.ck_vysledek_hlasovani=vysledek_hlasovani.id JOIN hlasovani_clena ON hlasovani_clena.ck_cislo_hlasovani=hlasovani.id JOIN vysledek_volice ON hlasovani_clena.ck_vysledek_volice=vysledek_volice.id WHERE zmb.poradi_ZMB=%s AND zasedani_zmb.cislo_zasedani=%s AND hlasovani.validni_hlasovani=1 AND zasedani_zmb.cislo_zasedani=hlasovani.cislo_zasedani GROUP BY hlasovani_clena.ck_clen_ZMB, vysledek_volice.text ORDER BY hlasovani_clena.ck_clen_ZMB"
        dbHandler.cursor.execute(sql, (orderZMB, session))

        totalAbsent = 0
        for item in dbHandler.cursor:
            if item[1] == 'nepřít.':
                if item[2] == validVotes:
                    totalAbsent = totalAbsent + 1

        attendance = 55-totalAbsent
        val = (session, attendance)

        sql = "SELECT poradi_zmb, ucast FROM _attendancestats WHERE poradi_zmb=%s"
        dbHandler.cursor.execute(sql, (session,))

        if dbHandler.cursor.rowcount == 1:
            # zaznam uz je v db
            cnt = 0
            for poradi_zmb, ucast in dbHandler.cursor:
                cnt = ucast

            # a jeho hodnota se zmenila
            if cnt != attendance:
                sql = "UPDATE _attendancestats SET ucast=%s WHERE _attendancestats.poradi_zmb=%s"
                dbHandler.cursor.execute(sql, (attendance, session))   
                updated = updated +1

        else:
            # zaznam neni v db
            sql = "INSERT INTO _attendancestats (poradi_zmb, ucast) VALUES (%s, %s)"
            dbHandler.cursor.execute(sql, val)
            added = added + 1

    # ukonceni spojeni s db
    dbHandler.closeConnection()
    
    return str(added) + ' zaznamu uspesne vlozeno; ' + str(updated) + ' zaznamu uspesne upraveno'