from sqlalchemy import and_, or_
from sqlalchemy.orm import Session
from model.advisor import Advisor
from model.student import Student
from scheme.advisor_scheme import AdvisorCreate
# 新增顾问信息（前端传过来的、且已经 scheme 校验好的数据）

def create_advisor(db: Session, advisor: AdvisorCreate) -> Advisor:
    db_advisor = Advisor(
        advisor_no=advisor.advisor_no,
        name=advisor.name,
        gender=advisor.gender,
        phone=advisor.phone,
        remark=advisor.remark,
    )
    db.add(db_advisor)
    db.commit()
    db.refresh(db_advisor)
    return db_advisor

# 顾问信息查询：支持全部查询 / 模糊搜索（匹配顾问编号、姓名）
def get_advisor_list(db: Session, search_keyword: str = None):
    q = db.query(Advisor).filter(Advisor.statu == 1)
    if search_keyword:
        q = q.filter(
            or_(
                Advisor.advisor_no.like(f"%{search_keyword}%"),
                Advisor.name.like(f"%{search_keyword}%"),
            )
        )
    return q.all()


# 根据顾问编号查询单个存在的顾问
def get_advisor_by_no(db: Session, advisor_no: str):
    return (
        db.query(Advisor)
        .filter(and_(Advisor.advisor_no == advisor_no, Advisor.statu == 1))
        .first()
    )

# 查询该顾问名下所有学生
def get_students_by_advisor_no(db: Session, advisor_no: str):
    return (
        db.query(Student)
        .filter(and_(Student.advisor_no == advisor_no, Student.statu == 1))
        .all()
    )

# 更新顾问信息（setattr(obj, "k", "v") 动态赋值）
def update_advisor(db: Session, advisor_no: str, update_data: dict):
    advisor = get_advisor_by_no(db, advisor_no)
    if not advisor:
        return None
    update_data.pop("advisor_no", None)
    for k, v in update_data.items():
        setattr(advisor, k, v)
    db.commit()
    db.refresh(advisor)
    return advisor

# 删除顾问：逻辑删除
def delete_advisor(db: Session, advisor_no: str):
    advisor = get_advisor_by_no(db, advisor_no)
    if not advisor:
        return None
    advisor.statu = 0
    db.commit()
    return advisor