from pathlib import Path

from PIL import Image
from pptx import Presentation
from pptx.dml.color import RGBColor
from pptx.enum.shapes import MSO_SHAPE
from pptx.enum.text import PP_ALIGN, MSO_ANCHOR
from pptx.util import Inches, Pt


ROOT = Path(__file__).resolve().parent
ASSETS = ROOT / "assets"
OUTPUT = ROOT / "campus_hub_10min_presentation.pptx"

WIDE_W = 13.333
WIDE_H = 7.5

COLORS = {
    "charcoal": "263238",
    "ink": "1F2933",
    "muted": "64748B",
    "paper": "F7F4EA",
    "white": "FFFFFF",
    "teal": "0E7C7B",
    "moss": "4B7F52",
    "amber": "F2B84B",
    "coral": "D95D39",
    "line": "D7D2C4",
    "soft": "EFE9DA",
}

FONT = "PingFang SC"
MONO = "Consolas"


def rgb(hex_color: str) -> RGBColor:
    value = COLORS.get(hex_color, hex_color).strip().lstrip("#")
    return RGBColor(int(value[0:2], 16), int(value[2:4], 16), int(value[4:6], 16))


def add_rect(slide, x, y, w, h, fill, line=None, radius=False):
    shape = slide.shapes.add_shape(
        MSO_SHAPE.ROUNDED_RECTANGLE if radius else MSO_SHAPE.RECTANGLE,
        Inches(x),
        Inches(y),
        Inches(w),
        Inches(h),
    )
    shape.fill.solid()
    shape.fill.fore_color.rgb = rgb(fill)
    if line:
        shape.line.color.rgb = rgb(line)
        shape.line.width = Pt(1)
    else:
        shape.line.fill.background()
    return shape


def add_text(slide, text, x, y, w, h, size=18, color="ink", bold=False, align="left", font=FONT):
    box = slide.shapes.add_textbox(Inches(x), Inches(y), Inches(w), Inches(h))
    frame = box.text_frame
    frame.clear()
    frame.word_wrap = True
    frame.margin_left = Inches(0.04)
    frame.margin_right = Inches(0.04)
    frame.margin_top = Inches(0.02)
    frame.margin_bottom = Inches(0.02)
    frame.vertical_anchor = MSO_ANCHOR.TOP
    p = frame.paragraphs[0]
    p.alignment = {"left": PP_ALIGN.LEFT, "center": PP_ALIGN.CENTER, "right": PP_ALIGN.RIGHT}[align]
    run = p.add_run()
    run.text = text
    run.font.name = font
    run.font.size = Pt(size)
    run.font.bold = bold
    run.font.color.rgb = rgb(COLORS[color])
    return box


def add_title(slide, title, subtitle=None, dark=False):
    color = "white" if dark else "ink"
    add_text(slide, title, 0.62, 0.34, 8.2, 0.55, 26, color, True)
    if subtitle:
        add_text(slide, subtitle, 0.66, 0.93, 8.8, 0.32, 10.5, "paper" if dark else "muted")


def add_footer(slide, index, total=12, dark=False):
    add_text(slide, f"{index:02d}/{total:02d}", 12.22, 7.02, 0.5, 0.22, 8.5, "paper" if dark else "muted", align="right")


def add_bullet_lines(slide, lines, x, y, w, h, size=13.2, color="ink"):
    box = slide.shapes.add_textbox(Inches(x), Inches(y), Inches(w), Inches(h))
    frame = box.text_frame
    frame.clear()
    frame.word_wrap = True
    frame.margin_left = Inches(0.08)
    frame.margin_right = Inches(0.06)
    frame.margin_top = Inches(0.04)
    frame.margin_bottom = Inches(0.04)
    for i, line in enumerate(lines):
        p = frame.paragraphs[0] if i == 0 else frame.add_paragraph()
        p.text = line
        p.level = 0
        p.font.name = FONT
        p.font.size = Pt(size)
        p.font.color.rgb = rgb(COLORS[color])
        p.space_after = Pt(5)
    return box


def add_image_contain(slide, image_name, x, y, w, h, border=True):
    path = ASSETS / image_name
    with Image.open(path) as img:
        iw, ih = img.size
    scale = min(w / iw, h / ih)
    dw, dh = iw * scale, ih * scale
    ox, oy = x + (w - dw) / 2, y + (h - dh) / 2
    if border:
        add_rect(slide, x, y, w, h, "white", "line")
    slide.shapes.add_picture(str(path), Inches(ox), Inches(oy), Inches(dw), Inches(dh))


def add_card(slide, title, body, x, y, w, h, accent="teal"):
    add_rect(slide, x, y, w, h, "white", "line", radius=True)
    add_rect(slide, x, y, 0.12, h, accent)
    add_text(slide, title, x + 0.25, y + 0.18, w - 0.35, 0.26, 12.5, "ink", True)
    add_text(slide, body, x + 0.25, y + 0.5, w - 0.35, h - 0.62, 10.5, "muted")


def add_metric(slide, number, label, x, y, color="amber", dark=False):
    add_text(slide, number, x, y, 1.45, 0.45, 25, color, True, align="center")
    add_text(slide, label, x, y + 0.45, 1.45, 0.24, 9.5, "paper" if dark else "muted", align="center")


def setup_slide(prs, bg="paper"):
    slide = prs.slides.add_slide(prs.slide_layouts[6])
    slide.background.fill.solid()
    slide.background.fill.fore_color.rgb = rgb(COLORS[bg])
    return slide


def build():
    prs = Presentation()
    prs.slide_width = Inches(WIDE_W)
    prs.slide_height = Inches(WIDE_H)

    # 1 Cover
    slide = setup_slide(prs, "charcoal")
    add_rect(slide, 0, 0, WIDE_W, WIDE_H, "charcoal")
    add_image_contain(slide, "facility-create.png", 7.15, 0.58, 5.55, 4.05, border=False)
    add_rect(slide, 7.15, 4.72, 5.55, 1.02, "teal")
    add_text(slide, "校园生活平台", 0.72, 0.86, 5.7, 0.55, 30, "white", True)
    add_text(slide, "数据库课程设计检查视频", 0.76, 1.48, 5.6, 0.44, 22, "amber", True)
    add_text(slide, "平台基础 / 功能房预约 / 课程资源分享", 0.78, 2.22, 5.75, 0.35, 15, "paper")
    add_text(slide, "围绕关系模式、完整性约束、索引、触发器、事务和运行验证展开。", 0.8, 2.75, 5.95, 0.55, 14, "paper")
    add_metric(slide, "3", "数据库主线", 0.78, 4.52, "amber", True)
    add_metric(slide, "8", "运行结果截图", 2.28, 4.52, "amber", True)
    add_metric(slide, "10min", "讲解时长", 3.78, 4.52, "amber", True)
    add_text(slide, "面向老师检查的单向讲解", 7.42, 4.93, 4.8, 0.35, 17, "white", True)
    add_text(slide, "从系统整体到三个数据库子模块。", 7.44, 5.34, 4.8, 0.28, 11.5, "paper")
    add_footer(slide, 1, dark=True)

    # 2 Route
    slide = setup_slide(prs)
    add_title(slide, "10 分钟讲解路线", "整体到局部，按三个数据库子模块展开")
    segments = [
        ("0:00", "整体系统", "项目范围、角色、三个主线", "teal"),
        ("1:45", "总体设计", "数据分层、E-R、关系模式", "moss"),
        ("2:40", "平台基础", "身份、组织、RBAC、审计", "amber"),
        ("4:10", "功能房预约", "时间冲突、状态、参与人", "coral"),
        ("6:25", "课程资源", "审核流、去重、事件事实", "teal"),
        ("8:40", "运行验证", "页面结果对应数据库规则", "moss"),
    ]
    x = 0.78
    for time, title, body, color in segments:
        add_rect(slide, x, 1.65, 1.85, 3.9, "white", "line", radius=True)
        add_rect(slide, x, 1.65, 1.85, 0.18, color)
        add_text(slide, time, x + 0.15, 2.05, 1.5, 0.33, 18, color, True, align="center")
        add_text(slide, title, x + 0.18, 2.56, 1.48, 0.3, 13.5, "ink", True, align="center")
        add_text(slide, body, x + 0.18, 3.05, 1.48, 1.25, 10.4, "muted", align="center")
        x += 2.02
    add_text(slide, "讲解原则：需求含义 → 数据对象 → 关系模式 → 物理对象 → 运行结果。", 1.0, 6.2, 11.1, 0.35, 14, "ink", True, align="center")
    add_footer(slide, 2)

    # 3 Scope
    slide = setup_slide(prs)
    add_title(slide, "系统整体与数据库范围", "保留系统全貌，讲解聚焦数据库课程设计主线")
    layers = [
        ("平台基础", "身份资料、组织树、RBAC、数据范围、审计日志", "teal"),
        ("功能房预约", "楼房房间、预约主体、参与人、封禁、时间冲突", "coral"),
        ("课程资源分享", "专业课程、资源审核、下载事件、积分榜单", "moss"),
    ]
    for i, (title, body, color) in enumerate(layers):
        y = 1.58 + i * 1.28
        add_rect(slide, 0.78, y, 5.65, 0.95, "white", "line", radius=True)
        add_rect(slide, 0.78, y, 0.32, 0.95, color)
        add_text(slide, title, 1.28, y + 0.16, 1.7, 0.28, 14, "ink", True)
        add_text(slide, body, 3.0, y + 0.17, 3.2, 0.44, 11, "muted")
    add_card(slide, "普通用户", "维护资料、提交预约、上传/下载课程资源。", 7.1, 1.45, 2.55, 1.08, "teal")
    add_card(slide, "工作人员", "处理功能房预约审核，通过或驳回申请。", 9.92, 1.45, 2.55, 1.08, "coral")
    add_card(slide, "管理员", "维护组织、角色、权限、配置、审计和业务数据。", 7.1, 2.92, 2.55, 1.08, "amber")
    add_card(slide, "专业负责人", "在负责专业范围内审核资源、设置最佳、查看统计。", 9.92, 2.92, 2.55, 1.08, "moss")
    add_rect(slide, 7.1, 4.62, 5.37, 1.02, "soft", "line")
    add_text(slide, "数据库设计目标", 7.32, 4.82, 1.72, 0.25, 13, "ink", True)
    add_text(slide, "把数据对象、业务联系、状态流转、约束规则和统计来源写进数据库设计。", 9.0, 4.79, 3.22, 0.42, 10.5, "muted")
    add_footer(slide, 3)

    # 4 Overall design
    slide = setup_slide(prs)
    add_title(slide, "需求到数据库设计链路", "不是按页面拆表，而是按业务事实建模")
    add_image_contain(slide, "er-platform-org.png", 0.78, 1.35, 3.25, 2.35)
    add_image_contain(slide, "er-facility.png", 4.95, 1.3, 3.7, 1.75)
    add_image_contain(slide, "er-resource.png", 9.12, 1.1, 3.25, 2.45)
    add_card(slide, "主体数据", "用户、部门、角色、楼房、房间、专业、课程、资源。", 0.78, 4.25, 3.25, 1.22, "teal")
    add_card(slide, "关系/状态数据", "用户角色、角色权限、预约参与人、预约状态、资源审核状态。", 4.95, 4.25, 3.7, 1.22, "coral")
    add_card(slide, "事件与审计数据", "下载事件、积分事件、审计日志支撑统计和追溯。", 9.12, 4.25, 3.25, 1.22, "moss")
    add_text(slide, "核心取舍：主数据边界清晰；必要冗余必须由复合外键、触发器或事务持续控制。", 1.0, 6.25, 11.2, 0.35, 13.5, "ink", True, align="center")
    add_footer(slide, 4)

    # 5 Platform
    slide = setup_slide(prs)
    add_title(slide, "平台基础：身份、组织、权限、审计", "两个业务模块共用的数据与权限底座")
    add_image_contain(slide, "profile-sync.png", 0.75, 1.32, 4.1, 2.56)
    add_image_contain(slide, "role-scope.png", 0.75, 4.22, 4.1, 2.15)
    add_card(slide, "身份资料分离", "`auth.users` 是认证事实源，`profiles.id` 共享主键扩展业务资料。", 5.28, 1.35, 3.0, 1.08, "teal")
    add_card(slide, "RBAC 桥接", "`user_roles` 与 `role_permissions` 用复合主键表达多对多授权。", 5.28, 2.68, 3.0, 1.08, "amber")
    add_card(slide, "部门闭包表", "`department_closure` 支撑本部门及子部门查询，并用触发器防环维护。", 5.28, 4.01, 3.0, 1.08, "moss")
    add_card(slide, "审计只追加", "`audit_logs` 保存操作者快照，触发器禁止更新和删除。", 5.28, 5.34, 3.0, 1.08, "coral")
    add_image_contain(slide, "department-tree.png", 8.75, 1.58, 3.7, 4.35)
    add_footer(slide, 5)

    # 6 Facility design
    slide = setup_slide(prs)
    add_title(slide, "功能房预约：把时间资源冲突写进数据库", "服务层友好校验，数据库层硬约束兜底")
    add_image_contain(slide, "er-facility.png", 0.75, 1.25, 5.35, 2.45)
    add_rect(slide, 6.45, 1.25, 5.95, 2.45, "ink", "line")
    sql = "exclude using gist (\n  room_id with =,\n  tstzrange(start_at,end_at,'[)') with &&\n)\nwhere status in ('pending','approved')"
    add_text(slide, sql, 6.72, 1.55, 5.38, 1.55, 15, "paper", font=MONO)
    add_text(slide, "数据库不变量：同一房间活跃预约时间区间不得重叠", 6.72, 3.22, 5.2, 0.28, 12.5, "amber", True)
    add_card(slide, "事务顺序", "锁定房间 → 重叠预查 → 插入预约 → 插入参与人 → 提交时触发器检查。", 0.78, 4.35, 3.6, 1.25, "teal")
    add_card(slide, "参与人一致性", "至少 3 人、恰有 1 名申请人、申请人标记必须等于主表 `applicant_id`。", 4.88, 4.35, 3.6, 1.25, "coral")
    add_card(slide, "状态一致性", "`approved/rejected/cancelled` 的审核、驳回、取消字段组合由 `CHECK` 限制。", 8.78, 4.35, 3.6, 1.25, "moss")
    add_text(slide, "关键位置：Final DDL `:879`、`:948`；服务层 `facilities.service.ts:360`、`:407`。", 1.0, 6.35, 11.2, 0.25, 11.5, "muted", align="center")
    add_footer(slide, 6)

    # 7 Facility demo
    slide = setup_slide(prs)
    add_title(slide, "功能房运行演示：提交、冲突、审核", "页面结果对应数据库规则")
    add_image_contain(slide, "facility-create.png", 0.72, 1.2, 3.85, 2.45)
    add_image_contain(slide, "facility-conflict.png", 4.75, 1.2, 3.85, 2.45)
    add_image_contain(slide, "facility-review.png", 8.78, 1.2, 3.85, 2.45)
    add_card(slide, "合法预约", "时间轴出现占用条，说明预约主体、房间和时间窗口可查询。", 0.72, 4.28, 3.85, 1.18, "teal")
    add_card(slide, "冲突拦截", "同房间重叠时间段被拒绝，体现重叠预查和排斥约束。", 4.75, 4.28, 3.85, 1.18, "coral")
    add_card(slide, "审核流转", "待审核记录可通过或驳回，状态字段与审核字段同步变化。", 8.78, 4.28, 3.85, 1.18, "moss")
    add_text(slide, "视频里演示一条完整链路即可：提交预约 → 尝试冲突 → 后台审核。", 1.0, 6.28, 11.2, 0.35, 13.5, "ink", True, align="center")
    add_footer(slide, 7)

    # 8 Resource design
    slide = setup_slide(prs)
    add_title(slide, "课程资源：状态流、去重、受控冗余", "资源主体与下载/积分事件分离")
    add_image_contain(slide, "er-resource.png", 0.72, 1.15, 4.85, 3.0)
    add_card(slide, "字段互斥", "`resource_type` + `CHECK`：文件型不能混入外链字段，外链型不能混入文件字段。", 6.05, 1.25, 2.95, 1.18, "teal")
    add_card(slide, "同课去重", "文件按 `sha256` 唯一，外链按规范化 URL 唯一，只约束未软删除资源。", 9.37, 1.25, 2.95, 1.18, "amber")
    add_card(slide, "受控冗余", "`course_id + major_id` 用复合外键锁定一致性，服务专业范围过滤与统计。", 6.05, 2.85, 2.95, 1.18, "coral")
    add_card(slide, "积分事实", "审核通过和设为最佳写入积分事件，唯一约束保证首次语义。", 9.37, 2.85, 2.95, 1.18, "moss")
    add_rect(slide, 0.72, 4.78, 11.6, 0.92, "ink")
    add_text(slide, "审核事务：资源状态 published + 首次通过积分事件，一起成功或一起回滚", 0.98, 5.05, 11.08, 0.35, 15, "paper", True, align="center")
    add_text(slide, "关键位置：Final DDL `:1177`、`:1185`、`:1287`、`:1333`；服务层 `courseResources.service.ts:1848`。", 1.0, 6.35, 11.2, 0.25, 11.5, "muted", align="center")
    add_footer(slide, 8)

    # 9 Resource demo
    slide = setup_slide(prs)
    add_title(slide, "课程资源运行演示：发布与榜单", "状态回显和统计结果来自数据库事实")
    add_image_contain(slide, "resource-publish.png", 0.78, 1.18, 5.55, 3.48)
    add_image_contain(slide, "resource-scoreboard.png", 6.75, 1.18, 5.55, 3.48)
    add_card(slide, "资源发布详情", "展示课程/专业归属、类型、状态、审核时间、发布时间、作者和下载次数。", 0.78, 5.14, 5.55, 0.92, "teal")
    add_card(slide, "下载榜与积分榜", "下载事件支撑资源榜；积分事件聚合出用户榜，统计来源可追溯。", 6.75, 5.14, 5.55, 0.92, "moss")
    add_footer(slide, 9)

    # 10 Code order
    slide = setup_slide(prs)
    add_title(slide, "录制时的代码展示顺序", "代码只服务于数据库设计说明")
    rows = [
        ("平台基础", "Final DDL :600 / :285 / :157 / :427", "注册同步、RBAC、闭包表、审计 append-only"),
        ("功能房预约", "Final DDL :879 / :948；service :360 / :407", "时间排斥、参与人延迟触发器、事务写入"),
        ("课程资源", "Final DDL :1177 / :1287 / :1333；service :1848", "复合外键、同课去重、审核积分事务"),
        ("运行证明", "report/assets/figures/results/*", "页面截图用于验证数据库规则，不展开 UI 细节"),
    ]
    y = 1.42
    for module, loc, point in rows:
        add_rect(slide, 0.85, y, 11.65, 0.92, "white", "line")
        add_rect(slide, 0.85, y, 1.52, 0.92, "teal" if module == "平台基础" else "coral" if module == "功能房预约" else "moss" if module == "课程资源" else "amber")
        add_text(slide, module, 1.0, y + 0.27, 1.22, 0.26, 12.5, "white", True, align="center")
        add_text(slide, loc, 2.62, y + 0.18, 3.55, 0.3, 11.3, "ink", True, font=MONO)
        add_text(slide, point, 6.42, y + 0.2, 5.72, 0.32, 12, "muted")
        y += 1.16
    add_rect(slide, 0.85, 6.1, 11.65, 0.45, "soft", "line")
    add_text(slide, "讲法：不要逐行读代码；说明它把哪个需求规则落实成了数据库约束或事务。", 1.04, 6.22, 11.25, 0.24, 12.5, "ink", True, align="center")
    add_footer(slide, 10)

    # 11 Validation
    slide = setup_slide(prs)
    add_title(slide, "运行验证与数据库规则", "运行结果用于检查数据库设计是否落地")
    checks = [
        ("学号与角色", "16 位学号 `CHECK`，角色/权限复合主键防重复。", "teal"),
        ("预约时间冲突", "`EXCLUDE USING gist` 拒绝同房间重叠时间段。", "coral"),
        ("参与人集合", "延迟触发器检查至少 3 人和申请人唯一匹配。", "amber"),
        ("资源专业一致", "`course_resources_course_major_fk` 拒绝专业课程错配。", "moss"),
        ("积分首次语义", "`(user_id,resource_id,event_type)` 唯一约束避免重复加分。", "teal"),
        ("审计日志", "append-only 触发器禁止修改或删除历史事实。", "coral"),
    ]
    for idx, (title, body, color) in enumerate(checks):
        col = idx % 3
        row = idx // 3
        add_card(slide, title, body, 0.82 + col * 4.05, 1.55 + row * 2.0, 3.48, 1.32, color)
    add_text(slide, "录制时重点讲结果和规则对应关系；失败样例可口头说明，不把视频变成排错现场。", 1.0, 6.2, 11.2, 0.35, 13.2, "ink", True, align="center")
    add_footer(slide, 11)

    # 12 Summary
    slide = setup_slide(prs, "charcoal")
    add_title(slide, "总结与后续改进", "从课程设计走完整条数据库链路", dark=True)
    add_rect(slide, 0.82, 1.5, 3.6, 3.85, "white", "line", radius=True)
    add_text(slide, "已完成", 1.1, 1.78, 3.0, 0.34, 18, "teal", True)
    add_bullet_lines(slide, [
        "需求分析与业务规则",
        "E-R 建模与关系模式转换",
        "约束、索引、触发器、事务",
        "页面运行与异常拦截验证",
    ], 1.1, 2.32, 3.0, 2.2, 12, "ink")
    add_rect(slide, 4.88, 1.5, 3.6, 3.85, "white", "line", radius=True)
    add_text(slide, "主要收获", 5.16, 1.78, 3.0, 0.34, 18, "coral", True)
    add_bullet_lines(slide, [
        "数据库不是系统最后的附属物",
        "业务边界可以写成硬约束",
        "必要冗余要被数据库持续控制",
        "统计结果要能追溯到事实表",
    ], 5.16, 2.32, 3.0, 2.2, 12, "ink")
    add_rect(slide, 8.93, 1.5, 3.6, 3.85, "white", "line", radius=True)
    add_text(slide, "后续改进", 9.21, 1.78, 3.0, 0.34, 18, "moss", True)
    add_bullet_lines(slide, [
        "补充大数据量执行计划对比",
        "把稳定统计整理成视图/物化视图",
        "完善封禁状态和审计追踪",
        "增加约束巡检与运维脚本",
    ], 9.21, 2.32, 3.0, 2.2, 12, "ink")
    add_text(slide, "谢谢老师观看。", 1.0, 6.28, 11.3, 0.38, 19, "amber", True, align="center")
    add_footer(slide, 12, dark=True)

    prs.save(OUTPUT)


if __name__ == "__main__":
    build()
    print(f"generated: {OUTPUT}")