import os
import pandas as pd
import numpy as np
from fastapi import HTTPException
from sqlalchemy.orm import Session
from app.api.private_lablling import schema
from app.dependency.authantication import JWTPayloadSchema
from app.locale.messages import Messages
from app.models.main.group import TblGroup
from app.models.main.private_lablling import PrivateLabllingBase, TblPrivateLablling
from app.utils.schemas_utils import CustomResponse

class PrivateLabllingService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_private_lablling(self, request:schema.PrivateLabllingCreate):
        created_private_lablling = PrivateLabllingBase.model_validate(request.model_dump())
        TblPrivateLablling.create_private_lablling(created_private_lablling, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.PRIVATE_LABLLING)
    
    async def get_private_lablling(self, private_get_id:int):
        new_get_private_lablling = TblPrivateLablling.get_private_lablling(private_get_id, self.db)
        if not new_get_private_lablling:
            raise HTTPException(status_code=404, detail="Get private Lablling ID not found")
        return schema.PrivateLabllingResponse.model_validate(new_get_private_lablling)
    
    async def get_group_private_lablling(self, group_id:int) ->"TblPrivateLablling":
        new_get_group_private_lablling = self.db.query(TblPrivateLablling).filter(TblPrivateLablling.group_id == group_id).all()
        if not new_get_group_private_lablling:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.PrivateLabllingResponse.model_validate(get_group) for get_group in new_get_group_private_lablling]
    
    async def get_excel_data_category_name(self, group_id:int) ->list[dict]:
        get_excel_data = self.db.query(TblGroup).filter(TblGroup.group_id == group_id).first()
        if not get_excel_data:
            raise HTTPException(status_code=404, detail="Not Found")
        excel_file = get_excel_data.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 update_private_lablling(self, request:schema.PrivateLabllingUpdate):
        updated_private_lablling = PrivateLabllingBase.model_validate(request.model_dump())
        if updated_private_lablling.private_id is None:
            return CustomResponse(status="-1", message=Messages.PRIVATE_LABLLING_NOT)
        TblPrivateLablling.update_private_lablling(updated_private_lablling.private_id, updated_private_lablling, self.db)
        return CustomResponse(status="1", message=Messages.PRIVATE_LABLLING_UPDATE)
    
    async def delete_private_lablling(self, group_id:int):
        deleted_private_lablling = TblPrivateLablling.delete_private_lablling(group_id, self.db)
        if not deleted_private_lablling:
            return CustomResponse(status="-1", message=Messages.PRIVATE_LABLLING_NOT)
        return CustomResponse(status="1", message=Messages.PRIVATE_LABLLING_DELETE)