from sqlalchemy import create_engine,Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey,Table
from sqlalchemy.orm import relationship


############创建数据表结构######################3
# 创建实例，并连接work库
engine = create_engine("mysql+pymysql://root:@localhost/home_work?charset=utf8")
Base = declarative_base()  # 生成orm基类

# 班级与讲师的对应关系表
teacher_m2m_class = Table("teacher_m2m_class",Base.metadata,
                          Column("teacher_id", Integer, ForeignKey("teacher.teacher_id")),
                          Column("class_id", Integer, ForeignKey("class.class_id")),
                          )

# 班级与学生的对应关系表
class_m2m_student = Table("class_m2m_student",Base.metadata,
                          Column("class_id",Integer,ForeignKey("class.class_id")),
                          Column("stu_id", Integer, ForeignKey("student.stu_id")),
                          )

class Class_m2m_Lesson(Base):
    ‘‘‘班级和课节对应表‘‘‘
    __tablename__ = "class_m2m_lesson"
    id =  Column(Integer, primary_key=True)
    class_id = Column(Integer,ForeignKey("class.class_id"))
    lesson_id = Column(Integer, ForeignKey("lesson.lesson_id"))

    classes = relationship("Class",backref="class_m2m_lessons")
    lessons = relationship("Lesson", backref="class_m2m_lessons")

    def __repr__(self):
        return "%s %s" % (self.classes,self.lessons)

class Study_record(Base):
    "上课记录"
    __tablename__ = "study_record"
    id = Column(Integer,primary_key=True)
    class_m2m_lesson_id = Column(Integer,ForeignKey("class_m2m_lesson.id"))
    stu_id = Column(Integer, ForeignKey("student.stu_id"))
    status = Column(String(32),nullable=False)
    score = Column(Integer,nullable=True)

    class_m2m_lessons = relationship("Class_m2m_Lesson",backref="my_study_record")
    students = relationship("Student", backref="my_study_record")

    def __repr__(self):
       return  "\033[35;0m%s,%s,状态：【%s】,成绩：【%s】\33[0m"%(self.class_m2m_lessons,self.students,self.status,self.score)

class Teacher(Base):
    "讲师"
    __tablename__ = "teacher"
    teacher_id = Column(Integer, primary_key=True)
    teacher_name = Column(String(32), nullable=False, unique=True)   #唯一

    classes = relationship("Class", secondary=teacher_m2m_class, backref="teachers")

    def __repr__(self):
        return "讲师：【%s】"%self.teacher_name

class Class(Base):
    "班级"
    __tablename__ ="class"
    class_id = Column(Integer, primary_key=True)
    class_name = Column(String(32), nullable=False,unique=True)
    course =  Column(String(32), nullable=False)

    students = relationship("Student",secondary=class_m2m_student,backref="classes")

    def __repr__(self):
        return "班级名：【%s】"%self.class_name

class Student(Base):
    "学生"
    __tablename__ ="student"
    stu_id = Column(Integer, primary_key=True)
    stu_name = Column(String(32), nullable=False, unique=True)
    QQ =  Column(Integer(), nullable=False)

    def __repr__(self):
        return "学生名：【%s】"%self.stu_name

class Lesson(Base):
    "课节"
    __tablename__ = "lesson"
    lesson_id = Column(Integer, primary_key=True)
    lesson_name = Column(String(32), nullable=False, unique=True)

    def __repr__(self):
        return "节次名：【%s】"%self.lesson_name


Base.metadata.create_all(engine)

from sqlalchemy import desc
from .models import Teacher,Class,Student,Lesson,Class_m2m_Lesson,Study_record
import random

class  Student_Center(object):
    "学生视图"
    def __init__(self,session):
        self.session = session
        self.authentication()
        self.handler()

    def handler(self):
        while True:
            print(‘‘‘\n\33[35;1m———欢迎来【%s】进入学员管理系统————\33[0m \n
            \33[34;0mup_homework 上传作业
            show_homework 查看作业成绩
            show_rank 查看班级排名
            exit 退出管理系统
            \33[0m‘‘‘ % self.student_obj.stu_name)
            user_func = input("\033[34;0m请输入进行操作的命令:\033[0m").strip()
            if hasattr(self, user_func):
                getattr(self, user_func)()

    def authentication(self):
        ‘‘‘认证‘‘‘
        while True:
            student_name = input("\033[34;0m请输入学生名:\033[0m").strip()
            self.student_obj = self.session.query(Student).filter_by(stu_name=student_name).first()
            if not self.student_obj:
                print("\33[31;1m输入错误：请输入有效的学生名\33[0m")
                continue
            else:
                # print(self.teacher_obj)
                break

    def up_homework(self):
        "上传作业"
        class_name = input("\033[34;0m请输入班级名:\033[0m")

        for class_obj in self.student_obj.classes:
            print(class_obj.class_name)
            if class_name == class_obj.class_name:
                lesson_name = input("\033[34;0m请输入的课节名（lesson）:\033[0m")
                lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

                if lesson_obj:  # 输入的lesson名字存在
                    class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                        Class_m2m_Lesson.class_id == class_obj.class_id).                         filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()
                    if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                        study_record_obj = self.session.query(Study_record).filter(
                            Study_record.class_m2m_lesson_id == class_m2m_lesson_obj.id).filter(
                            Study_record.stu_id == self.student_obj.stu_id).first()

                        if study_record_obj:  # 上课记录存在
                            score = random.randint(10,100)
                            study_record_obj.score = score
                            self.session.commit()
                            print("上传成功")
                        else:
                            print("\33[31;1m系统错误：当前上课记录已经创建\33[0m")
                else:
                    print("\33[31;1m系统错误：lesson未创建\33[0m")

    def show_homework(self):
        ‘‘‘查看作业成绩‘‘‘
        class_name = input("\033[34;0m请输入学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj:
            lesson_name = input("\033[34;0m请输入学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

            if lesson_obj:  # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                    Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()

                if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                    study_record_objs = self.session.query(Study_record).filter(
                        Study_record.class_m2m_lesson_id == class_m2m_lesson_obj.id).all()
                    for obj in study_record_objs:
                        if obj.stu_id == self.student_obj.stu_id:
                            print(obj)

    def show_rank(self):
        ‘‘‘查看排名‘‘‘

        class_name = input("\033[34;0m请输入学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj:
            lesson_name = input("\033[34;0m请输入学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

            if lesson_obj:  # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                    Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()

                if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                    score_rank_obj = self.session.query(Study_record).order_by(desc(Study_record.score))
                    for obj in score_rank_obj:
                        if obj.class_m2m_lesson_id == class_m2m_lesson_obj.id:
                            print(obj)

from .models import Teacher,Class,Student,Lesson,Class_m2m_Lesson,Study_record,class_m2m_student

class  Teacher_Center(object):
    "讲师视图"
    def __init__(self,session):
        self.session = session
        self.authentication()
        self.handler()

    def handler(self):
        while True:
            print(‘‘‘\n\33[35;1m———欢迎来【%s】进入讲师管理系统————\33[0m \n
        \33[34;0mshow_classes 显示可管理的班级
        add_class 创建班级
        add_student 添加学员
        add_lesson 添加课程节次
        add_studyrecord 创建上课记录
        modify_scores 修改学员成绩
        exit 退出管理系统
        \33[0m‘‘‘ % self.teacher_obj.teacher_name)
            user_func = input("\033[34;0m请输入进行操作的命令:\033[0m").strip()
            if hasattr(self, user_func):
                getattr(self, user_func)()


    def authentication(self):
        ‘‘‘认证‘‘‘
        while True:
            teacher_name = input("\033[34;0m请输入讲师名:\033[0m").strip()
            self.teacher_obj = self.session.query(Teacher).filter_by(teacher_name=teacher_name).first()
            if not self.teacher_obj:
                print("\33[31;1m输入错误：请输入有效的讲师名\33[0m")
                continue
            else:
                #print(self.teacher_obj)
                break


    def add_class(self):
        ‘‘‘创建班级‘‘‘
        class_name = input("\033[34;0m请输入创建班级的名称:\033[0m")
        course = input("\033[34;0m请输入创建班级的类型:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()
        if not class_obj:
            class_new = Class(class_name=class_name,course=course)
            self.teacher_obj.classes.append(class_new)
            self.session.add(class_new)
            self.session.commit()
            print("\033[34;1m班级创建成功\033[0m")
        else:
            print("\33[31;1m系统错误：班级已经存在\33[0m")


    def show_classes(self):
        ‘‘‘查看所有的班级‘‘‘
        print(self.teacher_obj)
        for class_obj in self.teacher_obj.classes:
            print("讲师：【%s】\t班级：【%s】\t类型：【%s】" % (class_obj.teachers[0].teacher_name,
                                                 class_obj.class_name,class_obj.course))


    def add_student(self):
        ‘‘‘添加学员‘‘‘
        class_name = input("\033[34;0m请输入要添加学员的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:
            stu_name = input("\033[34;0m请输入学员的姓名:\033[0m")
            QQ = input("\033[34;0m请输入学员的QQ号:\033[0m")
            student_obj = self.session.query(Student).filter_by(QQ = QQ).first()

            if student_obj:
                if student_obj.stu_name not in class_obj.students:
                    class_obj.students.append(student_obj)
                    self.session.add(class_obj)
                    self.session.commit()
                    print("\033[34;1m学员添加成功\033[0m")
                else:
                    print("\33[31;1m系统错误：学员已经存在此班中\33[0m")

            elif not student_obj:
                student_new = Student(stu_name=stu_name, QQ=QQ)
                class_obj.students.append(student_new)
                self.session.add(student_new)
                self.session.commit()
                print("\033[34;1m学员添加成功\033[0m")
            else:
                print("\33[31;1m系统错误：学员已经存在\33[0m")
        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def add_lesson(self):
        ‘‘‘添加课程节次‘‘‘
        class_name = input("\033[34;0m请输入要添加lesson的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:     #输入的班级名存在且在属于当前老师管理
            lesson_name = input("\033[34;0m请输入添加的lesson名（类day1）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()
            if not lesson_obj:                                          #输入的lesson名字不存在
                print("lesson  不存在")
                lesson_obj = Lesson(lesson_name=lesson_name)                #增加lesson表数据
                self.session.add(lesson_obj)
                self.session.commit()

            rest = self.session.query(Class_m2m_Lesson).filter(Class_m2m_Lesson.class_id==class_obj.class_id).                filter(Class_m2m_Lesson.lesson_id==lesson_obj.lesson_id).first()
            if not rest:                                                #如果class_m2m_lesson没有存储班级和课节的对应关系
                print("class:%s----lesson:%s"%(class_obj.class_id,lesson_obj.lesson_id))
                class_m2m_lesson_new = Class_m2m_Lesson(class_id=class_obj.class_id,lesson_id=lesson_obj.lesson_id)
                self.session.add(class_m2m_lesson_new)                  #创建class_m2m_lesson对应关系
                self.session.commit()

        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def add_studyrecord(self):
        ‘‘‘添加学习记录‘‘‘
        class_name = input("\033[34;0m请输入要添加学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:  # 输入的班级名存在且在属于当前老师管理
            lesson_name = input("\033[34;0m请输入添加学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()
            if lesson_obj:                                       # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()
                if class_m2m_lesson_obj:                                            # 班级对应的课lesson表数据存在

                    study_record_obj = self.session.query(Study_record).filter_by(class_m2m_lesson_id=class_m2m_lesson_obj.id).first()
                    if not study_record_obj:                                                    # 上课记录为创建
                        for student_obj in class_obj.students:
                            status = input("输入学生 %s 的上课状态（yes/no）："%student_obj.stu_name)
                            study_record_new = Study_record(class_m2m_lesson_id=class_m2m_lesson_obj.id,
                                                            stu_id=student_obj.stu_id,
                                                            status=status)
                            self.session.add(study_record_new)
                            self.session.commit()
                    else:
                        print("\33[31;1m系统错误：当前上课记录已经创建\33[0m")
                else:
                     print("\33[31;1m系统错误：当前班级的lesson课节未创建\33[0m")
            else:
                print("\33[31;1m系统错误：lesson未创建\33[0m")
        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def modify_scores(self):
        ‘‘‘修改成绩‘‘‘
        class_name = input("\033[34;0m请输入学习记录的班级名:\033[0m")
        class_obj = self.session.query(Class).filter_by(class_name=class_name).first()

        if class_obj and class_obj.teachers[0] == self.teacher_obj:  # 输入的班级名存在且在属于当前老师管理
            lesson_name = input("\033[34;0m请输入学习记录的课节名（lesson）:\033[0m")
            lesson_obj = self.session.query(Lesson).filter_by(lesson_name=lesson_name).first()

            if lesson_obj:  # 输入的lesson名字存在
                class_m2m_lesson_obj = self.session.query(Class_m2m_Lesson).filter(
                    Class_m2m_Lesson.class_id == class_obj.class_id).                     filter(Class_m2m_Lesson.lesson_id == lesson_obj.lesson_id).first()

                if class_m2m_lesson_obj:  # 班级对应的课lesson表数据存在
                    while True:
                        study_record_objs = self.session.query(Study_record).filter(
                                Study_record.class_m2m_lesson_id==class_m2m_lesson_obj.id).all()
                        for obj in  study_record_objs:
                            print(obj)

                        student_name = input("\033[34;0m输入要修改成绩的学生名：[Q 退出]\33[0m")
                        if student_name == "q" or student_name == "Q":break
                        student_obj = self.session.query(Student).filter_by(stu_name=student_name).first()
                        if student_obj:
                            study_record_obj = self.session.query(Study_record).filter(
                                Study_record.class_m2m_lesson_id==class_m2m_lesson_obj.id).filter(
                                Study_record.stu_id == student_obj.stu_id).first()

                            if study_record_obj:                            # 上课记录存在
                                score = input("\033[34;0m输入修改后的成绩\33[0m")
                                study_record_obj.score= score
                                self.session.commit()

                    else:
                        print("\33[31;1m系统错误：当前上课记录已经创建\33[0m")
                else:
                    print("\33[31;1m系统错误：当前班级的lesson课节未创建\33[0m")
            else:
                print("\33[31;1m系统错误：lesson未创建\33[0m")
        else:
            print("\33[31;1m输入错误：班级不存在或没有权限管理此班级\33[0m")


    def exit(self):
        exit()