-- IN: サブクエリ結果のリストに含まれるか
SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '東京'
);
 
-- 比較演算子: スカラーサブクエリ
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees  -- 全社平均を返す
);
 
-- ALL / ANY: 集合との比較
-- 全部署の平均給与より高い社員
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT AVG(salary) FROM employees GROUP BY department_id
);
 
-- いずれかの部署の平均給与より高い社員
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT AVG(salary) FROM employees GROUP BY department_id
);
 
-- BETWEEN とサブクエリの組み合わせ
SELECT name, salary
FROM employees
WHERE salary BETWEEN
    (SELECT AVG(salary) - STDDEV(salary) FROM employees)
    AND
    (SELECT AVG(salary) + STDDEV(salary) FROM employees);
-- → 標準偏差の範囲内にいる社員

-- 派生テーブル: サブクエリの結果をテーブルとして使用
SELECT
    dept_stats.department_name,
    dept_stats.avg_salary,
    dept_stats.employee_count
FROM (
    SELECT
        d.name AS department_name,
        AVG(e.salary) AS avg_salary,
        COUNT(*) AS employee_count
    FROM employees e
        INNER JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE dept_stats.avg_salary > 500000
ORDER BY dept_stats.avg_salary DESC;
 
-- SELECT句でのスカラーサブクエリ
SELECT
    e.name,
    e.salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    e.salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees e
ORDER BY diff_from_avg DESC;
 
-- 派生テーブルの中でウィンドウ関数を使用
SELECT
    ranked.name,
    ranked.salary,
    ranked.department_name,
    ranked.salary_rank
FROM (
    SELECT
        e.name,
        e.salary,
        d.name AS department_name,
        RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS salary_rank
    FROM employees e
        JOIN departments d ON e.department_id = d.id
) AS ranked
WHERE ranked.salary_rank <= 3;
-- → 各部署の給与TOP3

-- 派生テーブルで書いた場合
SELECT dept_name, avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 500000;
 
-- CTEで書いた場合（同等の結果）
WITH dept_stats AS (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
)
SELECT dept_name, avg_salary
FROM dept_stats
WHERE avg_salary > 500000;
 
-- CTEの利点:
-- 1. 同じサブクエリを複数回参照できる
-- 2. 可読性が高い（上から下に読める）
-- 3. 再帰クエリが書ける
 
-- パフォーマンスの違い（PostgreSQL 12以降）:
-- PostgreSQL 12以降ではCTEがインライン展開される（NOT MATERIALIZED）
-- → パフォーマンスは派生テーブルとほぼ同等
-- 強制的にマテリアライズしたい場合: WITH x AS MATERIALIZED (...)

-- 自部署の平均給与より高い社員を取得
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id  -- 外部行を参照
);
 
-- 各カテゴリで最新の商品を取得
SELECT p.name, p.category_id, p.created_at
FROM products p
WHERE p.created_at = (
    SELECT MAX(p2.created_at)
    FROM products p2
    WHERE p2.category_id = p.category_id
);
 
-- 同等のウィンドウ関数版（通常こちらが推奨）
SELECT name, category_id, created_at
FROM (
    SELECT
        name, category_id, created_at,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at DESC) AS rn
    FROM products
) sub
WHERE rn = 1;
 
-- 相関サブクエリでの更新
UPDATE employees e
SET salary = salary * 1.1
WHERE salary < (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);
-- → 自部署の平均以下の社員に10%昇給
 
-- 相関サブクエリでの削除
DELETE FROM order_items oi
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.id = oi.order_id
    AND o.status != 'cancelled'
);
-- → キャンセル済み注文の明細を削除

-- オプティマイザの変換を確認する例
EXPLAIN ANALYZE
SELECT e.name
FROM employees e
WHERE e.department_id IN (
    SELECT d.id FROM departments d WHERE d.location = '東京'
);
 
-- PostgreSQLの実行計画（変換後）:
-- Hash Semi Join  (actual time=0.050..1.200 rows=500 loops=1)
--   Hash Cond: (e.department_id = d.id)
--   -> Seq Scan on employees e
--   -> Hash
--     -> Seq Scan on departments d
--       Filter: (location = '東京')
-- → INサブクエリがSEMI JOINに自動変換されている

-- 注文実績のある顧客を取得
SELECT c.name, c.email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.order_date >= '2024-01-01'
);
 
-- 注文実績のない顧客を取得
SELECT c.name, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);
 
-- EXISTS vs IN の書き換え
-- 以下は論理的に同等（パフォーマンスは異なる場合あり）
 
-- INバージョン
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders);
 
-- EXISTSバージョン（大規模データではこちらが有利な場合が多い）
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
 
-- 複数条件のEXISTS
-- 「未出荷の注文がある」かつ「VIP顧客」
SELECT c.name, c.email
FROM customers c
WHERE c.tier = 'VIP'
  AND EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.status = 'pending'
      AND o.total_amount > 10000
  );

-- LATERAL JOIN: 各行に対してサブクエリを実行しFROM句で使用
-- 各部署の給与TOP3を取得
SELECT d.name AS department, top3.name, top3.salary
FROM departments d
    CROSS JOIN LATERAL (
        SELECT e.name, e.salary
        FROM employees e
        WHERE e.department_id = d.id
        ORDER BY e.salary DESC
        LIMIT 3
    ) AS top3;
 
-- 従来の相関サブクエリでは困難な「各グループのN件」を簡潔に表現
 
-- LATERAL JOINの別例: 最新の注文情報を横に並べる
SELECT
    c.name,
    c.email,
    latest.order_date,
    latest.total_amount
FROM customers c
    LEFT JOIN LATERAL (
        SELECT o.order_date, o.total_amount
        FROM orders o
        WHERE o.customer_id = c.id
        ORDER BY o.order_date DESC
        LIMIT 1
    ) AS latest ON TRUE;
-- LEFT JOIN LATERAL ... ON TRUE で顧客に注文がない場合もNULLで返す
 
-- LATERALで時系列データの前の行を参照
SELECT
    m.month,
    m.revenue,
    prev.revenue AS prev_revenue,
    ROUND((m.revenue - prev.revenue) / prev.revenue * 100, 1) AS growth_pct
FROM monthly_sales m
    LEFT JOIN LATERAL (
        SELECT revenue
        FROM monthly_sales
        WHERE month = m.month - INTERVAL '1 month'
    ) prev ON TRUE
ORDER BY m.month;

-- 全社員を4分位（四分位）に分類
SELECT
    name,
    salary,
    CASE
        WHEN salary >= (SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) FROM employees)
            THEN 'Q4 (上位25%)'
        WHEN salary >= (SELECT PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary) FROM employees)
            THEN 'Q3'
        WHEN salary >= (SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) FROM employees)
            THEN 'Q2'
        ELSE 'Q1 (下位25%)'
    END AS quartile
FROM employees
ORDER BY salary DESC;
 
-- ウィンドウ関数版（推奨: サブクエリが1回で済む）
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile_num,
    CASE NTILE(4) OVER (ORDER BY salary)
        WHEN 4 THEN 'Q4 (上位25%)'
        WHEN 3 THEN 'Q3'
        WHEN 2 THEN 'Q2'
        ELSE 'Q1 (下位25%)'
    END AS quartile
FROM employees
ORDER BY salary DESC;
 
-- 前月比の売上比較
SELECT
    current_month.month,
    current_month.total,
    prev_month.total AS prev_total,
    ROUND((current_month.total - prev_month.total) / prev_month.total * 100, 1) AS growth_pct
FROM (
    SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
    FROM sales GROUP BY 1
) current_month
LEFT JOIN (
    SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
    FROM sales GROUP BY 1
) prev_month ON current_month.month = prev_month.month + INTERVAL '1 month'
ORDER BY current_month.month;
 
-- CTEで書き直した版（DRY原則、推奨）
WITH monthly AS (
    SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total
    FROM sales GROUP BY 1
)
SELECT
    c.month,
    c.total,
    p.total AS prev_total,
    ROUND((c.total - p.total) / p.total * 100, 1) AS growth_pct
FROM monthly c
LEFT JOIN monthly p ON c.month = p.month + INTERVAL '1 month'
ORDER BY c.month;

-- パターン1: 存在確認と条件付き集約
-- 「直近30日に注文があり、かつ返品がない優良顧客」
SELECT c.id, c.name, c.email,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id
     AND o.order_date >= CURRENT_DATE - INTERVAL '30 days') AS recent_orders,
    (SELECT SUM(o.total_amount) FROM orders o WHERE o.customer_id = c.id
     AND o.order_date >= CURRENT_DATE - INTERVAL '30 days') AS recent_total
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
)
AND NOT EXISTS (
    SELECT 1 FROM returns r
    JOIN orders o ON r.order_id = o.id
    WHERE o.customer_id = c.id
    AND r.return_date >= CURRENT_DATE - INTERVAL '30 days'
);
 
-- パターン2: 行値コンストラクタとサブクエリ
-- 複数列の同時比較
SELECT * FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);
-- → 各部署の最高給与者を取得
 
-- パターン3: INSERT ... SELECT サブクエリ
-- アーカイブテーブルへの移行
INSERT INTO orders_archive (id, customer_id, order_date, total_amount)
SELECT id, customer_id, order_date, total_amount
FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '2 years'
  AND status = 'delivered';
 
-- パターン4: UPDATE with サブクエリ
-- 各商品の平均評価を更新
UPDATE products p
SET avg_rating = sub.avg_rating,
    review_count = sub.review_count
FROM (
    SELECT
        product_id,
        AVG(rating)::DECIMAL(3,2) AS avg_rating,
        COUNT(*) AS review_count
    FROM reviews
    GROUP BY product_id
) sub
WHERE p.id = sub.product_id;
 
-- パターン5: DELETE with サブクエリ
-- 重複行の削除（最小IDを残す）
DELETE FROM employees
WHERE id NOT IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY name, department_id, salary
);
 
-- より安全な書き方（NOT EXISTS版）
DELETE FROM employees e1
WHERE EXISTS (
    SELECT 1 FROM employees e2
    WHERE e2.name = e1.name
      AND e2.department_id = e1.department_id
      AND e2.salary = e1.salary
      AND e2.id < e1.id
);

-- テストデータの前提: employees 10万行, departments 50行
 
-- パターン1: INサブクエリ
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '東京'
);
-- 実行計画:
-- Hash Semi Join  (cost=1.63..2500.00 rows=20000)
--   (actual time=0.050..25.000 rows=20000 loops=1)
--   Hash Cond: (employees.department_id = departments.id)
--   -> Seq Scan on employees
--   -> Hash
--     -> Seq Scan on departments
--       Filter: (location = '東京')
-- Execution Time: 25.500 ms
 
-- パターン2: EXISTS
EXPLAIN ANALYZE
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = e.department_id AND d.location = '東京'
);
-- 実行計画（PostgreSQLでは同じ計画になることが多い）:
-- Hash Semi Join  (cost=1.63..2500.00 rows=20000)
--   (actual time=0.050..25.000 rows=20000 loops=1)
-- → オプティマイザが同じ実行計画に変換
 
-- パターン3: JOIN
EXPLAIN ANALYZE
SELECT e.* FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = '東京';
-- 実行計画:
-- Hash Join  (cost=1.63..2500.00 rows=20000)
--   (actual time=0.050..24.000 rows=20000 loops=1)
-- → ほぼ同じ実行計画（JOINは重複の可能性がある点に注意）

-- NG: NULLが含まれるとNOT INは全行を除外する
SELECT * FROM employees
WHERE department_id NOT IN (
    SELECT department_id FROM temp_exclusions
    -- temp_exclusionsにNULLが1行でもあると、結果は0行！
);
 
-- 理由: NULL との比較は常にUNKNOWN
-- x NOT IN (1, 2, NULL) → x<>1 AND x<>2 AND x<>NULL
-- → ... AND UNKNOWN → UNKNOWN → フィルタされない
 
-- OK: NOT EXISTS を使う
SELECT * FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM temp_exclusions t
    WHERE t.department_id = e.department_id
);
 
-- OK: NOT IN でもNULLを除外すれば安全
SELECT * FROM employees
WHERE department_id NOT IN (
    SELECT department_id FROM temp_exclusions
    WHERE department_id IS NOT NULL
);

-- NG: 行ごとにサブクエリが実行される（N+1問題と同等）
SELECT
    e.name,
    (SELECT d.name FROM departments d WHERE d.id = e.department_id) AS dept_name,
    (SELECT COUNT(*) FROM projects p WHERE p.lead_id = e.id) AS project_count,
    (SELECT MAX(r.rating) FROM reviews r WHERE r.employee_id = e.id) AS best_rating
FROM employees e;
-- → 社員1000人なら 1000 * 3 = 3000回のサブクエリ実行
 
-- OK: JOINと集約で1クエリに
SELECT
    e.name,
    d.name AS dept_name,
    COUNT(DISTINCT p.id) AS project_count,
    MAX(r.rating) AS best_rating
FROM employees e
    LEFT JOIN departments d ON d.id = e.department_id
    LEFT JOIN projects p ON p.lead_id = e.id
    LEFT JOIN reviews r ON r.employee_id = e.id
GROUP BY e.id, e.name, d.name;
 
-- OK: LATERAL JOINで集約を分離（JOINによる行爆発を防ぐ）
SELECT
    e.name,
    d.name AS dept_name,
    pc.project_count,
    br.best_rating
FROM employees e
    LEFT JOIN departments d ON d.id = e.department_id
    LEFT JOIN LATERAL (
        SELECT COUNT(*) AS project_count
        FROM projects p WHERE p.lead_id = e.id
    ) pc ON TRUE
    LEFT JOIN LATERAL (
        SELECT MAX(rating) AS best_rating
        FROM reviews r WHERE r.employee_id = e.id
    ) br ON TRUE;

-- NG: 不必要にネストされたサブクエリ
SELECT * FROM (
    SELECT * FROM (
        SELECT * FROM (
            SELECT id, name, salary, department_id
            FROM employees
            WHERE salary > 500000
        ) AS step1
        WHERE department_id IN (10, 20, 30)
    ) AS step2
    ORDER BY salary DESC
) AS step3
LIMIT 10;
 
-- OK: 1つのクエリに統合
SELECT id, name, salary, department_id
FROM employees
WHERE salary > 500000
  AND department_id IN (10, 20, 30)
ORDER BY salary DESC
LIMIT 10;

-- ALL で空集合との比較
-- salary > ALL (空集合) → TRUE（全ての要素を満たす = 空虚な真）
SELECT * FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = 999
    -- department_id=999が存在しない場合、空集合
);
-- → 全社員が返される
 
-- ANY で空集合との比較
-- salary > ANY (空集合) → FALSE
SELECT * FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department_id = 999
);
-- → 0行が返される
 
-- IN で空集合
-- department_id IN (空集合) → FALSE
SELECT * FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '火星'
);
-- → 0行が返される

-- スカラーサブクエリが複数行を返すとエラー
SELECT name, (
    SELECT salary FROM employees WHERE department_id = 10
    -- 部署10に複数社員がいるとエラー！
) AS salary
FROM departments;
-- → ERROR: more than one row returned by a subquery
 
-- 対策1: 集約関数で1行に確定
SELECT name, (
    SELECT AVG(salary) FROM employees e WHERE e.department_id = d.id
) AS avg_salary
FROM departments d;
 
-- 対策2: LIMIT 1 で強制的に1行
SELECT name, (
    SELECT salary FROM employees e
    WHERE e.department_id = d.id
    ORDER BY salary DESC LIMIT 1
) AS max_salary
FROM departments d;

-- 自分自身のテーブルを参照するサブクエリ
-- 「同じ部署の全員より給与が高い社員」= 各部署の最高給与者
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary >= ALL (
    SELECT e2.salary FROM employees e2
    WHERE e2.department_id = e.department_id
);
 
-- 注意: 部署に1人しかいない場合も正しく動作する
-- （自分自身との比較: salary >= salary → TRUE）
 
-- 「自分より給与が高い同僚がいない社員」（同等だが NOT EXISTS版）
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees e2
    WHERE e2.department_id = e.department_id
      AND e2.salary > e.salary
);

-- 元のクエリ
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE is_active = TRUE
);

-- 最適化前のクエリ（遅い）
SELECT
    c.name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count,
    (SELECT SUM(oi.quantity * oi.unit_price)
     FROM order_items oi
     JOIN orders o ON oi.order_id = o.id
     WHERE o.customer_id = c.id) AS lifetime_value,
    (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 5;
-- → SELECT句で4つの相関サブクエリ + WHERE句で1つ = 5回のサブクエリ

# 演習1: 基本実装のテンプレート
class Exercise1:
    """基本的な実装パターンの演習"""
 
    def __init__(self):
        self.data = []
 
    def validate_input(self, value):
        """入力値の検証"""
        if value is None:
            raise ValueError("入力値がNoneです")
        return True
 
    def process(self, value):
        """データ処理のメインロジック"""
        self.validate_input(value)
        self.data.append(value)
        return self.data
 
    def get_results(self):
        """処理結果の取得"""
        return {
            'count': len(self.data),
            'data': self.data
        }
 
# テスト
def test_exercise1():
    ex = Exercise1()
    assert ex.process(1) == [1]
    assert ex.process(2) == [1, 2]
    assert ex.get_results()['count'] == 2
 
    try:
        ex.process(None)
        assert False, "例外が発生するべき"
    except ValueError:
        pass
 
    print("全テスト合格!")
 
test_exercise1()

# 演習2: 応用パターン
from typing import List, Dict, Optional
from datetime import datetime
 
class AdvancedExercise:
    """応用パターンの演習"""
 
    def __init__(self, max_size: int = 100):
        self._items: List[Dict] = []
        self._max_size = max_size
        self._created_at = datetime.now()
 
    def add(self, key: str, value: any) -> bool:
        """アイテムの追加（サイズ制限付き）"""
        if len(self._items) >= self._max_size:
            return False
        self._items.append({
            'key': key,
            'value': value,
            'timestamp': datetime.now().isoformat()
        })
        return True
 
    def find(self, key: str) -> Optional[Dict]:
        """キーによる検索"""
        for item in reversed(self._items):
            if item['key'] == key:
                return item
        return None
 
    def remove(self, key: str) -> bool:
        """キーによる削除"""
        for i, item in enumerate(self._items):
            if item['key'] == key:
                self._items.pop(i)
                return True
        return False
 
    def stats(self) -> Dict:
        """統計情報"""
        return {
            'total_items': len(self._items),
            'max_size': self._max_size,
            'usage_percent': len(self._items) / self._max_size * 100,
            'uptime': str(datetime.now() - self._created_at)
        }
 
# テスト
def test_advanced():
    ex = AdvancedExercise(max_size=3)
    assert ex.add("a", 1) == True
    assert ex.add("b", 2) == True
    assert ex.add("c", 3) == True
    assert ex.add("d", 4) == False  # サイズ制限
    assert ex.find("b")['value'] == 2
    assert ex.remove("b") == True
    assert ex.find("b") is None
    stats = ex.stats()
    assert stats['total_items'] == 2
    print("応用テスト全合格!")
 
test_advanced()

# 演習3: パフォーマンス最適化
import time
from functools import lru_cache
 
# 最適化前（O(n^2)）
def slow_search(data: list, target: int) -> int:
    """非効率な検索"""
    for i in range(len(data)):
        for j in range(i + 1, len(data)):
            if data[i] + data[j] == target:
                return (i, j)
    return (-1, -1)
 
# 最適化後（O(n)）
def fast_search(data: list, target: int) -> tuple:
    """ハッシュマップを使った効率的な検索"""
    seen = {}
    for i, num in enumerate(data):
        complement = target - num
        if complement in seen:
            return (seen[complement], i)
        seen[num] = i
    return (-1, -1)
 
# ベンチマーク
def benchmark():
    import random
    data = list(range(5000))
    random.shuffle(data)
    target = data[100] + data[4000]
 
    start = time.time()
    result1 = slow_search(data, target)
    slow_time = time.time() - start
 
    start = time.time()
    result2 = fast_search(data, target)
    fast_time = time.time() - start
 
    print(f"非効率版: {slow_time:.4f}秒")
    print(f"効率版:   {fast_time:.6f}秒")
    print(f"高速化率: {slow_time/fast_time:.0f}倍")
 
benchmark()

# 設計判断の記録テンプレート
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

-- NG: ユーザー入力をサブクエリに直接埋め込む
-- （これは擬似コード: 実際のプログラミング言語で起こる問題）
-- query = "SELECT * FROM products WHERE id IN (" + user_input + ")"
-- user_input = "1); DROP TABLE products; --"
 
-- OK: プリペアドステートメントを使用
-- Python (psycopg2)
-- cursor.execute(
--     "SELECT * FROM products WHERE id IN (SELECT id FROM categories WHERE name = %s)",
--     (user_input,)
-- )
 
-- OK: INリストにはANY + 配列パラメータを使用（PostgreSQL）
-- cursor.execute(
--     "SELECT * FROM products WHERE id = ANY(%s)",
--     ([1, 2, 3],)
-- )