import os
from typing import List

from openpyxl import load_workbook
from app.api.operating_expenses import schema
from app.models.main.operating_expenses import OperatingExpensesBase, TblOperatingExpenses
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 OperatingExpensesService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
    
    async def create_operating_Expenses(self, request:schema.OperatingExpensesCreate):
        created_operating_Expenses = OperatingExpensesBase.model_validate(request.model_dump())
        TblOperatingExpenses.create_operating_expenses(created_operating_Expenses, self.db)
        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)

        # Load workbook only once
        if os.path.exists(OUTPUT_PATH):
            workbook = load_workbook(OUTPUT_PATH)
        else:
            workbook = load_workbook(EXCEL_PATH)
        param_to_cell = {
            "Maintenance and Repairs": "I15",
            "Admin Overheads": "I17",
        }
        format_to_sheet = {
            "Store Format A": "Assu Sum Mod A",
            "Store Format B": "Assu Sum Mod B",
        }
        for format_type, sheet_name in format_to_sheet.items():
            sheet = workbook[sheet_name]
            if request.store_format_type == format_type:
                cell = param_to_cell.get(request.operating_expenses_type)
                if cell:
                    sheet[cell] = created_operating_Expenses.percentage_of_sales_per_year
                #     print(f"✅ Updated {format_type} → {request.parameter} = {created_sales_estimate.value}")
                # else:
                #     print(f"⚠️ Unknown parameter: {request.parameter}")
        workbook.save(OUTPUT_PATH)
        workbook.close()
        # print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.OPERATING_EXPENSES)
    
    async def get_operating_Expenses(self, group_id:int):
        new_get_operating_Expenses = TblOperatingExpenses.get_operating_expenses(group_id, self.db)
        if not new_get_operating_Expenses:
            raise HTTPException(status_code=404, detail="Get groupID not found")
        return [schema.OperatingExpensesResponse.model_validate(get_group) for get_group in new_get_operating_Expenses]
    
    
    async def update_operating_expenses(self, request: List[schema.OperatingExpensesUpdate]):

        last_db_row = None
        for req in request:
            updated_operating_expenses = OperatingExpensesBase.model_validate(req.model_dump())

            if updated_operating_expenses.operating_id is None:
                return CustomResponse(status="-1", message=Messages.OPERATING_EXPENSES_NOT)
            last_db_row = TblOperatingExpenses.update_operating_expenses(
                updated_operating_expenses.operating_id,
                updated_operating_expenses,
                self.db
            )
        self.db.commit()
        if last_db_row:
            self.db.refresh(last_db_row)
        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)
        param_to_cell = {
            "Maintenance and Repairs": "I15",
            "Admin Overheads": "I17",
        }

        format_to_sheet = {
            "Store Format A": "Assu Sum Mod A",
            "Store Format B": "Assu Sum Mod B",
        }
        for req in request:

            sheet_name = format_to_sheet.get(req.store_format_type)
            if not sheet_name:
                continue

            sheet = workbook[sheet_name]

            cell_addr = param_to_cell.get(req.operating_expenses_type)
            if cell_addr:
                sheet[cell_addr] = req.percentage_of_sales_per_year
        workbook.save(OUTPUT_PATH)
        workbook.close()

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

    
    async def delete_operating_expenses(self, operating_id:int):
        deleted_operating_expenses = TblOperatingExpenses.delete_operatin_expenses(operating_id, self.db)
        if not deleted_operating_expenses:
            return CustomResponse(status="-1", message=Messages.OPERATING_EXPENSES_NOT)
        return CustomResponse(status="1", message=Messages.OPERATING_EXPENSES_DELETE)