import os
from typing import List
import pandas as pd
from app.api.category_captinship_planning import schema
from app.models.main.category_captinship_planning import CategoryCaptinshipPlanningBase, TblCategoryCaptinshipPlanning
from app.models.main.group import TblGroup
# import numpy as np
from fastapi import HTTPException
from sqlalchemy.orm import Session
from app.dependency.authantication import JWTPayloadSchema
from app.locale.messages import Messages
from app.utils.schemas_utils import CustomResponse

class CategoryCaptinshipPlanningService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_category_captinship_planning_new(self, request:schema.CategoryCaptinshipPlanningCreate):
        created_category_captinship_planning = CategoryCaptinshipPlanningBase.model_validate(request.model_dump())
        TblCategoryCaptinshipPlanning.create_category_captinship_planning(created_category_captinship_planning, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.CATEGORY_CAPTINSHIP_PLANNING)

    async def get_category_captinship_planning(self, category_plan_get_id:int):
        new_get_category_captinship_planning = TblCategoryCaptinshipPlanning.get_category_captinship_planning(category_plan_get_id, self.db)
        if not new_get_category_captinship_planning:
            raise HTTPException(status_code=404, detail="Get Category ID not found")
        return schema.CategoryCaptinshipPlanningResponse.model_validate(new_get_category_captinship_planning)
    
    async def get_group_category_captinship_planning(self, group_id:int) ->"TblCategoryCaptinshipPlanning":
        new_get_group_category_captinship_planning = self.db.query(TblCategoryCaptinshipPlanning).filter(TblCategoryCaptinshipPlanning.group_id == group_id).all()
        if not new_get_group_category_captinship_planning:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.CategoryCaptinshipPlanningResponse.model_validate(get_group) for get_group in new_get_group_category_captinship_planning]
    
    async def get_excel_category_captinship_planning(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_category_captinship_planning(self, request:List[schema.CategoryCaptinshipPlanningUpdate]):
        for req in request:
            updated_category_captinship_planning = CategoryCaptinshipPlanningBase.model_validate(req.model_dump())
            if updated_category_captinship_planning.category_plan_id is None:
                return CustomResponse(status="-1", message=Messages.CATEGORY_CAPTINSHIP_PLANNING_NOT)
            TblCategoryCaptinshipPlanning.update_category_captinship_planning(updated_category_captinship_planning.category_plan_id, updated_category_captinship_planning, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.CATEGORY_CAPTINSHIP_PLANNING_UPDATE)
    
    async def delete_category_captinship_planning(self, category_plan_id:int):
        deleted_category_captinship_planning = TblCategoryCaptinshipPlanning.delete_category_captinship_planning(category_plan_id, self.db)
        if not deleted_category_captinship_planning:
            return CustomResponse(status="-1", message=Messages.CATEGORY_CAPTINSHIP_PLANNING_NOT)
        return CustomResponse(status="1", message=Messages.CATEGORY_CAPTINSHIP_PLANNING_DELETE)