PART 02 — テーブル定義書
全カラム・制約・インデックスを網羅する物理設計書

No | 物理名          | 論理名       | 型               | 桁  | NULL | PK | FK | UK | デフォルト  | 設定値/説明
---|-----------------|-------------|------------------|-----|------|----|----|----|-----------|-----------------------------------------
1  | product_id      | 商品ID       | BIGINT           | —   | ×    | ○  |    |    | AUTO      | シーケンス自動採番
2  | category_id     | カテゴリID   | BIGINT           | —   | ○    |    | ○  |    | NULL      | FK→categories.category_id
3  | product_code    | 商品コード   | VARCHAR          | 20  | ×    |    |    | ○  | —         | 英数字+ハイフン。自社採番ルール準拠
4  | product_name    | 商品名       | VARCHAR          | 200 | ×    |    |    |    | —         | 販売用の商品名称
5  | description     | 商品説明     | TEXT             | —   | ○    |    |    |    | NULL      | 詳細説明文（HTML不可）
6  | unit_price      | 単価         | NUMERIC          |12,2 | ×    |    |    |    | 0         | 税抜き単価（円）。0以上
7  | stock_quantity  | 在庫数       | INTEGER          | —   | ×    |    |    |    | 0         | 0以上。在庫引当時に排他ロック取得
8  | is_active       | 販売フラグ   | BOOLEAN          | —   | ×    |    |    |    | true      | false=販売停止
9  | created_at      | 作成日時     | TIMESTAMPTZ      | —   | ×    |    |    |    | NOW()     | レコード作成日時
10 | updated_at      | 更新日時     | TIMESTAMPTZ      | —   | ×    |    |    |    | NOW()     | 最終更新日時（トリガーで自動更新）
11 | deleted_at      | 削除日時     | TIMESTAMPTZ      | —   | ○    |    |    |    | NULL      | NULL=有効。論理削除日時

"""
テーブル定義辞書から PostgreSQL DDL を自動生成する
"""
TABLE_DEF = {
    "name": "products",
    "columns": [
        {"name":"product_id",     "type":"BIGINT GENERATED ALWAYS AS IDENTITY", "nullable":False, "pk":True},
        {"name":"category_id",    "type":"BIGINT",         "nullable":True},
        {"name":"product_code",   "type":"VARCHAR(20)",    "nullable":False},
        {"name":"product_name",   "type":"VARCHAR(200)",   "nullable":False},
        {"name":"description",    "type":"TEXT",           "nullable":True},
        {"name":"unit_price",     "type":"NUMERIC(12,2)",  "nullable":False, "default":"0"},
        {"name":"stock_quantity", "type":"INTEGER",        "nullable":False, "default":"0"},
        {"name":"is_active",      "type":"BOOLEAN",        "nullable":False, "default":"true"},
        {"name":"created_at",     "type":"TIMESTAMPTZ",    "nullable":False, "default":"NOW()"},
        {"name":"updated_at",     "type":"TIMESTAMPTZ",    "nullable":False, "default":"NOW()"},
        {"name":"deleted_at",     "type":"TIMESTAMPTZ",    "nullable":True},
    ],
    "constraints": [
        "CONSTRAINT uk_products_code UNIQUE (product_code)",
        "CONSTRAINT fk_products_cat FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL",
        "CONSTRAINT chk_products_price CHECK (unit_price >= 0)",
        "CONSTRAINT chk_products_stock CHECK (stock_quantity >= 0)",
    ]
}

def gen_ddl(tbl: dict) -> str:
    cols = []
    pk_cols = [c["name"] for c in tbl["columns"] if c.get("pk")]
    for c in tbl["columns"]:
        line = f'  {c["name"]:<20} {c["type"]}'
        if not c["nullable"]:
            line += " NOT NULL"
        if "default" in c:
            line += f' DEFAULT {c["default"]}'
        cols.append(line)
    if pk_cols:
        cols.append(f'  CONSTRAINT pk_{tbl["name"]} PRIMARY KEY ({", ".join(pk_cols)})')
    for con in tbl.get("constraints", []):
        cols.append(f"  {con}")
    return f'CREATE TABLE {tbl["name"]} (
' + ",
".join(cols) + "
);"

print(gen_ddl(TABLE_DEF))