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

class GrossMarginContributionService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def create_gross_margin(self, request:GrossMarginContributionCreate):
        created_gross_margin = GrossMarginContributionBase.model_validate(request.model_dump())
        TblGrossMarginContribution.create_gross_margin_contribution(created_gross_margin, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.GROSS_MARGIN_CONTRIPUTION)
    
    async def get_gross_margin_contribution(self, gross_margin_get_id:int):
        new_get_gross_margin_contribution = TblGrossMarginContribution.get_gross_margin_contribution(gross_margin_get_id, self.db)
        if not new_get_gross_margin_contribution:
            raise HTTPException(status_code=404, detail="Get Gross ID not found")
        return schema.GrossMarginContributionResponse.model_validate(new_get_gross_margin_contribution)
    
    async def get_group_gross_margin_contribution(self, group_id:int) ->"TblGrossMarginContribution":
        new_get_gross_margin_contribution = self.db.query(TblGrossMarginContribution).filter(TblGrossMarginContribution.group_id == group_id).all()
        if not new_get_gross_margin_contribution:
            raise HTTPException(status_code=404, detail="Get Group ID not found")
        return [schema.GrossMarginContributionResponse.model_validate(get_group_data) for get_group_data in new_get_gross_margin_contribution]
        
    async def update_gross_margin_contribution_id(self, request:List[schema.GrossMarginContributionUpdate]):
        for req in request:
            updated_gross_margin_contribution = GrossMarginContributionBase.model_validate(req.model_dump())
            if updated_gross_margin_contribution.gross_id is None:
                return CustomResponse(status="-1", message=Messages.GROSS_MARGIN_CONTRIPUTION_NOT)
            TblGrossMarginContribution.update_gross_margin_contribution(updated_gross_margin_contribution.gross_id, updated_gross_margin_contribution, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.GROSS_MARGIN_CONTRIPUTION_UPDATE)
    
    async def delete_gross_margin_contribution_id(self, gross_id:int):
        deleted_gross_margin_contribution = TblGrossMarginContribution.delete_gross_margin_contribution(gross_id, self.db)
        if not deleted_gross_margin_contribution:
            return CustomResponse(status="-1", message=Messages.GROSS_MARGIN_CONTRIPUTION_NOT)
        return CustomResponse(status="1", message=Messages.GROSS_MARGIN_CONTRIPUTION_DELETE)
    
    async def get_excel(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="Group 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})

        # --- CHECK IF CATEGORY COLUMN EXISTS ---
        column_name = "Category Name "
        if column_name not in df.columns:
            raise HTTPException(
                status_code=400,
                detail=f"'{column_name}' column not found in uploaded Excel file"
            )

        # --- GET CATEGORY VALUES ---
        category_list = df[column_name].dropna().tolist()

        if not category_list:
            return {"message": "No categories found in Excel", "categories": []}

        return {"categories": category_list}

    # async def  get_excel(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()