import uuid
from datetime import datetime

from sqlalchemy import (
    TIMESTAMP,
    Boolean,
    Column,
    Date,
    ForeignKey,
    Integer,
    String,
    Text,
    func,
    literal,
    select,
)
from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property
from sqlalchemy.orm import aliased, relationship
from sqlalchemy.sql import select

from src.apps.role_permission.models.role_model import Role
from src.apps.role_permission.models.user_permission_model import UserPermissions
from src.core.models.base import Base


class Users(Base):
    """
    Database model for users.
    """

    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(String(36), unique=True, index=True, default=lambda: str(uuid.uuid4()))
    username = Column(String(100), nullable=False)

    email = Column(String(100), nullable=False, unique=True, index=True)
    phone = Column(String(15), nullable=True)
    password = Column(String(100), nullable=False)

    first_name = Column(String(100), nullable=True)
    last_name = Column(String(100), nullable=True)
    
    gender = Column(String(20), nullable=True)

    avatar_id = Column(Integer, ForeignKey("files.id"), nullable=True)

    is_active = Column(Boolean, nullable=False)
    is_verified = Column(Boolean, default=False)

    verification_token = Column(String(100), nullable=True, index=True)
    verification_token_expires_at = Column(TIMESTAMP, nullable=True)

    created_at = Column(TIMESTAMP, nullable=False, default=datetime.utcnow)
    updated_at = Column(TIMESTAMP, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)
    deleted_at = Column(TIMESTAMP, nullable=True)

    # Relationships
    sessions = relationship("Session", back_populates="user")
    avatar = relationship("File")

    roles = relationship(
        "UserPermissions",
        primaryjoin="Users.id==UserPermissions.user_id",
        back_populates="user",
        uselist=True,
    )

    @hybrid_property
    def all_roles(self):
        """
        Returns a list of all roles associated with the user.
        """
        return [role.role.slug for role in self.roles]

    @all_roles.expression
    def all_roles(cls):
        """
        SQL expression to get all roles associated with the user.
        """
        return (
            select(func.array_agg(Role.slug))
            .select_from(Role)
            .join(UserPermissions, UserPermissions.role_id == Role.id)
            .where(UserPermissions.user_id == cls.id)
            .correlate(cls)
            .scalar_subquery()
        )

    @hybrid_method
    def has_role(self, slug):
        return slug in self.all_roles

    @has_role.expression
    def has_role(cls, slug):
        return (
            select(literal(True))
            .select_from(UserPermissions)
            .join(Role, UserPermissions.role_id == Role.id)
            .where(UserPermissions.user_id == cls.id)
            .where(Role.slug == slug)
            .exists()
        )