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

class IntegratedMarketingService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_integarted_marketing(self, request:List[schema.IntegratedMarketingCreated]):
        created_integarted_marketing = []
        for item in request:
            estimate_budget = item.budget_allocated * item.marketing_budget
            requeste_data = item.model_dump()
            requeste_data["estimated_budget"] = estimate_budget
            create_item = IntegratedMarketingBase.model_validate(requeste_data)
            sale = TblIntegratedMarketing.create_integrated_marketing(create_item, self.db)
            created_integarted_marketing.append(sale)
        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["Assu Sum Mod A"]
        sheet_a["I16"] = item.marketing_budget
        sheet_b = workbook["Assu Sum Mod B"]
        sheet_b["I16"] = item.marketing_budget
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.INTEGRATED_MARKETING)
    
    async def get_integrated_marketing(self, group_id:int):
        new_get_integrated_marketing = TblIntegratedMarketing.get_integrated_marketing(group_id, self.db)
        if not new_get_integrated_marketing:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.IntegratedMarketingResponse.model_validate(get_group) for get_group in new_get_integrated_marketing]
    
    async def get_2_fields(self, group_id:int) ->"TblIntegratedMarketing":
        get_data = self.db.query(TblIntegratedMarketing).filter(TblIntegratedMarketing.group_id == group_id).all()
        if not get_data:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.Get2Fields.model_validate(get_group) for get_group in get_data]
    
    # async def update_integrated_marketing(self, request:List[schema.IntegratedMarketingUpdate]):
    #     for req in request:
    #         estimate_budget = req.budget_allocated * req.marketing_budget
    #         requeste_data = req.model_dump()
    #         requeste_data["estimated_budget"] = estimate_budget
    #         updated_integrated_marketing = IntegratedMarketingBase.model_validate(requeste_data)
    #         if updated_integrated_marketing.integrated_id is None:
    #             return CustomResponse(status="-1", message=Messages.INTEGRATED_MARKETING_NOT)
    #         TblIntegratedMarketing.update_integrated_marketing(updated_integrated_marketing.integrated_id, updated_integrated_marketing, self.db)
    #     self.db.commit()
    #     self.db.refresh(updated_integrated_marketing)
    #     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["Assu Sum Mod A"]
    #     sheet_a["K16"] = updated_integrated_marketing.marketing_budget
    #     sheet_b = workbook["Assu Sum Mod B"]
    #     sheet_b["K16"] = updated_integrated_marketing.marketing_budget
    #     workbook.save(OUTPUT_PATH)
    #     workbook.close()
    #     print("✅ Excel updated successfully at:", OUTPUT_PATH)
    #     return CustomResponse(status="1", message=Messages.INTEGRATED_MARKETING_UPDATE)
    
    async def update_integrated_marketing(self, request: List[schema.IntegratedMarketingUpdate]):

        for req in request:
            estimate_budget = req.budget_allocated * req.marketing_budget

            request_data = req.model_dump()
            request_data["estimated_budget"] = estimate_budget

            updated_integrated_marketing = IntegratedMarketingBase.model_validate(request_data)

            if updated_integrated_marketing.integrated_id is None:
                return CustomResponse(
                    status="-1", 
                    message=Messages.INTEGRATED_MARKETING_NOT
                )
            last_updated_obj = TblIntegratedMarketing.update_integrated_marketing(
                updated_integrated_marketing.integrated_id,
                updated_integrated_marketing,
                self.db
            )
        if last_updated_obj:
            self.db.refresh(last_updated_obj)

        # 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).")
        
        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)

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

        sheet_a["I16"] = last_updated_obj.marketing_budget
        sheet_b["I16"] = last_updated_obj.marketing_budget

        workbook.save(OUTPUT_PATH)
        workbook.close()

        print("✅ Excel updated successfully at:", OUTPUT_PATH)

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

    
    async def delete_integrated_marketing(self, integrated_id:int):
        deleted_integrated_marketing = TblIntegratedMarketing.delete_integrated_marketing(integrated_id, self.db)
        if not deleted_integrated_marketing:
            return CustomResponse(status="-1", message=Messages.INTEGRATED_MARKETING_NOT)
        return CustomResponse(status="1", message=Messages.INTEGRATED_MARKETING_DELETE)