import copy
import datetime
import random
import os

import pandas
import requests
import xlsxwriter
from progress.bar import IncrementalBar

EXCEL_DIR = "C:/Daten/RunningDinner/running_dinner/running_dinner.xlsx"

# Set the base url for the Google Maps Distance Matrix API
base_url = "https://maps.googleapis.com/maps/api/distancematrix/json?"
# Google Maps API Key
api_key = os.environ["MAPS_API_KEY"]

# Excel Sheet muss 7 Spalten haben:
# Zeitstempel (Erzeugt von Google Forms, wird in Zeile 30 entfernt) | Name |   | Zimmergröße | Handynummer | Adresse | Entfernung zu Afterparty
# Spalte mit Namen muss 'Namen' als Header haben

# Maximale Distanz von Nachspeise zu Afterparty
max_dist_to_ap = 2

rd = pandas.read_excel(EXCEL_DIR)
appendants = rd.set_index("Name").T.to_dict("list")

addresses = []
address_indices = []
for i, app in enumerate(appendants):
    appendants[app].pop(0)
    if "passen" in appendants[app][1]: # Zimmergröße sehr gut
        appendants[app][1] = 2
    elif "gehen" in appendants[app][1]: # Zimmergröße gut
        appendants[app][1] = 1
    else: # Zimmergröße passt nicht
        appendants[app][1] = 0
    if type(appendants[app][3]) == str:
        adr = appendants[app][3]
        addresses.append(adr)
        address_indices.append(i)

names = list(appendants.keys())
# print(appendants['Fabio Rodrigues'])

ind = 0
distances = []
for _ in range(len(names)):
    distances.append([float("inf")] * len(names))

for i, adr in enumerate(addresses):
    addresses_wo_adr = addresses.copy()
    addresses_wo_adr.pop(i)
    address_indices_wo_adr = address_indices.copy()
    address_indices_wo_adr.pop(i)
    # Build the request parameters
    params = {
        "origins": [adr],
        "destinations": "|".join(addresses_wo_adr),
        "key": api_key,
        "mode": "bicycling",
    }
    # Make the request to the Google Maps Distance Matrix API
    response = requests.get(base_url, params=params)
    # Extract the distance matrix from the response
    distance_matrix = response.json()["rows"][0]["elements"]

    distances[address_indices[i]][address_indices[i]] = 0.0
    for j in range(len(distance_matrix)):
        distances[address_indices[i]][address_indices_wo_adr[j]] = distance_matrix[j][
            "duration"
        ]["value"]

top1_max_distance = float("inf")
top1_total_distance = float("inf")
top1_teams = {}
top1_loc = {}
top2_max_distance = float("inf")
top2_total_distance = float("inf")
top2_teams = {}
top2_loc = {}
top3_max_distance = float("inf")
top3_total_distance = float("inf")
top3_teams = {}
top3_loc = {}
top4_max_distance = float("inf")
top4_total_distance = float("inf")
top4_teams = {}
top4_loc = {}
top5_max_distance = float("inf")
top5_total_distance = float("inf")
top5_teams = {}
top5_loc = {}

iterations = 1000

bar = IncrementalBar("Searching optimal Route", max=iterations)
for _ in range(iterations):
    searching = True
    while searching == True:
        random.shuffle(names)

        teams = dict()
        for i in range(len(names)):
            if i % 2 == 0 and i < len(names) - (len(names) % 6):
                teams["Team " + str(int(i / 2 + 1))] = {
                    "Namen": [names[i], names[i + 1]]
                }
            if i >= len(names) - (len(names) % 6):
                teams["Team " + str(i - (len(names) - (len(names) % 6)) + 1)][
                    "Namen"
                ].append(names[i])

        for t in teams:
            teams[t]["Location"] = []
            for n in teams[t]["Namen"]:
                if type(appendants[n][3]) == str:
                    teams[t]["Location"].append(appendants[n][3])

        num_teams = len(teams)
        t = list(range(1, num_teams + 1))
        num_groups = int(num_teams / 3)
        Hauptspeisen = t[0:num_groups]
        Vorspeisen = t[num_groups : num_groups * 2]
        Nachspeisen = t[num_groups * 2 : num_groups * 3]

        for i in Vorspeisen + Hauptspeisen + Nachspeisen:
            if i in Vorspeisen:
                index = Vorspeisen.index(i)
                teams["Team " + str(i)]["Gang"] = "Vorspeise"
                teams["Team " + str(i)]["Gäste"] = [
                    "Team " + str(Hauptspeisen[index]),
                    "Team " + str(Nachspeisen[index]),
                ]
                teams["Team " + str(i)]["Route"] = ["Team " + str(i)]
                teams["Team " + str(Hauptspeisen[index])]["Route"] = ["Team " + str(i)]
                teams["Team " + str(Nachspeisen[index])]["Route"] = ["Team " + str(i)]
                teams["Team " + str(i)]["Unverträglichkeiten der Gäste"] = []
                for g in teams["Team " + str(i)]["Gäste"]:
                    for n in teams[g]["Namen"]:
                        teams["Team " + str(i)]["Unverträglichkeiten der Gäste"].append(
                            appendants[n][0]
                        )
            elif i in Hauptspeisen:
                index = Hauptspeisen.index(i)
                teams["Team " + str(i)]["Gang"] = "Hauptspeise"
                teams["Team " + str(i)]["Gäste"] = [
                    "Team " + str(Vorspeisen[(index - 1) % num_groups]),
                    "Team " + str(Nachspeisen[(index + 1) % num_groups]),
                ]
                teams["Team " + str(i)]["Route"].append("Team " + str(i))
                teams["Team " + str(Vorspeisen[(index - 1) % num_groups])][
                    "Route"
                ].append("Team " + str(i))
                teams["Team " + str(Nachspeisen[(index + 1) % num_groups])][
                    "Route"
                ].append("Team " + str(i))
                teams["Team " + str(i)]["Unverträglichkeiten der Gäste"] = []
                for g in teams["Team " + str(i)]["Gäste"]:
                    for n in teams[g]["Namen"]:
                        teams["Team " + str(i)]["Unverträglichkeiten der Gäste"].append(
                            appendants[n][0]
                        )
            elif i in Nachspeisen:
                index = Nachspeisen.index(i)
                teams["Team " + str(i)]["Gang"] = "Nachspeise"
                teams["Team " + str(i)]["Gäste"] = [
                    "Team " + str(Vorspeisen[(index - 1) % num_groups]),
                    "Team " + str(Hauptspeisen[(index + 1) % num_groups]),
                ]
                teams["Team " + str(i)]["Route"].append("Team " + str(i))
                teams["Team " + str(Vorspeisen[(index - 1) % num_groups])][
                    "Route"
                ].append("Team " + str(i))
                teams["Team " + str(Hauptspeisen[(index + 1) % num_groups])][
                    "Route"
                ].append("Team " + str(i))
                teams["Team " + str(i)]["Unverträglichkeiten der Gäste"] = []
                for g in teams["Team " + str(i)]["Gäste"]:
                    for n in teams[g]["Namen"]:
                        teams["Team " + str(i)]["Unverträglichkeiten der Gäste"].append(
                            appendants[n][0]
                        )

        searching = False
        for t in teams:
            size = 0
            for n in teams[t]["Namen"]:
                size = size + appendants[n][1]
            if size == 0:
                # print(str(teams[t]['Namen']) + ' Raum zu klein')
                searching = True
                break

            max_room = 0
            for n in teams[t]["Namen"]:
                max_room = max(max_room, appendants[n][1])

            dist = 6
            for n in teams[t]["Namen"]:
                if appendants[n][1] == max_room:
                    dist = min(dist, int(appendants[n][4]))
            if dist > max_dist_to_ap and teams[t]["Gang"] == "Nachspeise":
                # print(str(teams[t]['Namen']) + ' Nachspeise, aber zu weit von Afterparty entfernt')
                searching = True
                break

            # if 'Fabio Rodrigues' in teams[t]['Namen'] and len(teams[t]['Namen']) == 3:
            #     print('Fabio in 3er Gruppe')
            #     searching = True
            #     break

            # if 'Fabio Rodrigues' in teams[t]['Namen'] and teams[t]['Gang'] != 'Nachspeise':
            #     print('Fabio macht nicht Nachspeise')
            #     searching = True
            #     break

            # if 'Fabio Rodrigues' in teams[t]['Namen'] and 'Alex Peeters' not in teams[t]['Namen']:
            #     print('Fabio nicht mit Alex')
            #     searching = True
            #     break

    possible_location_permutations = 1
    number_locations = []
    for t in teams:
        n_loc = len(teams[t]["Location"])
        number_locations.append(n_loc)
        possible_location_permutations *= n_loc

    perm = [0] * len(number_locations)
    for j in range(possible_location_permutations):
        if j > 0:
            i = 0
            adding = True
            while adding:
                if perm[i] + 1 < number_locations[i]:
                    perm[i] += 1
                    adding = False
                elif perm[i] + 1 == number_locations[i] and perm[i] > 0:
                    perm[i] = 0
                i += 1
        loc = {}
        for k, l in enumerate(teams):
            loc[l] = perm[k]

        max_distance = 0.0
        total_distance = 0.0
        for t in teams:
            way1 = distances[
                address_indices[
                    addresses.index(
                        teams[teams[t]["Route"][0]]["Location"][
                            loc[teams[t]["Route"][0]]
                        ]
                    )
                ]
            ][
                address_indices[
                    addresses.index(
                        teams[teams[t]["Route"][1]]["Location"][
                            loc[teams[t]["Route"][1]]
                        ]
                    )
                ]
            ]
            way2 = distances[
                address_indices[
                    addresses.index(
                        teams[teams[t]["Route"][1]]["Location"][
                            loc[teams[t]["Route"][1]]
                        ]
                    )
                ]
            ][
                address_indices[
                    addresses.index(
                        teams[teams[t]["Route"][2]]["Location"][
                            loc[teams[t]["Route"][2]]
                        ]
                    )
                ]
            ]
            way3 = distances[
                address_indices[
                    addresses.index(
                        teams[teams[t]["Route"][2]]["Location"][
                            loc[teams[t]["Route"][2]]
                        ]
                    )
                ]
            ][
                address_indices[
                    addresses.index("Sebastian-Kneipp-Straße 6, 76131 Karlsruhe")
                ]
            ]
            team_distance = way1 + way2 + way3
            total_distance += team_distance
            max_distance = max(max_distance, way1, way2)
            teams[t]["Zeit"] = str(datetime.timedelta(seconds=team_distance))

        if total_distance < top1_total_distance:
            top5_max_distance = top4_max_distance
            top5_total_distance = top4_total_distance
            top5_teams = copy.deepcopy(top4_teams)
            top5_loc = copy.deepcopy(top4_loc)

            top4_max_distance = top3_max_distance
            top4_total_distance = top3_total_distance
            top4_teams = copy.deepcopy(top3_teams)
            top4_loc = copy.deepcopy(top3_loc)

            top3_max_distance = top2_max_distance
            top3_total_distance = top2_total_distance
            top3_teams = copy.deepcopy(top2_teams)
            top3_loc = copy.deepcopy(top2_loc)

            top2_max_distance = top1_max_distance
            top2_total_distance = top1_total_distance
            top2_teams = copy.deepcopy(top1_teams)
            top2_loc = copy.deepcopy(top1_loc)

            top1_max_distance = max_distance
            top1_total_distance = total_distance
            top1_teams = copy.deepcopy(teams)
            top1_loc = copy.deepcopy(loc)

        elif total_distance < top2_total_distance:
            top5_max_distance = top4_max_distance
            top5_total_distance = top4_total_distance
            top5_teams = copy.deepcopy(top4_teams)
            top5_loc = copy.deepcopy(top4_loc)

            top4_max_distance = top3_max_distance
            top4_total_distance = top3_total_distance
            top4_teams = copy.deepcopy(top3_teams)
            top4_loc = copy.deepcopy(top3_loc)

            top3_max_distance = top2_max_distance
            top3_total_distance = top2_total_distance
            top3_teams = copy.deepcopy(top2_teams)
            top3_loc = copy.deepcopy(top2_loc)

            top2_max_distance = max_distance
            top2_total_distance = total_distance
            top2_teams = copy.deepcopy(teams)
            top2_loc = copy.deepcopy(loc)

        elif total_distance < top3_total_distance:
            top5_max_distance = top4_max_distance
            top5_total_distance = top4_total_distance
            top5_teams = copy.deepcopy(top4_teams)
            top5_loc = copy.deepcopy(top4_loc)

            top4_max_distance = top3_max_distance
            top4_total_distance = top3_total_distance
            top4_teams = copy.deepcopy(top3_teams)
            top4_loc = copy.deepcopy(top3_loc)

            top3_max_distance = max_distance
            top3_total_distance = total_distance
            top3_teams = copy.deepcopy(teams)
            top3_loc = copy.deepcopy(loc)

        elif total_distance < top4_total_distance:
            top5_max_distance = top4_max_distance
            top5_total_distance = top4_total_distance
            top5_teams = copy.deepcopy(top4_teams)
            top5_loc = copy.deepcopy(top4_loc)

            top4_max_distance = max_distance
            top4_total_distance = total_distance
            top4_teams = copy.deepcopy(teams)
            top4_loc = copy.deepcopy(loc)

        elif total_distance < top5_total_distance:
            top5_max_distance = max_distance
            top5_total_distance = total_distance
            top5_teams = copy.deepcopy(teams)
            top5_loc = copy.deepcopy(loc)

    bar.next()
bar.finish()

for t in top5_teams:
    top5_teams[t]["Location"] = top5_teams[t]["Location"][top5_loc[t]]
for t in top4_teams:
    top4_teams[t]["Location"] = top4_teams[t]["Location"][top4_loc[t]]
for t in top3_teams:
    top3_teams[t]["Location"] = top3_teams[t]["Location"][top3_loc[t]]
for t in top2_teams:
    top2_teams[t]["Location"] = top2_teams[t]["Location"][top2_loc[t]]
for t in top1_teams:
    top1_teams[t]["Location"] = top1_teams[t]["Location"][top1_loc[t]]

print("######################################################")

print("Top 5 Team:")
print(top3_teams)
print(
    "The max time of one single route is: {}".format(
        str(datetime.timedelta(seconds=top5_max_distance))
    )
)
print(
    "The total time spent on bike is: {}".format(
        str(datetime.timedelta(seconds=top5_total_distance))
    )
)

print("Top 4 Team:")
print(top3_teams)
print(
    "The max time of one single route is: {}".format(
        str(datetime.timedelta(seconds=top4_max_distance))
    )
)
print(
    "The total time spent on bike is: {}".format(
        str(datetime.timedelta(seconds=top4_total_distance))
    )
)

print("Top 3 Team:")
print(top3_teams)
print(
    "The max time of one single route is: {}".format(
        str(datetime.timedelta(seconds=top3_max_distance))
    )
)
print(
    "The total time spent on bike is: {}".format(
        str(datetime.timedelta(seconds=top3_total_distance))
    )
)

print("Top 2 Team:")
print(top2_teams)
print(
    "The max time of one single route is: {}".format(
        str(datetime.timedelta(seconds=top2_max_distance))
    )
)
print(
    "The total time spent on bike is: {}".format(
        str(datetime.timedelta(seconds=top2_total_distance))
    )
)

print("Top 1 Team:")
print(top1_teams)
print(
    "The max time of one single route is: {}".format(
        str(datetime.timedelta(seconds=top1_max_distance))
    )
)
print(
    "The total time spent on bike is: {}".format(
        str(datetime.timedelta(seconds=top1_total_distance))
    )
)

print("Type 1, 2, 3, 4 or 5 to choose the teams configuration:")
inp = int(input())

if inp == 1:
    print("You chose the 1st option.")
    teams = copy.deepcopy(top1_teams)
elif inp == 2:
    print("You chose the 2nd option.")
    teams = copy.deepcopy(top2_teams)
elif inp == 3:
    print("You chose the 3rd option.")
    teams = copy.deepcopy(top3_teams)
elif inp == 4:
    print("You chose the 4th option.")
    teams = copy.deepcopy(top4_teams)
elif inp == 5:
    print("You chose the 5th option.")
    teams = copy.deepcopy(top5_teams)
else:
    print("Input could not be resolved, choosing number 1.")
    teams = copy.deepcopy(top1_teams)

for t in teams:
    if "Fabio Rodrigues" in teams[t]["Namen"]:
        print(t + ": " + str(teams[t]["Namen"]) + " machen " + str(teams[t]["Gang"]))
    if "Arnold Resch" in teams[t]["Namen"]:
        print(t + ": " + str(teams[t]["Namen"]) + " machen " + str(teams[t]["Gang"]))

print("######################################################")

# Creating xlsx file

workbook = xlsxwriter.Workbook("running_dinner_masterplan.xlsx")
bold = workbook.add_format({"bold": True})

worksheet = workbook.add_worksheet("Übersicht")
worksheet.set_column(0, 0, 20)
worksheet.set_column(1, 1, 30)
worksheet.set_column(2, 2, 20)
worksheet.set_column(3, 3, 40)
worksheet.set_column(4, 4, 80)

row = 0
col = 0

header = workbook.add_format(
    {
        "bold": 1,
        "border": 1,
        "align": "center",
        "valign": "vcenter",
        "fg_color": "yellow",
    }
)
worksheet.merge_range("A1:E1", "Der große Running-Dinner Masterplan", header)
row += 2

worksheet.write(row, col, "Teams", bold)
worksheet.write(row, col + 1, "Namen", bold)
worksheet.write(row, col + 2, "Telefonnummer", bold)
worksheet.write(row, col + 3, "Adresse", bold)
worksheet.write(row, col + 4, "Unverträglichkeiten", bold)
row += 1
for t in teams:
    worksheet.write(row, col, t, bold)
    worksheet.write(row + 1, col, teams[t]["Gang"])
    for name in teams[t]["Namen"]:
        worksheet.write(row, col + 1, name)
        worksheet.write(row, col + 2, appendants[name][2])
        if type(appendants[name][3]) == str:
            worksheet.write(row, col + 3, appendants[name][3])
        worksheet.write(row, col + 4, appendants[name][0])
        row += 1

row += 3
worksheet.merge_range("A" + str(row) + ":E" + str(row), "Die Routen", header)

row += 1
worksheet.write(row, col, "18:30 Uhr", bold)
worksheet.write(row + 6, col, "20:00 Uhr", bold)
worksheet.write(row + 12, col, "22:00 Uhr", bold)


row += 1
vor_g = 1
haupt_g = 1
nach_g = 1
for t in teams:
    if teams[t]["Gang"] == "Vorspeise":
        worksheet.write(row, col + vor_g - 1, "Vorspeise Gruppe " + str(vor_g), bold)
        worksheet.write(row + 1, col + vor_g - 1, t, bold)
        for i, g in enumerate(teams[t]["Gäste"]):
            worksheet.write(row + 2 + i, col + vor_g - 1, g)
        vor_g += 1
    elif teams[t]["Gang"] == "Hauptspeise":
        worksheet.write(
            row + 6, col + haupt_g - 1, "Hauptspeise Gruppe " + str(haupt_g), bold
        )
        worksheet.write(row + 7, col + haupt_g - 1, t, bold)
        for i, g in enumerate(teams[t]["Gäste"]):
            worksheet.write(row + 8 + i, col + haupt_g - 1, g)
        haupt_g += 1
    elif teams[t]["Gang"] == "Nachspeise":
        worksheet.write(
            row + 12, col + nach_g - 1, "Nachspeise Gruppe " + str(nach_g), bold
        )
        worksheet.write(row + 13, col + nach_g - 1, t, bold)
        for i, g in enumerate(teams[t]["Gäste"]):
            worksheet.write(row + 14 + i, col + nach_g - 1, g)
        nach_g += 1


for t in teams:
    worksheet = workbook.add_worksheet(t)
    worksheet.set_column(0, 0, 60)
    worksheet.set_column(1, 1, 100)
    row = 0
    col = 0

    worksheet.merge_range("A1:B1", t, header)
    row += 2

    for n in [
        "Namen",
        "Gang",
        "Location",
        "Route",
        "Gäste",
        "Unverträglichkeiten der Gäste",
        "Zeit",
    ]:
        if n == "Namen":
            for i, name in enumerate(teams[t][n]):
                if i == 0:
                    text = name
                else:
                    text += " und " + name
            worksheet.write(row, col, "Namen", bold)
            worksheet.write(row, col + 1, text)
            row += 1
        elif n == "Gang":
            worksheet.write(row, col, "Euer Gang", bold)
            worksheet.write(row, col + 1, teams[t][n])
            row += 1
        elif n == "Location":
            worksheet.write(row, col, "Location (automatisch optimal gewählt)", bold)
            worksheet.write(row, col + 1, teams[t][n])
            row += 1
        elif n == "Gäste":
            for i, gaeste in enumerate(teams[t][n]):
                if i == 0:
                    text = gaeste
                else:
                    text += " und " + gaeste
            worksheet.write(row, col, "Eure Gäste", bold)
            worksheet.write(row, col + 1, text)
            row += 1
            for i, gaeste in enumerate(teams[t][n]):
                for j, namen in enumerate(teams[gaeste]["Namen"]):
                    if i == 0 and j == 0:
                        text = namen
                    elif (
                        i == len(teams[t][n]) - 1
                        and j == len(teams[gaeste]["Namen"]) - 1
                    ):
                        text += " und " + namen
                    else:
                        text += ", " + namen
            worksheet.write(row, col + 1, text)
            row += 2
        elif n == "Route":
            for i, route in enumerate(teams[t][n]):
                if i == 0:
                    text = "Zuerst bei " + route
                else:
                    text += ", dann bei " + route
            worksheet.write(row, col, "Eure Route", bold)
            worksheet.write(row, col + 1, text)
            row += 2
        elif n == "Unverträglichkeiten der Gäste":
            for i, unv in enumerate(teams[t][n]):
                if i == 0:
                    text = unv
                else:
                    text += " und " + unv
            worksheet.write(row, col, "Achtet auf diese Eigenarten eurer Gäste", bold)
            worksheet.write(row, col + 1, text)
            row += 2
        elif n == "Zeit":
            worksheet.write(
                row,
                col,
                "Gesamtzeit für eure Route von Vorspeise bis Afterparty (per Fahrrad)",
                bold,
            )
            worksheet.write(row, col + 1, teams[t][n])

workbook.close()