from sqlalchemy.orm import Session

from model import Advisor, Student


#查所有返回的是列表
def get_all(db:Session):
    return db.query(Advisor).filter(Advisor.flag==1).all()

#查单个,first返回第一个数据，没有就返回None
def get_by_id(db:Session,advisor_id:int):
    return db.query(Advisor).filter(Advisor.id==advisor_id,Advisor.flag==1).first()

#查指定顾问下的学生信息
def   adv_all_stu(db:Session,advisor_id:int):
    advisor= db.query(Advisor).filter(Advisor.id == advisor_id, Advisor.flag == 1).first()
    if advisor is None:
        return None
    else:
        # students=advisor.students.filter(Student.flag==1).all()
        students = db.query(Student).filter(Student.advisor_id == advisor_id, Student.flag == 1).all()
        return students

#增加顾问
def create_advisor(db:Session,advisor_name:str,phone:str|None=None,gender=None):
    advisor=Advisor(advisor_name=advisor_name,phone=phone,gender=gender,flag=1)
    db.add(advisor)
    db.commit()
    db.refresh(advisor)
    return advisor

#删除或者恢复顾问状态 先查到没有被删除再删除
def change_advisor(db:Session,advisor_id:int,flag:int):
    advisor=db.query(Advisor).filter(Advisor.id==advisor_id).first()
    if advisor:
        advisor.flag=flag
    else:
        return None
    db.commit()
    return advisor

#修改顾问信息
def update_advisor(db:Session,advisor_id:int,advisor_name:str,phone:str|None=None,gender=None):
    advisor = db.query(Advisor).filter(Advisor.id == advisor_id, Advisor.flag == 1).first()
    if advisor :
        advisor.advisor_name=advisor_name
        # 没传的字段保持原值，不覆盖成 None
        if phone is not None:
            advisor.phone=phone
        if gender is not None:
            advisor.gender=gender
    else:
        return None
    db.commit()
    db.refresh(advisor)  #它去数据库把这条记录的最新值（包括新生成的 id）读回来，更新到 advisor 对象上。
    return advisor