import pandas as pd
from tkinter import Tk
from tkinter.filedialog import askopenfilenames
import os

# Open a Tk window
Tk().withdraw()

# Prompt the user to select input files
input_files = askopenfilenames(title="Select input files", filetypes=[("Excel Files", "*.xlsx")])

# Prompt the user to select the “Staff Gl Mapping.xlsx” file
mapping_file = askopenfilenames(title="Select Staff Gl Mapping file", filetypes=[("Excel Files", ".xlsx")])

# Prompt the user to select the “Employees Details Report old.xlsx” file
employees_file = askopenfilenames(title="Select Employees Details Report file", filetypes=[("Excel Files", ".xlsx")])

# Read the mapping file
mapping_df = pd.read_excel(mapping_file[0])

# Read the employees details file
employees_df = pd.read_excel(employees_file[0])
        
# Create an empty dataframe to store merged data
merged_df = pd.DataFrame()

# Read and merge the input files
for file in input_files:
    df = pd.read_excel(file)
    df["Project Description"] = df["Name"].str.split("|").str[2]
    df["Worker Name"] = df["Name"].str.split("|").str[4]
    df["Current"] = df["Debit"] - df["Credit"]
    df = df.rename(columns={"Closing balance": "Cumulative"})
    file_name, _ = os.path.splitext(os.path.basename(file))
    df["Date"] = file_name
    
print(mapping_df.columns)
# Merge with the mapping file based on “Main account” column
df = pd.merge(df, mapping_df[["Main account", "Name y"]], left_on="MainAccount", right_on="Main account", how="left")
merged_df = pd.concat([merged_df, df])   

# Merge with the employees details file based on “Worker Name” and “Personal Number” columns
merged_df = pd.merge(merged_df, employees_df[["Worker Name", "Position", "Description_4"]], on=["Worker Name"], how="left")
#merged_df = merged_df.drop_duplicates(subset=["Worker Name"])
# Remove the unwanted columns
unwanted_cols = ["MainAccount", "Division", "Site", "Name", "Opening balance","Main account"]
merged_df = merged_df.drop(columns=unwanted_cols)

# Rename the columns
merged_df = merged_df.rename(columns={
"Projects": "Project ID",
"Worker": "Worker ID",
"Name y": "Name",
"Description_4": "Project",
"Position_y": "Position"
})

print(merged_df.columns)
# Reorder the columns
new_order = ["Project ID", "Project Description", "Date", "Worker ID", "Worker Name", "Name", "Current", "Cumulative", "Position", "Project"]
merged_df = merged_df[new_order]


# Write the merged dataframe to an output Excel file
output_file = "merged_data.xlsx"
merged_df.to_excel(output_file, index=False)

print("Merged dataframe has been written to", output_file)
# Merge with the mapping file based on “Main account” column
df = pd.merge(df, mapping_df[["Main account", "Name y"]], left_on="MainAccount", right_on="Main account", how="left")
merged_df = pd.concat([merged_df, df])   

# Merge with the employees details file based on “Worker Name” and “Personal Number” columns
merged_df = pd.merge(merged_df, employees_df[["Worker Name", "Position", "Description_4"]], on=["Worker Name"], how="left")
#merged_df = merged_df.drop_duplicates(subset=["Worker Name"])
# Remove the unwanted columns
unwanted_cols = ["MainAccount", "Division", "Site", "Name", "Opening balance", "Main account"]
merged_df = merged_df.drop(columns=unwanted_cols)

# Rename the columns
merged_df = merged_df.rename(columns={
"Projects": "Project ID",
"Worker": "Worker ID",
"Name y": "Name",
"Description_4": "Project",
"Position_y": "Position"
})

print(merged_df.columns)
# Reorder the columns
new_order = ["Project ID", "Project Description", "Date", "Worker ID", "Worker Name", "Name", "Current", "Cumulative", "Position", "Project"]
merged_df = merged_df[new_order]

# Concatenate the “Position” values for each “Worker Name”
#merged_df = merged_df.groupby("Worker Name").agg({"Position": ", ".join}).reset_index()

# Write the merged dataframe to an output Excel file
output_file = "merged_data.xlsx"
merged_df.to_excel(output_file, index=False)

print("Merged dataframe has been written to", output_file)