import os
from typing import List
from openpyxl import load_workbook
from sqlalchemy import func
from sqlalchemy.orm import Session
from app.api.digital_asset_acquisition import schema
from app.dependency.authantication import JWTPayloadSchema
from app.models.main.digital_asset_acquisition import DigitalAssetBase, TblDigitalAsset
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 DigitalAssetServise:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_digital_asset(self, request: List[schema.DigitalAssetCreate]):
        created_digital_asset = []

        for item in request:
            sale = TblDigitalAsset.create_digital_asset(item, self.db)
            created_digital_asset.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(OUTPUT_PATH) if os.path.exists(OUTPUT_PATH) else 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["Assu Sum Mod Online"]
        format_cell_map = {
            "Website Development": "G11",
            "Aggregator Set-up Costs": "G12",
            "Marketplace Set-up Costs": "G13",
            "Content work (Copywriting and Images)": "G14",
        }

        total_cost = 0
        for item in request:
            cell = format_cell_map.get(item.item)
            if cell:
                sheet[cell] = item.estimate_cost
                total_cost += item.estimate_cost
                # print(f"✅ Wrote {item.estimate_cost} to {cell} for {item.item}")
            # else:
                # print(f"⚠️ Unknown asset type: {item.item}")
        sheet["G15"] = total_cost
        # print(f"✅ Total digital asset cost stored in 'Others' (E15): {total_cost}")

        workbook.save(OUTPUT_PATH)
        workbook.close()

        print(f"✅ Excel updated successfully at: {OUTPUT_PATH}")
        return CustomResponse(status="1", message=Messages.DIGITAL_ASSET)
    
    async def get_digital_asset(self, group_id:int):
        new_get_digital_asset = TblDigitalAsset.get_digital_asset(group_id, self.db)
        if not new_get_digital_asset:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.DigitalAssetResponse.model_validate(get_group) for get_group in new_get_digital_asset]
    
    async def get_sum_of_estimate_cost(self) ->"TblDigitalAsset":
        new_get_sum_of_estimate_cost = self.db.query(func.sum(TblDigitalAsset.estimate_cost)).scalar()
        return new_get_sum_of_estimate_cost
    
    async def update_digital_asset(self, request: List[schema.DigitalAssetUpdate]):
        updated_db_objects = []  # store ORM objects to refresh later

        for req in request:
            if req.digital_id is None:
                return CustomResponse(status="-1", message=Messages.DIGITAL_ASSET_NOT)
            
            # Update the database and get the mapped SQLAlchemy object
            db_obj = TblDigitalAsset.update_digital_asset(req.digital_id, req, self.db)
            if db_obj:
                updated_db_objects.append(db_obj)
        
        # Commit all changes at once
        self.db.commit()

        # Refresh ORM objects (not Pydantic objects)
        for obj in updated_db_objects:
            self.db.refresh(obj)

        # # Excel update
        # 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(OUTPUT_PATH) if os.path.exists(OUTPUT_PATH) else load_workbook(EXCEL_PATH)
        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 = workbook["Assu Sum Mod Online"]

        format_cell_map = {
            "Website Development": "G11",
            "Aggregator Set-up Costs": "G12",
            "Marketplace Set-up Costs": "G13",
            "Content work (Copywriting and Images)": "G14",
        }

        total_cost = 0
        for item in request:
            cell = format_cell_map.get(item.item)
            if cell:
                sheet[cell] = item.estimate_cost
                total_cost += item.estimate_cost

        sheet["G15"] = total_cost
        workbook.save(OUTPUT_PATH)
        workbook.close()

        print(f"✅ Excel updated successfully at: {OUTPUT_PATH}")
        return CustomResponse(status="1", message=Messages.DIGITAL_ASSET_UPDATE)

    
    async def delete_digital_asset(self, digital_id:int):
        deleted_digital_asset = TblDigitalAsset.delete_digital_asset(digital_id, self.db)
        if not deleted_digital_asset:
            return CustomResponse(status="-1", message=Messages.DIGITAL_ASSET_NOT)
        return CustomResponse(status="1", message=Messages.DIGITAL_ASSET_DELETE)