-- クエリA 最適化
-- 問題1: 相関サブクエリ → JOIN に変換
-- 問題2: ILIKE はインデックスが効かない → pg_trgm or 全文検索
-- 問題3: OFFSET 10000 → カーソルベースに変更

-- pg_trgm 拡張の有効化
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- 最適化後
SELECT p.*, c.name AS category_name,
       rs.avg_rating, rs.review_count
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
  SELECT product_id,
         AVG(rating) AS avg_rating,
         COUNT(*) AS review_count
  FROM reviews
  GROUP BY product_id
) rs ON rs.product_id = p.id
WHERE p.status = 'active'
  AND p.price BETWEEN 1000 AND 5000
  AND p.name ILIKE '%ワイヤレス%'
  AND (p.created_at, p.id) < ($last_created_at, $last_id)  -- カーソルベース
ORDER BY p.created_at DESC, p.id DESC
LIMIT 20;

-- さらに: マテリアライズドビューで review_stats を事前集計
CREATE MATERIALIZED VIEW product_review_stats AS
SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews GROUP BY product_id;
CREATE UNIQUE INDEX ON product_review_stats (product_id);

-- クエリB 最適化
-- 問題: 90日分の JOIN が重い → マテリアライズドビューで日次集計

CREATE MATERIALIZED VIEW daily_order_stats AS
SELECT
  DATE_TRUNC('day', o.created_at) AS day,
  o.status,
  COUNT(*) AS order_count,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY DATE_TRUNC('day', o.created_at), o.status;

-- 最適化後のクエリ（ミリ秒単位で完了）
SELECT day, SUM(order_count) AS order_count, SUM(revenue) AS revenue
FROM daily_order_stats
WHERE day >= NOW() - INTERVAL '90 days'
  AND status IN ('completed', 'shipped')
GROUP BY day
ORDER BY day DESC;

-- クエリC 最適化
-- 問題: LEFT JOIN + GROUP BY + HAVING が重い
-- 対策: 注文統計を事前集計、HAVING を WHERE に変換

-- インデックス追加
CREATE INDEX idx_orders_user_status ON orders (user_id, status)
  WHERE status != 'cancelled';

-- 最適化後
WITH user_stats AS (
  SELECT
    user_id,
    MAX(created_at) AS last_order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY user_id
  HAVING COUNT(*) > 0
)
SELECT u.id, u.name, u.email,
       us.last_order_date, us.order_count, us.lifetime_value
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE u.created_at >= NOW() - INTERVAL '1 year'
ORDER BY us.lifetime_value DESC
LIMIT 100;

-- ESR ルール（Equality → Sort → Range）を適用

-- Q1 用: user_id(E) → created_at(S) + status フィルタ
-- 最頻出クエリなので最優先
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC)
  WHERE status != 'cancelled';
-- 部分インデックスでサイズ削減（cancelled は全体の5%と想定）

-- Q2 用: order_items の結合効率化
CREATE INDEX idx_order_items_order_id ON order_items (order_id)
  INCLUDE (product_id, quantity, unit_price);
-- カバリングインデックスで order_items テーブルアクセスを回避

-- Q2 用: orders の日付範囲スキャン
-- orders は時系列で物理順序あり → BRIN が効率的
CREATE INDEX idx_orders_created_brin ON orders USING brin (created_at)
  WITH (pages_per_range = 64);

-- Q3 用: 直近24時間のステータス集計
-- Q3 は1分キャッシュなので専用インデックスの優先度は低い
-- idx_orders_created_brin で十分（created_at での絞り込み後に集計）

-- Q4 用: category_id(E) → rating(S) → price(R)
CREATE INDEX idx_products_cat_rating_price ON products (category_id, rating DESC, price)
  WHERE status = 'active';
-- 部分インデックス + ESR ルール

-- Q5 用: pending のみの部分インデックス
CREATE INDEX idx_orders_pending_queue ON orders (created_at ASC)
  WHERE status = 'pending';
-- pending は全体の0.1%程度、非常に小さなインデックス

-- 基本的な外部キーインデックス（JOIN用）
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

-- 合計: 7インデックス（8個以内）
-- 不要なインデックスを作らない判断も重要

// 1. 最適コネクション数の計算
const config = {
  primaryCpuCores: 16,
  primaryMaxConn: 200,
  replicaMaxConn: 200,
  replicaCount: 2,
  normalQps: 15000,
  peakQps: 30000,
  readRatio: 0.80,
  writeRatio: 0.20,
  avgReadMs: 3,
  avgWriteMs: 10,
  podCount: 20,
  podMaxCount: 30,
};

// Primary: CPU cores * 2 + 1 = 33
// 管理用に5コネクション確保 → 有効 195
// PgBouncer プールサイズ: min(33, 195) = 33

// Replica: 同様に 33 接続が最適
// 2台合計で 66 リードコネクション

// 2. リトルの法則で必要な同時コネクション数
// Write: 15000 * 0.20 * 0.010 = 30 同時コネクション
// Read:  15000 * 0.80 * 0.003 = 36 同時コネクション
// Peak Write: 30000 * 0.20 * 0.010 = 60
// Peak Read:  30000 * 0.80 * 0.003 = 72

// PgBouncer 設計
const pgbouncerConfig = `
[databases]
mydb_primary = host=pg-primary port=5432 dbname=mydb
mydb_replica1 = host=pg-replica1 port=5432 dbname=mydb
mydb_replica2 = host=pg-replica2 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction

; Primary: 書き込み用
; 通常30、ピーク60必要 → pool_size=33 でカバー
default_pool_size = 33
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3

max_client_conn = 3000
; Pod 30台 × 各20コネクション = 600 < 3000

server_idle_timeout = 300
client_idle_timeout = 60
query_timeout = 30

; Replica 用は別 PgBouncer インスタンス
; 各 Replica に pool_size=33
`;

// 3. Pod ごとのプールサイズ
// Primary 接続: PgBouncer が集約するので Pod 側は大きめに設定可能
// Pod 20台の場合: 各 Pod → Primary PgBouncer: 10接続
// Pod 30台の場合: 各 Pod → Primary PgBouncer: 7接続
// → max=10, min=3 で設定

// Replica 接続: 2台にラウンドロビン
// 各 Replica PgBouncer に Pod あたり 10接続
// → max=10, min=3 per replica

const podPoolConfig = {
  primary: { max: 10, min: 3, idleTimeoutMs: 30000 },
  replica: { max: 10, min: 3, idleTimeoutMs: 30000 },
  // 読み取りは2台のReplicaにラウンドロビン
};

// 4. ピーク時の安全マージン
// Primary: 必要60 / 利用可能33+10(reserve) = 60/43 → 余裕あり
// Replica: 必要72 / 利用可能66(33×2)+20(reserve) = 72/86 → 余裕あり
// Pod スケール 30台時: 30 × 10 = 300 論理接続 → PgBouncer で 33 物理に集約
// → PgBouncer がボトルネックになる場合は PgBouncer を複数台に

-- 1. レンジパーティショニング（月単位）
-- 理由:
--   - 日時範囲クエリが主要 → パーティションプルーニングが効く
--   - 月次の古データ削除 → DROP PARTITION で瞬時（DELETE + VACUUM 不要）
--   - 月3000万行/パーティション → 管理しやすいサイズ

-- 2. DDL
CREATE TABLE audit_logs (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    resource    VARCHAR(200),
    payload     JSONB,
    ip_address  INET,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id, created_at)  -- パーティションキーを含む
) PARTITION BY RANGE (created_at);

-- 24ヶ月分 + 3ヶ月先のパーティションを作成
-- （例: 2024-01 〜 2026-03）
CREATE TABLE audit_logs_2025_01 PARTITION OF audit_logs
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE audit_logs_2025_02 PARTITION OF audit_logs
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ... 以降同様

CREATE TABLE audit_logs_default PARTITION OF audit_logs DEFAULT;

-- パーティションごとのインデックス
CREATE INDEX ON audit_logs (user_id, created_at DESC);
CREATE INDEX ON audit_logs (action_type, created_at DESC);

-- 3. 古いデータの削除（DROP PARTITION）
-- DELETE の代わりに DETACH → DROP で瞬時に完了
ALTER TABLE audit_logs DETACH PARTITION audit_logs_2023_01;
DROP TABLE audit_logs_2023_01;
-- ロック競合なし、VACUUM 不要、ストレージ即時解放

// 4. パーティション管理の自動化
class AuditLogPartitionManager {
  constructor(
    private db: Pool,
    private retentionMonths: number = 24,
    private futureMonths: number = 3
  ) {}

  // 月次 cron で実行
  async maintain(): Promise<{
    created: string[];
    dropped: string[];
  }> {
    const created = await this.createFuturePartitions();
    const dropped = await this.dropExpiredPartitions();

    // Slack 通知
    console.log(`Partitions created: ${created.join(', ')}`);
    console.log(`Partitions dropped: ${dropped.join(', ')}`);

    return { created, dropped };
  }

  private async createFuturePartitions(): Promise<string[]> {
    const created: string[] = [];
    const now = new Date();

    for (let i = 0; i <= this.futureMonths; i++) {
      const start = new Date(now.getFullYear(), now.getMonth() + i, 1);
      const end = new Date(now.getFullYear(), now.getMonth() + i + 1, 1);
      const name = `audit_logs_${this.fmt(start)}`;

      try {
        await this.db.query(`
          CREATE TABLE IF NOT EXISTS ${name}
          PARTITION OF audit_logs
          FOR VALUES FROM ('${this.toDateStr(start)}')
                      TO ('${this.toDateStr(end)}')
        `);
        created.push(name);
      } catch (e: any) {
        if (!e.message.includes('already exists')) throw e;
      }
    }
    return created;
  }

  private async dropExpiredPartitions(): Promise<string[]> {
    const dropped: string[] = [];
    const cutoff = new Date();
    cutoff.setMonth(cutoff.getMonth() - this.retentionMonths);

    const result = await this.db.query(`
      SELECT inhrelid::regclass::text AS name
      FROM pg_inherits
      WHERE inhparent = 'audit_logs'::regclass
    `);

    for (const row of result.rows) {
      const match = row.name.match(/audit_logs_(\d{4})_(\d{2})/);
      if (!match) continue;
      const partDate = new Date(parseInt(match[1]), parseInt(match[2]) - 1, 1);

      if (partDate < cutoff) {
        await this.db.query(`ALTER TABLE audit_logs DETACH PARTITION ${row.name}`);
        await this.db.query(`DROP TABLE ${row.name}`);
        dropped.push(row.name);
      }
    }
    return dropped;
  }

  private fmt(d: Date): string {
    return `${d.getFullYear()}_${String(d.getMonth()+1).padStart(2,'0')}`;
  }
  private toDateStr(d: Date): string {
    return d.toISOString().split('T')[0];
  }
}