import os
from typing import List

from openpyxl import load_workbook
from app.api.store_level_staff_needs import schema
from app.models.main.store_level_staff_needs import StoreLevelStaffNeedsBase, TblStoreLevelStaffNeeds
from app.utils.common_utils import remove_external_links, safe_load_workbook
from app.utils.schemas_utils import CustomResponse
from sqlalchemy.orm import Session
from app.dependency.authantication import JWTPayloadSchema
from app.locale.messages import Messages
from fastapi import HTTPException

class StoreLevelStaffNeedsService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_store_level_staff(self, request: List[schema.StoreLevelStaffNeedsCreate]):
        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(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).")
        sheet = workbook["Org Structure"]
        for req in request:
            created_store_level_staff = StoreLevelStaffNeedsBase.model_validate(req.model_dump())
            test = TblStoreLevelStaffNeeds.create_store_level_staff(created_store_level_staff, self.db)
            self.db.commit()
            if test.store_id <= 6:
                store_id = test.store_id
                fmt = test.store_formate_type
                row_map = {
                    1: (29, 39),
                    2: (30, 40),
                    3: (31, 41),
                    4: (32, 42),
                    5: (33, 43),
                    6: (34, 44),
                }
                if store_id in row_map:
                    row_a, row_b = row_map[store_id]
                    sheet[f"D{row_a}"] = created_store_level_staff.average_salary
                    sheet[f"D{row_b}"] = created_store_level_staff.average_salary

                    if fmt == "Store Format A":
                        sheet[f"C{row_a}"] = created_store_level_staff.count
                    elif fmt == "Store Format B":
                        sheet[f"C{row_b}"] = created_store_level_staff.count

        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)

        return CustomResponse(status="1", message=Messages.STORE_LEVEL_STAFF_NEEDS)

    async def get_store_level_staff(self, group_id:int):
        new_get_store_level_staff = TblStoreLevelStaffNeeds.get_store_level_staff(group_id, self.db)
        if not new_get_store_level_staff:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.StoreLevelStaffNeedsResponse.model_validate(get_group) for get_group in new_get_store_level_staff]

    async def update_store_level_staff(self, request: List[schema.StoreLevelStaffNeedsUpdate]):
        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)
        # 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).")
        sheet = workbook["Org Structure"]
        row_map = {
            1: (29, 39),
            2: (30, 40),
            3: (31, 41),
            4: (32, 42),
            5: (33, 43),
            6: (34, 44),
        }

        for req in request:
            updated_store_level_staff = StoreLevelStaffNeedsBase.model_validate(req.model_dump())
            if updated_store_level_staff.store_id is None:
                return CustomResponse(status="-1", message=Messages.STORE_LEVEL_STAFF_NEEDS_NOT)
            TblStoreLevelStaffNeeds.update_store_level_staff(
                updated_store_level_staff.store_id,
                updated_store_level_staff,
                self.db
            )
            self.db.commit()
            store_id = updated_store_level_staff.store_id
            fmt = updated_store_level_staff.store_formate_type
            if store_id in row_map:
                row_a, row_b = row_map[store_id]
                sheet[f"D{row_a}"] = updated_store_level_staff.average_salary
                sheet[f"D{row_b}"] = updated_store_level_staff.average_salary
                if fmt == "Store Format A":
                    sheet[f"C{row_a}"] = updated_store_level_staff.count
                elif fmt == "Store Format B":
                    sheet[f"C{row_b}"] = updated_store_level_staff.count
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.STORE_LEVEL_STAFF_NEEDS_UPDATE)
    
    async def  delete_store_level_staff(self, store_id:int):
        deleted_store_level_staff = TblStoreLevelStaffNeeds.delete_store_level_staff(store_id, self.db)
        if not deleted_store_level_staff:
            return CustomResponse(status="-1", message=Messages.STORE_LEVEL_STAFF_NEEDS_NOT)
        return CustomResponse(status="1", message=Messages.STORE_LEVEL_STAFF_NEEDS_DELETE)