#创建方法
from datetime import date

from sqlalchemy.orm import sessionmaker, Session

from model.counselor import Counselor


#增
def add_counselor(db:Session,id: int,name:str,c_time:date):
    c1 = Counselor(
        id=id,
        name=name,
        c_time=c_time,
        c_de=False
    )
    try:
        db.add(c1)
        db.commit()
        db.refresh(Counselor)
        return "添加成功"
    except Exception as e:
        print(e)

#删
def delete_counselor_id(db:Session,id:int):
    res=db.query(Counselor).filter(Counselor.id == id).first()
    res.c_de=True
    db.commit()
    return "删除成功"

def delete_counselor_name(db:Session,name:str):
    db.query(Counselor).filter(Counselor.name==name).delete()
    db.commit()
    db.refresh(Counselor)
    return "删除成功"

#改,根据id修改
def update_counselor(db:Session,id:int,new_name,new_c_time):
    counselor=db.query(Counselor).filter(Counselor.id==id,Counselor.c_de==False).first()
    if not counselor:
        return {"msg":"没找到顾问信息"}
    counselor.name=new_name
    counselor.c_time=new_c_time
    try:
        db.commit()
        db.refresh(counselor)
        return {"msg":"修改成功"}
    except Exception as e:
        print(e)
        return {"msg": "修改失败"}

#改，根据名字修改
def update_counselor_name(db:Session,id:int,name:str,c_time:date):
    if Counselor.name==name:
        Counselor.id==id
        Counselor.c_time=c_time
        db.commit()
        db.refresh(Counselor)
        return f"修改成功"

#查，根据id查询
#根据id查
def query_counselor(db:Session,id:int):
    counselor=db.query(Counselor).filter(Counselor.id==id,Counselor.c_de==False).first()
    if not counselor:
        return None
    l2={
        "id":counselor.id,
        "name":counselor.name,
        "t_created":counselor.c_time.strftime("%Y-%m-%d")
    }
    return l2


#根据名字查询
def query_counselor_name(db:Session,name:str):
    res=db.query(Counselor).filter(Counselor.name==name).first()
    db.commit()
    db.refresh(Counselor)
    return res
#筛选低于60的同学