import os
from typing import List
from openpyxl import load_workbook
from sqlalchemy import func
from app.api.utilitys import schema
from app.models.main.utility import TblUtility, UtilityBase
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 UtilityService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_utility(self, request:schema.UtilityCreate):
        created_utility = UtilityBase.model_validate(request.model_dump())
        TblUtility.create_utility(created_utility, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.UTILITY)
    
    
    async def get_utility(self, group_id:int):
        new_get_utility = TblUtility.get_utility(group_id, self.db)
        if not new_get_utility:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.UtilityResponse.model_validate(get_group) for get_group in new_get_utility]
    
    async def get_total_cost(self) ->"TblUtility":
        total_a = self.db.query(func.sum(TblUtility.cost_per_month)).filter(TblUtility.store_format_type == "Store Formate A").scalar() or 0
        total_b = self.db.query(func.sum(TblUtility.cost_per_month)).filter(TblUtility.store_format_type == "Store Formate B").scalar() or 0
        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)
        # else:
        #     workbook = load_workbook(EXCEL_PATH)
        sheet_a = workbook["Assu Sum Mod A"]
        sheet_a["I14"] = total_a
        sheet_b = workbook["Assu Sum Mod B"]
        sheet_b["I14"] = total_b
        workbook.save(OUTPUT_PATH)
        workbook.close()

        print(f"✅ Excel updated successfully at: {OUTPUT_PATH}")
        return {"totalA": total_a, "totalB": total_b}

    
    async def update_utility(self, request:List[schema.UtilityUpdate]):
        for req in request:
            updated_utility = UtilityBase.model_validate(req.model_dump())
            if updated_utility.utility_id is None:
                return CustomResponse(status="-1", message=Messages.UTILITY_NOT)
            TblUtility.update_utility(updated_utility, updated_utility.utility_id, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.UTILITY_UPDATE)
    
    async def delete_utility(self, utility_id:int):
        deleted_utility = TblUtility.delete_utility(utility_id, self.db)
        if not deleted_utility:
            return CustomResponse(status="-1", message=Messages.UTILITY_NOT)
        return CustomResponse(status="1", message=Messages.UTILITY_DELETE)