from sqlalchemy.ext.asyncio import AsyncSession
from src.models.member.models import Member
from src.models.project.models import Project
from src.models.users.models import HrmsEmployeeProfile
from sqlalchemy import select, func
from datetime import datetime,timezone
from src.request.member.MemberRequest import MemberCreateRequest, MemberUpdateRequest, MemberWithProject, MemberDDLReponse 

class MemberRepo:
    def __init__(self,session:AsyncSession) -> None:
        self.session = session
        
    async def create(self, member:MemberCreateRequest, user_id: int) -> Member:
        record=Member(**member.model_dump())
        record.created_by = user_id
        self.session.add(record)
        await self.session.commit()
        await self.session.refresh(record)
        return record

    async def update(self, id: int, update_data: MemberUpdateRequest, user_id:int) -> Member:                       
        db_state=await self.find(id)
        if not db_state:
            return None
        for key,value in update_data.model_dump(exclude_unset=True).items():
            setattr(db_state,key,value)

        db_state.updated_at = datetime.now(timezone.utc)
        db_state.updated_by=user_id
        await self.session.commit()
        await self.session.refresh(db_state)
        return db_state
    
    async def find(self, id: int) -> Member | None:
        result=await self.session.execute(select(Member).where(Member.id==id))
        return result.scalar_one_or_none()
    
    async def readAll(self) -> list[Member]:
        result = await self.session.execute(select(Member))
        return result.scalars().all()


    async def get_members_by_project(self, project_id: int) -> list[MemberWithProject]:
        stmt = (
            select(
                Member.id.label("member_id"),
                Member.member_hrms_user_id.label("user_id"),
               # (HrmsEmployeeProfile.first_name + " " + HrmsEmployeeProfile.last_name).label("member_name"), 
                func.trim((func.coalesce(HrmsEmployeeProfile.first_name, '') + 
                    " " + 
                func.coalesce(HrmsEmployeeProfile.last_name, ''))).label("member_name"),
                Member.is_active,
                Project.id.label("project_id"),
                Project.name.label("project_name"),
            )
            .join(Project, Project.id == Member.project_id)
            .join(HrmsEmployeeProfile, Member.member_hrms_user_id == HrmsEmployeeProfile.user_id)
            .where(Project.id == project_id)
        )

        result = await self.session.execute(stmt)
        rows = result.all()

        return [MemberWithProject(**row._asdict()) for row in rows]    
  
    async def get_members_ddl(self, project_id: int) -> list[MemberDDLReponse]:
        stmt = (
            select(
                Member.member_hrms_user_id.label("id"),
                #(HrmsEmployeeProfile.first_name + " " + HrmsEmployeeProfile.last_name).label("name"), 
                func.trim((func.coalesce(HrmsEmployeeProfile.first_name, '') + 
                    " " + 
                func.coalesce(HrmsEmployeeProfile.last_name, ''))).label("name"),
                
            )
            .join(Project, Project.id == Member.project_id)
            .join(HrmsEmployeeProfile, Member.member_hrms_user_id == HrmsEmployeeProfile.user_id)
            .where(Project.id == project_id)
        )
        result = await self.session.execute(stmt)
        rows = result.all()

        return [MemberDDLReponse(**row._asdict()) for row in rows]