PART 01 — ER図（詳細版・物理設計）
テーブル・カラム・制約・インデックスの完全定義

テーブル: orders（注文）

カラム物理名     | 論理名       | データ型            | NULL | デフォルト | 説明
-----------------|-------------|---------------------|------|------------|-----------------------------
order_id         | 注文ID       | BIGINT              | ×    | AUTO       | PK。シーケンス自動採番
user_id          | ユーザーID   | BIGINT              | ×    | —          | FK → users.user_id
order_date       | 注文日時     | TIMESTAMP WITH TZ   | ×    | NOW()      | 注文を受け付けた日時
status           | ステータス   | SMALLINT            | ×    | 10         | 10=仮受付 20=確認済 30=出荷済 40=完了 90=キャンセル
total_amount     | 合計金額     | NUMERIC(12,2)       | ×    | 0          | 消費税込み合計金額（円）
shipping_address | 配送先住所   | VARCHAR(500)        | ○    | NULL       | NULL の場合は登録住所を使用
note             | 備考         | TEXT                | ○    | NULL       | 顧客からの申し送り事項
created_at       | 作成日時     | TIMESTAMP WITH TZ   | ×    | NOW()      | レコード作成日時
updated_at       | 更新日時     | TIMESTAMP WITH TZ   | ×    | NOW()      | 最終更新日時（更新トリガーで自動設定）
deleted_at       | 削除日時     | TIMESTAMP WITH TZ   | ○    | NULL       | 論理削除日時（NULL=有効）

CREATE TABLE orders (
  order_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id          BIGINT        NOT NULL,
  order_date       TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  status           SMALLINT      NOT NULL DEFAULT 10,
  total_amount     NUMERIC(12,2) NOT NULL DEFAULT 0,
  shipping_address VARCHAR(500),
  note             TEXT,
  created_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  deleted_at       TIMESTAMPTZ,

  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE RESTRICT,

  CONSTRAINT chk_orders_status
    CHECK (status IN (10, 20, 30, 40, 90)),

  CONSTRAINT chk_orders_amount
    CHECK (total_amount >= 0)
);

"""
PostgreSQL の情報スキーマからテーブル定義を取得する
pip install psycopg2-binary
"""
import psycopg2

DSN = "host=localhost dbname=myapp user=postgres password=secret"

def get_columns(conn, schema="public", table=None):
    query = """
        SELECT
            c.table_name,
            c.column_name,
            c.data_type,
            c.character_maximum_length,
            c.is_nullable,
            c.column_default,
            pgd.description AS column_comment
        FROM information_schema.columns c
        LEFT JOIN pg_catalog.pg_statio_all_tables st
            ON st.schemaname = c.table_schema AND st.relname = c.table_name
        LEFT JOIN pg_catalog.pg_description pgd
            ON pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position
        WHERE c.table_schema = %s
          AND (%s IS NULL OR c.table_name = %s)
        ORDER BY c.table_name, c.ordinal_position
    """
    with conn.cursor() as cur:
        cur.execute(query, (schema, table, table))
        return cur.fetchall()

def get_indexes(conn, schema="public", table=None):
    query = """
        SELECT
            t.relname AS table_name,
            i.relname AS index_name,
            ix.indisunique,
            ix.indisprimary,
            array_to_string(array_agg(a.attname ORDER BY k.n), ', ') AS columns
        FROM pg_class t
        JOIN pg_index ix ON t.oid = ix.indrelid
        JOIN pg_class i  ON i.oid = ix.indexrelid
        JOIN pg_namespace n ON t.relnamespace = n.oid
        JOIN LATERAL unnest(ix.indkey) WITH ORDINALITY AS k(attnum, n) ON true
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum
        WHERE n.nspname = %s
          AND (%s IS NULL OR t.relname = %s)
        GROUP BY t.relname, i.relname, ix.indisunique, ix.indisprimary
        ORDER BY t.relname, i.relname
    """
    with conn.cursor() as cur:
        cur.execute(query, (schema, table, table))
        return cur.fetchall()

conn = psycopg2.connect(DSN)
cols = get_columns(conn, table="orders")
print("## orders テーブル カラム定義")
for row in cols:
    print(f"  {row[1]:25s} {row[2]:20s} NULL={row[4]:3s} default={row[5]}")

idxs = get_indexes(conn, table="orders")
print("\n## orders テーブル インデックス")
for row in idxs:
    kind = "PK" if row[3] else ("UNIQUE" if row[2] else "INDEX")
    print(f"  [{kind}] {row[1]}: ({row[4]})")

conn.close()