import os
from typing import List
from openpyxl import load_workbook
from sqlalchemy.orm import Session
from app.api.recurring_operating_expenses import schema
from app.models.main.recurring_operating_expenses import RecurringOperatingBase, TblRecurringOperating
from app.utils.schemas_utils import CustomResponse
from app.locale.messages import Messages
from fastapi import HTTPException
from app.dependency.authantication import JWTPayloadSchema

class RecurringOperatingService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_recurring_operating(self, request:List[schema.RecurringOperatingCreate]):
        created_recurring_operating = []
        for item in request:
            sale = TblRecurringOperating.create_recurring_operating(item, self.db)
            created_recurring_operating.append(sale)
        self.db.commit()
        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 = {
            "Marketing Spend": "G17",
            "Website Maintenance Costs": "G18",
            "Commissions for Aggregators": "G19",
            "Commissions for Marketplace": "G20",
            "Loss from Returns and Exchanges":"G21",
            "Logistics and Delivery":"G22",
            "Packaging":"G23"
            
        }

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

        workbook.save(OUTPUT_PATH)
        workbook.close()

        print(f"✅ Excel updated successfully at: {OUTPUT_PATH}")
        return CustomResponse(status="1", message=Messages.RECURRING_OPERATING)
    
    async def get_recurring_operating(self, group_id:int):
        new_get_recurring_operating = TblRecurringOperating.get_recurring_operating(group_id, self.db)
        if not new_get_recurring_operating:
            raise HTTPException(status_code=404, detail="Get droup ID not found")
        return [schema.RecurringOperatingResponse.model_validate(get_group) for get_group in new_get_recurring_operating]
    
    async def update_recurring_operating1(self, request:List[schema.RecurringOperatingUpdate]):
        for req in request:
            updated_recurring_operating = RecurringOperatingBase.model_validate(req.model_dump())
            if updated_recurring_operating.recurring_id is None:
                return CustomResponse(status="-1", message=Messages.RECURRING_OPERATING_NOT)
            TblRecurringOperating.update_recurring_operating(updated_recurring_operating.recurring_id, updated_recurring_operating, self.db)
        self.db.commit()
        self.db.refresh(updated_recurring_operating)
        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 = {
            "Marketing Spend": "G17",
            "Website Maintenance Costs": "G18",
            "Commissions for Aggregators": "G19",
            "Commissions for Marketplace": "G20",
            "Loss from Returns and Exchanges":"G21",
            "Logistics and Delivery":"G22",
            "Packaging":"G23"
            
        }

        total_cost = 0
        for item in request:
            cell = format_cell_map.get(item.expenses_type)
            if cell:
                sheet[cell] = item.percentage_of__sales
                total_cost += item.percentage_of__sales
            #     print(f"✅ Wrote {item.percentage_of__sales} to {cell} for {item.expenses_type}")
            # else:
            #     print(f"⚠️ Unknown asset type: {item.expenses_type}")

        # Write total in Others (E15)
        sheet["G24"] = total_cost
        # print(f"✅ Total digital asset cost stored in 'Others' (G24): {total_cost}")

        workbook.save(OUTPUT_PATH)
        workbook.close()

        print(f"✅ Excel updated successfully at: {OUTPUT_PATH}")
        return CustomResponse(status="1", message=Messages.RECURRING_OPERATING_UPDATE)
    
    async def delete_recurring_operating(self, recurring_id:int):
        deleted_recurring_operating = TblRecurringOperating.delete_recurring_operating(recurring_id, self.db)
        if not deleted_recurring_operating:
            return CustomResponse(status="-1", message=Messages.RECURRING_OPERATING_NOT)
        return CustomResponse(status="1", message=Messages.RECURRING_OPERATING_DELETE)