from sqlalchemy import func
from app.api.civil.schemas import CivilCreate, CivilResponse
from app.models.main.civil import CivilBase, TblCivil
from sqlalchemy.orm import Session
from app.models.main.store_formate import TblStoreFormat
from app.utils.schemas_utils import CustomResponse

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

    async def create_civil(self, request: CivilCreate):
        store_format = (self.db.query(TblStoreFormat).filter(
                TblStoreFormat.group_id == request.group_id,TblStoreFormat.store_format_type == request.store_format_type
            ).first())
        if not store_format:
            return CustomResponse(status="0", message="Store format not found for given group and format type", data={})
        store_size = store_format.store_size or 0
        total_sqft = request.rate_per_sqft * store_size
        civil_schema = CivilResponse.model_validate(request)
        civil_schema.total_sqft = total_sqft
        civil_record = TblCivil.create(CivilBase.model_validate(civil_schema.model_dump()), self.db)
        self.db.commit()
        self.db.refresh(civil_record)
        return CustomResponse(status="1",message="Civil created successfully",data={"civil_id": civil_record.civil_id,"total_sqft": civil_record.total_sqft})


    async def get_civil_by_group_id(self, group_id: int):
        civil_record = self.db.query(TblCivil).filter(TblCivil.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=CivilResponse.model_validate(civil_record))
    
    def get_subtotal_by_store_format(self, group_id: int):
        results = (
            self.db.query(
                TblCivil.store_format_type,
                func.sum(TblCivil.total_sqft).label("subtotal")
            )
            .filter(TblCivil.group_id == group_id)
            .group_by(TblCivil.store_format_type)
            .all()
        )

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