from typing import List
from fastapi import HTTPException
from sqlalchemy import func
from app.api.civil import schemas
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
from app.locale.messages import Messages

class CivilService:
    def __init__(self, db: Session, token):
        self.db = db
        self.token = token
        
    # async def create(self, request: CivilCreate):
    # # get the store_size value for the given store_format_id (or however it's linked)
    #     store_format = (self.db.query(TblStoreFormat).filter(
    #         TblStoreFormat.group_id == request.group_id,TblStoreFormat.store_format_type == request.store_format_type
    #         ).first())

    #     if store_format is None:
    #         raise HTTPException(status_code=404, detail="Store format not found")

    #     # compute using the real number, not a SQLAlchemy column
    #     total = request.rate_per_sqft * store_format

    #     # prepare data for model validation
    #     request_data = request.model_dump()
    #     request_data["total_sqft"] = total

    #     created = CivilBase.model_validate(request_data)
    #     TblCivil.create(created, self.db)
    #     self.db.commit()

    #     return CustomResponse(status="1", message=Messages.CIVIL)

        
    # async def create(self, request:CivilCreate):
    #     total = request.rate_per_sqft * TblStoreFormat.store_size
    #     request_data = request.model_dump()
    #     request_data["total_sqft"] = total
    #     created = CivilBase.model_validate(request_data)
    #     TblCivil.create(created, self.db)
    #     self.db.commit()
    #     return CustomResponse(status="1", message=Messages.CIVIL)

    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).all()
        if not civil_record:
            return CustomResponse(status="-1", message="No Civil Data Found")
        return [CivilResponse.model_validate(get_group) for get_group in 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
        ] 
    
    # 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
    #     ]
        
    async def update(self, request:List[schemas.CivilUpdate]):
        for req in request:
            store_format = (self.db.query(TblStoreFormat).filter(
                TblStoreFormat.group_id == req.group_id,TblStoreFormat.store_format_type == req.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 = req.rate_per_sqft * store_size
            updated = CivilBase.model_validate(req.model_dump())
            updated.total_sqft = total_sqft
            if updated.civil_id is None:
                return CustomResponse(status="-1", message=Messages.CIVIL_NOT)
            TblCivil.update(updated.civil_id, updated, self.db)
        self.db.commit()
        return CustomResponse(status="1", message=Messages.CIVIL_UPDATE)
    
    async def delete(self, civil_id:int):
        deleted = TblCivil.delete(civil_id, self.db)
        if not deleted:
            return CustomResponse(status="-1", message=Messages.CIVIL_NOT)
        return CustomResponse(status="1", message=Messages.CIVIL_DELETE)