import pandas as pd
import argparse

def modify_column(base_excel, sheet_base, field_to_modify, text_before, text_after, output_excel, output_sheet):
    # Read the base Excel sheet into a DataFrame
    df_base = pd.read_excel(base_excel, sheet_name=sheet_base)

    # Modify the specified column by adding text before the existing content
    df_base[field_to_modify] = text_before + df_base[field_to_modify].astype(str) + text_after

    # Write the modified DataFrame to the output Excel file
    with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:
        df_base.to_excel(writer, sheet_name=output_sheet, index=False)



def compareAndAdd(base_file, sheet_base, field_base, compare_file, sheet_compare, field_compare, field_to_add, output_excel, output_sheet):
    # Read the base Excel sheet into a DataFrame
    df_base = pd.read_excel(base_file, sheet_name=sheet_base)

    # Read the compare Excel sheet into a DataFrame
    df_compare = pd.read_excel(compare_file, sheet_name=sheet_compare)

    # Create a mapping of field_compare to field_to_add from the compare DataFrame
    compare_mapping = df_compare.set_index(field_compare)[field_to_add].to_dict()

    # Add the field_to_add to the base DataFrame
    df_base[field_to_add] = df_base[field_base].map(compare_mapping)

    # Write the combined DataFrame to the output Excel file
    with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:
        df_base.to_excel(writer, sheet_name=output_sheet, index=False)

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description='Compare and add fields or modify a column in an Excel file.')
    subparsers = parser.add_subparsers(dest='command', help='Sub-command help')

    parser_compare = subparsers.add_parser('compareAndAdd', help='Compare and add fields from one Excel file to another.')
    parser_compare.add_argument('base_file', help='The path to the base file')
    parser_compare.add_argument('sheet_base', help='The sheet name in the base Excel file', default='Sheet1')
    parser_compare.add_argument('field_base', help='The field name in the base file to compare')
    parser_compare.add_argument('compare_file', help='The path to the compare file')
    parser_compare.add_argument('sheet_compare', help='The sheet name in the compare Excel file', default='Sheet1')
    parser_compare.add_argument('field_compare', help='The field name in the compare file to compare')
    parser_compare.add_argument('field_to_add', help='The field name in the compare file to add to the base file')
    parser_compare.add_argument('output_file', help='The path to the output file')
    parser_compare.add_argument('output_sheet', help='The sheet name for the output Excel file')
    

    # Subparser for the modify_column function
    parser_modify = subparsers.add_parser('modify', help='Modify a column in an Excel file by adding text before existing content.')
    parser_modify.add_argument('base_excel', help='The path to the base Excel file')
    parser_modify.add_argument('sheet_base', help='The sheet name in the base Excel file')
    parser_modify.add_argument('field_to_modify', help='The field (column) to modify')
    parser_modify.add_argument('text_before', help='The text to add before the existing content')
    parser_modify.add_argument('text_after', help='The text to add after the existing content')
    parser_modify.add_argument('output_excel', help='The path to the output Excel file')
    parser_modify.add_argument('output_sheet', help='The sheet name for the output Excel file')


    args = parser.parse_args()

    if args.command == 'compareAndAdd':
        compareAndAdd(
            args.base_file,
            args.sheet_base,
            args.field_base,
            args.compare_file,
            args.sheet_compare,
            args.field_compare,
            args.field_to_add,
            args.output_file,
            args.output_sheet
        )

    elif args.command == 'modify':
        modify_column(
            args.base_excel,
            args.sheet_base,
            args.field_to_modify,
            args.text_before,
            args.text_after,
            args.output_excel,
            args.output_sheet
        )