self-constructing_graph/agent/models/db/user.py

from sqlalchemy import create_engine, Column, Integer, String, MetaData,DateTime,Text,ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from agent.db.database import Base
from datetime import datetime

# Many-to-many association table for Users and Roles
user_roles = Table('user_roles',
    Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('role_id', Integer, ForeignKey('roles.id'), primary_key=True)
)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(32), nullable=False)
    full_name = Column(String(64))
    email = Column(String(100), nullable=False)
    hashed_password = Column(String(64), nullable=True)
    status = Column(Integer, default=0)
    roles = relationship("Role", secondary=user_roles, back_populates="users")
    
class Session(Base):
    __tablename__ = 'sessions'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    session_id = Column(String(64), nullable=False)
    username = Column(String(32), nullable=False)
    full_name = Column(String(64))
    created = Column(DateTime, default=datetime.now())
    updated = Column(DateTime, default=datetime.now())



# Many-to-many association table for Roles and Permissions
role_permissions = Table('role_permissions',
    Base.metadata,
    Column('role_id', Integer, ForeignKey('roles.id'), primary_key=True),
    Column('permission_id', Integer, ForeignKey('permissions.id'), primary_key=True)
)

class Role(Base):
    __tablename__ = 'roles'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), unique=True, index=True, nullable=False)
    description = Column(String(255))

    users = relationship("User", secondary=user_roles, back_populates="roles")
    permissions = relationship("Permission", secondary=role_permissions, back_populates="roles")

class Permission(Base):
    __tablename__ = 'permissions'
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(50), unique=True, index=True, nullable=False) # e.g., "view_dashboard", "edit_settings"
    description = Column(String(255))
    # For menu items
    menu_name = Column(String(50)) # Display name for the menu
    menu_route = Column(String(100)) # Frontend route
    menu_icon = Column(String(50)) # Icon for the menu
    parent_id = Column(Integer, ForeignKey('permissions.id'), nullable=True) # For hierarchical menus

    roles = relationship("Role", secondary=role_permissions, back_populates="permissions")
    parent = relationship("Permission", remote_side=[id]) # Self-referential relationship for parent menu