from utils.db import Database, open_db
from geopy.geocoders import Nominatim
from geopy.distance import vincenty

def calc(field, operation, grouping, display, filters=[]):
    if field == 'remun':            fi = 1
    elif field == 'd_work_home':    fi = 7
    elif field == 'd_school_home':  fi = 8

    with open_db() as db:

        count = db.cur.execute('''
            SELECT s.idStage,
                s.remuneration,     p.annee,    p.libelle,
                ela.lat,            ela.lon,    ela.pays,
                ca.lat,             ca.lon,     ca.pays,
                ena.lat,            ena.lon,    ena.pays
            FROM Stage as s
            LEFT JOIN (Eleve as el, Adresse as ela) ON (el.idEleve = s.idEleve AND ela.idAdresse = el.idADresse)
            LEFT JOIN (Entreprise as en, Adresse as ena) ON (en.idEntreprise = s.idEntreprise AND ena.idAdresse = en.idAdresse)

            LEFT JOIN (Appartient, Promo as p, Campus, Adresse as ca) ON (
                Appartient.idEleve = el.idEleve AND p.id = Appartient.idPromo AND p.annee = s.annee
                AND Campus.idCampus = p.idCampus AND ca.idAdresse = Campus.idAdresse
            )
        ''')

        row = db.cur.fetchone()
        data = []
        visited = {}
        geoloc = Nominatim()
        while row:
            k = row[0]
            if k in visited.keys():
                data[visited[k]][3].append(row[3])
            else:
                data.append((
                    row[0], row[1] if row[1] and row[1] > 10 else None, row[2], [ row[3] ],
                    row[6], row[9], row[12],
                    vincenty((row[4], row[5]), (row[10], row[11])).kilometers if row[4] and row[10] else None,
                    vincenty((row[4], row[5]), (row[7], row[8])).kilometers if row[4] and row[7] else None
                ))

                visited[k] = len(data) - 1

            row = db.cur.fetchone()

        i = 0
        while i < len(data):
            if data[i][fi] == None:
                del data[i]
                continue

            for filtr in filters:

                pfield = filtr[0]
                if pfield == 'remun':           field = data[i][1]
                elif pfield == 'country':       field = data[i][6]
                elif pfield == 'promo':         field = data[i][3]
                elif pfield == 'd_work_home':   field = data[i][7]
                elif pfield == 'd_school_home': field = data[i][8]


                value = filtr[2]
                if pfield == 'remun' or pfield == 'd_work_home' or pfield == 'd_school_home':
                    value = int(value)

                pop = filtr[1]
                if field == None or (
                    (pop == 'eq' and (field != value if pfield != 'promo' else value not in field)) or
                    (pop == 'neq' and (field == value if pfield != 'promo' else value in field)) or
                    (pop == 'geq' and field < value) or
                    (pop == 'leq' and field > value)
                ):
                    del data[i]
                    i -= 1
                    break

            i += 1

        if grouping == 'individual':
            groups = { k: [k] for k in range(len(data)) }
            data.sort(key=lambda x, fi=fi: x[fi])
        elif grouping == 'promo' or grouping == 'promo_simple':
            groups = {}

            if grouping == 'promo_simple':
                keys = ( ('ING1',), ('ING2',), ('ING3',), ('ING4',) )
            else:
                keys = db.exec("""SELECT DISTINCT libelle FROM Promo""")[1]
            for k in keys:
                k = k[0]
                groups[k] = []
                for i in range(len(data)):
                    if k in data[i][3]:
                        groups[k].append(i)

            for filtr in filters:
                if filtr[0] == 'promo':
                    value = filtr[2]
                    pop = filtr[1]
                    groups = { k: groups[k] for k in groups if (
                        (pop == 'eq' and value == k) or
                        (pop == 'neq' and value != k)
                    ) }
        elif grouping == 'year':
            groups = {}

            keys = db.exec('SELECT DISTINCT annee FROM Promo')[1]
            for k in keys:
                k = k[0]
                groups[k] = []
                for i in range(len(data)):
                    if data[i][2] == k: groups[k].append(i)
        elif grouping == 'country':
            groups = {}

            keys = db.exec('SELECT DISTINCT pays FROM Adresse')[1]
            for k in keys:
                k = k[0]
                groups[k] = []
                for i in range(len(data)):
                    if data[i][6] == k: groups[k].append(i)

        groups = { k: groups[k] for k in groups.keys() if len(groups[k]) }
        ret, count = {}, { k: len(groups[k]) for k in groups.keys() }
        if operation == 'sum' or operation == 'mean':
            for k in groups.keys():
                ret[k] = 0
                for idx in groups[k]:
                    ret[k] += data[idx][fi]

                if operation == 'mean': ret[k] /= count[k]
        elif operation == 'median':
            for k in groups.keys():
                if count[k] == 1:
                    ret[k] = data[groups[k][0]][fi]
                    continue
                groups[k].sort(key=lambda x, data=data, fi=fi: data[x][fi])

                idx = groups[k][int(count[k]/2)]
                if count[k] % 2 == 0:
                    ret[k] = data[idx][fi]
                elif count[k] % 2 == 1:
                    idx2 = groups[k][int(count[k]/2) + 1]
                    ret[k] = (data[idx][fi] + data[idx2][fi])/2


        return (groups, count, len(data), ret)