from fastapi import HTTPException
from pydantic import Field
from sqlalchemy import (INTEGER,VARCHAR,)
from sqlalchemy.orm import (Mapped,Session,mapped_column,)
from app.models.main import Base
from app.utils.schemas_utils import CustomModel

class UsersBase(CustomModel):
    __tablename__ = "tbl_users"

    usr_id: int | None = Field(default=None)  # User ID
    email: str | None = Field(default=None)  # Email
    name: str | None = Field(default=None)  # Name
    password: str | None = Field(default=None)  # Password
    mobile_no: str | None = Field(default=None)  # Mobile number
    # created_at: datetime | None = Field(default=None)  # Created at timestamp
    # updated_at: datetime | None = Field(default=None)  # Updated at timestamp

class UsersFilter(CustomModel):
    usr_id: int | None = Field(default=None)  # User ID for filtering
    email: str | None = Field(default=None)  # Email for filtering

class TblUsers(Base):
    __tablename__ = "tbl_users"

    usr_id: Mapped[int] = mapped_column("usr_id", INTEGER, primary_key=True, autoincrement=True)  # User ID column
    email: Mapped[str] = mapped_column("usr_email", VARCHAR(255), nullable=True, server_default=None)  # Email column
    name: Mapped[str] = mapped_column("usr_name", VARCHAR(255), nullable=True, server_default=None)  # Name column
    password: Mapped[str] = mapped_column("usr_password", VARCHAR(255), nullable=True, server_default=None)  # Password column
    mobile_no: Mapped[str] = mapped_column("usr_mobileNo", VARCHAR(255), nullable=True, server_default=None)  # Mobile number column
    age: Mapped[str] = mapped_column("usr_age", VARCHAR(255), nullable=True, server_default=None)
    address: Mapped[str] = mapped_column("usr_address", VARCHAR(255), nullable=True, server_default=None)
    last_login_status: Mapped[str] = mapped_column("usr_lastLogin_status", VARCHAR(255), nullable=True, server_default=None)
    # created_at: Mapped[str] = mapped_column("usr_created_At", TIMESTAMP, nullable=False, server_default=text("current_timestamp()"))  # Created at column
    # updated_At: Mapped[str] = mapped_column("usr_updated_At", TIMESTAMP, nullable=True, server_default=text("NULL ON UPDATE current_timestamp()"))  # Updated at column
    
    @classmethod
    def create(cls, data: UsersBase, db: Session) -> "TblUsers":
        data_dict = data.model_dump()  # Dump model data to dictionary
        new_data = cls(**data_dict)  # Create new instance with data
        db.add(new_data)  # Add to session
        db.flush()  # Flush to the database
        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()  # Query for user by ID
        if not get_data:
            raise HTTPException(status_code=404, detail="User not found")  # Raise exception if user not found
        return UsersBase.model_validate(get_data)  # Validate and return user data

    @classmethod
    def update(cls, usr_id: int, data: UsersBase, db: Session) -> "TblUsers":
        get_data = db.query(cls).filter(cls.usr_id == usr_id).first()  # Query for user by ID
        if not get_data:
            raise HTTPException(status_code=404, detail="User not found")  # Raise exception if user not found
        data_dict = data.model_dump()  # Dump model data to dictionary
        for key, value in data_dict.items():
            setattr(get_data, key, value)  # Update user data
        db.add(get_data)  # Add to session
        db.flush()  # Flush to the database
        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