import os
from typing import List
from openpyxl import load_workbook
from sqlalchemy.orm import Session
from app.api.optimising_assets_for_revenue import schema
from app.models.main.optimising_assets_for_revenue import OptimisingAssetsBase, TblOptimisingAssets
from app.models.main.recovery_of_promotional import TblRecoveryOfPromotional
from app.models.main.short_negotiation_final import TblShortNegotiationFinal
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
from app.dependency.authantication import JWTPayloadSchema

class OptimisingAssetsService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    # async def create_optimising_assets(self, request:schema.OptimisingAssetsCreate):
    #     created_optimising_assets = OptimisingAssetsBase.model_validate(request.model_dump())
    #     TblOptimisingAssets.create_optimising_assets(created_optimising_assets, self.db)
    #     self.db.commit()
    #     test1 = self.db.query(TblRecoveryOfPromotional.what_percentage).first()
    #     test2 = self.db.query(TblShortNegotiationFinal.negotiation_what_percentage_of_sales).first()
    #     test3 = self.db.query(TblOptimisingAssets.percentage_of_sales).first()
        
    #     add1 = test1[0] if test1 else 0
    #     add2 = test2[0] if test2 else 0
    #     add3 = test3[0] if test3 else 0
        
    #     total = add1 + add2 + add3
    
    async def create_optimising_assets(self, request:schema.OptimisingAssetsCreate):
        created_optimising_assets = OptimisingAssetsBase.model_validate(request.model_dump())
        TblOptimisingAssets.create_optimising_assets(created_optimising_assets, self.db)
        self.db.commit()

        test1 = self.db.query(TblRecoveryOfPromotional.what_percentage).first()
        test2 = self.db.query(TblShortNegotiationFinal.negotiation_what_percentage_of_sales).first()
        test3 = self.db.query(TblOptimisingAssets.percentage_of_sales).first()

        add1 = test1[0] if test1 and test1[0] is not None else 0
        add2 = test2[0] if test2 and test2[0] is not None else 0
        add3 = test3[0] if test3 and test3[0] is not None else 0

        total = add1 + add2 + add3
        
        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["Assu Sum Mod A"]
        sheet_a["I11"] = total
        sheet_b = workbook["Assu Sum Mod B"]
        sheet_b["I11"] = total
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.OPTIMISING_ASSETS)
    
    async def get_optimising_assets(self, group_id:int):
        new_get_optimising_assets = TblOptimisingAssets.get_optimising_assets(group_id, self.db)
        if not new_get_optimising_assets:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.OptimisingAssetsResponse.model_validate(get_group) for get_group in new_get_optimising_assets]
    
    async def update_optimising_assets(self, request: List[schema.OptimisingAssetsUpdate]):
        for req in request:
            updated_data = OptimisingAssetsBase.model_validate(req.model_dump())
            if updated_data.optimising_id is None:
                return CustomResponse(status="-1", message=Messages.OPTIMISING_ASSETS_NOT)
            
            TblOptimisingAssets.update_optimising_assets(
                updated_data.optimising_id, updated_data, self.db
            )

        self.db.commit()
        
        # No need to refresh Pydantic object
        # If you want, you can fetch ORM objects here
        test1 = self.db.query(TblRecoveryOfPromotional.what_percentage).first()
        test2 = self.db.query(TblShortNegotiationFinal.negotiation_what_percentage_of_sales).first()
        test3 = self.db.query(TblOptimisingAssets.percentage_of_sales).first()

        add1 = test1[0] if test1 and test1[0] is not None else 0
        add2 = test2[0] if test2 and test2[0] is not None else 0
        add3 = test3[0] if test3 and test3[0] is not None else 0
        
        total = add1 + add2 + add3
        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 not os.path.exists(OUTPUT_PATH) else OUTPUT_PATH)
        
        sheet_a = workbook["Assu Sum Mod A"]
        sheet_a["I11"] = total
        sheet_b = workbook["Assu Sum Mod B"]
        sheet_b["I11"] = total
        
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        
        return CustomResponse(status="1", message=Messages.OPTIMISING_ASSETS_UPDATE)

    

    
    async def delete_optimising_assets(self, optimising_id:int):
        deleted_optimising_assets = TblOptimisingAssets.delete_optimising_assets(optimising_id, self.db)
        if not deleted_optimising_assets:
            return CustomResponse(status="-1", message=Messages.OPTIMISING_ASSETS_NOT)
        return CustomResponse(status="1", message=Messages.OPTIMISING_ASSETS_DELETE)