from sqlalchemy.orm import Session
# 导入SQLAlchemy ORM数据库模型：顾问表实体
from model.wl_advisor_model import Advisor
from model.wl_student_model import Student
# 导入Pydantic请求模型：新增、更新顾问的入参校验模型
from scheme.wl_advisor_scheme import AdvisorCreate, AdvisorUpdate


def create_advisor(db: Session, data: AdvisorCreate) -> Advisor:

    # 将Pydantic入参转为字典，解包赋值给Advisor ORM模型，构建数据库实体对象
    advisor = Advisor(**data.model_dump())
    # 把对象加入数据库会话，此时还未写入数据库
    db.add(advisor)
    # 提交事务，真正执行INSERT语句，保存数据到数据库
    db.commit()
    # 刷新对象，从数据库取回最新数据（如自动生成的主键adv_id）
    db.refresh(advisor)
    # 返回新增的顾问对象
    return advisor


def search_advisors(db: Session, adv_name=None, adv_phone=None,
                    adv_region=None, adv_gender=None, page=1, size=10):

    # 基础查询：查询Advisor表，过滤掉已逻辑删除的数据（is_deleted=0代表未删除）
    q = db.query(Advisor).filter(Advisor.is_deleted == 0)

    # 如果传入姓名，添加模糊查询条件 %xxx% 包含匹配
    if adv_name:
        q = q.filter(Advisor.adv_name.like(f"%{adv_name}%"))
    # 如果传入电话，添加电话模糊查询
    if adv_phone:
        q = q.filter(Advisor.adv_phone.like(f"%{adv_phone}%"))
    # 如果传入地区，添加地区模糊查询
    if adv_region:
        q = q.filter(Advisor.adv_region.like(f"%{adv_region}%"))
    # 如果传入性别，性别使用精确匹配
    if adv_gender:
        q = q.filter(Advisor.adv_gender == adv_gender)

    # 按顾问ID倒序排列，新数据排在前面
    q = q.order_by(Advisor.adv_id.desc())

    # offset：跳过前面(page-1)*size条数据；limit：限制本次查询返回size条
    # 实现分页逻辑
    return q.offset((page - 1) * size).limit(size).all()

# 根据顾问ID查询单条顾问详情（过滤逻辑删除）
def get_advisor_by_id(db: Session, adv_id: int) -> Advisor | None:

    return db.query(Advisor).filter(
        Advisor.adv_id == adv_id,      # ID匹配
        Advisor.is_deleted == 0,       # 只查询未逻辑删除的数据
    ).first()  # first() 取第一条，无结果返回None


# 查询顾问信息和该顾问名下的学生
def get_advisor_with_students(db: Session, adv_id: int):
    advisor = get_advisor_by_id(db, adv_id)
    if not advisor:
        return None

    # 学生表中的顾问外键字段名是 advisor_id
    student_list = db.query(Student).filter(
        Student.advisor_id == adv_id,
        Student.is_deleted == 0
    ).all()
    advisor.student_list = student_list
    return advisor


def update_advisor(db: Session, adv_id: int, data: AdvisorUpdate) -> Advisor | None:
    # 根据ID查询顾问
    advisor = get_advisor_by_id(db, adv_id)
    # 判断顾问是否存在，不存在直接返回None
    if not advisor:
        return None

    # model_dump(exclude_unset=True) 只取前端实际传递过来的字段
    # 循环动态给ORM对象赋值，实现局部更新
    for k, v in data.model_dump(exclude_unset=True).items():
        setattr(advisor, k, v)

    # 提交事务，保存修改
    db.commit()
    # 刷新对象，同步数据库最新数据
    db.refresh(advisor)
    # 返回更新后的顾问对象
    return advisor

# 逻辑删除顾问（不是DELETE语句，修改is_deleted标记）
def delete_advisor(db: Session, adv_id: int) -> bool:
    # 查询顾问
    advisor = get_advisor_by_id(db, adv_id)
    # 顾问不存在返回False
    if not advisor:
        return False

    # 逻辑删除：将删除标记置为1，表示已删除
    advisor.is_deleted = 1
    # 提交事务保存修改
    db.commit()
    # 删除标记修改成功返回True
    return True