-- 学校主表
CREATE TABLE schools (
school_id        VARCHAR(32) PRIMARY KEY,
name_ja          VARCHAR(255) NOT NULL,
name_zh          VARCHAR(255),
prefecture       VARCHAR(64)  NOT NULL,  -- 都道府県
city             VARCHAR(64)  NOT NULL,
address_ja       TEXT,
website          TEXT,
org_id           VARCHAR(32)  NOT NULL,  -- 对应法人
official_ref_id  VARCHAR(64)  NOT NULL,  -- 官方名录编号
status           VARCHAR(16)  NOT NULL,  -- active/inactive
created_at       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
updated_at       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);
-- 法人/运营主体
CREATE TABLE organizations (
org_id           VARCHAR(32) PRIMARY KEY,
org_name_ja      VARCHAR(255) NOT NULL,
org_name_zh      VARCHAR(255),
official_ref_id  VARCHAR(64) NOT NULL,
est_year         INT,
updated_at       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP
);
-- 课程与学制
CREATE TABLE programs (
program_id       VARCHAR(32) PRIMARY KEY,
school_id        VARCHAR(32) NOT NULL REFERENCES schools(school_id),
term_months      INT CHECK (term_months > 0),
intake_months    VARCHAR(64),               -- 例："4,7,10"
tuition_total_jpy DECIMAL(12,2) CHECK (tuition_total_jpy >= 0),
dorm_available   BOOLEAN,
updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 指标（年度切片）
CREATE TABLE school_metrics (
school_id        VARCHAR(32) NOT NULL REFERENCES schools(school_id),
year             INT NOT NULL,
pass_rate        DECIMAL(5,2),             -- 升学/考试通过率
intl_ratio       DECIMAL(5,2),             -- 国际生占比
cn_students      INT,                       -- 中国学生数量（如有）
updated_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (school_id, year)
);
-- 溯源与版本（可追溯证据链）
CREATE TABLE lineage (
lineage_id       VARCHAR(36) PRIMARY KEY,
entity_type      VARCHAR(32) NOT NULL,     -- schools/programs/organizations/metrics
entity_id        VARCHAR(36) NOT NULL,
source_url       TEXT NOT NULL,
source_org       VARCHAR(64) NOT NULL,     -- MEXT/ISA/Immigration 等
fetched_at       TIMESTAMP NOT NULL,
hash_sha256      CHAR(64) NOT NULL,        -- 原文或页面摘要哈希
version_tag      VARCHAR(32) NOT NULL      -- v2025.09 等
);

设计要点：

lineage 统一管理“证据链”，与任何实体解耦，便于审计与对账。
指标表按年度主键，天然支持“时间序列/快照”。
official_ref_id 保持与日本官方名录的稳定对照。

2) ETL Pipeline（采集→清洗→建模→校验）

E（Extract）：从官方名录与院校站点获取结构化/半结构化数据（HTML/CSV/PDF）。
T（Transform）：正则/规则与字典统一字段口径；日中名称对齐；金额与日期标准化。
L（Load）：落库前做主键去重与外键校验；写入 lineage 生成版本标签。

# 伪代码示例（Transform & Validate）
import pandas as pd
schools = pd.read_csv("mext_schools_2025.csv")
# 字段标准化
schools["prefecture"] = schools["prefecture"].str.strip()
schools["official_ref_id"] = schools["official_ref_id"].astype(str)
# 基本校验
assert schools["official_ref_id"].notna().all()
assert schools["school_id"].is_unique
assert schools["prefecture"].isin(PREF_LIST).all()
# 金额标准化（去除日文货币符号与逗号）
def normalize_jpy(x):
x = str(x).replace(",", "").replace("円", "").strip()
return float(x) if x else 0.0
programs["tuition_total_jpy"] = programs["tuition_total_jpy"].map(normalize_jpy)

质量门禁（DQ Gates）：

完整性：主键唯一、外键存在、必填字段非空；
一致性：prefecture/城市等字段限定在受控词表；
合理性：学制月数>0、学费≥0、比率∈[0,100]；
对账：对比上期快照，异常波动触发复核工单。

3) 版本化与可追溯（Snapshots & Diff）

时间切片：以 version_tag 或自然月作为数据快照；
差分对比：对 schools 与 school_metrics 计算字段级 diff，生成更新报告；
证据固化：对源页面做哈希与归档，lineage.hash_sha256 用于不可抵赖。

4) 结构化标注（JSON-LD 示例）

为院校页生成结构化数据，利于搜索引擎索引：

{
"@context": "https://schema.org",
"@type": "CollegeOrUniversity",
"name": "示例日本语学院",
"address": {
"@type": "PostalAddress",
"addressRegion": "Tokyo",
"addressLocality": "Shinjuku"
},
"url": "https://www.yuxiaowang.com/schools/XXXX",
"identifier": "official-ref-12345",
"knowsAbout": ["Japanese language", "JLPT"],
"offers": {
"@type": "Offer",
"priceCurrency": "JPY",
"price": "720000"
}
}