from datetime import datetime
from typing import Union
from fastapi import HTTPException
from fastapi.security import OAuth2PasswordRequestForm
from pydantic import BaseModel, Field
from sqlalchemy import (INTEGER,TIMESTAMP,VARCHAR, ForeignKey, select,text,)
from sqlalchemy.orm import (Mapped,Session,mapped_column,relationship,object_session)
from app.models.main import Base
from app.models.main.admin import TblAdmin
from app.models.main.seafarer import TblSeafarers
from app.utils.schemas_utils import CustomModel
from sqlalchemy.ext.hybrid import hybrid_property

class UsersBase(CustomModel):
    
    usr_id: int | None = Field(default=None)
    user_name: str | None = Field(default=None)
    password: str | None = Field(default=None)
    role_id: int | None = Field(default=None) 
    entity_id: int | None = Field(default=None)
    created_at: datetime | None = Field(default=None)
    updated_at: datetime | None = Field(default=None)

class UsersFilter(CustomModel):
    usr_id: int | None = Field(default=None)
    user_name: str | None = Field(default=None)

class TblUsers(Base):
    __tablename__ = "tbl_users"

    usr_id: Mapped[int] = mapped_column("usr_id", INTEGER, primary_key=True, autoincrement=True)
    user_name: Mapped[str] = mapped_column("user_name", VARCHAR(255), nullable=True, server_default=None)
    password: Mapped[str] = mapped_column("password", VARCHAR(255), nullable=True, server_default=None)  
    role_id: Mapped[int] = mapped_column(INTEGER, ForeignKey("tbl_role.role_id"), nullable=True)
    entity_id: Mapped[int] = mapped_column(INTEGER, nullable=True)
    created_at: Mapped[str] = mapped_column("usr_created_At", TIMESTAMP, nullable=False, server_default=text("current_timestamp()"))
    updated_at: Mapped[str] = mapped_column("updated_at", TIMESTAMP, nullable=True, server_default=text("NULL ON UPDATE current_timestamp()"))

    role = relationship("TblRole", back_populates="users")

    @hybrid_property
    def entity(self):
        session = object_session(self)
        if session is None or self.entity_id is None or self.role_id is None:
            return None
        if self.role_id in {1, 2, 3}:
            stmt = select(TblAdmin).where(TblAdmin.admin_id == self.entity_id)
        elif self.role_id == 4:
            stmt = select(TblSeafarers).where(TblSeafarers.seafarer_id == self.entity_id)
        else:
            return None
        return session.execute(stmt).scalar_one_or_none()

    @classmethod
    def create(cls, data: UsersBase, db: Session) -> "TblUsers":
        data_dict = data.model_dump()
        new_data = cls(**data_dict)
        db.add(new_data)
        db.flush()
        return new_data
    
    @classmethod
    def get_usr_id(cls, usr_id: int, db: Session) -> UsersBase:
        get_data = db.query(cls).filter(cls.usr_id == usr_id).first()
        if not get_data:
            raise HTTPException(status_code=404, detail="User not found")
        return UsersBase.model_validate(get_data)
    
    @classmethod
    def get_by_user_filter(cls,request: Union[dict, UsersFilter, OAuth2PasswordRequestForm],db: Session) -> UsersBase | None:
        if isinstance(request, OAuth2PasswordRequestForm):
            filter_data = {"user_name": request.username}
        elif isinstance(request, BaseModel):
            filter_data = request.model_dump(exclude_none=True)
        elif isinstance(request, dict):
            filter_data = request
        else:
            raise TypeError("Unsupported request type passed to get_by_user_filter")
        user = db.query(cls).filter_by(**filter_data).first()
        if not user:
            return None
        return UsersBase(**user.__dict__)

    @classmethod
    def update(cls, usr_id: int, data: UsersBase, db: Session) -> "TblUsers":
        get_data = db.query(cls).filter(cls.usr_id == usr_id).first()
        if not get_data:
            raise HTTPException(status_code=404, detail="User not found")
        data_dict = data.model_dump()
        for key, value in data_dict.items():
            setattr(get_data, key, value)
        db.add(get_data)
        db.flush()
        return get_data
   
              

    
    # @classmethod
    # def get_by_filter(cls, request: UsersFilter, db: Session) -> UsersBase:
    #     filter_data = request.model_dump(exclude_none=True)  # Dump filter data to dictionary
    #     get_data = db.query(cls).filter_by(**filter_data).first()  # Query for user by filter data
    #     return UsersBase.model_validate(get_data)  # Validate and return user data
    
    # @classmethod
    # def get_by_filter(cls, request: UsersFilter, db: Session) -> UsersBase | None:
    #     filter_data = request.model_dump(exclude_none=True)
    #     get_data = db.query(cls).filter_by(**filter_data).first()

    #     if get_data is None:
    #         return None  # ← Avoid validation error

    #     return UsersBase.model_validate(get_data)