import pandas as pd
import os
import glob
import sys

def merge_excel_files(input_directory, output_filename="merged_output.xlsx"):
    """
    Merges all .xlsx files in the specified directory into a single Excel file,
    with each original file becoming a sheet named after the original filename.

    Args:
        input_directory (str): The path to the directory containing the Excel files.
        output_filename (str): The name of the output merged Excel file.
    """

    excel_files = glob.glob(os.path.join(input_directory, "**/*.xlsx"), recursive=True)


    if not excel_files:
        print(f"No .xlsx files found in '{input_directory}'.")
        return

    print(f"Found {len(excel_files)} Excel files to merge:")
    for f in excel_files:
        print(f"- {os.path.basename(f)}")

    output_path = os.path.join(input_directory, output_filename)

    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        for excel_file in excel_files:
            try:
                df = pd.read_excel(excel_file)
                sheet_name = os.path.splitext(os.path.basename(excel_file))[0]
                df.to_excel(writer, sheet_name=sheet_name, index=False)
                print(f"Successfully added '{os.path.basename(excel_file)}' as sheet '{sheet_name}'.")
            except Exception as e:
                print(f"Error processing '{os.path.basename(excel_file)}': {e}")

    print(f"All Excel files merged into '{output_path}'.")

if __name__ == "__main__":
    if len(sys.argv) > 1:
        input_dir = sys.argv[1]
    else:
        input_dir = os.getcwd()

    directory_name = os.path.basename(input_dir)
    output_filename = f"{directory_name}.xlsx"
    merge_excel_files(input_dir, output_filename=output_filename)