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

from api.DatabaseHandler import DatabaseHandler

'''

Endpoint vracejici zakladni data o zastupitelstvu

'''

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

    # ziskani potrebnych dat
    orderZMB = 8
    data = {}
    data["cislo_zmb"] = orderZMB
    
    sql = "SELECT zmb.funkcni_obdobi_od, zmb.funkcni_obdobi_do, clen_zmb.jmeno_clena, clen_zmb.prijmeni_clena, clen_zmb.pohlavi FROM zmb JOIN clen_zmb ON zmb.ck_leader = clen_zmb.id WHERE zmb.poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))
    row = dbHandler.cursor.fetchone()
    data["funkcni_obdobi"] = str(row[0]) + '–' + str(row[1])
    data["leader"] = row[2] + ' ' + row[3]
    data["leader_pohlavi"] = row[4]
    
    sql = "SELECT COUNT(cislo_zasedani) FROM zasedani_zmb WHERE ck_poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))
    row = dbHandler.cursor.fetchone()
    data["pocet_zasedani"] = row[0]

    sql = "SELECT COUNT(cislo_hlasovani) FROM hlasovani WHERE ck_poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))
    row = dbHandler.cursor.fetchone()
    data["pocet_hlasovani"] = row[0]

    sql = "SELECT COUNT(hlasovani.cislo_hlasovani) FROM hlasovani JOIN hlasovani_clena ON hlasovani.id = hlasovani_clena.ck_cislo_hlasovani WHERE hlasovani.ck_poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))
    row = dbHandler.cursor.fetchone()
    data["pocet_hlasovani_clenu"] = row[0]

    sql = "SELECT DISTINCT clen_zmb.jmeno_clena, clen_zmb.prijmeni_clena FROM clen_zmb JOIN prislusi_k ON clen_zmb.id = prislusi_k.ck_clen_ZMB WHERE clen_zmb.pohlavi=0 AND prislusi_k.ck_poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))
    data["pocet_zastupitelu"] = dbHandler.cursor.rowcount

    sql = "SELECT DISTINCT clen_zmb.jmeno_clena, clen_zmb.prijmeni_clena FROM clen_zmb JOIN prislusi_k ON clen_zmb.id = prislusi_k.ck_clen_ZMB WHERE clen_zmb.pohlavi=1 AND prislusi_k.ck_poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))
    data["pocet_zastupitelek"] = dbHandler.cursor.rowcount

    data["nejnovejsi_zasedani"] = {}
    sql = "SELECT cislo_zasedani, DATE_FORMAT(datum_zasedani,'%d.%m.%Y') FROM zasedani_zmb WHERE ck_poradi_ZMB=%s ORDER BY `datum_zasedani` DESC"
    dbHandler.cursor.execute(sql, (orderZMB,))
    row = dbHandler.cursor.fetchone()
    data["nejnovejsi_zasedani"]["cislo"] = row[0]
    data["nejnovejsi_zasedani"]["datum"] = row[1]

    sql = "SELECT COUNT(hlasovani_clena.ck_clen_ZMB) FROM zmb JOIN hlasovani ON zmb.poradi_ZMB=hlasovani.ck_poradi_ZMB JOIN hlasovani_clena ON hlasovani.id = hlasovani_clena.ck_cislo_hlasovani JOIN clen_zmb ON hlasovani_clena.ck_clen_ZMB = clen_zmb.id WHERE hlasovani.ck_poradi_ZMB=%s AND hlasovani.cislo_zasedani=%s AND hlasovani.cislo_hlasovani=1 AND clen_zmb.pohlavi = 0"
    dbHandler.cursor.execute(sql, (orderZMB, data["nejnovejsi_zasedani"]["cislo"]))
    row = dbHandler.cursor.fetchone()
    data["nejnovejsi_zasedani"]["zastupitele"] = row[0]

    sql = "SELECT COUNT(hlasovani_clena.ck_clen_ZMB) FROM zmb JOIN hlasovani ON zmb.poradi_ZMB=hlasovani.ck_poradi_ZMB JOIN hlasovani_clena ON hlasovani.id = hlasovani_clena.ck_cislo_hlasovani JOIN clen_zmb ON hlasovani_clena.ck_clen_ZMB = clen_zmb.id WHERE hlasovani.ck_poradi_ZMB=%s AND hlasovani.cislo_zasedani=%s AND hlasovani.cislo_hlasovani=1 AND clen_zmb.pohlavi = 1"
    dbHandler.cursor.execute(sql, (orderZMB, data["nejnovejsi_zasedani"]["cislo"]))
    row = dbHandler.cursor.fetchone()
    data["nejnovejsi_zasedani"]["zastupitelky"] = row[0]

    data["nejnovejsi_zasedani"]["mandaty"] = []
    date = str(data["nejnovejsi_zasedani"]["datum"]).split(".")
    date = date[2] + '-' + date[1] + '-' + date[0]
    sql = "SELECT politicka_strana.id, politicka_strana.zkratka, politicka_strana.barva, COUNT(politicka_strana.zkratka) FROM prislusi_k JOIN clen_zmb ON prislusi_k.ck_clen_ZMB=clen_zmb.id JOIN politicka_strana ON prislusi_k.ck_politicka_strana=politicka_strana.id WHERE prislusi_k.ck_poradi_ZMB=%s AND prislusi_k.datum_do=%s GROUP BY politicka_strana.zkratka ORDER BY COUNT(politicka_strana.zkratka) DESC"
    dbHandler.cursor.execute(sql, (orderZMB, date))
    for item in dbHandler.cursor:
        data["nejnovejsi_zasedani"]["mandaty"].append({
          "strana_id": item[0], 
          "strana_zkratka": item[1], 
          "strana_barva": item[2], 
          "pocet_mandatu": item[3]
        })
    
    sql = "SELECT politicka_strana.zkratka FROM koalice JOIN politicka_strana ON koalice.ck_politicka_strana = politicka_strana.id WHERE koalice.ck_poradi_ZMB=%s"
    dbHandler.cursor.execute(sql, (orderZMB,))

    data["koalice"] = []
    for item in dbHandler.cursor:
      data["koalice"].append({'strana_zkratka' : item[0]})

    sql = "SELECT DISTINCT politicka_strana.id, politicka_strana.zkratka, politicka_strana.barva FROM politicka_strana JOIN prislusi_k ON politicka_strana.id=prislusi_k.ck_politicka_strana WHERE ck_poradi_ZMB=%s ORDER BY politicka_strana.zkratka"
    dbHandler.cursor.execute(sql, (orderZMB,))
    data["celkem_stran"] = dbHandler.cursor.rowcount

    # ukonceni spojeni s db
    dbHandler.closeConnection()
    
    return data