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_role_organ_id = Column(Integer, ForeignKey('sys_user_role_organ.id')) 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