import os
from typing import List

from openpyxl import load_workbook
from app.api.pre_operating_expenses import schemas
from app.api.pre_operating_expenses.schemas import PreOperatingExpenseCreate, PreOperatingExpenseResponce
from app.locale.messages import Messages
from sqlalchemy.orm import Session
from app.models.main.group import TblGroup
from app.models.main.pre_operating_expenses import PreOperatingExpenseBase, TblPreOperatingExpense
from app.utils.common_utils import remove_external_links, safe_load_workbook
from app.utils.schemas_utils import CustomResponse

class PreOperatingExpenseService:
    def __init__(self, db: Session, token):
        self.db = db
        self.token = token

    async def create_pre_operating_expense(self, request: PreOperatingExpenseCreate):
       
        group = self.db.query(TblGroup).filter(TblGroup.group_id == request.group_id).first()
        if not group:
            return CustomResponse(status="-1", message=f"group_id={request.group_id} does not exist")
        trading_data = PreOperatingExpenseBase.model_validate(request.model_dump())
        TblPreOperatingExpense.create(trading_data, self.db)
        self.db.commit()
        
        OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        workbook = safe_load_workbook(OUTPUT_PATH, EXCEL_PATH)
        remove_external_links(workbook)
        workbook.save(OUTPUT_PATH)
        
        # # 3️⃣ File paths
        # EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        # OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        # os.makedirs("uploaded_files", exist_ok=True)

        # # 4️⃣ Load workbook
        # workbook = load_workbook(OUTPUT_PATH if os.path.exists(OUTPUT_PATH) else EXCEL_PATH)
        # if os.path.exists(OUTPUT_PATH):
        #     workbook = load_workbook(OUTPUT_PATH)
        #     # print("📂 Loaded existing output file to keep previous data.")
        # else:
        #     workbook = load_workbook(EXCEL_PATH)
        #     # print("🆕 Loaded clean template (first run).")

        # 5️⃣ Define target cell locations (adjust cell positions if needed)
        param_to_cell = {
            "pre_operating_months_store_a": "K25",  # for Assu Sum Mod A
            "pre_operating_months_store_b": "K25",  # for Assu Sum Mod B
        }

        # 6️⃣ Update sheet “Assu Sum Mod A”
        sheet_a = workbook["Assu Sum Mod A"]
        cell_a = param_to_cell["pre_operating_months_store_a"]
        sheet_a[cell_a] = trading_data.pre_operating_months_store_a

        # 7️⃣ Update sheet “Assu Sum Mod B”
        sheet_b = workbook["Assu Sum Mod B"]
        cell_b = param_to_cell["pre_operating_months_store_b"]
        sheet_b[cell_b] = trading_data.pre_operating_months_store_b

        # 8️⃣ Save workbook
        workbook.save(OUTPUT_PATH)
        workbook.close()
        return CustomResponse(status="1", message="Pre Operating Expense created successfully")

    async def get_pre_operating_expense(self, group_id: int):
        segment = TblPreOperatingExpense.get_by_group_id(group_id, self.db)
        if not segment:
            return CustomResponse(status="-1", message=Messages.SEGMENT_NOT_FOUND)
        return [PreOperatingExpenseResponce.model_validate(get_group) for get_group in segment]
    
    async def update(self, request: List[schemas.PreOperatingExpenseUpdate]):
        last_updated_row = None

        for req in request:
            updated = PreOperatingExpenseBase.model_validate(req.model_dump())

            if updated.pre_operating_id is None:
                return CustomResponse(status="-1", message=Messages.PRE_OPERATING_EXPENSES_NOT)
            TblPreOperatingExpense.update(updated.pre_operating_id, updated, self.db)
            last_updated_row = (
                self.db.query(TblPreOperatingExpense)
                .filter(TblPreOperatingExpense.pre_operating_id == updated.pre_operating_id)
                .first()
            )

        self.db.commit()
        if last_updated_row:
            self.db.refresh(last_updated_row)
        OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        workbook = safe_load_workbook(OUTPUT_PATH, EXCEL_PATH)
        remove_external_links(workbook)
        workbook.save(OUTPUT_PATH)
        # EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        # OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        # os.makedirs("uploaded_files", exist_ok=True)

        # workbook = load_workbook(OUTPUT_PATH if os.path.exists(OUTPUT_PATH) else EXCEL_PATH)

        param_to_cell = {
            "pre_operating_months_store_a": "K25",
            "pre_operating_months_store_b": "K25",
        }

        sheet_a = workbook["Assu Sum Mod A"]
        sheet_b = workbook["Assu Sum Mod B"]

        sheet_a[param_to_cell["pre_operating_months_store_a"]] = last_updated_row.pre_operating_months_store_a
        sheet_b[param_to_cell["pre_operating_months_store_b"]] = last_updated_row.pre_operating_months_store_b

        workbook.save(OUTPUT_PATH)
        workbook.close()

        return CustomResponse(status="1", message=Messages.PRE_OPERATING_EXPENSES_UPDATE)
    
    async def delete(self, pre_operating_id:int):
        deleted = TblPreOperatingExpense.delete(pre_operating_id, self.db)
        if not deleted:
            return CustomResponse(status="-1", message=Messages.PRE_OPERATING_EXPENSES_NOT)
        return CustomResponse(status="1", message=Messages.PRE_OPERATING_EXPENSES_DELETE)