import glob
import math
import os
import pandas as pd
import zipfile
import warnings
# ignoro warning di openpyxl
warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")

folder_path = '.'

df_list = []

# scorro tutti i file .xlsx nella cartella
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(folder_path, filename)
        try:
            df = pd.read_excel(file_path, engine='openpyxl')
            df_list.append(df)
        except Exception as e:
            print(f"Errore nel file {filename}: {e}")

# combino tutti i dataframe in uno solo
df = pd.concat(df_list, ignore_index=True)
df['Account'] = df['Account'].astype(str)
#creo il dataframe
df = df[['Account', 'Traffic type','Quantity','Total per unit']]
df["Total per unit"] = pd.to_numeric(df["Total per unit"], errors="coerce")
df = df[df["Total per unit"] > 0]
#associo ogni apikey al cliente
api_keys = {
    "BeCloud Solutions - Alidays Spa": {
        "nome": "Alidays"
    },
    "BeCloud solutions - Arquati Srl.": {
        "nome": "Arquati"
    },
    "Estendo SpA - BeCloud Solutions": {
        "nome": "Estendo"
    },
    "BeCloud Solutions - Farmacie Italiane S.r.l.": {
        "nome": "Farmacie"
    },
    "BeCloud Solutions  - F.IMM Srl.": {
        "nome": "Fimm"
    },
    "3902300821": {
        "nome": "Grenke"
    },
    "CFT SpA - BeCloud Solutions": {
        "nome": "CFT"
    },
    "Lario Reti Holding S.P.A. - BeCloud Solutions": {
        "nome": "LRH"
    },
    "SONOVA ITALIA S.R.L. - BeCloud Solutions": {
        "nome": "Sonova"
    }
}
#creo i dataframes per ogni apikey
accounts = df["Account"].unique()
for account in accounts:
    df['Account'] = df['Account'].replace({account: api_keys[account]["nome"]})
dfs = {sender: group for sender, group in df.groupby('Account')}
dfs = list(dfs.values())
#faccio i conti e stampo
for d in dfs:
    service = d[(d["Traffic type"] != "utility") & (d["Traffic type"] != "marketing")]["Quantity"].sum()
    utility = d[d["Traffic type"] == "utility"]["Quantity"].sum()
    marketing = d[d["Traffic type"] == "marketing"]["Quantity"].sum()
    cost_service = round(d[(d["Traffic type"] != "utility") & (d["Traffic type"] != "marketing")]["Total per unit"].sum(),2)
    cost_utility = round(d[d["Traffic type"] == "utility"]["Total per unit"].sum(),2)
    cost_marketing = round(d[d["Traffic type"] == "marketing"]["Total per unit"].sum(),2)
    cost_all = round(cost_service + cost_utility + cost_marketing,2)
    if d["Account"].iloc[0] == "LRH":
        fatturare = round((service * 0.002100) + (utility * 0.037200) + (marketing * 0.085800),2)
    else:
        fatturare = round((service * 0.002804) + (utility * 0.049600) + (marketing * 0.125840),2)
    print("\033[1m",d["Account"].iloc[0],"\033[0m")
    print("\tService: ", service)
    print("\tTemplates Utility: ", utility)
    print("\tTemplates Marketing: ", marketing)
    print("\tCosto a noi Service: ", cost_service, "€")
    print("\tCosto a noi Utility: ", cost_utility, "€")
    print("\tCosto a noi Marketing: ", cost_marketing, "€")
    print("\tCosto a noi TOT: ", cost_all, "€")
    print("\tFatturare: ", fatturare, "€")