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

from api.DatabaseHandler import DatabaseHandler

'''

Pomocny endpoint na vypocet podrobne ucasti 
(po zastupitelich a zasedanich)

'''

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

    # ziskani potrebnych dat
    orderZMB = 8
    cntr = 1

    sql = "DELETE FROM _attendancestatsdetailed"
    dbHandler.cursor.execute(sql)

    members = []
    sql = "SELECT id, jmeno_clena, prijmeni_clena FROM clen_zmb"
    dbHandler.cursor.execute(sql)

    for id, jmeno_clena, prijmeni_clena in dbHandler.cursor:
        members.append({'id' : id, 'jmeno' : jmeno_clena + ' ' + prijmeni_clena})

    for member in members:
        # data pro 'politicke_strany'
        parties = []
        sql = "SELECT politicka_strana.id, politicka_strana.zkratka, politicka_strana.barva, prislusi_k.datum_od, prislusi_k.datum_do FROM prislusi_k INNER JOIN politicka_strana ON politicka_strana.id=prislusi_k.ck_politicka_strana WHERE prislusi_k.ck_poradi_ZMB=%s AND prislusi_k.ck_clen_ZMB=%s"
        dbHandler.cursor.execute(sql, (orderZMB, member['id']))
        
        # prislusnost ve stranach od-do
        result = []
        for id, zkratka, barva, datum_od, datum_do in dbHandler.cursor:
            result.append({'id': id, 'zkratka': zkratka, 'strana_barva': barva, 'datum_od': datum_od, 'datum_do': datum_do})

        # pocet zasedani pod kazdou zjistenou stranou (chronologicky)
        sessions = []
        for item in result:
            sql = "SELECT cislo_zasedani FROM zasedani_zmb WHERE ck_poradi_ZMB=%s AND datum_zasedani>=%s AND datum_zasedani<=%s"
            dbHandler.cursor.execute(sql, (orderZMB, item['datum_od'], item['datum_do']))
            
            for cislo_zasedani in dbHandler.cursor:
                sessions.append({'poradi_ZMB': cislo_zasedani[0], 'strana_id': item['id'], 'strana_zkratka': item['zkratka'], 'strana_barva': item['strana_barva']})

        # jednotliva platna hlasovani v ramci zasedani
        attendance = ''
        for session in sessions:
            sql = "SELECT vysledek_volice.text, COUNT(*) AS count FROM zmb JOIN hlasovani ON zmb.poradi_ZMB = hlasovani.ck_poradi_ZMB JOIN hlasovani_clena ON hlasovani.id = hlasovani_clena.ck_cislo_hlasovani JOIN vysledek_volice ON hlasovani_clena.ck_vysledek_volice = vysledek_volice.id WHERE hlasovani.ck_poradi_ZMB=%s AND hlasovani.cislo_zasedani=%s AND hlasovani_clena.ck_clen_ZMB=%s AND hlasovani.validni_hlasovani=1 GROUP BY vysledek_volice.text ORDER BY vysledek_volice.id"
            dbHandler.cursor.execute(sql, (orderZMB, session['poradi_ZMB'], member['id']))

            # pouze pokud clen mel pro dane zasedani mandat
            if dbHandler.cursor.rowcount != 0:
                voteOptions = {}
                for text, count in dbHandler.cursor:
                    voteOptions[text.lower()] = count

                if len(voteOptions) == 1 and ('nepřít.' in voteOptions.keys() or 'nepřít. (VK)' in voteOptions.keys()):
                    attendance = 'nepřítomnost'

                elif ('nepřít.' not in voteOptions.keys()) and ('nepřít. (VK)' not in voteOptions.keys()):
                    attendance =  'přítomnost'
                
                else:
                    attendance = 'částečná přítomnost'

                val = (cntr, session['poradi_ZMB'], session['strana_id'], session['strana_zkratka'], session['strana_barva'], member['id'], member['jmeno'], attendance)
                sql = "INSERT INTO _attendancestatsdetailed (id,poradi_ZMB,strana_id,strana_zkratka,strana_barva,clen_ZMB_id,clen_ZMB,dochazka) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
                dbHandler.cursor.execute(sql, val)

                dbHandler.commitStatement()

            cntr = cntr + 1
    
    # ukonceni spojeni s db
    dbHandler.closeConnection()
    
    return 'tabulka _attendancestatsdetailed byla uspesne aktualizovana, bylo vlozeno '+ str(cntr-1) + ' zaznamu'