PART 01 — ER図
エンティティ抽出・リレーション・正規化方針・カーディナリティで定義すべきこと

"""
PostgreSQLのinformation_schemaからテーブル構造とFK関係を取得してER図作成に活用する。
pip install psycopg2-binary pandas
"""
import psycopg2
import pandas as pd
from contextlib import contextmanager


DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "mydb",
    "user": "myuser",
    "password": "mypassword",
}


@contextmanager
def get_connection():
    conn = psycopg2.connect(**DB_CONFIG)
    try:
        yield conn
    finally:
        conn.close()


def get_tables(schema: str = "public") -> pd.DataFrame:
    """スキーマ内の全テーブル一覧を取得"""
    sql = """
    SELECT
        t.table_name,
        obj_description(c.oid, 'pg_class') AS table_comment,
        pg_total_relation_size(c.oid) AS table_size_bytes
    FROM information_schema.tables t
    JOIN pg_class c ON c.relname = t.table_name
    WHERE t.table_schema = %s
      AND t.table_type = 'BASE TABLE'
    ORDER BY t.table_name;
    """
    with get_connection() as conn:
        return pd.read_sql(sql, conn, params=(schema,))


def get_foreign_keys(schema: str = "public") -> pd.DataFrame:
    """外部キー関係を取得（ER図のリレーション情報）"""
    sql = """
    SELECT
        tc.table_name          AS child_table,
        kcu.column_name        AS child_column,
        ccu.table_name         AS parent_table,
        ccu.column_name        AS parent_column,
        tc.constraint_name
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage ccu
        ON ccu.constraint_name = tc.constraint_name
    WHERE tc.constraint_type = 'FOREIGN KEY'
      AND tc.table_schema = %s
    ORDER BY tc.table_name, kcu.column_name;
    """
    with get_connection() as conn:
        return pd.read_sql(sql, conn, params=(schema,))


def export_er_info(schema: str = "public") -> None:
    """テーブル一覧とFK情報をExcelに出力"""
    tables = get_tables(schema)
    fks = get_foreign_keys(schema)

    # テーブルサイズを人間が読みやすい形式に変換
    tables["table_size"] = tables["table_size_bytes"].apply(
        lambda b: f"{b/1024/1024:.1f} MB" if b >= 1024*1024 else f"{b/1024:.1f} KB"
    )

    print(f"テーブル数: {len(tables)}")
    print(f"FK関係数: {len(fks)}")
    print("\n=== FK関係（ER図のリレーション）===")
    print(fks.to_string(index=False))

    with pd.ExcelWriter("er_info.xlsx") as writer:
        tables[["table_name", "table_comment", "table_size"]].to_excel(
            writer, sheet_name="テーブル一覧", index=False
        )
        fks.to_excel(writer, sheet_name="FK関係（リレーション）", index=False)
    print("\ner_info.xlsx に出力しました")


if __name__ == "__main__":
    export_er_info("public")