#!/usr/bin/env python
# _*_ coding:utf-8 _*_
import paramiko
import sys
import os
import socket
import getpass
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Table, DateTime, Boolean, Enum
from sqlalchemy import or_, and_
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from sqlalchemy.sql import func
# from sqlalchemy_utils import ChoiceType  # 需要安装sqlalchemy_utils这个组件
from paramiko.py3compat import u

# 首先建库，并且让mysql给权限登录
engine = create_engine("mysql+pymysql://root:Aa132123.@10.1.1.200:3306/s14?charset=utf8", max_overflow=5, )
Base = declarative_base()  # 创建orm的基类


class Group(Base):  # 主机组
    __tablename__ = ‘group‘
    group_id = Column(Integer, primary_key=True, autoincrement=True)
    group_name = Column(String(32), nullable=False, unique=True)


class Host(Base):  # 主机和主机组，是一对多关系：即一个主机必须属于一个组
    __tablename__ = ‘host‘
    host_id = Column(Integer, primary_key=True, autoincrement=True)
    host_ip = Column(String(32), nullable=False, unique=True)
    hostname = Column(String(255), nullable=False, unique=True)
    port = Column(String(32), nullable=False)
    group_id = Column(Integer, ForeignKey(‘group.group_id‘), nullable=False)
    re_group_id = relationship(‘Group‘, backref=‘a‘)


class UserFile(Base):  # 用户表
    __tablename__ = ‘user_file‘
    user_id = Column(Integer, primary_key=True, autoincrement=True)
    user_name = Column(String(32), nullable=False, unique=True)
    gender = Column(Enum(‘male‘, ‘female‘), server_default=‘male‘)  # 让用户选择只能输入其中一个
    password = Column(String(32), nullable=False)


class UserToGroup(Base):  # 用户和主机组的关系表，多对多：一个用户可以属于多个主机组，一个主机组可以有多个用户
    __tablename__ = ‘user_to_group‘
    HU_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey(‘user_file.user_id‘), nullable=False)
    group_id = Column(Integer, ForeignKey(‘group.group_id‘), nullable=False)
    __table_args__ = (
        UniqueConstraint(‘user_id‘, ‘group_id‘),
    )  # 为了减少错误，我们这里使用联合唯一索引
    re_group_id = relationship(‘Group‘, backref=‘b‘)
    re_user_id = relationship(‘UserFile‘, backref=‘c‘)


class RemoteUserFile(Base):  # 主机用户
    __tablename__ = ‘remote_user_file‘
    user_id = Column(Integer, primary_key=True, autoincrement=True)
    user_name = Column(String(32), nullable=False, unique=True)
    password = Column(String(32), nullable=False)
    # AuthTypes = [
    #     (‘p‘, ‘SSH/Password‘),
    #     (‘r‘, ‘SSH/KEY‘)
    # ]
    # auth_type = Column(ChoiceType(AuthTypes))
    auth_type = Column(Enum(‘SSH/Password‘, ‘SSH/KEY‘), default=‘SSH/Password‘)


class RemoteUserToHost(Base):  # 主机用户和主机的关系表，多对多的关系：一个主机可以有多个主机用户，一个用户有多个主机
    __tablename__ = ‘remote_user_to_host‘
    RH_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey(‘remote_user_file.user_id‘), nullable=False)
    host_id = Column(Integer, ForeignKey(‘host.host_id‘), nullable=False)
    __table_args__ = (
        UniqueConstraint(‘user_id‘, ‘host_id‘),
    )
    re_user_id = relationship(‘RemoteUserFile‘, backref=‘d‘)
    re_host_id = relationship(‘Host‘, backref=‘e‘)


class AuditLog(Base):
    __tablename__ = ‘audit_log‘
    audit_id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey(‘user_file.user_id‘), nullable=False)
    remote_user_id = Column(Integer, ForeignKey(‘remote_user_file.user_id‘), nullable=False)
    host_id = Column(Integer, ForeignKey(‘host.host_id‘), nullable=False)
    cmd = Column(String(65535))
    handle_time = Column(DateTime, server_default=func.now())  
    # server_default表示交给数据库处理，default表示交给程序处理
    
    re_user_id = relationship(‘UserFile‘, backref=‘f‘)
    re_remote_user_id = relationship(‘RemoteUserFile‘, backref=‘g‘)
    re_host_id = relationship(‘Host‘, backref=‘h‘)


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()