import pandas as pd
import os
import glob
import sys
import numpy as np
# openpyxl is used by Pandas for .xlsx files, and we'll use its utility for column letters
from openpyxl.utils import get_column_letter

def auto_adjust_column_width(worksheet, df, start_col_offset=0):
    """
    Auto-adjusts the column widths for a given worksheet.
    :param worksheet: The openpyxl worksheet object.
    :param df: The DataFrame that was written to this part of the sheet.
    :param start_col_offset: The starting column index (0-based) where df was written.
    """
    for col_idx, column in enumerate(df.columns):
        column_letter = get_column_letter(col_idx + 1 + start_col_offset) # openpyxl is 1-indexed
        max_length = 0
        
        # Check header length
        column_header = str(column)
        if len(column_header) > max_length:
            max_length = len(column_header)
            
        # Check cell content lengths
        # Need to handle potential NaNs or non-string types gracefully
        for cell_value in df[column]:
            try:
                cell_str = str(cell_value)
                if len(cell_str) > max_length:
                    max_length = len(cell_str)
            except:
                pass # Ignore errors in converting cell value to string for length check
                
        adjusted_width = (max_length + 2) # Add a little padding
        worksheet.column_dimensions[column_letter].width = adjusted_width

def process_excel_file(input_filename):
    print(f"\n--- Processing file: {input_filename} ---")
    sys.stdout.flush() # Ensure this print is displayed immediately
    try:
        xls = pd.ExcelFile(input_filename)
        sheet_names = xls.sheet_names
    except FileNotFoundError:
        print(f"Error: The file '{input_filename}' was not found.")
        sys.stdout.flush()
        return
    except Exception as e:
        print(f"Error reading Excel file '{input_filename}': {e}")
        sys.stdout.flush()
        return

    all_data_frames = []
    header_row_index = 6
    key_column_name = 'PRODUCTION ORDER' # This is the clean, stripped version
    column_to_drop = "S.No" # This is the clean, stripped version

    if not sheet_names:
        print(f"No sheets found in '{input_filename}'. Skipping this file.")
        sys.stdout.flush()
        return

    for sheet_name in sheet_names:
        try:
            df = pd.read_excel(xls, sheet_name=sheet_name, header=header_row_index)
            # Strip whitespace from column names for the current sheet's DataFrame
            df.columns = [str(col).strip() for col in df.columns]

            if df.empty:
                print(f"Sheet '{sheet_name}' in '{input_filename}' is empty. Skipping this sheet.")
                sys.stdout.flush()
                continue

            if all(str(col).startswith('Unnamed:') for col in df.columns):
                print(f"Warning: Sheet '{sheet_name}' in '{input_filename}' seems to have no valid header at row {header_row_index + 1}, or is empty below it. Columns found: {df.columns.tolist()}. Skipping this sheet.")
                sys.stdout.flush()
                continue

            if key_column_name not in df.columns:
                print(f"Warning: Column '{key_column_name}' not found in sheet '{sheet_name}' of file '{input_filename}' (available columns: {df.columns.tolist()}). Skipping data from this sheet.")
                sys.stdout.flush()
                continue

            original_row_count = len(df)
            # Ensure key column is treated as string for stripping, then check notna on original
            condition = df[key_column_name].notna() & (df[key_column_name].astype(str).str.strip() != '')
            df_filtered = df[condition]
            filtered_row_count = len(df_filtered)

            if filtered_row_count == 0:
                print(f"Sheet '{sheet_name}': No rows found with a value in '{key_column_name}' column (originally {original_row_count} rows). Skipping data from this sheet.")
                sys.stdout.flush()
                continue

            print(f"Sheet '{sheet_name}': Kept {filtered_row_count} rows out of {original_row_count} based on '{key_column_name}' column.")
            sys.stdout.flush()

            df_to_append = df_filtered.copy()
            df_to_append.insert(0, "DATE", sheet_name)
            all_data_frames.append(df_to_append)
            print(f"Successfully processed and added data from sheet: {sheet_name}")
            sys.stdout.flush()

        except Exception as e:
            print(f"Error processing sheet '{sheet_name}' in file '{input_filename}': {e}")
            sys.stdout.flush()

    if not all_data_frames:
        print(f"No data was successfully processed and filtered from any sheet in '{input_filename}'. No output file will be generated for this input.")
        sys.stdout.flush()
        return

    try:
        combined_df = pd.concat(all_data_frames, ignore_index=True)
        # Strip whitespace from column names for the combined DataFrame as a safeguard
        combined_df.columns = [str(col).strip() for col in combined_df.columns]
        print(f"Normalized column names for combined_df from '{input_filename}'.")
        sys.stdout.flush()

    except Exception as e:
        print(f"Error combining dataframes for '{input_filename}': {e}")
        sys.stdout.flush()
        return

    if column_to_drop in combined_df.columns:
        try:
            combined_df.drop(columns=[column_to_drop], inplace=True)
            print(f"Successfully dropped column: '{column_to_drop}' from the combined data.")
            sys.stdout.flush()
        except Exception as e:
            print(f"An error occurred while trying to drop column '{column_to_drop}': {e}")
            sys.stdout.flush()
    else:
        print(f"Column '{column_to_drop}' not found in the combined data (available: {combined_df.columns.tolist()}). No column dropped.")
        sys.stdout.flush()


    pivot_table_df_1_output = None 
    pivot_table_df_2_output = None 
    
    scrap_perct_col_name = "scrap perct" 
    scrap_item_perct_col_name = "Scrap_item_perct"


    base_filename = os.path.basename(input_filename)
    if "mecatherm".lower() in base_filename.lower():
        print(f"File '{base_filename}' identified as a Mecatherm file. Proceeding with scrap calculations and pivot tables.")
        sys.stdout.flush()


        cutting_scrap_col = "Cutting scrap"
        other_scrap_col = "Other scrap"
        total_batch_weight_col = "Total Batch Weight (Kg)"
        item_code_col = "ITEM CODE"
        item_desc_col = "ITEM DESCRIPTION"


        required_cols_for_scrap_calc = [cutting_scrap_col, other_scrap_col, total_batch_weight_col]
        missing_scrap_calc_cols = [col for col in required_cols_for_scrap_calc if col not in combined_df.columns]

        if missing_scrap_calc_cols:
            print(f"Warning: For Mecatherm file '{base_filename}', missing columns for '{scrap_perct_col_name}' calculation: {missing_scrap_calc_cols} (available: {combined_df.columns.tolist()}). '{scrap_perct_col_name}' will be 0.")
            sys.stdout.flush()
            combined_df[scrap_perct_col_name] = 0.0 
        else:
            combined_df[cutting_scrap_col] = pd.to_numeric(combined_df[cutting_scrap_col], errors='coerce').fillna(0)
            combined_df[other_scrap_col] = pd.to_numeric(combined_df[other_scrap_col], errors='coerce').fillna(0)
            combined_df[total_batch_weight_col] = pd.to_numeric(combined_df[total_batch_weight_col], errors='coerce')

            sum_scraps_row_level = combined_df[cutting_scrap_col] + combined_df[other_scrap_col]
            combined_df[scrap_perct_col_name] = np.where(
                (combined_df[total_batch_weight_col].isna()) | (combined_df[total_batch_weight_col] == 0),
                0.0, 
                sum_scraps_row_level / combined_df[total_batch_weight_col]
            )
            combined_df[scrap_perct_col_name] = combined_df[scrap_perct_col_name].fillna(0.0) 
            print(f"Successfully calculated raw numeric '{scrap_perct_col_name}' column (0-1 range) for '{base_filename}'.")
            sys.stdout.flush()


        pivot1_row_columns = ['PRODUCTION ORDER', item_code_col, item_desc_col, 'MIXES DETAILS']
        valid_pivot1_row_columns = [col for col in pivot1_row_columns if col in combined_df.columns]

        if scrap_perct_col_name not in combined_df.columns:
            print(f"Warning: Numeric '{scrap_perct_col_name}' column not found in '{base_filename}' for Pivot Table 1. Skipping Pivot Table 1 generation.")
            sys.stdout.flush()
        elif not valid_pivot1_row_columns or len(valid_pivot1_row_columns) != len(pivot1_row_columns):
            print(f"Warning: For Pivot Table 1 in '{base_filename}', one or more defined row columns are missing from the data. Expected: {pivot1_row_columns}, Found in data: {valid_pivot1_row_columns}. Skipping Pivot Table 1 generation.")
            sys.stdout.flush()
        else:
            try:
                temp_pivot_table_df_1 = pd.pivot_table(combined_df, index=valid_pivot1_row_columns, values=scrap_perct_col_name, aggfunc='mean', fill_value=0)
                pivot_table_df_1_output = temp_pivot_table_df_1.reset_index()
                
                if scrap_perct_col_name in pivot_table_df_1_output.columns:
                    pivot_table_df_1_output[scrap_perct_col_name] = (pivot_table_df_1_output[scrap_perct_col_name] * 100).round(2)
                    print(f"Converted '{scrap_perct_col_name}' to percentage value in Pivot Table 1 data.")
                    sys.stdout.flush()
                
                print(f"Successfully created data for Pivot Table 1 part of 'Scrap_Pivot' sheet for '{base_filename}'.")
                sys.stdout.flush()
            except Exception as e:
                print(f"Error creating or converting data for Pivot Table 1 part of 'Scrap_Pivot' sheet for '{base_filename}': {e}")
                sys.stdout.flush()
                pivot_table_df_1_output = None


        # --- Create Pivot Table 2 (Item level sums for Scrap_item_perct) for the second part of "Scrap_Pivot" sheet ---
        pivot2_row_columns = [item_code_col, item_desc_col]
        pivot2_value_columns = [total_batch_weight_col, cutting_scrap_col, other_scrap_col]
        
        valid_pivot2_row_columns = [col for col in pivot2_row_columns if col in combined_df.columns]
        valid_pivot2_value_columns = [col for col in pivot2_value_columns if col in combined_df.columns]

        if len(valid_pivot2_row_columns) != len(pivot2_row_columns) or len(valid_pivot2_value_columns) != len(pivot2_value_columns):
            print(f"Warning: For Pivot Table 2 in '{base_filename}', missing columns. Expected rows: {pivot2_row_columns} (found: {valid_pivot2_row_columns}), Expected values: {pivot2_value_columns} (found: {valid_pivot2_value_columns}). Skipping Pivot Table 2 generation.")
            sys.stdout.flush()
        else:
            try:
                temp_pivot_table_df_2 = pd.pivot_table(combined_df, index=valid_pivot2_row_columns, values=valid_pivot2_value_columns, aggfunc='sum', fill_value=0)
                
                sum_cutting_scrap = temp_pivot_table_df_2[cutting_scrap_col]
                sum_other_scrap = temp_pivot_table_df_2[other_scrap_col]
                sum_total_batch_weight = temp_pivot_table_df_2[total_batch_weight_col]

                temp_pivot_table_df_2[scrap_item_perct_col_name] = np.where(
                    sum_total_batch_weight == 0, 0.0, (sum_cutting_scrap + sum_other_scrap) / sum_total_batch_weight
                )
                temp_pivot_table_df_2[scrap_item_perct_col_name] = temp_pivot_table_df_2[scrap_item_perct_col_name].fillna(0.0)
                
                if scrap_item_perct_col_name in temp_pivot_table_df_2.columns:
                    temp_pivot_table_df_2[scrap_item_perct_col_name] = (temp_pivot_table_df_2[scrap_item_perct_col_name] * 100).round(2)
                    print(f"Converted '{scrap_item_perct_col_name}' to percentage value in Pivot Table 2 data.")
                    sys.stdout.flush()

                pivot_table_df_2_output = temp_pivot_table_df_2.reset_index()[[item_code_col, item_desc_col, scrap_item_perct_col_name]]
                print(f"Successfully created data for Pivot Table 2 part of 'Scrap_Pivot' sheet for '{base_filename}'.")
                sys.stdout.flush()

            except Exception as e:
                print(f"Error creating or converting data for Pivot Table 2 part of 'Scrap_Pivot' sheet for '{base_filename}': {e}")
                sys.stdout.flush()
                pivot_table_df_2_output = None
        
        if pivot_table_df_1_output is None and pivot_table_df_2_output is None and ("mecatherm".lower() in base_filename.lower()):
            print(f"Neither part of the 'Scrap_Pivot' sheet could be prepared for Mecatherm file '{base_filename}'.")
            sys.stdout.flush()

    else: # Not a Mecatherm file
        print(f"File '{base_filename}' is not a Mecatherm file. Skipping scrap calculations and pivot tables.")
        sys.stdout.flush()
        if scrap_perct_col_name in combined_df.columns:
            is_numeric_and_small = pd.api.types.is_numeric_dtype(combined_df[scrap_perct_col_name]) and \
                                   (combined_df[scrap_perct_col_name].max() if combined_df[scrap_perct_col_name].notna().any() else 0) < 2 
            if not is_numeric_and_small:
                print(f"Note: Potentially modified '{scrap_perct_col_name}' column was found in non-Mecatherm file '{base_filename}'. This is unexpected.")
                sys.stdout.flush()


    # --- Saving to Excel ---
    base_output_name, ext = os.path.splitext(input_filename)
    output_filename = f"{base_output_name}_clubbed{ext}"

    try:
        with pd.ExcelWriter(output_filename, engine='openpyxl') as writer:
            # Write Clubbed_Data sheet
            combined_df.to_excel(writer, index=False, sheet_name="Clubbed_Data")
            print(f"Successfully wrote 'Clubbed_Data' sheet to {output_filename}")
            sys.stdout.flush()
            # Auto-adjust columns for Clubbed_Data sheet
            worksheet_clubbed = writer.sheets["Clubbed_Data"]
            auto_adjust_column_width(worksheet_clubbed, combined_df)
            print(f"Auto-adjusted column widths for 'Clubbed_Data' sheet.")
            sys.stdout.flush()


            # Write the two parts to "Scrap_Pivot" sheet if they exist
            sheet_written_scrap_pivot = False
            if pivot_table_df_1_output is not None:
                pivot_table_df_1_output.to_excel(writer, sheet_name="Scrap_Pivot", index=False, startcol=0) 
                print(f"Successfully wrote Pivot Table 1 data to 'Scrap_Pivot' sheet (cols A-...) for {output_filename}")
                sys.stdout.flush()
                # Auto-adjust columns for the first part of Scrap_Pivot
                worksheet_scrap_pivot = writer.sheets["Scrap_Pivot"]
                auto_adjust_column_width(worksheet_scrap_pivot, pivot_table_df_1_output, start_col_offset=0)
                print(f"Auto-adjusted column widths for Pivot Table 1 part of 'Scrap_Pivot' sheet.")
                sys.stdout.flush()
                sheet_written_scrap_pivot = True
            
            if pivot_table_df_2_output is not None:
                pivot_table_df_2_output.to_excel(writer, sheet_name="Scrap_Pivot", index=False, startcol=6) # Start at column G (0-indexed 6)
                print(f"Successfully wrote Pivot Table 2 data to 'Scrap_Pivot' sheet (cols G-I) for {output_filename}")
                sys.stdout.flush()
                # Auto-adjust columns for the second part of Scrap_Pivot
                # If the sheet was already created by pivot_table_df_1_output, we use the existing worksheet object
                if "Scrap_Pivot" not in writer.sheets: # Should not happen if pivot_table_df_1_output was written
                    print("Error: 'Scrap_Pivot' sheet not found for adjusting Pivot Table 2 columns.")
                    sys.stdout.flush()
                else:
                    worksheet_scrap_pivot = writer.sheets["Scrap_Pivot"]
                    auto_adjust_column_width(worksheet_scrap_pivot, pivot_table_df_2_output, start_col_offset=6)
                    print(f"Auto-adjusted column widths for Pivot Table 2 part of 'Scrap_Pivot' sheet.")
                    sys.stdout.flush()
                sheet_written_scrap_pivot = True # Mark as written even if only this part is written
            
            if not sheet_written_scrap_pivot and "mecatherm".lower() in base_filename.lower(): 
                print(f"'Scrap_Pivot' sheet was not generated because no pivot data was available for Mecatherm file '{base_filename}'.")
                sys.stdout.flush()
        
        print(f"Successfully created/updated the output file: {output_filename}")
        sys.stdout.flush()

    except Exception as e:
        print(f"Error saving the Excel file '{output_filename}' or adjusting column widths: {e}")
        sys.stdout.flush()


if __name__ == "__main__":
    if getattr(sys, 'frozen', False):
        application_path = os.path.dirname(sys.executable)
    elif __file__:
        application_path = os.path.dirname(os.path.abspath(__file__))
    else:
        application_path = os.getcwd()

    print(f"Scanning for .xlsx files in directory: {application_path}")
    sys.stdout.flush() # Crucial for first output in frozen app

    excel_files = glob.glob(os.path.join(application_path, "*.xlsx"))
    processed_count = 0

    if not excel_files:
        print("No .xlsx files found in the application's directory.")
        sys.stdout.flush()
    else:
        excel_files.sort() 
        print(f"Found {len(excel_files)} .xlsx file(s).")
        sys.stdout.flush()

        files_to_process = []
        for file_path in excel_files:
            filename = os.path.basename(file_path)
            if "_clubbed.xlsx" in filename.lower():
                print(f"Identified as an output file, skipping: {filename}")
                sys.stdout.flush()
                continue
            if filename.startswith("~$"): 
                print(f"Skipping temporary Excel file: {filename}")
                sys.stdout.flush()
                continue
            files_to_process.append(file_path)

        if not files_to_process:
            print("No suitable .xlsx files to process after filtering out temporary/output files.")
            sys.stdout.flush()
        else:
            print(f"Files to be processed: {[os.path.basename(f) for f in files_to_process]}")
            sys.stdout.flush()
            for file_path in files_to_process:
                process_excel_file(file_path)
                processed_count += 1

    if processed_count > 0:
        print(f"\n--- Finished processing {processed_count} file(s). ---")
        sys.stdout.flush()
    elif not excel_files:
        pass # Message already printed
    elif not files_to_process:
        pass # Message already printed
    else:
        print("\n--- No new files were processed. ---")
        sys.stdout.flush()

    if getattr(sys, 'frozen', False):
        print("Processing complete. Press Enter to exit...") # This print should also be flushed if it were not followed by input()
        sys.stdout.flush() # Good practice, though input() might flush implicitly.
        input() # No need to flush after input(), as it waits for user interaction.



WHAT DO THIS CODE DO?