import os
from pathlib import Path
import pandas as pd
import sys

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "../..")))
from experiments_utils.constants import SCORES_SUMMARY_PATH

# analysis spreadsheet
analysis_xlsxm_path = Path("/mnt/g/My Drive/!Projects/WebGui/analysis.xlsm")
# analysis_xlsxm_path = Path("/mnt/c/Users/Moises/Desktop/analysis.xlsm")
scores_per_model = pd.read_csv(SCORES_SUMMARY_PATH)


def paste_scores_per_model_to_analysis(scores_per_model, analysis_xlsxm_path):
    from openpyxl import load_workbook

    def get_contiguous_region_from_A1(ws):
        # Determine the last contiguous row starting in A1 (simulate Ctrl+Down)
        last_row = 1
        while True:
            next_row = last_row + 1
            if ws.cell(row=next_row, column=1).value is None:
                break
            last_row = next_row

        # Determine the last contiguous column starting in A1 (simulate Ctrl+Right)
        last_col = 1
        while True:
            next_col = last_col + 1
            if ws.cell(row=1, column=next_col).value is None:
                break
            last_col = next_col

        return last_row, last_col

    # Load the workbook with macros preserved.
    wb = load_workbook(analysis_xlsxm_path, keep_vba=True, keep_links=True)

    # Get the "scores_per_model" sheet; if not exists, create it.
    if "scores_summary" in wb.sheetnames:
        ws = wb["scores_summary"]
    else:
        ws = wb.create_sheet("scores_summary")

    # Determine the contiguous region starting at A1 (simulate Ctrl+Down/Right)
    last_row, last_col = get_contiguous_region_from_A1(ws)

    # Clear the contiguous region (only cells within A1 to cell(last_row, last_col))
    for row in ws.iter_rows(min_row=1, max_row=last_row, min_col=1, max_col=last_col):
        for cell in row:
            cell.value = None

    # Paste the header from the DataFrame (starting at A1)
    for col_idx, col_name in enumerate(scores_per_model.columns, start=1):
        ws.cell(row=1, column=col_idx, value=col_name)

    # Paste each row of values
    for row_idx, row in enumerate(scores_per_model.itertuples(index=False, name=None), start=2):
        for col_idx, value in enumerate(row, start=1):
            ws.cell(row=row_idx, column=col_idx, value=value)

    # Save the workbook (overwriting the original analysis file)
    try:
        wb.save(analysis_xlsxm_path)
    except PermissionError:
        input(
            f"The file {analysis_xlsxm_path} is open. Close it and press enter to save. If not closed, will save to a copy."
        )
        try:
            wb.save(analysis_xlsxm_path)
        except PermissionError:
            from datetime import datetime

            copy_filename = f"{analysis_xlsxm_path.stem}_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsm"
            print(f"PermissionError: Unable to save to {analysis_xlsxm_path}. Saving to {copy_filename}.")
            wb.save(os.path.join(analysis_xlsxm_path.parent, copy_filename))


if not os.path.exists(analysis_xlsxm_path):
    print(f"Analysis file not found at: {analysis_xlsxm_path}")
    exit()

paste_scores_per_model_to_analysis(scores_per_model, analysis_xlsxm_path)
