-- 1:1 リレーション
-- 実装パターン: 共有主キー（FK = PK）
CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email    VARCHAR(255) UNIQUE NOT NULL
);
 
CREATE TABLE user_profiles (
    user_id    INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio        TEXT,
    avatar_url VARCHAR(500),
    birthdate  DATE,
    -- user_id が PK かつ FK → 1:1 を強制
    -- ON DELETE CASCADE → ユーザー削除時にプロフィールも削除
    CONSTRAINT chk_birthdate CHECK (birthdate <= CURRENT_DATE)
);
 
-- 1:1 リレーション: 代替パターン（UNIQUE FK）
CREATE TABLE user_settings (
    id       SERIAL PRIMARY KEY,
    user_id  INTEGER UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    -- UNIQUE制約でuser_idの重複を防止 → 1:1を実現
    theme    VARCHAR(20) DEFAULT 'light',
    language VARCHAR(10) DEFAULT 'ja',
    notifications_enabled BOOLEAN DEFAULT TRUE
);
 
-- 1:N リレーション
CREATE TABLE departments (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    location VARCHAR(100),
    -- 部署コードのユニーク制約
    code     VARCHAR(10) UNIQUE NOT NULL
);
 
CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL,
    salary        DECIMAL(10, 2) CHECK (salary >= 0),
    hired_date    DATE NOT NULL DEFAULT CURRENT_DATE,
    email         VARCHAR(255) UNIQUE NOT NULL,
    -- 部分インデックス: アクティブな社員のメール一意性
    is_active     BOOLEAN NOT NULL DEFAULT TRUE
);
 
-- 部分ユニークインデックス（PostgreSQL）
CREATE UNIQUE INDEX idx_active_employees_email
    ON employees (email) WHERE is_active = TRUE;
 
-- M:N リレーション（中間テーブル）
CREATE TABLE projects (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(200) NOT NULL,
    deadline DATE,
    status   VARCHAR(20) NOT NULL DEFAULT 'planning'
             CHECK (status IN ('planning', 'active', 'completed', 'cancelled'))
);
 
CREATE TABLE employee_projects (
    employee_id INTEGER REFERENCES employees(id) ON DELETE CASCADE,
    project_id  INTEGER REFERENCES projects(id) ON DELETE CASCADE,
    role        VARCHAR(50) DEFAULT 'member',
    joined_at   DATE NOT NULL DEFAULT CURRENT_DATE,
    left_at     DATE,
    PRIMARY KEY (employee_id, project_id),
    -- 期間制約: 退出日は参加日以降
    CONSTRAINT chk_dates CHECK (left_at IS NULL OR left_at >= joined_at)
);
 
-- 自己参照リレーション（上司-部下）
CREATE TABLE employees_hierarchy (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees_hierarchy(id) ON DELETE SET NULL,
    level      INTEGER NOT NULL DEFAULT 0
);
 
-- 自己参照のツリー構造を確認するクエリ
WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees_hierarchy WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.depth + 1
    FROM employees_hierarchy e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT REPEAT('  ', depth) || name AS org_chart FROM hierarchy ORDER BY depth;

-- NOT NULL: NULL禁止
-- UNIQUE: 重複禁止
-- CHECK: 値の範囲制約
-- DEFAULT: デフォルト値
-- REFERENCES: 外部キー制約
 
CREATE TABLE orders (
    id              SERIAL PRIMARY KEY,
    customer_id     INTEGER NOT NULL REFERENCES customers(id),
    order_number    VARCHAR(20) UNIQUE NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped',
                                      'delivered', 'cancelled')),
    total_amount    DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
    discount_rate   DECIMAL(3, 2) DEFAULT 0.00
                    CHECK (discount_rate BETWEEN 0 AND 1),
    order_date      DATE NOT NULL DEFAULT CURRENT_DATE,
    shipped_date    DATE,
    delivered_date  DATE,
 
    -- テーブルレベルの制約（複数カラムにまたがる制約）
    CONSTRAINT chk_dates CHECK (
        shipped_date IS NULL OR shipped_date >= order_date
    ),
    CONSTRAINT chk_delivery CHECK (
        delivered_date IS NULL OR delivered_date >= shipped_date
    ),
    -- 状態遷移の制約（簡易版）
    CONSTRAINT chk_status_dates CHECK (
        (status = 'shipped' AND shipped_date IS NOT NULL)
        OR (status != 'shipped')
    )
);
 
-- 排他制約（PostgreSQL: 期間の重複防止）
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- 排他制約に必要
 
CREATE TABLE reservations (
    id        SERIAL PRIMARY KEY,
    room_id   INTEGER NOT NULL,
    guest     VARCHAR(100),
    period    DATERANGE NOT NULL,
    EXCLUDE USING GIST (
        room_id WITH =,
        period WITH &&  -- 同じ部屋の期間重複を禁止
    )
);
 
-- 排他制約の動作確認
INSERT INTO reservations (room_id, guest, period)
VALUES (101, '田中', '[2024-03-01, 2024-03-05)');
 
-- 重複する予約は拒否される
INSERT INTO reservations (room_id, guest, period)
VALUES (101, '鈴木', '[2024-03-03, 2024-03-07)');
-- → ERROR: conflicting key value violates exclusion constraint
 
-- 異なる部屋なら予約可能
INSERT INTO reservations (room_id, guest, period)
VALUES (102, '鈴木', '[2024-03-03, 2024-03-07)');
-- → 成功
 
-- 複合ユニーク制約の活用
CREATE TABLE subscriptions (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    plan_id     INTEGER NOT NULL REFERENCES plans(id),
    started_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ended_at    TIMESTAMPTZ,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    -- 同一ユーザーは同一プランのアクティブサブスクリプションを1つだけ
    CONSTRAINT uq_active_subscription UNIQUE (user_id, plan_id, is_active)
    -- 注意: is_active=FALSE の重複は許容される
);
 
-- 条件付きユニーク（PostgreSQL: 部分ユニークインデックス）
CREATE UNIQUE INDEX idx_active_subscription
    ON subscriptions (user_id, plan_id)
    WHERE is_active = TRUE;
-- → アクティブなサブスクリプションのみユニーク制約

-- ON DELETE / ON UPDATE のオプション
CREATE TABLE order_items (
    id         SERIAL PRIMARY KEY,
    order_id   INTEGER NOT NULL
               REFERENCES orders(id)
               ON DELETE CASCADE      -- 親削除時: 子も削除
               ON UPDATE CASCADE,     -- 親更新時: 子も更新
    product_id INTEGER NOT NULL
               REFERENCES products(id)
               ON DELETE RESTRICT,    -- 親削除時: エラー（子あれば削除拒否）
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL
);
 
-- 参照アクション一覧:
-- CASCADE    : 親に追従（削除/更新）
-- RESTRICT   : 子がある場合は親の操作を拒否（即時チェック）
-- NO ACTION  : 子がある場合は親の操作を拒否（遅延チェック可）※デフォルト
-- SET NULL   : 子のFK列をNULLに設定
-- SET DEFAULT: 子のFK列をDEFAULT値に設定

-- 制約の遅延チェック: トランザクション終了時に評価
CREATE TABLE categories (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id)
              DEFERRABLE INITIALLY DEFERRED
);
 
-- 循環参照を避けつつ、相互参照を可能にする
BEGIN;
INSERT INTO categories (id, name, parent_id) VALUES (1, 'Root', NULL);
INSERT INTO categories (id, name, parent_id) VALUES (2, 'Child', 1);
-- parent_id=1は既に存在するので問題ない
 
-- 遅延制約なら、以下も可能:
-- INSERT INTO categories VALUES (10, 'A', 20);  -- 20はまだない
-- INSERT INTO categories VALUES (20, 'B', 10);  -- 10は上で挿入済み
-- COMMIT; -- ここで初めてFK制約がチェックされる
COMMIT;

-- レンジパーティション（日付ベース）
CREATE TABLE access_logs (
    id         BIGSERIAL,
    user_id    INTEGER,
    action     VARCHAR(50),
    ip_address INET,
    created_at TIMESTAMP NOT NULL,
    -- パーティションテーブルの場合、PKにパーティションキーを含める必要がある
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
 
-- 月別パーティション作成
CREATE TABLE access_logs_2024_01
    PARTITION OF access_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
 
CREATE TABLE access_logs_2024_02
    PARTITION OF access_logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
 
-- デフォルトパーティション（範囲外のデータを受け入れ）
CREATE TABLE access_logs_default
    PARTITION OF access_logs DEFAULT;
 
-- パーティションの自動作成スクリプト（PostgreSQL: 関数で自動化）
CREATE OR REPLACE FUNCTION create_monthly_partition(
    table_name TEXT,
    year INTEGER,
    month INTEGER
) RETURNS VOID AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    partition_name := format('%s_%s_%s', table_name, year,
                            LPAD(month::TEXT, 2, '0'));
    start_date := make_date(year, month, 1);
    end_date := start_date + INTERVAL '1 month';
 
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, table_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;
 
-- 使用例: 2024年の12ヶ月分を一括作成
SELECT create_monthly_partition('access_logs', 2024, m)
FROM generate_series(1, 12) AS m;
 
-- リストパーティション（地域ベース）
CREATE TABLE sales (
    id     SERIAL,
    region VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2),
    date   DATE,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);
 
CREATE TABLE sales_japan PARTITION OF sales
    FOR VALUES IN ('tokyo', 'osaka', 'nagoya');
CREATE TABLE sales_asia PARTITION OF sales
    FOR VALUES IN ('seoul', 'taipei', 'singapore');
CREATE TABLE sales_default PARTITION OF sales DEFAULT;
 
-- ハッシュパーティション（均等分散）
CREATE TABLE events (
    id      BIGSERIAL,
    user_id INTEGER NOT NULL,
    data    JSONB,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);
 
CREATE TABLE events_0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);
 
-- パーティションプルーニングの確認
EXPLAIN ANALYZE
SELECT * FROM access_logs WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';
-- → access_logs_2024_03 のみがスキャンされる

-- 古いパーティションの削除（DROP: 瞬時に実行）
DROP TABLE access_logs_2023_01;
 
-- パーティションのデタッチ（テーブルは残す）
ALTER TABLE access_logs DETACH PARTITION access_logs_2023_01;
 
-- デタッチしたテーブルをアーカイブテーブルに名前変更
ALTER TABLE access_logs_2023_01 RENAME TO access_logs_archive_2023_01;
 
-- パーティションの統計情報確認
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE tablename LIKE 'access_logs_%'
ORDER BY tablename;

-- 1. 自然キー: ビジネス上の意味を持つキー
CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,   -- ISO 3166-1 alpha-2
    name VARCHAR(100) NOT NULL
);
 
-- 2. サロゲートキー: 人工的な連番ID
CREATE TABLE products (
    id   SERIAL PRIMARY KEY,    -- サロゲートキー
    sku  VARCHAR(20) UNIQUE NOT NULL,  -- 自然キー（ビジネス識別子）
    name VARCHAR(200) NOT NULL
);
 
-- SERIAL vs IDENTITY の違い（PostgreSQL 10+）
CREATE TABLE products_v2 (
    -- IDENTITY列（SQL標準、推奨）
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);
-- GENERATED ALWAYS: 手動でのID指定を拒否（安全）
-- GENERATED BY DEFAULT: 手動指定も許可
 
-- 3. UUID: 分散システム向け
CREATE TABLE events (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type VARCHAR(50) NOT NULL,
    payload    JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- UUID v7: 時系列ソート可能（PostgreSQL 17+またはアプリ生成）
-- UUIDv7のフォーマット:
-- ┌─────────────────────┬──────┬───────────────────┐
-- │  48bit timestamp    │ ver  │  74bit random      │
-- └─────────────────────┴──────┴───────────────────┘
-- → タイムスタンプベースでソート可能、B-Treeに有利
 
-- 4. ULID: 時系列ソート可能なUUID代替
-- アプリケーション側で生成するのが一般的
-- PostgreSQLでは拡張を使用するか、テキストで格納
CREATE TABLE activities (
    id         CHAR(26) PRIMARY KEY,  -- ULID（Base32エンコード、26文字）
    action     VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- 5. 複合キー: 中間テーブルで使用
CREATE TABLE user_roles (
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
    granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, role_id)
);

-- 推奨されるテーブル構造
CREATE TABLE articles (
    -- 主キー
    id          BIGSERIAL PRIMARY KEY,
 
    -- ビジネスデータ
    title       VARCHAR(500) NOT NULL,
    slug        VARCHAR(500) UNIQUE NOT NULL,
    body        TEXT NOT NULL,
    excerpt     VARCHAR(1000),
    status      VARCHAR(20) NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft', 'published', 'archived')),
 
    -- リレーション
    author_id   INTEGER NOT NULL REFERENCES users(id),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
 
    -- メタデータ
    tags        TEXT[] DEFAULT '{}',
    metadata    JSONB DEFAULT '{}',
 
    -- 監査列（全テーブル共通）
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ,  -- 論理削除用
 
    -- インデックス
    CONSTRAINT articles_slug_format CHECK (slug ~ '^[a-z0-9-]+$')
);
 
-- 更新日時の自動更新トリガー
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_articles_updated_at
    BEFORE UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();
 
-- 論理削除のソフトデリートパターン
-- 削除済みレコードを除外するビュー
CREATE VIEW active_articles AS
SELECT * FROM articles WHERE deleted_at IS NULL;
 
-- 論理削除関数
CREATE OR REPLACE FUNCTION soft_delete_article(article_id BIGINT)
RETURNS VOID AS $$
BEGIN
    UPDATE articles SET deleted_at = NOW() WHERE id = article_id;
END;
$$ LANGUAGE plpgsql;
 
-- インデックス設計
CREATE INDEX idx_articles_author ON articles (author_id);
CREATE INDEX idx_articles_category ON articles (category_id);
CREATE INDEX idx_articles_status ON articles (status) WHERE deleted_at IS NULL;
CREATE INDEX idx_articles_created ON articles (created_at DESC);
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
CREATE INDEX idx_articles_metadata ON articles USING GIN (metadata);

-- 監査テーブル（変更履歴の記録）
CREATE TABLE audit_log (
    id          BIGSERIAL PRIMARY KEY,
    table_name  VARCHAR(100) NOT NULL,
    record_id   TEXT NOT NULL,
    action      VARCHAR(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_data    JSONB,
    new_data    JSONB,
    changed_by  INTEGER REFERENCES users(id),
    changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address  INET
) PARTITION BY RANGE (changed_at);
 
-- 汎用監査トリガー
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, action, new_data, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'INSERT', row_to_json(NEW)::JSONB,
                current_setting('app.current_user_id', TRUE)::INTEGER);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'UPDATE',
                row_to_json(OLD)::JSONB, row_to_json(NEW)::JSONB,
                current_setting('app.current_user_id', TRUE)::INTEGER);
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, changed_by)
        VALUES (TG_TABLE_NAME, OLD.id::TEXT, 'DELETE', row_to_json(OLD)::JSONB,
                current_setting('app.current_user_id', TRUE)::INTEGER);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;
 
-- articlesテーブルに監査を適用
CREATE TRIGGER trg_articles_audit
    AFTER INSERT OR UPDATE OR DELETE ON articles
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

-- NG: 型に応じてFK先が変わる（外部キー制約が使えない）
CREATE TABLE comments (
    id              SERIAL PRIMARY KEY,
    commentable_type VARCHAR(50),   -- 'article' or 'product' or 'video'
    commentable_id   INTEGER,       -- FK先が不明
    body            TEXT
);
-- → commentable_id に REFERENCES を付けられない
-- → 参照整合性がアプリケーション層に依存
 
-- OK: 専用の中間テーブルを使う
CREATE TABLE comments (
    id   SERIAL PRIMARY KEY,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE article_comments (
    comment_id INTEGER PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
    article_id INTEGER NOT NULL REFERENCES articles(id) ON DELETE CASCADE
);
CREATE TABLE product_comments (
    comment_id INTEGER PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE
);
 
-- OK: 排他的リレーション（PostgreSQL CHECK制約）
CREATE TABLE comments_v2 (
    id          SERIAL PRIMARY KEY,
    body        TEXT NOT NULL,
    article_id  INTEGER REFERENCES articles(id) ON DELETE CASCADE,
    product_id  INTEGER REFERENCES products(id) ON DELETE CASCADE,
    video_id    INTEGER REFERENCES videos(id) ON DELETE CASCADE,
    -- 正確に1つだけがNOT NULL
    CONSTRAINT chk_exclusive CHECK (
        (article_id IS NOT NULL)::INTEGER +
        (product_id IS NOT NULL)::INTEGER +
        (video_id IS NOT NULL)::INTEGER = 1
    )
);

-- NG: 制約なしのテーブル
CREATE TABLE users (
    id    SERIAL,
    email TEXT,
    age   INTEGER
);
-- → 重複メール、NULL、負の年齢、不正な形式が全て許容される
 
-- OK: 適切な制約を設定
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
          CHECK (email ~* '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$'),
    age   INTEGER CHECK (age BETWEEN 0 AND 200)
);

-- NG: FK列にインデックスがない
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id)
    -- customer_id にインデックスがない
    -- → JOINやDELETEが遅い
);
 
-- OK: FK列にインデックスを作成
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id)
);
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- → customers.idのDELETE時にorders側のインデックスで高速確認
-- → JOINもインデックス使用で高速化

-- 循環参照の例: 部署に「現在のリーダー」を設定したい
-- employees → departments (所属) と departments → employees (リーダー)
CREATE TABLE departments (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    leader_id INTEGER  -- 後でFKを追加
);
 
CREATE TABLE employees (
    id      SERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    dept_id INTEGER REFERENCES departments(id)
);
 
-- 循環FK
ALTER TABLE departments ADD CONSTRAINT fk_leader
    FOREIGN KEY (leader_id) REFERENCES employees(id)
    DEFERRABLE INITIALLY DEFERRED;
 
-- 挿入時は遅延制約で対応
BEGIN;
INSERT INTO departments (id, name) VALUES (1, '開発部');
INSERT INTO employees (id, name, dept_id) VALUES (1, '田中', 1);
UPDATE departments SET leader_id = 1 WHERE id = 1;
COMMIT;

-- 大量データロード時のFK制約を一時的に無効化
-- PostgreSQL:
ALTER TABLE order_items DISABLE TRIGGER ALL;
 
-- データロード
COPY order_items FROM '/data/order_items.csv' CSV HEADER;
 
-- 制約を再有効化
ALTER TABLE order_items ENABLE TRIGGER ALL;
 
-- データ整合性の手動検証
SELECT oi.id FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
-- → 0行であることを確認

-- 方式1: 行レベル分離（1テーブルに全テナント）
CREATE TABLE tenant_users (
    id        SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    name      VARCHAR(100) NOT NULL,
    email     VARCHAR(255) NOT NULL,
    UNIQUE (tenant_id, email)  -- テナント内でのメール一意性
);
 
-- RLSで自動フィルタリング
ALTER TABLE tenant_users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tenant_users
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);
 
-- 方式2: スキーマ分離（テナントごとにスキーマ）
CREATE SCHEMA tenant_001;
CREATE TABLE tenant_001.users (...);
-- メリット: 完全な分離、テナント削除が容易
-- デメリット: テナント数が多いとスキーマ管理が困難

# 演習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

-- Row Level Security（RLS）の活用
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
-- 一般ユーザー: 自分の注文のみ閲覧可能
CREATE POLICY orders_user_policy ON orders
    FOR SELECT
    USING (customer_id = current_setting('app.current_user_id')::INTEGER);
 
-- 管理者: 全注文閲覧可能
CREATE POLICY orders_admin_policy ON orders
    FOR ALL
    USING (current_setting('app.current_role') = 'admin');
 
-- テーブル権限の最小化
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
-- DELETE権限は付与しない → 論理削除のみ
REVOKE DELETE ON orders FROM app_user;