from sqlalchemy import func
from app.api.display_racking_create.schemas import DisplayRackingUnitCreate,DisplayRackingUnitResponce
from app.models.main.display_racking_unit import DisplayRackingUnitBase, TblDisplayRackingUnit
from app.utils.schemas_utils import CustomResponse

class DisplayRackingUnitService:
    def __init__(self, db, token):
        self.db = db
        self.token = token

    async def create_display_racking_unit(self, request: DisplayRackingUnitCreate):
        total = request.cost_per_unit * request.number_of_units
        rack_data = DisplayRackingUnitBase(
            group_id=request.group_id,
            store_format_type=request.store_format_type,
            type_display_racking_unit=request.type_display_racking_unit,
          
            number_of_units=request.number_of_units,
            cost_per_unit=request.cost_per_unit,
            total=total,
            remarks=request.remarks,
        )
        rack_record = TblDisplayRackingUnit.create(rack_data, self.db)
        self.db.commit()
        self.db.refresh(rack_record)
        response = DisplayRackingUnitResponce.model_validate(rack_record, from_attributes=True)
        return CustomResponse(status="1", message="Display Racking Unit", data=response)
    
    async def get_racking_by_group_id(self, group_id: int):
        civil_record = self.db.query(TblDisplayRackingUnit).filter(TblDisplayRackingUnit.group_id == group_id).first()
        if not civil_record:
            return CustomResponse(status="-1", message="No Civil Data Found")
        return CustomResponse(status="1",message="Civil data fetched successfully",data=DisplayRackingUnitResponce.model_validate(civil_record))

    def get_subtotal_by_store_format(self, group_id: int):
        results = (
            self.db.query(
                TblDisplayRackingUnit.store_format_type,
                func.sum(TblDisplayRackingUnit.total).label("subtotal")
            )
            .filter(TblDisplayRackingUnit.group_id == group_id)
            .group_by(TblDisplayRackingUnit.store_format_type)
            .all()
        )

        return [
            {"store_format_type": store_format_type, "subtotal": subtotal or 0.0}
            for store_format_type, subtotal in results
        ]