# 第4-4课练习：使用SQLAlchemy完成关系映射与多表查询
#
# 本文件只提供题目，不提供代码骨架、测试数据代码或参考答案。
# 练习会创建course_orm_category和course_orm_lesson两张表，
# 并只操作ORM-C-分类前缀及ORM-L-课程前缀的数据。
# 请勿改用现有业务表，也不要删除不属于本练习的数据。


# 第一部分：导入、配置与声明式基类
# 1. 导入dataclass、Decimal、Path和tomllib。
# 2. 从sqlalchemy导入ForeignKey、Numeric、String、URL、create_engine、
#    delete、func和select。
# 3. 从sqlalchemy.exc导入SQLAlchemyError。
# 4. 从sqlalchemy.orm导入DeclarativeBase、Mapped、Session、mapped_column、
#    relationship、selectinload和sessionmaker。
# 5. 使用Path(__file__).with_name("config.toml")定义CONFIG_PATH。
# 6. 定义Base(DeclarativeBase)，类体中不添加业务字段。
# 7. 实现load_database_config(config_path)，读取并返回[postgresql]配置字典。
# 8. 实现create_database_url(database_config)，使用URL.create()创建
#    postgresql+psycopg连接地址，不手工拼接包含密码的字符串。



# 第二部分：定义Category和Lesson ORM模型
# 1. 定义Category(Base)：
#    - __tablename__ = "course_orm_category"；
#    - id：int主键，由数据库生成；
#    - category_code：最长30字符，唯一且非空；
#    - category_name：最长100字符且非空；
#    - lessons：一对多课程集合，使用relationship()；
#    - 通过back_populates与Lesson.category建立双向关系；
#    - 配置cascade="all, delete-orphan"。
# 2. 定义Lesson(Base)：
#    - __tablename__ = "course_orm_lesson"；
#    - id：int主键，由数据库生成；
#    - lesson_code：最长30字符，唯一且非空；
#    - lesson_name：最长100字符且非空；
#    - price：NUMERIC(10, 2)且非空；
#    - category_id：int、非空，并使用ForeignKey引用course_orm_category.id；
#    - category：使用relationship()指向所属Category；
#    - 通过back_populates与Category.lessons建立双向关系。
# 3. 类名使用Category和Lesson，不要使用数据库表名作为Python类名。
#
# 关系映射提醒：
# - ForeignKey建立数据库层面的外键约束；
# - relationship()建立Python对象之间的导航关系；
# - Category.lessons的元素类型应为Lesson，而不是int；
# - 双向关系的两端必须使用对应的back_populates名称。








# 第三部分：定义DTO
# 1. 使用@dataclass(frozen=True)定义 LessonSummaryDTO。
# 2. 依次声明以下字段：
#    - lesson_code：str；
#    - lesson_name：str；
#    - category_name：str；
#    - price：Decimal。
# 3. DTO只承载联表查询结果，不继承Base，也不调用session.add()持久化。



# 第四部分：重置并新增练习数据
# 1. 定义reset_practice_data(session)：
#    - 先找到category_code LIKE "ORM-C-%"的分类ID；
#    - 先删除这些分类下lesson_code LIKE "ORM-L-%"的课程；
#    - 再删除category_code LIKE "ORM-C-%"的分类；
#    - 使用SQLAlchemy的delete()，不拼接SQL；
#    - 不在函数中commit()。
# 2. 定义add_practice_data(session)，创建以下对象关系：
#    - 分类ORM-C-001，名称“数据库课程”；
#      包含ORM-L-001“PostgreSQL入门”，价格99.00；
#      包含ORM-L-002“SQLAlchemy基础”，价格129.00；
#    - 分类ORM-C-002，名称“Web课程”；
#      包含ORM-L-003“HTTP基础”，价格69.00；
#      包含ORM-L-004“FastAPI入门”，价格159.00。
# 3. 通过Category.lessons建立对象关系，不手工给category_id编造主键值。
# 4. 调用session.add_all()新增两个分类，依靠关系级联新增四门课程。
# 5. 不在add_practice_data()中调用commit()。



# 第五部分：实现关系对象查询
# 1. 定义find_categories_with_lessons(session)。
# 2. 查询category_code LIKE "ORM-C-%"的Category，并按category_code排序。
# 3. 使用.options(selectinload(Category.lessons))预加载课程集合。
# 4. 返回Category对象列表；没有数据时返回空列表，不返回None。
# 5. 不使用旧式session.query()。
# 6. 输出时通过category.lessons读取课程，不再为每个分类单独查询课程。


# 第六部分：实现DTO联表查询
# 1. 定义find_lesson_summaries(session)。
# 2. select()只查询Lesson.lesson_code、Lesson.lesson_name、
#    Category.category_name和Lesson.price。
# 3. 使用join()连接Category和Lesson，不使用字符串拼接SQL。
# 4. 只查询lesson_code LIKE "ORM-L-%"的数据。
# 5. 按Category.category_code和Lesson.lesson_code升序排列。
# 6. 调用session.execute(statement).all()取得查询行。
# 7. 将每一行转换成LessonSummaryDTO并返回DTO列表。


# 第七部分：实现聚合查询
# 1. 定义count_lessons_by_category(session)。
# 2. 使用select()查询Category.category_name和func.count(Lesson.id)。
# 3. 使用join()关联课程表，并只统计ORM-C-前缀分类。
# 4. 使用group_by()按分类分组，按category_code升序排列。
# 5. 返回“分类名称、课程数量”组成的查询结果。


# 第八部分：输出和main()流程
# 1. 定义print_categories(categories)，输出分类及其课程：
#    - 先输出“关系对象查询：”；
#    - 每个分类先输出分类名称；
#    - 再逐行输出两个空格和课程名称。
# 2. 定义print_lesson_summaries(summaries)，先输出“DTO联表查询：”，
#    再按以下格式逐行输出：
#    “ORM-L-001｜PostgreSQL入门｜数据库课程｜价格：99.00”。
# 3. 定义print_category_counts(category_counts)，先输出“分类统计：”，
#    再按以下格式逐行输出：
#    “数据库课程｜课程数量：2”。
# 4. main()依次执行：
#    - 读取本地TOML配置并创建数据库URL；
#    - 创建一次Engine，启用pool_pre_ping并配置连接超时；
#    - 使用sessionmaker(engine, expire_on_commit=False)创建Session工厂；
#    - 调用Base.metadata.create_all(engine)创建缺失的练习表；
#    - 使用with session_factory.begin() as session，在同一个事务中
#      调用reset_practice_data(session)和add_practice_data(session)；
#    - 使用独立的with session_factory() as session执行三类查询并输出；
#    - 在finally中调用engine.dispose()释放连接池。
# 5. 分别捕获配置错误和SQLAlchemyError，并输出中文场景说明。
# 6. 添加程序入口判断并调用main()。
#
# 预期关键输出：
# 关系对象查询：
# 数据库课程
#   PostgreSQL入门
#   SQLAlchemy基础
# Web课程
#   HTTP基础
#   FastAPI入门
#
# DTO联表查询：
# ORM-L-001|PostgreSQL入门|数据库课程|价格：99.00
# ORM-L-002|SQLAlchemy基础|数据库课程|价格：129.00
# ORM-L-003|HTTP基础|Web课程|价格：69.00
# ORM-L-004|FastAPI入门|Web课程|价格：159.00
#
# 分类统计：
# 数据库课程|课程数量：2
# Web课程|课程数量：2
#


# 自查清单：
# 1. Python模型类名是否为Category和Lesson，而不是数据库表名？
# 2. Category.lessons是否声明为Lesson对象列表，而不是int列表？
# 3. Category.lessons和Lesson.category是否使用back_populates互相对应？
# 4. category_id是否通过ForeignKey建立真实数据库外键？
# 5. 是否通过对象关系新增课程，而不是手工猜测category_id？
# 6. 关系对象查询是否使用selectinload()避免N+1查询？
# 7. DTO查询是否只选择需要的列并使用join()？
# 8. DTO是否没有继承Base，也没有承担持久化职责？
# 9. 聚合数量是否由数据库的count()和group_by()完成？
# 10. 数据访问函数是否都没有擅自commit()？
# 11. 是否只清理ORM-C-和ORM-L-前缀的本课练习数据？


# 最终验收标准：
# 1. practice.py通过语法检查并能连续运行两次；
# 2. 两张表之间存在真实数据库外键和双向对象关系；
# 3. 两个分类与四门课程的数据及对象关系正确；
# 4. 关系对象查询结果正确，并使用预加载避免N+1查询；
# 5. DTO联表查询包含两张表的数据，字段和顺序符合预期；
# 6. 聚合查询正确统计每个分类的课程数量；
# 7. 初始化事务由外层统一提交，数据访问函数不自行提交；
# 8. 不使用SQLAlchemy 1.x旧式查询写法；
# 9. config.toml与真实数据库信息没有进入Git。