import os
from typing import List
from openpyxl import load_workbook
from sqlalchemy.orm import Session
from app.api.cap_ex_refurbish import schema
from app.dependency.authantication import JWTPayloadSchema
from app.models.main.cap_ex_refurbish import CapExRefurbishBase, TblCapExRefurbish
from app.utils.common_utils import remove_external_links, safe_load_workbook
from app.utils.schemas_utils import CustomResponse
from app.locale.messages import Messages
from fastapi import HTTPException

class CapExRefurbishService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def created(self, request:schema.CapExRefurbishCreate):
        created_data = CapExRefurbishBase.model_validate(request.model_dump())
        TblCapExRefurbish.create(created_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)
        
        # 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_a = workbook["Plan Working A"]
        sheet_a["L30"] = created_data.y7_y8
        sheet_b = workbook["Plan Working B"]
        sheet_b["L30"] = created_data.y7_y8
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.CAP_EX_REFURBISH)
    
    async def geted(self, group_id:int):
        geted_data = TblCapExRefurbish.get(group_id, self.db)
        if not geted_data:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.CapExRefurbishResponse.model_validate(get_group) for get_group in geted_data]
    
    async def updated(self, request: List[schema.CapExRefurbishUpdate]):
        
        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).")
        obj = None
        for req in request:
            updated_data = CapExRefurbishBase.model_validate(req.model_dump())

            if updated_data.cap_id is None:
                return CustomResponse(status="-1", message=Messages.CAP_EX_REFURBISH_NOT)
            obj = TblCapExRefurbish.update(updated_data.cap_id, updated_data, self.db)
        sheet_a = workbook["Plan Working A"]
        sheet_a["L30"] = obj.y7_y8
        sheet_b = workbook["Plan Working B"]
        sheet_b["L30"] = obj.y7_y8
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.CAP_EX_REFURBISH_UPDATE)

    
    # async def updated(self, request:List[schema.CapExRefurbishUpdate]):
    #     for req in request:
    #         updated_data = CapExRefurbishBase.model_validate(req.model_dump())
    #         if updated_data.cap_id is None:
    #             return CustomResponse(status="-1", message=Messages.CAP_EX_REFURBISH_NOT)
    #         obj = TblCapExRefurbish.update(updated_data.cap_id, updated_data, self.db)
    #     self.db.commit()
    #     self.db.refresh(updated_data)
    #     EXCEL_PATH = "files/Simulation_Test_file_clean.xlsx"
    #     OUTPUT_PATH = "uploaded_files/Simulation_Test_file_output.xlsx"
    #     os.makedirs("uploaded_files", exist_ok=True)
    #     workbook = load_workbook(EXCEL_PATH)
    #     sheet_a = workbook["Plan Working A"]
    #     sheet_a["L30"] = obj.y7_y8
    #     sheet_b = workbook["Plan Working B"]
    #     sheet_b["L30"] = obj.y7_y8
    #     workbook.save(OUTPUT_PATH)
    #     workbook.close()
    #     print("✅ Excel updated successfully at:", OUTPUT_PATH)
    #     return CustomResponse(status="1", message=Messages.CAP_EX_REFURBISH_UPDATE)
        
    async def deleted(self, cap_id:int):
        deleted_data = TblCapExRefurbish.delete(cap_id, self.db)
        if not deleted_data:
            return CustomResponse(status="-1", message=Messages.CAP_EX_REFURBISH_NOT)
        return CustomResponse(status="1", message=Messages.CAP_EX_REFURBISH_DELETE)