# -*- coding: utf-8 -*-
"""
示例 06 · 从台账到数据库：用 SQLite 亲手建两张大表 + 一张聪明查询
================================================================
配套教材：第 8.6 章「从台账到数据库」

为什么用 SQLite？它是 Python 自带的数据库，不需要安装任何东西，
用文件就能模拟"正式数据库"里最重要的两个概念：
  主键（每行唯一） + 外键（把两张表连起来） + SQL 查询（会查表格）。

本示例做三件事：
  1. 建"作业"和"违章"两张表（含主键/外键）
  2. 插入几行样例数据
  3. 用 SQL 回答"每个公司违章率是多少"——这正是 Excel 难做、数据库擅长的跨表汇总

运行：python "06_台账建库与SQL.py"
"""
import sqlite3
from pathlib import Path

db_path = Path("06_demo.db")
if db_path.exists():
    db_path.unlink()   # 每次运行重来，方便对照

conn = sqlite3.connect(db_path)
cur = conn.cursor()

# ---------- 1. 建表 ----------
# 作业表：一行 = 一条作业计划。work_id 是"身份证号"(主键 PK)。
cur.execute("""
    CREATE TABLE work (
        work_id   INTEGER PRIMARY KEY,        -- 作业号
        company   TEXT,                       -- 作业公司
        work_type TEXT,                       -- 作业类型
        risk      TEXT                        -- 风险等级：高/中/低
    )
""")

# 违章表：一行 = 一条违章。vi_id 主键；work_id 指着上面的 work_id，就是"外键 FK"
cur.execute("""
    CREATE TABLE violation (
        vi_id     INTEGER PRIMARY KEY,        -- 违章号
        work_id   INTEGER,                    -- 关联哪一条作业（外键）
        company   TEXT,
        source    TEXT                        -- 发现渠道：内部/外部
    )
""")

# ---------- 2. 插入样例 ----------
plans = [
    (1, "电建", "基建", "高"), (2, "电建", "生技", "中"),
    (3, "番电", "基建", "高"), (4, "番电", "营销", "低"), (5, "番电", "营销", "低"),
    (6, "泰捷", "基建", "高"), (7, "泰捷", "基建", "中"), (8, "泰捷", "运维", "中"),
]
cur.executemany("INSERT INTO work VALUES (?,?,?,?)", plans)

viols = [
    (101, 1, "电建", "外部"),
    (102, 2, "电建", "内部"),
    (103, 6, "泰捷", "外部"),
    (104, 6, "泰捷", "外部"),   # 泰捷两次外部 = 连续被点名的问题公司
]
cur.executemany("INSERT INTO violation VALUES (?,?,?,?)", viols)

# ---------- 3. 会查表格（SQL） ----------
# ① 最基础的查询：整张表
print("① 作业表里有哪些公司？")
for r in cur.execute("SELECT company, COUNT(*) AS 项数 FROM work GROUP BY company"):
    print("   ", r)

print("\n② 把违章和作业 join 起来（靠 work_id 对接），看每条违章属于什么风险的作业")
for r in cur.execute("""
    SELECT v.vi_id, v.source, w.risk
    FROM violation AS v
    JOIN work AS w ON v.work_id = w.work_id
"""):
    print("   ", r)

print("\n③ 重点：每个公司的违章率 = 外部违章数 ÷ 作业数（Excel 要手拉公式，SQL 一句话）")
for r in cur.execute("""
    SELECT w.company,
           COUNT(DISTINCT w.work_id)            AS 作业数,
           SUM(CASE WHEN v.vi_id IS NOT NULL AND v.source='外部'
                    THEN 1 ELSE 0 END)          AS 外部违章数,
           ROUND(100.0 * SUM(CASE WHEN v.vi_id IS NOT NULL AND v.source='外部'
                    THEN 1 ELSE 0 END)
                    / NULLIF(COUNT(DISTINCT w.work_id), 0), 1) AS 外部违章率百分比
    FROM work w
    LEFT JOIN violation v ON v.work_id = w.work_id
    GROUP BY w.company
"""):
    print("   ", r)

conn.commit()
conn.close()
print("\n已生成数据库文件：", db_path)
print("提示：这就是 8.6.3 讲的'维度建模'雏形——把'作业'当事实、'公司'当维度。")