-- 中国机车图鉴 · 数据底座 schema（SQLite，设计为可移植到 PostgreSQL）
-- 对应任务 T-1.1。三层模型：Category -> Model -> Unit，加 Model 关系表。

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS category (
    id       INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    subcat   TEXT DEFAULT '',
    slug     TEXT,
    UNIQUE(name, subcat)
);

CREATE TABLE IF NOT EXISTS model (
    id               INTEGER PRIMARY KEY,
    category_id      INTEGER NOT NULL REFERENCES category(id),
    series           TEXT DEFAULT '',
    model_code       TEXT NOT NULL,
    full_name        TEXT DEFAULT '',
    aliases          TEXT DEFAULT '',
    manufacturer     TEXT DEFAULT '',
    country          TEXT DEFAULT '中国',
    country_type     TEXT DEFAULT '国产',   -- 国产/进口/引进仿制/中外合资/未知
    first_year       INTEGER,
    last_year        INTEGER,
    status           TEXT DEFAULT '未知',
    usage            TEXT DEFAULT '',
    production_count TEXT DEFAULT '',
    axle_arrangement TEXT DEFAULT '',
    drive            TEXT DEFAULT '',
    efficiency       TEXT DEFAULT '',
    -- 数值 + 单位（拆分存储）
    length_value REAL, length_unit TEXT,
    width_value  REAL, width_unit  TEXT,
    height_value REAL, height_unit TEXT,
    wheelbase_value REAL, wheelbase_unit TEXT,
    weight_value REAL, weight_unit TEXT,
    axle_load_value REAL, axle_load_unit TEXT,
    load_value   REAL, load_unit   TEXT,
    tractive_start_value REAL, tractive_start_unit TEXT,
    tractive_cont_value  REAL, tractive_cont_unit  TEXT,
    power_kw_value REAL, power_kw_unit TEXT,
    max_speed_value REAL, max_speed_unit TEXT,
    capacity_value REAL, capacity_unit TEXT,
    raw_json         TEXT,        -- 原始清洗后所有列，保真不丢字段
    source_sheet     TEXT
);
CREATE INDEX IF NOT EXISTS idx_model_category ON model(category_id);
CREATE INDEX IF NOT EXISTS idx_model_code ON model(model_code);
CREATE INDEX IF NOT EXISTS idx_model_first_year ON model(first_year);

CREATE TABLE IF NOT EXISTS unit (
    id           INTEGER PRIMARY KEY,
    category_id  INTEGER NOT NULL REFERENCES category(id),
    model_id     INTEGER REFERENCES model(id),
    car_number   TEXT,
    model_name   TEXT DEFAULT '',
    function     TEXT DEFAULT '',
    depot        TEXT DEFAULT '',
    livery       TEXT DEFAULT '',
    status       TEXT DEFAULT '未知',
    location     TEXT DEFAULT '',
    note         TEXT DEFAULT '',
    raw_json     TEXT,
    source_sheet TEXT
);
CREATE INDEX IF NOT EXISTS idx_unit_category ON unit(category_id);
CREATE INDEX IF NOT EXISTS idx_unit_car_number ON unit(car_number);

-- 车型族谱关系（父型号/衍生/国外原型），本期预留，后续填充
CREATE TABLE IF NOT EXISTS model_relation (
    id            INTEGER PRIMARY KEY,
    from_model_id INTEGER NOT NULL REFERENCES model(id),
    to_model_id   INTEGER NOT NULL REFERENCES model(id),
    rel_type      TEXT NOT NULL,  -- parent / derivative / prototype
    UNIQUE(from_model_id, to_model_id, rel_type)
);