-- テーブル準備
CREATE TABLE departments (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
 
CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id),
    salary        DECIMAL(10, 2),
    status        VARCHAR(20) DEFAULT 'active',
    hired_date    DATE NOT NULL DEFAULT CURRENT_DATE
);
 
INSERT INTO departments VALUES (1, '営業'), (2, '開発'), (3, '人事'), (4, '企画');
INSERT INTO employees (name, department_id, salary, status, hired_date) VALUES
    ('田中太郎', 1, 450000, 'active', '2020-04-01'),
    ('鈴木花子', 2, 520000, 'active', '2019-07-15'),
    ('佐藤次郎', 1, 380000, 'active', '2021-01-10'),
    ('高橋三郎', 2, 600000, 'active', '2018-04-01'),
    ('山田四郎', 3, 420000, 'active', '2022-04-01'),
    ('伊藤五郎', 2, 480000, 'inactive', '2017-10-01'),
    ('渡辺六子', 1, 510000, 'active', '2019-01-15');
 
-- SELECT文の完全な構文例
SELECT DISTINCT
    d.name AS department,              -- ⑥ 列を選択・計算
    COUNT(*) AS employee_count,        -- ⑥ 集約関数の評価
    AVG(e.salary) AS avg_salary        -- ⑥ 集約関数の評価
FROM employees e                       -- ① テーブルを特定
    INNER JOIN departments d           -- ① テーブル結合
        ON e.department_id = d.id      -- ② 結合条件の評価
WHERE e.status = 'active'              -- ③ 行レベルのフィルタ
GROUP BY d.name                        -- ④ グループ化
HAVING COUNT(*) >= 2                   -- ⑤ グループレベルのフィルタ
ORDER BY avg_salary DESC               -- ⑧ 並べ替え
LIMIT 10;                              -- ⑨ 件数制限

-- === 比較演算子 ===
SELECT * FROM products WHERE price > 1000;               -- より大きい
SELECT * FROM products WHERE price >= 1000;              -- 以上
SELECT * FROM products WHERE price < 5000;               -- より小さい
SELECT * FROM products WHERE price <= 5000;              -- 以下
SELECT * FROM products WHERE price <> 1000;              -- 等しくない（標準SQL）
SELECT * FROM products WHERE price != 1000;              -- 等しくない（多くのRDBMSで使用可）
 
-- === 範囲 ===
SELECT * FROM products WHERE price BETWEEN 1000 AND 5000;
-- ↑ price >= 1000 AND price <= 5000 と同等（両端を含む）
 
-- === パターンマッチ（LIKE） ===
SELECT * FROM users WHERE name LIKE '田中%';             -- 前方一致
SELECT * FROM users WHERE email LIKE '%@gmail.com';      -- 後方一致
SELECT * FROM users WHERE name LIKE '%太%';              -- 部分一致
SELECT * FROM users WHERE code LIKE 'A_B';               -- _ = 任意の1文字
-- LIKE のエスケープ
SELECT * FROM products WHERE name LIKE '%25\%%' ESCAPE '\';  -- '%'を含む
 
-- === NULL判定 ===
-- 重要: = NULL は動作しない（3値論理のため）
SELECT * FROM users WHERE phone IS NULL;                  -- NULLの行
SELECT * FROM users WHERE phone IS NOT NULL;              -- NULLでない行
 
-- === IN / NOT IN ===
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');
 
-- === 複合条件（AND / OR） ===
SELECT * FROM products
WHERE (category = 'electronics' OR category = 'books')
  AND price < 5000
  AND stock > 0;
 
-- === EXISTS ===
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.total > 10000
);
 
-- === ANY / ALL ===
SELECT * FROM employees
WHERE salary > ALL (
    SELECT AVG(salary) FROM employees GROUP BY department_id
);
 
-- === CASE式をWHEREで使用 ===
SELECT * FROM orders
WHERE CASE
    WHEN priority = 'high' THEN total_amount > 0
    WHEN priority = 'low'  THEN total_amount > 1000
    ELSE TRUE
END;

-- === 計算列 ===
SELECT
    name,
    salary,
    salary * 12 AS annual_salary,                    -- 年収
    salary * 12 * 1.1 AS annual_with_bonus,          -- 年収+賞与
    ROUND(salary / 160, 0) AS hourly_rate            -- 時給換算
FROM employees;
 
-- === CASE式（条件分岐） ===
SELECT
    name,
    salary,
    CASE
        WHEN salary >= 600000 THEN 'S'
        WHEN salary >= 500000 THEN 'A'
        WHEN salary >= 400000 THEN 'B'
        ELSE 'C'
    END AS grade,
    -- CASE式の簡略形（単純CASE）
    CASE status
        WHEN 'active'   THEN '在籍'
        WHEN 'inactive' THEN '休職'
        WHEN 'retired'  THEN '退職'
        ELSE '不明'
    END AS status_label
FROM employees;
 
-- === COALESCE（最初の非NULL値） ===
SELECT
    name,
    COALESCE(phone, mobile, email, '連絡先なし') AS primary_contact
FROM employees;
 
-- === NULLIF（2値が等しければNULL） ===
-- 0除算防止パターン
SELECT
    department_id,
    total_revenue,
    total_cost,
    total_revenue / NULLIF(total_cost, 0) AS cost_ratio  -- 0除算を回避
FROM department_financials;
 
-- === 型変換（CAST） ===
SELECT
    CAST(price AS INTEGER) AS rounded_price,
    CAST(created_at AS DATE) AS created_date,
    CAST(quantity AS VARCHAR(10)) || '個' AS quantity_text
FROM products;
 
-- === サブクエリとの組み合わせ ===
SELECT
    e.name,
    e.salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    e.salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg,
    ROUND(e.salary / (SELECT AVG(salary) FROM employees) * 100, 1) AS pct_of_avg
FROM employees e
ORDER BY diff_from_avg DESC;

-- 基本的なソート
SELECT * FROM employees ORDER BY salary DESC;                -- 降順
SELECT * FROM employees ORDER BY salary ASC;                 -- 昇順（デフォルト）
SELECT * FROM employees ORDER BY department_id ASC, salary DESC;  -- 複合ソート
 
-- NULLの並び順制御
SELECT * FROM employees ORDER BY department_id NULLS FIRST;  -- NULLを先頭
SELECT * FROM employees ORDER BY department_id NULLS LAST;   -- NULLを末尾
 
-- 列番号によるソート（可読性が低いため非推奨だが知っておくべき）
SELECT name, salary FROM employees ORDER BY 2 DESC;  -- 2番目の列 = salary
 
-- CASE式でカスタムソート
SELECT * FROM orders
ORDER BY
    CASE status
        WHEN 'urgent'     THEN 1
        WHEN 'processing' THEN 2
        WHEN 'pending'    THEN 3
        WHEN 'completed'  THEN 4
        ELSE 5
    END,
    created_at DESC;

-- DISTINCT: 重複行の排除
SELECT DISTINCT department_id FROM employees;
 
-- DISTINCT ON (PostgreSQL固有): 各グループの最初の行のみ
SELECT DISTINCT ON (department_id) *
FROM employees
ORDER BY department_id, salary DESC;
-- → 各部署で最も給与の高い社員1名ずつ
 
-- LIMIT / OFFSET: ページネーション
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;     -- 1ページ目（1-20件）
 
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;    -- 2ページ目（21-40件）
 
-- 標準SQL: FETCH FIRST
SELECT * FROM products
ORDER BY created_at DESC
OFFSET 20 ROWS
FETCH NEXT 20 ROWS ONLY;

-- === 基本的なINSERT（単一行） ===
INSERT INTO employees (name, department_id, salary, hired_date)
VALUES ('山田太郎', 10, 400000, '2024-04-01');
 
-- === 複数行の一括INSERT ===
-- パフォーマンス: 個別INSERTの10〜100倍高速
INSERT INTO employees (name, department_id, salary, hired_date)
VALUES
    ('鈴木花子', 20, 450000, '2024-04-01'),
    ('佐藤次郎', 10, 380000, '2024-04-01'),
    ('高橋三郎', 30, 420000, '2024-04-01');
 
-- === SELECTの結果をINSERT ===
-- データ移行・アーカイブに最適
INSERT INTO employee_archive (name, department_id, salary, archived_at)
SELECT name, department_id, salary, CURRENT_TIMESTAMP
FROM employees
WHERE status = 'retired';
 
-- === RETURNING句で挿入結果を取得（PostgreSQL） ===
-- アプリケーション側で自動生成されたIDを即座に取得可能
INSERT INTO employees (name, department_id, salary)
VALUES ('新人一号', 10, 350000)
RETURNING id, name, created_at;
-- → id=42, name='新人一号', created_at='2024-04-01 10:30:00'
 
-- === DEFAULT VALUES ===
-- 全列がDEFAULT値またはNULL許可の場合
INSERT INTO audit_log DEFAULT VALUES;
 
-- === INSERT with CTE ===
-- 複雑な変換を伴う挿入
WITH source_data AS (
    SELECT
        name,
        department_id,
        salary * 1.05 AS adjusted_salary  -- 5%昇給した金額で挿入
    FROM employees
    WHERE status = 'active' AND hired_date < '2020-01-01'
)
INSERT INTO salary_adjustments (employee_name, dept_id, new_salary, adjusted_at)
SELECT name, department_id, adjusted_salary, CURRENT_TIMESTAMP
FROM source_data;

-- === PostgreSQL: ON CONFLICT ===
-- user_settingsテーブル: (user_id, key) がUNIQUE制約
CREATE TABLE user_settings (
    user_id    INTEGER NOT NULL,
    key        VARCHAR(100) NOT NULL,
    value      TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, key)
);
 
-- 存在すればvalueを更新、なければ新規挿入
INSERT INTO user_settings (user_id, key, value)
VALUES (1, 'theme', 'dark')
ON CONFLICT (user_id, key)
DO UPDATE SET
    value = EXCLUDED.value,          -- EXCLUDED = 挿入しようとした値
    updated_at = CURRENT_TIMESTAMP;
 
-- ON CONFLICT DO NOTHING: 重複を無視（エラーを出さない）
INSERT INTO user_settings (user_id, key, value)
VALUES (1, 'theme', 'dark')
ON CONFLICT (user_id, key) DO NOTHING;
 
-- 条件付きUPSERT: 既存値より新しい場合のみ更新
INSERT INTO cache_entries (key, value, version)
VALUES ('user:1', '{"name":"田中"}', 5)
ON CONFLICT (key)
DO UPDATE SET
    value = EXCLUDED.value,
    version = EXCLUDED.version
WHERE cache_entries.version < EXCLUDED.version;  -- バージョンが新しい場合のみ
 
-- === MySQL: ON DUPLICATE KEY UPDATE ===
INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (1, 'theme', 'dark')
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = NOW();
 
-- === SQL Server: MERGE ===
MERGE INTO user_settings AS target
USING (VALUES (1, 'theme', 'dark')) AS source (user_id, key, value)
ON target.user_id = source.user_id AND target.key = source.key
WHEN MATCHED THEN
    UPDATE SET value = source.value, updated_at = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (user_id, key, value) VALUES (source.user_id, source.key, source.value);

-- === 基本的なUPDATE ===
UPDATE employees
SET salary = 500000
WHERE employee_id = 42;
 
-- === 複数列の同時更新 ===
UPDATE employees
SET salary = salary * 1.05,
    updated_at = CURRENT_TIMESTAMP
WHERE department_id = 10
  AND performance_rating >= 4;
 
-- === 計算式を使った更新 ===
-- 全社員の給与を等級に応じて昇給
UPDATE employees
SET salary = CASE
    WHEN grade = 'S' THEN salary * 1.10   -- S等級: 10%昇給
    WHEN grade = 'A' THEN salary * 1.07   -- A等級: 7%昇給
    WHEN grade = 'B' THEN salary * 1.05   -- B等級: 5%昇給
    ELSE salary * 1.03                     -- その他: 3%昇給
END,
updated_at = CURRENT_TIMESTAMP
WHERE status = 'active';
 
-- === JOINを使ったUPDATE（PostgreSQL） ===
-- 禁止顧客の未処理注文をキャンセル
UPDATE orders o
SET status = 'cancelled',
    cancelled_at = NOW(),
    cancel_reason = 'customer_banned'
FROM customers c
WHERE o.customer_id = c.id
  AND c.is_banned = TRUE
  AND o.status = 'pending';
 
-- === JOINを使ったUPDATE（MySQL） ===
-- UPDATE orders o
-- INNER JOIN customers c ON o.customer_id = c.id
-- SET o.status = 'cancelled',
--     o.cancelled_at = NOW()
-- WHERE c.is_banned = TRUE
--   AND o.status = 'pending';
 
-- === サブクエリを使ったUPDATE ===
UPDATE products
SET price = price * 0.9
WHERE category_id IN (
    SELECT id FROM categories WHERE name = 'セール対象'
);
 
-- === RETURNING句で更新結果を確認（PostgreSQL） ===
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 20
RETURNING id, name, salary AS new_salary;
 
-- === CTEを使った複雑なUPDATE ===
-- 部署平均を下回る社員を最低ラインまで引き上げ
WITH dept_min AS (
    SELECT department_id, AVG(salary) * 0.8 AS min_salary
    FROM employees
    WHERE status = 'active'
    GROUP BY department_id
)
UPDATE employees e
SET salary = dm.min_salary
FROM dept_min dm
WHERE e.department_id = dm.department_id
  AND e.salary < dm.min_salary
  AND e.status = 'active'
RETURNING e.id, e.name, e.salary AS adjusted_salary;

-- === 基本的なDELETE ===
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;
 
-- === JOINを使ったDELETE（PostgreSQL: USING句） ===
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.status = 'cancelled';
 
-- === RETURNING句で削除した行を取得 ===
DELETE FROM notifications
WHERE user_id = 42 AND read_at IS NOT NULL
RETURNING id, message, created_at;
 
-- === 論理削除（ソフトデリート）の実装パターン ===
-- 基本パターン
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP,
    status = 'deleted',
    -- 個人情報の匿名化（GDPR対応）
    email = 'deleted_' || id || '@deleted.example.com',
    name = '削除済みユーザー'
WHERE id = 42;
 
-- 論理削除対応のビュー
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
 
-- 論理削除 + UNIQUE制約の問題と解決
-- 問題: deleted_atがNULLの行同士でのみUNIQUEを保証したい
-- PostgreSQL: 部分インデックスで解決
CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE deleted_at IS NULL;
 
-- === TRUNCATE: テーブル全行の高速削除（DDL操作） ===
TRUNCATE TABLE temp_import_data;
-- ※ WHERE句使用不可
-- ※ TRUNCATEはロールバック不可（MySQL）、PostgreSQLでは可能
-- ※ トリガーが発火しない
-- ※ 自動連番がリセットされる
 
-- === CASCADE付きTRUNCATE ===
-- 外部キーで参照されているテーブルも同時にTRUNCATE
TRUNCATE TABLE orders CASCADE;  -- order_itemsも同時にTRUNCATE
 
-- === DELETEの安全な実行（トランザクション） ===
BEGIN;
    -- Step 1: 削除対象の確認
    SELECT COUNT(*) FROM logs WHERE created_at < '2023-01-01';
    -- → 15,000行
 
    -- Step 2: 削除実行
    DELETE FROM logs WHERE created_at < '2023-01-01';
    -- → DELETE 15000
 
    -- Step 3: 確認して確定
    SELECT COUNT(*) FROM logs WHERE created_at < '2023-01-01';
    -- → 0行（期待通り）
COMMIT;
 
-- === 大量データの分割削除（ロック時間を短くする） ===
-- 一度に全件削除するとロック時間が長くなるため、バッチで実行
DO $$
DECLARE
    deleted_count INTEGER;
BEGIN
    LOOP
        DELETE FROM logs
        WHERE id IN (
            SELECT id FROM logs
            WHERE created_at < '2023-01-01'
            LIMIT 10000  -- 1回あたり10,000件ずつ
        );
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        EXIT WHEN deleted_count = 0;
 
        -- 他のトランザクションに実行機会を与える
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

-- NG: 全行が更新されてしまう！
UPDATE employees SET salary = 0;
-- → 全社員の給与が0になる
 
-- NG: 全行が削除されてしまう！
DELETE FROM employees;
-- → 全社員のデータが消える
 
-- OK: 必ずWHERE句で対象を限定
UPDATE employees SET salary = 500000 WHERE employee_id = 42;
 
-- 安全策1: 先にSELECTで確認
SELECT COUNT(*), MIN(salary), MAX(salary) FROM employees
WHERE department_id = 99;
-- → 5行、320000〜480000（妥当か確認）
 
DELETE FROM employees WHERE department_id = 99;
 
-- 安全策2: トランザクションで囲む
BEGIN;
    DELETE FROM employees WHERE department_id = 99;
    -- DELETE 5 ← 件数が期待通りか確認
    -- 問題なければ → COMMIT;
    -- 問題あれば → ROLLBACK;
COMMIT;
 
-- 安全策3: 本番DBではsafe_updateモード有効化（MySQL）
-- SET sql_safe_updates = 1;
-- → WHERE句のないUPDATE/DELETEがエラーになる

-- NG: 不要な列まで全て取得
SELECT * FROM orders;
 
-- 問題点:
-- 1. ネットワーク帯域の浪費（BLOB/TEXT列がある場合は特に深刻）
--    例: 100万行 × 不要なBLOB列 = 数GBのネットワーク転送
-- 2. テーブル構造変更時にアプリケーションが壊れる
--    例: 列の追加/削除/順序変更でORMのマッピングが崩れる
-- 3. インデックスオンリースキャン（カバリングインデックス）が効かない
--    例: CREATE INDEX ON orders(status, customer_id)があっても
--         SELECT * では全行ヒープアクセスが必要
-- 4. 実行計画が非効率になる
--    不要な列のデシリアライズ、メモリ消費、ソートコスト増大
 
-- OK: 必要な列だけ明示的に指定
SELECT order_id, customer_id, total_amount, status
FROM orders
WHERE status = 'pending';
 
-- 例外: 対話的な探索やデバッグ時にはSELECT *は許容される
-- ただしLIMITを付ける
SELECT * FROM orders LIMIT 10;

-- NG: SQLインジェクションの脆弱性
-- Python疑似コード
-- query = f"SELECT * FROM users WHERE name = '{user_input}'"
-- user_input = "'; DROP TABLE users; --"
-- → SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
 
-- OK: パラメータバインド（プリペアドステートメント）
-- Python (psycopg2)
-- cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))
-- Java (JDBC)
-- PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
-- ps.setString(1, userInput);
-- Node.js (pg)
-- client.query('SELECT * FROM users WHERE name = $1', [userInput])
 
-- PostgreSQLでのプリペアドステートメント
PREPARE get_user (TEXT) AS
SELECT * FROM users WHERE name = $1;
 
EXECUTE get_user('田中太郎');
 
DEALLOCATE get_user;

CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);
 
CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE,
    department_id INTEGER REFERENCES departments(id),
    salary        DECIMAL(10, 2) NOT NULL CHECK (salary >= 0),
    status        VARCHAR(20) DEFAULT 'active',
    hired_date    DATE NOT NULL DEFAULT CURRENT_DATE
);

-- テーブル定義
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total      DECIMAL(10, 2),
    status     VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
 
CREATE TABLE user_sessions (
    id         UUID PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cart_items (
    id         SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    added_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, product_id)
);

# デバッグ用ユーティリティ
import logging
import traceback
from functools import wraps
 
# ロガーの設定
logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s [%(levelname)s] %(name)s: %(message)s'
)
logger = logging.getLogger(__name__)
 
def debug_decorator(func):
    """関数の入出力をログ出力するデコレータ"""
    @wraps(func)
    def wrapper(*args, **kwargs):
        logger.debug(f"呼び出し: {func.__name__}(args={args}, kwargs={kwargs})")
        try:
            result = func(*args, **kwargs)
            logger.debug(f"戻り値: {func.__name__} -> {result}")
            return result
        except Exception as e:
            logger.error(f"例外発生: {func.__name__}: {e}")
            logger.error(traceback.format_exc())
            raise
    return wrapper
 
@debug_decorator
def process_data(items):
    """データ処理（デバッグ対象）"""
    if not items:
        raise ValueError("空のデータ")
    return [item * 2 for item in items]

# 設計判断の記録テンプレート
class ArchitectureDecisionRecord:
    """ADR (Architecture Decision Record) の作成"""
 
    def __init__(self, title: str):
        self.title = title
        self.context = ""
        self.decision = ""
        self.consequences = []
        self.alternatives = []
 
    def set_context(self, context: str):
        """背景と課題の記述"""
        self.context = context
        return self
 
    def set_decision(self, decision: str):
        """決定内容の記述"""
        self.decision = decision
        return self
 
    def add_consequence(self, consequence: str, positive: bool = True):
        """結果の追加"""
        self.consequences.append({
            'description': consequence,
            'type': 'positive' if positive else 'negative'
        })
        return self
 
    def add_alternative(self, name: str, reason_rejected: str):
        """却下した代替案の追加"""
        self.alternatives.append({
            'name': name,
            'reason_rejected': reason_rejected
        })
        return self
 
    def to_markdown(self) -> str:
        """Markdown形式で出力"""
        md = f"# ADR: {self.title}\n\n"
        md += f"## 背景\n{self.context}\n\n"
        md += f"## 決定\n{self.decision}\n\n"
        md += "## 結果\n"
        for c in self.consequences:
            icon = "✅" if c['type'] == 'positive' else "⚠️"
            md += f"- {icon} {c['description']}\n"
        md += "\n## 却下した代替案\n"
        for a in self.alternatives:
            md += f"- **{a['name']}**: {a['reason_rejected']}\n"
        return md

WITH old AS (
    SELECT id, salary FROM employees WHERE id = 42
)
UPDATE employees SET salary = salary * 1.1 WHERE id = 42
RETURNING id,
    (SELECT salary FROM old) AS old_salary,
    salary AS new_salary;
-- → id=42, old_salary=450000, new_salary=495000

CREATE TABLE salary_audit (
    id          SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary  DECIMAL(10, 2),
    new_salary  DECIMAL(10, 2),
    changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_salary_audit
    AFTER UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_salary_change();

-- PostgreSQL: COPY文（最速）
COPY employees (name, department_id, salary, hired_date)
FROM '/path/to/data.csv'
WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',');
 
-- さらに高速化する場合
BEGIN;
    -- 1. インデックスを一時的に無効化
    ALTER TABLE employees DISABLE TRIGGER ALL;
    DROP INDEX IF EXISTS idx_employees_dept;
 
    -- 2. COPY実行
    COPY employees FROM '/path/to/data.csv' WITH (FORMAT CSV, HEADER);
 
    -- 3. インデックスを再作成
    CREATE INDEX idx_employees_dept ON employees(department_id);
    ALTER TABLE employees ENABLE TRIGGER ALL;
 
    -- 4. 統計情報の更新
    ANALYZE employees;
COMMIT;