前情提要

經過 Day 27 的審計日誌系統建置,我們已經可以完整追蹤所有操作記錄。今天我們要實作 資料分析引擎與報表生成系統,這是 SaaS 產品為客戶創造價值的核心功能。我們需要從海量資料中提取洞察、生成各種格式的報表(Excel, PDF)、並確保查詢效能不影響主系統運作。我們將使用 PostgreSQL 進階功能、Redis 快取層、以及 BullMQ 背景任務處理。

資料分析架構設計

/**
 * 資料分析引擎完整架構
 *
 * ┌──────────────────────────────────────────────┐
 * │      Analytics Engine Architecture          │
 * └──────────────────────────────────────────────┘
 *
 * 1. OLTP vs OLAP 分離
 * ┌─────────────────────────────────────┐
 * │  OLTP (Transaction)                 │
 * │  ├─ 主資料庫                         │
 * │  ├─ 寫入優化                         │
 * │  └─ 即時一致性                       │
 * │                                      │
 * │  OLAP (Analytics)                   │
 * │  ├─ Read Replica                    │
 * │  ├─ Materialized Views              │
 * │  ├─ 查詢優化                         │
 * │  └─ 可容忍延遲                       │
 * └─────────────────────────────────────┘
 *
 * 2. 分析查詢流程
 * ┌─────────────────────────────────────┐
 * │  Request                            │
 * │  ↓                                  │
 * │  Redis Cache (檢查快取)             │
 * │  ↓ (Cache Miss)                     │
 * │  Query Builder (構建查詢)           │
 * │  ↓                                  │
 * │  PostgreSQL Read Replica            │
 * │  ├─ Window Functions                │
 * │  ├─ CTEs                            │
 * │  ├─ Aggregations                    │
 * │  └─ Indexes                         │
 * │  ↓                                  │
 * │  Redis Cache (儲存結果)             │
 * │  ↓                                  │
 * │  Response                           │
 * └─────────────────────────────────────┘
 *
 * 3. 報表生成流程
 * ┌─────────────────────────────────────┐
 * │  用戶請求報表                        │
 * │  ↓                                  │
 * │  BullMQ Job (背景任務)              │
 * │  ├─ 查詢資料                         │
 * │  ├─ 聚合計算                         │
 * │  ├─ 格式轉換                         │
 * │  │  ├─ Excel (exceljs)             │
 * │  │  ├─ PDF (puppeteer)             │
 * │  │  └─ CSV                          │
 * │  ├─ 上傳 S3                          │
 * │  └─ 通知用戶                         │
 * │  ↓                                  │
 * │  下載連結 (Pre-signed URL)          │
 * └─────────────────────────────────────┘
 *
 * 4. 效能優化策略
 * ┌─────────────────────────────────────┐
 * │  ✅ Materialized Views (預計算)     │
 * │  ✅ Partial Indexes (條件索引)      │
 * │  ✅ Redis Cache (多層快取)          │
 * │  ✅ Connection Pooling              │
 * │  ✅ Query Pagination                │
 * │  ✅ Background Jobs                 │
 * │  ✅ Read Replicas                   │
 * └─────────────────────────────────────┘
 */

PostgreSQL 分析表設計

-- migrations/002_create_analytics_tables.sql

/**
 * OTP 統計聚合表 (Materialized View)
 *
 * 用途: 預計算每日 OTP 統計,避免即時查詢
 * 更新頻率: 每小時刷新
 */
CREATE MATERIALIZED VIEW otp_daily_stats AS
SELECT
  DATE_TRUNC('day', created_at) as date,
  tenant_id,
  provider,
  COUNT(*) as total_requests,
  COUNT(*) FILTER (WHERE status = 'sent') as sent_count,
  COUNT(*) FILTER (WHERE status = 'verified') as verified_count,
  COUNT(*) FILTER (WHERE status = 'failed') as failed_count,
  AVG(CASE WHEN verified_at IS NOT NULL THEN
    EXTRACT(EPOCH FROM (verified_at - created_at))
  END) as avg_verification_time,
  SUM(cost) as total_cost
FROM otp_requests
WHERE created_at >= NOW() - INTERVAL '90 days' -- 保留 90 天
GROUP BY DATE_TRUNC('day', created_at), tenant_id, provider
ORDER BY date DESC;

-- 建立索引
CREATE UNIQUE INDEX idx_otp_daily_stats_unique
ON otp_daily_stats (date, tenant_id, provider);

CREATE INDEX idx_otp_daily_stats_tenant_date
ON otp_daily_stats (tenant_id, date DESC);

-- 自動刷新函數
CREATE OR REPLACE FUNCTION refresh_otp_daily_stats()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY otp_daily_stats;
END;
$$ LANGUAGE plpgsql;

-- 定期刷新 (透過 pg_cron)
-- SELECT cron.schedule('refresh-otp-stats', '0 * * * *', 'SELECT refresh_otp_daily_stats()');

/**
 * 用戶活動分析表
 */
CREATE TABLE user_activity_summary (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  tenant_id UUID NOT NULL,
  date DATE NOT NULL,
  login_count INT DEFAULT 0,
  api_calls INT DEFAULT 0,
  otp_sent INT DEFAULT 0,
  last_active_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(user_id, date)
);

CREATE INDEX idx_user_activity_tenant_date
ON user_activity_summary (tenant_id, date DESC);

/**
 * API 使用統計表
 */
CREATE TABLE api_usage_stats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  api_key_id UUID,
  endpoint TEXT NOT NULL,
  method TEXT NOT NULL,
  date DATE NOT NULL,
  hour INT NOT NULL CHECK (hour >= 0 AND hour < 24),
  request_count INT DEFAULT 0,
  success_count INT DEFAULT 0,
  error_count INT DEFAULT 0,
  avg_response_time NUMERIC(10, 2),
  p95_response_time NUMERIC(10, 2),
  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(tenant_id, endpoint, method, date, hour)
);

CREATE INDEX idx_api_usage_tenant_date
ON api_usage_stats (tenant_id, date DESC, hour DESC);

Analytics Query Builder

// packages/kyo-core/src/analytics/query-builder.ts
import { Pool } from 'pg';

export interface TimeSeriesQuery {
  tenantId: string;
  metric: string;
  startDate: Date;
  endDate: Date;
  granularity: 'hour' | 'day' | 'week' | 'month';
  filters?: Record<string, any>;
}

export interface AggregationResult {
  timestamp: Date;
  value: number;
  metadata?: Record<string, any>;
}

/**
 * Analytics Query Builder
 *
 * 功能:
 * - 時間序列查詢
 * - 聚合計算
 * - 同比/環比分析
 * - 漏斗分析
 */
export class AnalyticsQueryBuilder {
  constructor(private pool: Pool) {}

  /**
   * 時間序列查詢
   */
  async getTimeSeries(query: TimeSeriesQuery): Promise<AggregationResult[]> {
    const { tenantId, metric, startDate, endDate, granularity, filters } = query;

    // 根據指標類型選擇資料源
    const tableMap: Record<string, string> = {
      'otp.sent': 'otp_daily_stats',
      'otp.verified': 'otp_daily_stats',
      'otp.failed': 'otp_daily_stats',
      'api.requests': 'api_usage_stats',
      'users.active': 'user_activity_summary',
    };

    const table = tableMap[metric];
    if (!table) {
      throw new Error(`Unknown metric: ${metric}`);
    }

    // 構建 SQL
    let sql: string;
    let params: any[];

    if (metric.startsWith('otp.')) {
      sql = this.buildOTPTimeSeriesQuery(metric, granularity);
      params = [tenantId, startDate, endDate, filters?.provider];
    } else if (metric.startsWith('api.')) {
      sql = this.buildAPITimeSeriesQuery(metric, granularity);
      params = [tenantId, startDate, endDate, filters?.endpoint];
    } else {
      sql = this.buildUserTimeSeriesQuery(metric, granularity);
      params = [tenantId, startDate, endDate];
    }

    const result = await this.pool.query(sql, params.filter(Boolean));

    return result.rows.map((row) => ({
      timestamp: row.timestamp,
      value: parseFloat(row.value),
      metadata: row.metadata,
    }));
  }

  /**
   * OTP 時間序列查詢
   */
  private buildOTPTimeSeriesQuery(metric: string, granularity: string): string {
    const metricMap: Record<string, string> = {
      'otp.sent': 'sent_count',
      'otp.verified': 'verified_count',
      'otp.failed': 'failed_count',
    };

    const column = metricMap[metric];
    const dateFunc = this.getDateTruncFunction(granularity);

    return `
      SELECT
        ${dateFunc}(date) as timestamp,
        SUM(${column}) as value,
        json_build_object('provider', provider) as metadata
      FROM otp_daily_stats
      WHERE tenant_id = $1
        AND date >= $2
        AND date <= $3
        ${metric === 'otp.sent' ? 'AND provider = $4' : ''}
      GROUP BY ${dateFunc}(date), provider
      ORDER BY timestamp
    `;
  }

  /**
   * API 時間序列查詢
   */
  private buildAPITimeSeriesQuery(metric: string, granularity: string): string {
    const dateFunc = this.getDateTruncFunction(granularity);

    return `
      SELECT
        ${dateFunc}(date) as timestamp,
        SUM(request_count) as value,
        json_build_object(
          'endpoint', endpoint,
          'success_rate', ROUND(SUM(success_count)::numeric / NULLIF(SUM(request_count), 0) * 100, 2)
        ) as metadata
      FROM api_usage_stats
      WHERE tenant_id = $1
        AND date >= $2
        AND date <= $3
        ${metric === 'api.requests' ? 'AND endpoint = $4' : ''}
      GROUP BY ${dateFunc}(date), endpoint
      ORDER BY timestamp
    `;
  }

  /**
   * 用戶時間序列查詢
   */
  private buildUserTimeSeriesQuery(metric: string, granularity: string): string {
    const dateFunc = this.getDateTruncFunction(granularity);

    return `
      SELECT
        ${dateFunc}(date) as timestamp,
        COUNT(DISTINCT user_id) as value
      FROM user_activity_summary
      WHERE tenant_id = $1
        AND date >= $2
        AND date <= $3
        AND login_count > 0
      GROUP BY ${dateFunc}(date)
      ORDER BY timestamp
    `;
  }

  /**
   * 同比分析 (Year-over-Year)
   */
  async getYoYComparison(
    tenantId: string,
    metric: string,
    currentPeriodStart: Date,
    currentPeriodEnd: Date
  ): Promise<{
    current: number;
    previous: number;
    change: number;
    changePercentage: number;
  }> {
    const periodDays = Math.floor(
      (currentPeriodEnd.getTime() - currentPeriodStart.getTime()) / (1000 * 60 * 60 * 24)
    );

    const previousPeriodStart = new Date(currentPeriodStart);
    previousPeriodStart.setFullYear(previousPeriodStart.getFullYear() - 1);

    const previousPeriodEnd = new Date(previousPeriodStart);
    previousPeriodEnd.setDate(previousPeriodEnd.getDate() + periodDays);

    const [currentData, previousData] = await Promise.all([
      this.getTimeSeries({
        tenantId,
        metric,
        startDate: currentPeriodStart,
        endDate: currentPeriodEnd,
        granularity: 'day',
      }),
      this.getTimeSeries({
        tenantId,
        metric,
        startDate: previousPeriodStart,
        endDate: previousPeriodEnd,
        granularity: 'day',
      }),
    ]);

    const current = currentData.reduce((sum, item) => sum + item.value, 0);
    const previous = previousData.reduce((sum, item) => sum + item.value, 0);
    const change = current - previous;
    const changePercentage = previous === 0 ? 0 : (change / previous) * 100;

    return {
      current,
      previous,
      change,
      changePercentage: parseFloat(changePercentage.toFixed(2)),
    };
  }

  /**
   * 漏斗分析
   */
  async getFunnelAnalysis(
    tenantId: string,
    startDate: Date,
    endDate: Date
  ): Promise<Array<{ step: string; count: number; percentage: number }>> {
    const sql = `
      WITH funnel_data AS (
        SELECT
          COUNT(*) FILTER (WHERE status = 'created') as step1_created,
          COUNT(*) FILTER (WHERE status = 'sent') as step2_sent,
          COUNT(*) FILTER (WHERE status = 'verified') as step3_verified
        FROM otp_requests
        WHERE tenant_id = $1
          AND created_at >= $2
          AND created_at <= $3
      )
      SELECT
        json_build_array(
          json_build_object('step', 'Created', 'count', step1_created, 'percentage', 100.0),
          json_build_object('step', 'Sent', 'count', step2_sent, 'percentage', ROUND(step2_sent::numeric / NULLIF(step1_created, 0) * 100, 2)),
          json_build_object('step', 'Verified', 'count', step3_verified, 'percentage', ROUND(step3_verified::numeric / NULLIF(step1_created, 0) * 100, 2))
        ) as funnel
      FROM funnel_data
    `;

    const result = await this.pool.query(sql, [tenantId, startDate, endDate]);
    return result.rows[0]?.funnel || [];
  }

  /**
   * 取得 date_trunc 函數
   */
  private getDateTruncFunction(granularity: string): string {
    const funcMap: Record<string, string> = {
      hour: "date_trunc('hour', ",
      day: "date_trunc('day', ",
      week: "date_trunc('week', ",
      month: "date_trunc('month', ",
    };
    return funcMap[granularity] || funcMap.day;
  }
}

Redis 快取層

// packages/kyo-core/src/analytics/cache.ts
import Redis from 'ioredis';

/**
 * Analytics Cache Layer
 *
 * 快取策略:
 * - 時間序列資料: 5 分鐘
 * - 聚合資料: 15 分鐘
 * - 報表: 1 小時
 */
export class AnalyticsCache {
  constructor(private redis: Redis) {}

  /**
   * 取得快取的查詢結果
   */
  async get<T>(key: string): Promise<T | null> {
    const cached = await this.redis.get(key);
    if (!cached) return null;

    try {
      return JSON.parse(cached) as T;
    } catch (error) {
      console.error('Failed to parse cached data:', error);
      return null;
    }
  }

  /**
   * 儲存查詢結果
   */
  async set(key: string, value: any, ttlSeconds: number = 300): Promise<void> {
    await this.redis.setex(key, ttlSeconds, JSON.stringify(value));
  }

  /**
   * 生成快取鍵
   */
  generateKey(prefix: string, params: Record<string, any>): string {
    const sortedParams = Object.keys(params)
      .sort()
      .map((key) => `${key}:${params[key]}`)
      .join('|');

    return `analytics:${prefix}:${sortedParams}`;
  }

  /**
   * 清除租戶相關快取
   */
  async invalidateTenant(tenantId: string): Promise<void> {
    const pattern = `analytics:*tenant_id:${tenantId}*`;
    const keys = await this.redis.keys(pattern);

    if (keys.length > 0) {
      await this.redis.del(...keys);
    }
  }

  /**
   * 清除指標相關快取
   */
  async invalidateMetric(metric: string): Promise<void> {
    const pattern = `analytics:*metric:${metric}*`;
    const keys = await this.redis.keys(pattern);

    if (keys.length > 0) {
      await this.redis.del(...keys);
    }
  }
}

Excel 報表生成

// packages/kyo-core/src/reports/excel-generator.ts
import ExcelJS from 'exceljs';
import { format } from 'date-fns';

export interface ReportData {
  title: string;
  subtitle?: string;
  generatedAt: Date;
  sheets: Array<{
    name: string;
    headers: string[];
    data: any[][];
    charts?: Array<{
      type: 'line' | 'bar' | 'pie';
      title: string;
      data: any;
    }>;
  }>;
}

/**
 * Excel Report Generator
 *
 * 功能:
 * - 多工作表
 * - 圖表
 * - 樣式設定
 * - 公式
 */
export class ExcelReportGenerator {
  /**
   * 生成 Excel 報表
   */
  async generate(reportData: ReportData): Promise<Buffer> {
    const workbook = new ExcelJS.Workbook();

    // 設定元資料
    workbook.creator = 'Kyo System';
    workbook.created = reportData.generatedAt;
    workbook.modified = reportData.generatedAt;

    // 建立工作表
    for (const sheetData of reportData.sheets) {
      const worksheet = workbook.addWorksheet(sheetData.name);

      // 標題行
      worksheet.addRow([reportData.title]);
      worksheet.mergeCells(1, 1, 1, sheetData.headers.length);
      worksheet.getCell(1, 1).font = { size: 16, bold: true };
      worksheet.getCell(1, 1).alignment = { horizontal: 'center', vertical: 'middle' };

      // 副標題
      if (reportData.subtitle) {
        worksheet.addRow([reportData.subtitle]);
        worksheet.mergeCells(2, 1, 2, sheetData.headers.length);
        worksheet.getCell(2, 1).font = { size: 12, italic: true };
        worksheet.getCell(2, 1).alignment = { horizontal: 'center' };
      }

      // 空白行
      worksheet.addRow([]);

      // 表頭
      const headerRow = worksheet.addRow(sheetData.headers);
      headerRow.font = { bold: true };
      headerRow.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FF4472C4' },
      };
      headerRow.font = { color: { argb: 'FFFFFFFF' }, bold: true };
      headerRow.alignment = { horizontal: 'center', vertical: 'middle' };

      // 資料行
      sheetData.data.forEach((row) => {
        worksheet.addRow(row);
      });

      // 設定欄寬
      worksheet.columns.forEach((column, index) => {
        let maxLength = sheetData.headers[index].length;

        sheetData.data.forEach((row) => {
          const cellValue = row[index]?.toString() || '';
          if (cellValue.length > maxLength) {
            maxLength = cellValue.length;
          }
        });

        column.width = Math.min(maxLength + 2, 50);
      });

      // 凍結窗格 (凍結標題行)
      worksheet.views = [{ state: 'frozen', ySplit: 4 }];

      // 自動篩選
      worksheet.autoFilter = {
        from: { row: 4, column: 1 },
        to: { row: 4, column: sheetData.headers.length },
      };

      // 新增圖表 (如果有)
      if (sheetData.charts) {
        // ExcelJS 圖表支援有限,這裡僅示意
        // 實務上可考慮使用其他函式庫或預先生成圖片
      }
    }

    // 生成 Buffer
    const buffer = await workbook.xlsx.writeBuffer();
    return buffer as Buffer;
  }

  /**
   * 生成 OTP 使用報表
   */
  async generateOTPUsageReport(
    tenantId: string,
    startDate: Date,
    endDate: Date,
    data: any[]
  ): Promise<Buffer> {
    const reportData: ReportData = {
      title: 'OTP 使用統計報表',
      subtitle: `期間: ${format(startDate, 'yyyy-MM-dd')} ~ ${format(endDate, 'yyyy-MM-dd')}`,
      generatedAt: new Date(),
      sheets: [
        {
          name: '每日統計',
          headers: ['日期', '發送次數', '驗證成功', '驗證失敗', '成功率 (%)', '總成本 (TWD)'],
          data: data.map((item) => [
            format(new Date(item.date), 'yyyy-MM-dd'),
            item.sent_count,
            item.verified_count,
            item.failed_count,
            ((item.verified_count / item.sent_count) * 100).toFixed(2),
            item.total_cost.toFixed(2),
          ]),
        },
        {
          name: '供應商統計',
          headers: ['供應商', '發送次數', '成功率 (%)', '平均延遲 (秒)', '總成本 (TWD)'],
          data: this.aggregateByProvider(data),
        },
      ],
    };

    return this.generate(reportData);
  }

  /**
   * 按供應商聚合
   */
  private aggregateByProvider(data: any[]): any[][] {
    const grouped = data.reduce((acc: Record<string, any>, item) => {
      const provider = item.provider || 'unknown';

      if (!acc[provider]) {
        acc[provider] = {
          sent: 0,
          verified: 0,
          totalDelay: 0,
          count: 0,
          cost: 0,
        };
      }

      acc[provider].sent += item.sent_count;
      acc[provider].verified += item.verified_count;
      acc[provider].totalDelay += item.avg_verification_time || 0;
      acc[provider].count += 1;
      acc[provider].cost += item.total_cost;

      return acc;
    }, {});

    return Object.entries(grouped).map(([provider, stats]: [string, any]) => [
      provider,
      stats.sent,
      ((stats.verified / stats.sent) * 100).toFixed(2),
      (stats.totalDelay / stats.count).toFixed(2),
      stats.cost.toFixed(2),
    ]);
  }
}

PDF 報表生成

// packages/kyo-core/src/reports/pdf-generator.ts
import puppeteer from 'puppeteer';
import { format } from 'date-fns';

/**
 * PDF Report Generator
 *
 * 使用 Puppeteer 將 HTML 轉換為 PDF
 */
export class PDFReportGenerator {
  /**
   * 生成 PDF 報表
   */
  async generate(htmlContent: string): Promise<Buffer> {
    const browser = await puppeteer.launch({
      headless: true,
      args: ['--no-sandbox', '--disable-setuid-sandbox'],
    });

    try {
      const page = await browser.newPage();

      // 設定內容
      await page.setContent(htmlContent, { waitUntil: 'networkidle0' });

      // 生成 PDF
      const pdf = await page.pdf({
        format: 'A4',
        printBackground: true,
        margin: {
          top: '20mm',
          right: '15mm',
          bottom: '20mm',
          left: '15mm',
        },
      });

      return pdf;
    } finally {
      await browser.close();
    }
  }

  /**
   * 生成 OTP 使用報表 HTML
   */
  generateOTPReportHTML(
    tenantName: string,
    startDate: Date,
    endDate: Date,
    data: any[]
  ): string {
    const totalSent = data.reduce((sum, item) => sum + item.sent_count, 0);
    const totalVerified = data.reduce((sum, item) => sum + item.verified_count, 0);
    const successRate = ((totalVerified / totalSent) * 100).toFixed(2);

    return `
      <!DOCTYPE html>
      <html>
      <head>
        <meta charset="UTF-8">
        <style>
          body {
            font-family: 'Arial', sans-serif;
            margin: 0;
            padding: 20px;
          }
          .header {
            text-align: center;
            margin-bottom: 30px;
          }
          .header h1 {
            color: #2563eb;
            margin-bottom: 10px;
          }
          .header p {
            color: #666;
          }
          .summary {
            display: flex;
            justify-content: space-around;
            margin-bottom: 30px;
          }
          .summary-card {
            text-align: center;
            padding: 20px;
            background: #f3f4f6;
            border-radius: 8px;
            flex: 1;
            margin: 0 10px;
          }
          .summary-card h3 {
            margin: 0;
            font-size: 32px;
            color: #2563eb;
          }
          .summary-card p {
            margin: 5px 0 0;
            color: #666;
          }
          table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
          }
          th, td {
            padding: 12px;
            text-align: left;
            border-bottom: 1px solid #ddd;
          }
          th {
            background-color: #2563eb;
            color: white;
          }
          tr:hover {
            background-color: #f3f4f6;
          }
          .footer {
            margin-top: 40px;
            text-align: center;
            color: #666;
            font-size: 12px;
          }
        </style>
      </head>
      <body>
        <div class="header">
          <h1>OTP 使用統計報表</h1>
          <p>${tenantName}</p>
          <p>期間: ${format(startDate, 'yyyy-MM-dd')} ~ ${format(endDate, 'yyyy-MM-dd')}</p>
        </div>

        <div class="summary">
          <div class="summary-card">
            <h3>${totalSent.toLocaleString()}</h3>
            <p>總發送次數</p>
          </div>
          <div class="summary-card">
            <h3>${totalVerified.toLocaleString()}</h3>
            <p>驗證成功</p>
          </div>
          <div class="summary-card">
            <h3>${successRate}%</h3>
            <p>成功率</p>
          </div>
        </div>

        <table>
          <thead>
            <tr>
              <th>日期</th>
              <th>發送次數</th>
              <th>驗證成功</th>
              <th>驗證失敗</th>
              <th>成功率</th>
            </tr>
          </thead>
          <tbody>
            ${data
              .map(
                (item) => `
              <tr>
                <td>${format(new Date(item.date), 'yyyy-MM-dd')}</td>
                <td>${item.sent_count.toLocaleString()}</td>
                <td>${item.verified_count.toLocaleString()}</td>
                <td>${item.failed_count.toLocaleString()}</td>
                <td>${((item.verified_count / item.sent_count) * 100).toFixed(2)}%</td>
              </tr>
            `
              )
              .join('')}
          </tbody>
        </table>

        <div class="footer">
          <p>Generated by Kyo System - ${format(new Date(), 'yyyy-MM-dd HH:mm:ss')}</p>
        </div>
      </body>
      </html>
    `;
  }
}

BullMQ 報表生成任務

// apps/kyo-otp-service/src/jobs/report-generator.ts
import { Worker, Job, Queue } from 'bullmq';
import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';
import { getSignedUrl } from '@aws-sdk/s3-request-presigner';
import Redis from 'ioredis';
import { ExcelReportGenerator } from '@kyong/kyo-core/reports/excel-generator';
import { PDFReportGenerator } from '@kyong/kyo-core/reports/pdf-generator';

interface ReportJobData {
  reportId: string;
  tenantId: string;
  userId: string;
  reportType: 'otp-usage' | 'api-usage' | 'user-activity';
  format: 'excel' | 'pdf' | 'csv';
  startDate: Date;
  endDate: Date;
  options?: Record<string, any>;
}

/**
 * Report Generator Worker
 */
export class ReportGeneratorWorker {
  private worker: Worker;
  private queue: Queue;
  private s3Client: S3Client;
  private excelGenerator: ExcelReportGenerator;
  private pdfGenerator: PDFReportGenerator;

  constructor(redis: Redis) {
    this.s3Client = new S3Client({});
    this.excelGenerator = new ExcelReportGenerator();
    this.pdfGenerator = new PDFReportGenerator();

    // 建立 Queue
    this.queue = new Queue('report-generation', { connection: redis });

    // 建立 Worker
    this.worker = new Worker(
      'report-generation',
      async (job: Job<ReportJobData>) => {
        return this.processReportJob(job);
      },
      {
        connection: redis,
        concurrency: 5, // 同時處理 5 個報表
      }
    );

    this.worker.on('completed', (job) => {
      console.log(`Report job ${job.id} completed`);
    });

    this.worker.on('failed', (job, error) => {
      console.error(`Report job ${job?.id} failed:`, error);
    });
  }

  /**
   * 加入報表生成任務
   */
  async queueReport(data: ReportJobData): Promise<string> {
    const job = await this.queue.add('generate-report', data, {
      attempts: 3,
      backoff: {
        type: 'exponential',
        delay: 2000,
      },
      removeOnComplete: {
        age: 86400, // 保留 24 小時
        count: 100,
      },
    });

    return job.id!;
  }

  /**
   * 處理報表生成任務
   */
  private async processReportJob(job: Job<ReportJobData>): Promise<string> {
    const { reportId, tenantId, reportType, format, startDate, endDate } = job.data;

    // 1. 查詢資料
    await job.updateProgress(10);
    const data = await this.fetchReportData(tenantId, reportType, startDate, endDate);

    // 2. 生成報表
    await job.updateProgress(50);
    let buffer: Buffer;

    if (format === 'excel') {
      buffer = await this.excelGenerator.generateOTPUsageReport(
        tenantId,
        startDate,
        endDate,
        data
      );
    } else if (format === 'pdf') {
      const html = this.pdfGenerator.generateOTPReportHTML(
        tenantId,
        startDate,
        endDate,
        data
      );
      buffer = await this.pdfGenerator.generate(html);
    } else {
      // CSV
      buffer = this.generateCSV(data);
    }

    // 3. 上傳到 S3
    await job.updateProgress(80);
    const s3Key = `reports/${tenantId}/${reportId}.${format === 'excel' ? 'xlsx' : format}`;

    await this.s3Client.send(
      new PutObjectCommand({
        Bucket: process.env.REPORTS_BUCKET!,
        Key: s3Key,
        Body: buffer,
        ContentType: this.getContentType(format),
      })
    );

    // 4. 生成下載連結
    await job.updateProgress(100);
    const downloadUrl = await getSignedUrl(
      this.s3Client,
      new PutObjectCommand({
        Bucket: process.env.REPORTS_BUCKET!,
        Key: s3Key,
      }),
      { expiresIn: 3600 } // 1 小時有效
    );

    return downloadUrl;
  }

  /**
   * 查詢報表資料
   */
  private async fetchReportData(
    tenantId: string,
    reportType: string,
    startDate: Date,
    endDate: Date
  ): Promise<any[]> {
    // 實作查詢邏輯
    // 這裡簡化處理
    return [];
  }

  /**
   * 生成 CSV
   */
  private generateCSV(data: any[]): Buffer {
    if (data.length === 0) {
      return Buffer.from('');
    }

    const headers = Object.keys(data[0]);
    const csvContent = [
      headers.join(','),
      ...data.map((row) =>
        headers.map((header) => JSON.stringify(row[header] ?? '')).join(',')
      ),
    ].join('\n');

    return Buffer.from('\ufeff' + csvContent, 'utf-8');
  }

  /**
   * 取得 Content Type
   */
  private getContentType(format: string): string {
    const contentTypes: Record<string, string> = {
      excel: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      pdf: 'application/pdf',
      csv: 'text/csv',
    };
    return contentTypes[format] || 'application/octet-stream';
  }

  /**
   * 關閉 Worker
   */
  async close(): Promise<void> {
    await this.worker.close();
    await this.queue.close();
  }
}

今日總結

我們今天完成了 Kyo System 的資料分析引擎:

核心功能

1. Query Builder: 靈活的分析查詢
2. Materialized Views: 實體化檢視表優化
3. Redis 快取: 多層快取策略
4. Excel 生成: 豐富格式支援
5. PDF 生成: 視覺化報表
6. BullMQ 任務: 背景處理
7. S3 儲存: 報表歸檔

技術分析

OLTP vs OLAP:

• OLTP: 交易處理、即時
• OLAP: 分析查詢、可延遲
• 💡 Read Replica 分離負載

Materialized Views:

• 實體化檢視表聚合結果
• 定期刷新 (CONCURRENTLY)
• 查詢速度提升 10-100 倍
• 💡 平衡即時性與效能

快取策略:

• L1: 應用層記憶體
• L2: Redis
• L3: Materialized Views
• 💡 多層快取減少 DB 負擔

報表生成:

• Excel: 資料分析、公式
• PDF: 視覺化、列印
• CSV: 輕量、匯入
• 💡 依用途選擇格式

分析引擎檢查清單

• ✅ Materialized Views
• ✅ Query Builder
• ✅ 時間序列查詢
• ✅ 同比/環比分析
• ✅ 漏斗分析
• ✅ Redis 快取層
• ✅ Excel 報表生成
• ✅ PDF 報表生成
• ✅ BullMQ 背景任務
• ✅ S3 報表儲存