import os
from typing import List
import pandas as pd
import numpy as np
from app.api.category_wish_inventory import schema
from app.dependency.authantication import JWTPayloadSchema
from app.locale.messages import Messages
from fastapi import HTTPException
from app.models.main.category_wish_inventory import CategoryWishInventoryBase, TblCategoryWishInventory
from app.models.main.gross_margin_contributions import TblGrossMarginContribution
from app.models.main.group import TblGroup
from app.utils.schemas_utils import CustomResponse
from sqlalchemy.orm import Session

class CategoryWishInventoryService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_category_wish_inventory(self, request:schema.CategoryWishInventoryCreate):
        created_category_wish_inventory = CategoryWishInventoryBase.model_validate(request.model_dump())
        TblCategoryWishInventory.create_category_wish_inventory(created_category_wish_inventory, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.CATEGORY_WISH_INVENTORY)
    
    async def get(self) ->"TblCategoryWishInventory":
        # get = self.db.query(TblGrossMarginContribution.contribution_to_gross_margin, TblGrossMarginContribution.contribution_to_total_sales).filter(TblGrossMarginContribution.gross_id == group_id).all()
        get = self.db.query(TblGrossMarginContribution.contribution_to_gross_margin, TblGrossMarginContribution.contribution_to_total_sales).filter(TblGrossMarginContribution.group_id == TblCategoryWishInventory.group_id).first()
        if not get:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return schema.MarginSales.model_validate(get)
    
    async def get_category_name_excel(self, group_id:int) ->list[dict]:
        get_category_name = self.db.query(TblGroup).filter(TblGroup.group_id == group_id).first()
        # get_category_name = self.db.query(TblGroup.excel_pre_select).first()
        if not get_category_name:
            raise HTTPException(status_code=404, detail="Not found")
        excel_file = get_category_name.excel_pre_select
        if not excel_file or not os.path.exists(excel_file):
            raise HTTPException(status_code=404, detail="Excel file path is invalid or file does not exist")
        df = pd.read_excel(excel_file)
        df = df.replace({np.nan: None, np.inf: None, -np.inf: None})
        # return df.to_dict(orient="records")
        return df["Category Name "].dropna().tolist()
    
    async def get_group_id(self, group_id:int):
        new_get_group_id = TblCategoryWishInventory.get_group_id(group_id, self.db)
        
        if not new_get_group_id:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.CategoryWishInventoryResponse.model_validate(get_group) for get_group in new_get_group_id]
    
    async def update_category_wish_inventory(self, request:List[schema.CategoryWishInventoryUpdate]):
        for req in request:
            updated_category_wish_inventory = CategoryWishInventoryBase.model_validate(req.model_dump())
            if updated_category_wish_inventory.category_wish_id is None:
                return CustomResponse(status="-1", message=Messages.CATEGORY_WISH_INVENTORY_NOT)
            TblCategoryWishInventory.update_category_wish_inventory(updated_category_wish_inventory.category_wish_id, updated_category_wish_inventory, self.db)
        return CustomResponse(status="1", message=Messages.CATEGORY_WISH_INVENTORY_UPDATE)
    
    async def delete_category_wish_inventory(self, category_wish_id:int):
        deleted_category_wish_inventory = TblCategoryWishInventory.delete_category_wish_inventory(category_wish_id, self.db)
        if not deleted_category_wish_inventory:
            return CustomResponse(status="-1", message=Messages.CATEGORY_WISH_INVENTORY_NOT)
        return CustomResponse(status="1", message=Messages.CATEGORY_WISH_INVENTORY_DELETE)