from sqlalchemy.orm import Session
from app.api.company_mapping.schemas import CompanyCourseRoleCreate, CompanyRoleCreate
from app.models.main.company_course import CompanyCourseRoleMapBase, TblCompanyCourseRoleMap
from app.models.main.company_role import CompanyRoleMapBase, TblCompanyRoleMap
from app.models.main.course_list import TblCourseList
from app.models.main.seafarer_role import TblSeafarerRole
from app.utils.schemas_utils import CustomResponse

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

    async def create_role_mappings(self, requests: list[CompanyRoleCreate]):
        new_mappings = []
        for req in requests:
            created_role = CompanyRoleMapBase.model_validate(req)
            new_mapping = TblCompanyRoleMap.create(created_role, self.db)
            new_mappings.append(new_mapping)

        self.db.commit()
        return CustomResponse(
            status="1",
            message=f"{len(new_mappings)} Role(s) Created Successfully"
        )
    
    async def get_company_roles(self, company_id: int):
        roles = (
            self.db.query(TblCompanyRoleMap)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblCompanyRoleMap.sf_role_id)
            .filter(TblCompanyRoleMap.company_id == company_id)
            .all()
        )

        if not roles:
            return CustomResponse(status="0", message="No roles found for this company")

        role_list = [
            {
                "role_map_id": r.role_map_id,
                "sf_role_id": r.seafarer_role.sf_role_id,
                "sf_role_name": r.seafarer_role.sf_role_name
            }
            for r in roles
        ]

        return CustomResponse(status="1", message="Success", data=role_list)

    async def create_course_mappings(self, requests: list[CompanyCourseRoleCreate]):
        new_mappings = []
        for req in requests:
            created_mapping = CompanyCourseRoleMapBase.model_validate(req)
            new_mapping = TblCompanyCourseRoleMap.create(created_mapping, self.db)
            new_mappings.append(new_mapping)

        self.db.commit()
        return CustomResponse(
            status="1",
            message=f"{len(new_mappings)} Course-Role mapping(s) created successfully"
        )
    
    async def get_company_courses_roles(self, company_id: int, role_id: int):
        query = (
            self.db.query(TblCompanyCourseRoleMap)
            .join(TblCompanyRoleMap, TblCompanyRoleMap.role_map_id == TblCompanyCourseRoleMap.role_map_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblCompanyRoleMap.sf_role_id)
            .join(TblCourseList, TblCourseList.course_id == TblCompanyCourseRoleMap.course_id)
            .filter(TblCompanyRoleMap.company_id == company_id)
        )

        if role_id:
            query = query.filter(TblCompanyRoleMap.sf_role_id == role_id)

        mappings = query.all()

        if not mappings:
            return CustomResponse(status="0", message="No course-role mappings found")

        data = [
            {
                "sf_role_id": m.company_role_map.seafarer_role.sf_role_id,
                "sf_role_name": m.company_role_map.seafarer_role.sf_role_name,
                "course_id": m.course.course_id,
                "course_name": m.course.course_name,
            }
            for m in mappings
        ]

        return CustomResponse(status="1", message="Success", data=data)

    async def get_courses_by_role_mapid(self, role_map_id: int):
        mappings = (
            self.db.query(TblCompanyCourseRoleMap)
            .join(TblCompanyRoleMap, TblCompanyRoleMap.role_map_id == TblCompanyCourseRoleMap.role_map_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblCompanyRoleMap.sf_role_id)
            .join(TblCourseList, TblCourseList.course_id == TblCompanyCourseRoleMap.course_id)
            .filter(TblCompanyCourseRoleMap.role_map_id == role_map_id)
            .all()
        )

        if not mappings:
            return CustomResponse(status="0", message="No mappings found for this role_map_id")

        data = []
        for m in mappings:
            data.append({
                "course_map_id": m.course_map_id,
                "role_map_id": m.role_map_id,
                "sf_role_id": m.company_role_map.seafarer_role.sf_role_id,
                "sf_role_name": m.company_role_map.seafarer_role.sf_role_name,
                "company_id": m.company_role_map.company_id,
                "course_id": m.course.course_id,
                "course_name": m.course.course_name,
            })

        return CustomResponse(status="1", message="Success", data=data)



    # async def get_company_courses_roles(self, company_id: int, role_id: int | None = None):
    #     query = (
    #         self.db.query(TblCompanyCourseRoleMap)
    #         .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblCompanyCourseRoleMap.sf_role_id)
    #         .join(TblCourseList, TblCourseList.course_id == TblCompanyCourseRoleMap.course_id)
    #         .filter(TblCompanyCourseRoleMap.company_id == company_id)
    #     )

    #     if role_id:
    #         query = query.filter(TblCompanyCourseRoleMap.sf_role_id == role_id)

    #     mappings = query.all()

    #     if not mappings:
    #         return CustomResponse(status="0", message="No course-role mappings found")

    #     data = [
    #         {
    #             "sf_role_id": m.seafarer_role.sf_role_id,
    #             "sf_role_name": m.seafarer_role.sf_role_name,
    #             "course_id": m.course.course_id,
    #             "course_name": m.course.course_name,
    #         }
    #         for m in mappings
    #     ]

    #     return CustomResponse(status="1", message="Success", data=data)