前情提要

在 Day 14 我們建立了基礎的課程管理系統，今天我們將深入實作課程模板系統的後端架構。這個系統不僅要支援複雜的模板管理，還需要處理大量批次操作、衝突檢測、以及高效能的資料處理。我們將從資料庫設計開始，逐步建構一個完整的企業級後端系統。

系統架構概覽

核心設計原則

// packages/kyo-core/src/services/template-engine-types.ts
export interface TemplateEngineConfig {
  // 效能配置
  batchSize: number;
  maxConcurrentOperations: number;
  operationTimeout: number;

  // 衝突處理策略
  conflictResolution: 'abort' | 'skip' | 'override' | 'merge';

  // 快取配置
  cacheStrategy: 'memory' | 'redis' | 'hybrid';
  cacheTTL: number;

  // 驗證配置
  enableStrictValidation: boolean;
  allowPartialUpdates: boolean;

  // 審計配置
  enableAuditLog: boolean;
  auditRetentionDays: number;
}

export interface TemplateSystemMetrics {
  totalTemplates: number;
  activeTemplates: number;
  templatesUsedCount: Map<string, number>;
  averageOperationTime: number;
  conflictRate: number;
  successRate: number;
  peakConcurrency: number;
}

export interface TemplateEngineContext {
  tenantId: string;
  userId: string;
  operationId: string;
  timestamp: Date;
  userPermissions: string[];
  systemLimits: {
    maxTemplatesPerTenant: number;
    maxOperationsPerMinute: number;
    maxBatchSize: number;
  };
}

資料庫架構設計

主要資料表結構

-- 課程模板主表
CREATE TABLE course_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category_id UUID REFERENCES course_categories(id),

  -- 模板層級與繼承
  template_type VARCHAR(50) NOT NULL CHECK (template_type IN ('base', 'specialized', 'custom')),
  parent_template_id UUID REFERENCES course_templates(id),
  inheritance_depth INTEGER DEFAULT 0,

  -- 模板配置
  default_duration INTEGER NOT NULL,
  default_max_participants INTEGER NOT NULL,
  default_min_participants INTEGER DEFAULT 1,
  default_price DECIMAL(10,2),
  default_skill_level VARCHAR(20) DEFAULT 'beginner',

  -- 動態屬性 (JSON)
  dynamic_properties JSONB DEFAULT '{}',
  validation_rules JSONB DEFAULT '{}',
  ui_configuration JSONB DEFAULT '{}',

  -- 使用統計
  usage_count INTEGER DEFAULT 0,
  last_used_at TIMESTAMP,

  -- 狀態管理
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('draft', 'active', 'archived', 'deprecated')),
  is_public BOOLEAN DEFAULT false,
  is_system_template BOOLEAN DEFAULT false,

  -- 版本控制
  version INTEGER DEFAULT 1,
  previous_version_id UUID REFERENCES course_templates(id),

  -- 審計字段
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES users(id),
  updated_by UUID REFERENCES users(id),

  -- 索引優化
  CONSTRAINT unique_template_name_per_tenant UNIQUE (tenant_id, name, status)
);

-- 模板標籤關聯表
CREATE TABLE template_tags (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id UUID NOT NULL REFERENCES course_templates(id) ON DELETE CASCADE,
  tag_name VARCHAR(100) NOT NULL,
  tag_value VARCHAR(255),
  tag_type VARCHAR(50) DEFAULT 'custom',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT unique_template_tag UNIQUE (template_id, tag_name)
);

-- 模板收藏表
CREATE TABLE template_favorites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id UUID NOT NULL REFERENCES course_templates(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT unique_user_template_favorite UNIQUE (user_id, template_id)
);

-- 批次操作記錄表
CREATE TABLE batch_operations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  operation_type VARCHAR(50) NOT NULL,
  operation_name VARCHAR(255),

  -- 操作配置
  target_count INTEGER NOT NULL DEFAULT 0,
  completed_count INTEGER DEFAULT 0,
  failed_count INTEGER DEFAULT 0,
  skipped_count INTEGER DEFAULT 0,

  -- 狀態追蹤
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'completed', 'failed', 'cancelled')),
  progress_percentage DECIMAL(5,2) DEFAULT 0,

  -- 錯誤處理
  error_summary TEXT,
  conflict_resolution_strategy VARCHAR(20),

  -- 執行配置
  execution_config JSONB DEFAULT '{}',
  result_summary JSONB DEFAULT '{}',

  -- 時間追蹤
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  estimated_completion_time TIMESTAMP,

  -- 審計
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by UUID REFERENCES users(id),

  -- 索引
  INDEX idx_batch_operations_tenant_status (tenant_id, status),
  INDEX idx_batch_operations_created_at (created_at DESC)
);

-- 批次操作詳細記錄表
CREATE TABLE batch_operation_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  batch_operation_id UUID NOT NULL REFERENCES batch_operations(id) ON DELETE CASCADE,

  -- 目標資源
  target_type VARCHAR(50) NOT NULL, -- 'template', 'course', 'schedule'
  target_id UUID NOT NULL,
  target_name VARCHAR(255),

  -- 操作詳情
  operation_data JSONB NOT NULL,
  original_data JSONB,

  -- 狀態
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'skipped')),
  error_message TEXT,
  retry_count INTEGER DEFAULT 0,

  -- 時間記錄
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  processing_duration_ms INTEGER,

  -- 索引
  INDEX idx_batch_items_batch_id_status (batch_operation_id, status),
  INDEX idx_batch_items_target (target_type, target_id)
);

-- 模板使用歷史表
CREATE TABLE template_usage_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id UUID NOT NULL REFERENCES course_templates(id),
  course_id UUID REFERENCES courses(id),

  -- 使用詳情
  used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  used_by UUID REFERENCES users(id),
  usage_type VARCHAR(50) NOT NULL, -- 'create_course', 'bulk_create', 'schedule_generation'

  -- 客製化記錄
  customizations JSONB DEFAULT '{}',
  original_template_version INTEGER,

  -- 結果追蹤
  success BOOLEAN DEFAULT true,
  error_message TEXT,

  -- 索引
  INDEX idx_template_usage_template_date (template_id, used_at DESC),
  INDEX idx_template_usage_user_date (used_by, used_at DESC)
);

資料庫索引策略

-- 效能優化索引
CREATE INDEX CONCURRENTLY idx_course_templates_tenant_status_type
ON course_templates (tenant_id, status, template_type)
WHERE status = 'active';

CREATE INDEX CONCURRENTLY idx_course_templates_parent_inheritance
ON course_templates (parent_template_id, inheritance_depth)
WHERE parent_template_id IS NOT NULL;

CREATE INDEX CONCURRENTLY idx_course_templates_usage_stats
ON course_templates (usage_count DESC, last_used_at DESC)
WHERE status = 'active';

-- JSON 欄位索引 (PostgreSQL 13+)
CREATE INDEX CONCURRENTLY idx_course_templates_dynamic_properties
ON course_templates USING GIN (dynamic_properties);

CREATE INDEX CONCURRENTLY idx_course_templates_validation_rules
ON course_templates USING GIN (validation_rules);

-- 複合索引用於複雜查詢
CREATE INDEX CONCURRENTLY idx_course_templates_search
ON course_templates (tenant_id, status, category_id, template_type, name text_pattern_ops);

核心服務實作

模板管理服務

// packages/kyo-core/src/services/CourseTemplateService.ts
import { Redis } from 'ioredis';
import { z } from 'zod';
import { DatabasePool } from './database';
import { AuditLogger } from './audit/AuditLogger';
import { ConflictResolver } from './template-engine/ConflictResolver';
import { ValidationEngine } from './template-engine/ValidationEngine';

export class CourseTemplateService {
  private readonly db: DatabasePool;
  private readonly redis: Redis;
  private readonly auditLogger: AuditLogger;
  private readonly conflictResolver: ConflictResolver;
  private readonly validationEngine: ValidationEngine;
  private readonly config: TemplateEngineConfig;

  constructor(dependencies: {
    db: DatabasePool;
    redis: Redis;
    auditLogger: AuditLogger;
    conflictResolver: ConflictResolver;
    validationEngine: ValidationEngine;
    config: TemplateEngineConfig;
  }) {
    this.db = dependencies.db;
    this.redis = dependencies.redis;
    this.auditLogger = dependencies.auditLogger;
    this.conflictResolver = dependencies.conflictResolver;
    this.validationEngine = dependencies.validationEngine;
    this.config = dependencies.config;
  }

  /**
   * 創建新模板 - 支援複雜繼承和動態屬性
   */
  async createTemplate(
    data: CreateTemplateRequest,
    context: TemplateEngineContext
  ): Promise<CourseTemplate> {
    const startTime = Date.now();

    try {
      // 1. 權限檢查
      await this.validatePermissions(context, 'create_template');

      // 2. 數據驗證
      await this.validationEngine.validateTemplateData(data, context);

      // 3. 處理模板繼承
      const inheritanceData = await this.resolveTemplateInheritance(
        data.parentTemplateId,
        context
      );

      // 4. 合併動態屬性
      const mergedProperties = await this.mergeDynamicProperties(
        data.dynamicProperties || {},
        inheritanceData?.dynamicProperties || {},
        data.inheritanceStrategy || 'merge'
      );

      // 5. 開始資料庫事務
      const client = await this.db.connect();

      try {
        await client.query('BEGIN');

        // 插入主模板記錄
        const templateId = await this.insertTemplate(client, {
          ...data,
          dynamicProperties: mergedProperties,
          inheritanceDepth: (inheritanceData?.inheritanceDepth || 0) + 1,
          tenantId: context.tenantId,
          createdBy: context.userId
        });

        // 插入標籤
        if (data.tags && data.tags.length > 0) {
          await this.insertTemplateTags(client, templateId, data.tags);
        }

        // 記錄審計日誌
        await this.auditLogger.logTemplateCreation(templateId, data, context);

        await client.query('COMMIT');

        // 清除相關快取
        await this.invalidateTemplateCache(context.tenantId);

        // 更新使用統計
        await this.updateTemplateUsageStats(templateId, 'created');

        const template = await this.getTemplate(templateId, context);

        return template;

      } catch (error) {
        await client.query('ROLLBACK');
        throw error;
      } finally {
        client.release();
      }

    } catch (error) {
      await this.auditLogger.logError('template_creation_failed', error, context);
      throw this.enhanceError(error, 'createTemplate', { data, context });
    } finally {
      const duration = Date.now() - startTime;
      await this.recordMetrics('template_creation', duration, context);
    }
  }

  /**
   * 批次創建模板 - 高效能批次處理
   */
  async createTemplatesBatch(
    templates: CreateTemplateRequest[],
    context: TemplateEngineContext
  ): Promise<BatchOperationResult<CourseTemplate>> {
    const batchId = crypto.randomUUID();
    const startTime = Date.now();

    try {
      // 1. 創建批次操作記錄
      await this.createBatchOperation(batchId, 'bulk_template_creation', {
        targetCount: templates.length,
        operationType: 'CREATE_TEMPLATES',
        executionConfig: {
          batchSize: this.config.batchSize,
          maxConcurrency: this.config.maxConcurrentOperations
        }
      }, context);

      // 2. 預處理和驗證
      const validationResults = await this.validateTemplatesBatch(templates, context);
      const validTemplates = templates.filter((_, index) =>
        validationResults[index].isValid
      );

      // 3. 分批處理
      const results: Array<{ success: boolean; data?: CourseTemplate; error?: Error }> = [];
      const batches = this.chunkArray(validTemplates, this.config.batchSize);

      for (const [batchIndex, batch] of batches.entries()) {
        await this.updateBatchProgress(batchId, {
          currentBatch: batchIndex + 1,
          totalBatches: batches.length,
          progressPercentage: (batchIndex / batches.length) * 100
        });

        // 並發處理批次內的模板
        const batchPromises = batch.map(async (template, itemIndex) => {
          try {
            const globalIndex = batchIndex * this.config.batchSize + itemIndex;

            // 記錄項目開始處理
            await this.updateBatchItem(batchId, globalIndex, 'processing', {
              targetType: 'template',
              targetName: template.name,
              operationData: template
            });

            const createdTemplate = await this.createTemplate(template, {
              ...context,
              operationId: `${batchId}-${globalIndex}`
            });

            // 記錄項目完成
            await this.updateBatchItem(batchId, globalIndex, 'completed', {
              resultData: { templateId: createdTemplate.id }
            });

            return { success: true, data: createdTemplate };

          } catch (error) {
            // 記錄項目失敗
            await this.updateBatchItem(batchId, globalIndex, 'failed', {
              errorMessage: error.message,
              errorDetails: error
            });

            return { success: false, error: error as Error };
          }
        });

        const batchResults = await Promise.allSettled(batchPromises);
        results.push(...batchResults.map(result =>
          result.status === 'fulfilled' ? result.value :
          { success: false, error: new Error('Promise rejected') }
        ));

        // 批次間延遲，避免系統過載
        if (batchIndex < batches.length - 1) {
          await this.delay(100);
        }
      }

      // 4. 彙整結果
      const successful = results.filter(r => r.success);
      const failed = results.filter(r => !r.success);

      // 5. 完成批次操作
      await this.completeBatchOperation(batchId, {
        completedCount: successful.length,
        failedCount: failed.length,
        status: failed.length === 0 ? 'completed' : 'partial_success',
        resultSummary: {
          successfulTemplates: successful.map(r => r.data?.id),
          errorSummary: failed.map(r => r.error?.message)
        }
      });

      return {
        batchId,
        totalItems: templates.length,
        successful: successful.map(r => r.data!),
        failed: failed.map((r, index) => ({
          index,
          item: templates[results.indexOf(r)],
          error: r.error!
        })),
        metrics: {
          totalDuration: Date.now() - startTime,
          averageItemDuration: (Date.now() - startTime) / templates.length,
          successRate: (successful.length / templates.length) * 100
        }
      };

    } catch (error) {
      await this.failBatchOperation(batchId, error);
      throw error;
    }
  }

  /**
   * 智能搜索模板 - 支援複雜查詢和快取
   */
  async searchTemplates(
    criteria: TemplateSearchCriteria,
    context: TemplateEngineContext
  ): Promise<PaginatedResult<CourseTemplate>> {
    const cacheKey = this.generateSearchCacheKey(criteria, context.tenantId);

    // 嘗試從快取獲取
    if (this.config.cacheStrategy !== 'memory') {
      const cachedResult = await this.redis.get(cacheKey);
      if (cachedResult) {
        return JSON.parse(cachedResult);
      }
    }

    try {
      // 建構複雜查詢
      const query = this.buildSearchQuery(criteria, context);

      // 執行搜索
      const results = await this.db.query(query.sql, query.params);
      const totalCount = await this.getSearchResultCount(criteria, context);

      // 後處理結果
      const templates = await Promise.all(
        results.rows.map(row => this.hydrateTemplate(row, context))
      );

      const paginatedResult: PaginatedResult<CourseTemplate> = {
        items: templates,
        totalCount,
        page: criteria.page || 1,
        pageSize: criteria.pageSize || 20,
        totalPages: Math.ceil(totalCount / (criteria.pageSize || 20)),
        hasNextPage: ((criteria.page || 1) * (criteria.pageSize || 20)) < totalCount,
        hasPreviousPage: (criteria.page || 1) > 1
      };

      // 快取結果
      await this.redis.setex(
        cacheKey,
        this.config.cacheTTL,
        JSON.stringify(paginatedResult)
      );

      return paginatedResult;

    } catch (error) {
      throw this.enhanceError(error, 'searchTemplates', { criteria, context });
    }
  }

  /**
   * 模板繼承解析 - 處理複雜的繼承鏈
   */
  private async resolveTemplateInheritance(
    parentTemplateId: string | undefined,
    context: TemplateEngineContext,
    maxDepth: number = 10
  ): Promise<ResolvedInheritance | null> {
    if (!parentTemplateId) return null;

    const inheritanceChain: CourseTemplate[] = [];
    let currentTemplateId: string | undefined = parentTemplateId;
    let depth = 0;

    // 追蹤繼承鏈，防止循環繼承
    while (currentTemplateId && depth < maxDepth) {
      const template = await this.getTemplate(currentTemplateId, context);

      if (inheritanceChain.some(t => t.id === template.id)) {
        throw new Error(`Circular inheritance detected in template chain: ${template.id}`);
      }

      inheritanceChain.push(template);
      currentTemplateId = template.parentTemplateId;
      depth++;
    }

    if (depth >= maxDepth) {
      throw new Error(`Template inheritance too deep (max: ${maxDepth})`);
    }

    // 由最頂層開始合併屬性
    let mergedProperties = {};
    let mergedValidationRules = {};
    let mergedUiConfiguration = {};

    for (const template of inheritanceChain.reverse()) {
      mergedProperties = this.deepMerge(mergedProperties, template.dynamicProperties);
      mergedValidationRules = this.deepMerge(mergedValidationRules, template.validationRules);
      mergedUiConfiguration = this.deepMerge(mergedUiConfiguration, template.uiConfiguration);
    }

    return {
      inheritanceChain: inheritanceChain.reverse(),
      inheritanceDepth: depth,
      mergedProperties: {
        dynamicProperties: mergedProperties,
        validationRules: mergedValidationRules,
        uiConfiguration: mergedUiConfiguration
      }
    };
  }

  /**
   * 動態屬性合併邏輯
   */
  private async mergeDynamicProperties(
    childProperties: Record<string, any>,
    parentProperties: Record<string, any>,
    strategy: InheritanceStrategy
  ): Promise<Record<string, any>> {
    switch (strategy) {
      case 'override':
        return { ...parentProperties, ...childProperties };

      case 'merge':
        return this.deepMerge(parentProperties, childProperties);

      case 'extend':
        return this.extendProperties(parentProperties, childProperties);

      case 'selective':
        return this.selectivelyMergeProperties(parentProperties, childProperties);

      default:
        return childProperties;
    }
  }

  /**
   * 深度合併物件
   */
  private deepMerge(target: any, source: any): any {
    const result = { ...target };

    for (const key in source) {
      if (source[key] && typeof source[key] === 'object' && !Array.isArray(source[key])) {
        result[key] = this.deepMerge(result[key] || {}, source[key]);
      } else if (Array.isArray(source[key])) {
        result[key] = [...(result[key] || []), ...source[key]];
      } else {
        result[key] = source[key];
      }
    }

    return result;
  }

  /**
   * 建構搜索查詢
   */
  private buildSearchQuery(
    criteria: TemplateSearchCriteria,
    context: TemplateEngineContext
  ): { sql: string; params: any[] } {
    const params: any[] = [context.tenantId];
    let sql = `
      SELECT
        t.*,
        c.name as category_name,
        c.color as category_color,
        array_agg(DISTINCT tag.tag_name) as tags,
        COUNT(DISTINCT f.user_id) as favorite_count,
        EXISTS(
          SELECT 1 FROM template_favorites tf
          WHERE tf.template_id = t.id AND tf.user_id = $2
        ) as is_favorited
      FROM course_templates t
      LEFT JOIN course_categories c ON t.category_id = c.id
      LEFT JOIN template_tags tag ON t.id = tag.template_id
      LEFT JOIN template_favorites f ON t.id = f.template_id
      WHERE t.tenant_id = $1
    `;
    params.push(context.userId);

    // 狀態篩選
    if (criteria.status && criteria.status.length > 0) {
      sql += ` AND t.status = ANY($${params.length + 1})`;
      params.push(criteria.status);
    } else {
      sql += ` AND t.status = 'active'`;
    }

    // 類別篩選
    if (criteria.categoryIds && criteria.categoryIds.length > 0) {
      sql += ` AND t.category_id = ANY($${params.length + 1})`;
      params.push(criteria.categoryIds);
    }

    // 模板類型篩選
    if (criteria.templateTypes && criteria.templateTypes.length > 0) {
      sql += ` AND t.template_type = ANY($${params.length + 1})`;
      params.push(criteria.templateTypes);
    }

    // 技能等級篩選
    if (criteria.skillLevels && criteria.skillLevels.length > 0) {
      sql += ` AND t.default_skill_level = ANY($${params.length + 1})`;
      params.push(criteria.skillLevels);
    }

    // 關鍵字搜索
    if (criteria.keyword) {
      sql += ` AND (
        t.name ILIKE $${params.length + 1} OR
        t.description ILIKE $${params.length + 1} OR
        EXISTS(
          SELECT 1 FROM template_tags tt
          WHERE tt.template_id = t.id AND tt.tag_name ILIKE $${params.length + 1}
        )
      )`;
      params.push(`%${criteria.keyword}%`);
    }

    // 標籤篩選
    if (criteria.tags && criteria.tags.length > 0) {
      sql += ` AND EXISTS(
        SELECT 1 FROM template_tags tt
        WHERE tt.template_id = t.id AND tt.tag_name = ANY($${params.length + 1})
      )`;
      params.push(criteria.tags);
    }

    // 價格範圍
    if (criteria.priceRange) {
      if (criteria.priceRange.min !== undefined) {
        sql += ` AND t.default_price >= $${params.length + 1}`;
        params.push(criteria.priceRange.min);
      }
      if (criteria.priceRange.max !== undefined) {
        sql += ` AND t.default_price <= $${params.length + 1}`;
        params.push(criteria.priceRange.max);
      }
    }

    // 時長範圍
    if (criteria.durationRange) {
      if (criteria.durationRange.min !== undefined) {
        sql += ` AND t.default_duration >= $${params.length + 1}`;
        params.push(criteria.durationRange.min);
      }
      if (criteria.durationRange.max !== undefined) {
        sql += ` AND t.default_duration <= $${params.length + 1}`;
        params.push(criteria.durationRange.max);
      }
    }

    // 收藏篩選
    if (criteria.favoritesOnly) {
      sql += ` AND EXISTS(
        SELECT 1 FROM template_favorites tf
        WHERE tf.template_id = t.id AND tf.user_id = $2
      )`;
    }

    // 使用頻率篩選
    if (criteria.popularOnly) {
      sql += ` AND t.usage_count > $${params.length + 1}`;
      params.push(10); // 可設定的熱門門檻
    }

    // 分組
    sql += `
      GROUP BY t.id, c.name, c.color
    `;

    // 排序
    const sortField = criteria.sortBy || 'updated_at';
    const sortOrder = criteria.sortOrder || 'desc';

    switch (sortField) {
      case 'name':
        sql += ` ORDER BY t.name ${sortOrder.toUpperCase()}`;
        break;
      case 'created_at':
        sql += ` ORDER BY t.created_at ${sortOrder.toUpperCase()}`;
        break;
      case 'usage_count':
        sql += ` ORDER BY t.usage_count ${sortOrder.toUpperCase()}`;
        break;
      case 'last_used':
        sql += ` ORDER BY t.last_used_at ${sortOrder.toUpperCase()} NULLS LAST`;
        break;
      case 'popularity':
        sql += ` ORDER BY favorite_count ${sortOrder.toUpperCase()}, t.usage_count ${sortOrder.toUpperCase()}`;
        break;
      default:
        sql += ` ORDER BY t.updated_at ${sortOrder.toUpperCase()}`;
    }

    // 分頁
    const limit = criteria.pageSize || 20;
    const offset = ((criteria.page || 1) - 1) * limit;

    sql += ` LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
    params.push(limit, offset);

    return { sql, params };
  }
}

批次操作引擎

// packages/kyo-core/src/services/template-engine/BatchOperationEngine.ts
import { EventEmitter } from 'events';
import { Worker } from 'worker_threads';
import { Queue, QueueScheduler } from 'bullmq';

export class BatchOperationEngine extends EventEmitter {
  private readonly operationQueue: Queue;
  private readonly queueScheduler: QueueScheduler;
  private readonly workers: Map<string, Worker> = new Map();
  private readonly activeOperations: Map<string, BatchOperationStatus> = new Map();

  constructor(
    private readonly redis: Redis,
    private readonly config: BatchOperationConfig
  ) {
    super();

    this.operationQueue = new Queue('batch-operations', {
      connection: this.redis,
      defaultJobOptions: {
        removeOnComplete: 100,
        removeOnFail: 50,
        attempts: 3,
        backoff: {
          type: 'exponential',
          delay: 5000,
        }
      }
    });

    this.queueScheduler = new QueueScheduler('batch-operations', {
      connection: this.redis
    });

    this.setupWorkers();
    this.setupEventHandlers();
  }

  /**
   * 執行批次模板創建
   */
  async executeBatchTemplateCreation(
    operation: BatchTemplateCreationOperation
  ): Promise<string> {
    const operationId = crypto.randomUUID();

    // 驗證操作權限和限制
    await this.validateBatchOperation(operation);

    // 創建操作記錄
    const batchRecord = await this.createBatchRecord(operationId, {
      type: 'template_creation',
      tenantId: operation.context.tenantId,
      targetCount: operation.templates.length,
      executionConfig: {
        ...operation.config,
        operationId
      }
    });

    // 將操作加入佇列
    await this.operationQueue.add(
      'batch-template-creation',
      {
        operationId,
        operation,
        batchRecord
      },
      {
        priority: operation.priority || 0,
        delay: operation.scheduleAt ?
          operation.scheduleAt.getTime() - Date.now() : 0
      }
    );

    this.activeOperations.set(operationId, {
      id: operationId,
      type: 'template_creation',
      status: 'queued',
      progress: 0,
      startedAt: new Date(),
      estimatedCompletionTime: this.estimateCompletionTime(operation)
    });

    return operationId;
  }

  /**
   * 執行批次模板更新
   */
  async executeBatchTemplateUpdate(
    operation: BatchTemplateUpdateOperation
  ): Promise<string> {
    const operationId = crypto.randomUUID();

    try {
      // 衝突檢測
      const conflicts = await this.detectUpdateConflicts(operation);
      if (conflicts.length > 0 && operation.conflictResolution === 'abort') {
        throw new BatchOperationError(
          'Conflicts detected and resolution strategy is abort',
          { conflicts }
        );
      }

      // 備份受影響的模板
      const backupId = await this.createTemplateBackup(
        operation.targetTemplateIds,
        operation.context
      );

      // 分析更新影響
      const impactAnalysis = await this.analyzeUpdateImpact(operation);

      const batchRecord = await this.createBatchRecord(operationId, {
        type: 'template_update',
        tenantId: operation.context.tenantId,
        targetCount: operation.targetTemplateIds.length,
        executionConfig: {
          ...operation.config,
          operationId,
          backupId,
          impactAnalysis,
          conflicts
        }
      });

      await this.operationQueue.add(
        'batch-template-update',
        {
          operationId,
          operation,
          batchRecord
        },
        {
          priority: operation.priority || 0
        }
      );

      this.activeOperations.set(operationId, {
        id: operationId,
        type: 'template_update',
        status: 'queued',
        progress: 0,
        startedAt: new Date(),
        impactAnalysis,
        backupId
      });

      return operationId;

    } catch (error) {
      await this.logOperationError(operationId, error);
      throw error;
    }
  }

  /**
   * 智能衝突檢測
   */
  private async detectUpdateConflicts(
    operation: BatchTemplateUpdateOperation
  ): Promise<TemplateUpdateConflict[]> {
    const conflicts: TemplateUpdateConflict[] = [];

    // 檢查並發修改衝突
    const concurrentModifications = await this.checkConcurrentModifications(
      operation.targetTemplateIds
    );
    conflicts.push(...concurrentModifications);

    // 檢查依賴關係衝突
    const dependencyConflicts = await this.checkDependencyConflicts(
      operation.targetTemplateIds,
      operation.updateData
    );
    conflicts.push(...dependencyConflicts);

    // 檢查資料完整性衝突
    const integrityConflicts = await this.checkIntegrityConflicts(
      operation.targetTemplateIds,
      operation.updateData
    );
    conflicts.push(...integrityConflicts);

    // 檢查業務規則衝突
    const businessRuleConflicts = await this.checkBusinessRuleConflicts(
      operation.targetTemplateIds,
      operation.updateData,
      operation.context
    );
    conflicts.push(...businessRuleConflicts);

    return conflicts;
  }

  /**
   * 更新影響分析
   */
  private async analyzeUpdateImpact(
    operation: BatchTemplateUpdateOperation
  ): Promise<UpdateImpactAnalysis> {
    const affectedTemplates = await this.getTemplateDetails(
      operation.targetTemplateIds
    );

    // 分析子模板影響
    const childTemplateImpacts = await Promise.all(
      affectedTemplates.map(template =>
        this.analyzeChildTemplateImpact(template, operation.updateData)
      )
    );

    // 分析現有課程影響
    const courseImpacts = await Promise.all(
      affectedTemplates.map(template =>
        this.analyzeCourseImpact(template, operation.updateData)
      )
    );

    // 分析排程影響
    const scheduleImpacts = await Promise.all(
      affectedTemplates.map(template =>
        this.analyzeScheduleImpact(template, operation.updateData)
      )
    );

    return {
      totalAffectedTemplates: affectedTemplates.length,
      childTemplateImpacts: childTemplateImpacts.flat(),
      courseImpacts: courseImpacts.flat(),
      scheduleImpacts: scheduleImpacts.flat(),
      estimatedRecoveryTime: this.estimateRecoveryTime(
        childTemplateImpacts.flat().length +
        courseImpacts.flat().length +
        scheduleImpacts.flat().length
      ),
      riskLevel: this.calculateRiskLevel([
        ...childTemplateImpacts.flat(),
        ...courseImpacts.flat(),
        ...scheduleImpacts.flat()
      ])
    };
  }

  /**
   * 設置工作執行緒
   */
  private setupWorkers(): void {
    const workerCount = this.config.maxConcurrentWorkers || 4;

    for (let i = 0; i < workerCount; i++) {
      const worker = new Worker(
        path.join(__dirname, 'workers/batch-operation-worker.js'),
        {
          workerData: {
            workerId: i,
            config: this.config
          }
        }
      );

      worker.on('message', this.handleWorkerMessage.bind(this));
      worker.on('error', this.handleWorkerError.bind(this));
      worker.on('exit', this.handleWorkerExit.bind(this));

      this.workers.set(`worker-${i}`, worker);
    }
  }

  /**
   * 處理工作執行緒訊息
   */
  private handleWorkerMessage(message: WorkerMessage): void {
    const { type, operationId, data } = message;

    switch (type) {
      case 'progress':
        this.updateOperationProgress(operationId, data.progress);
        this.emit('progress', { operationId, progress: data.progress });
        break;

      case 'item_completed':
        this.updateOperationItem(operationId, data.itemId, 'completed', data.result);
        break;

      case 'item_failed':
        this.updateOperationItem(operationId, data.itemId, 'failed', data.error);
        break;

      case 'operation_completed':
        this.completeOperation(operationId, data.result);
        this.emit('completed', { operationId, result: data.result });
        break;

      case 'operation_failed':
        this.failOperation(operationId, data.error);
        this.emit('failed', { operationId, error: data.error });
        break;
    }
  }

  /**
   * 取得操作狀態
   */
  async getOperationStatus(operationId: string): Promise<BatchOperationStatus | null> {
    const activeStatus = this.activeOperations.get(operationId);
    if (activeStatus) {
      return activeStatus;
    }

    // 從資料庫獲取歷史狀態
    const dbStatus = await this.getOperationStatusFromDB(operationId);
    return dbStatus;
  }

  /**
   * 取消操作
   */
  async cancelOperation(operationId: string): Promise<void> {
    const operation = this.activeOperations.get(operationId);
    if (!operation) {
      throw new Error(`Operation ${operationId} not found`);
    }

    if (operation.status === 'completed' || operation.status === 'failed') {
      throw new Error(`Cannot cancel operation in ${operation.status} state`);
    }

    // 更新狀態
    operation.status = 'cancelling';
    this.activeOperations.set(operationId, operation);

    // 停止佇列中的任務
    await this.operationQueue.removeJobs(operationId);

    // 通知工作執行緒取消
    this.workers.forEach(worker => {
      worker.postMessage({
        type: 'cancel_operation',
        operationId
      });
    });

    // 更新資料庫狀態
    await this.updateOperationStatus(operationId, 'cancelled');

    this.activeOperations.delete(operationId);
    this.emit('cancelled', { operationId });
  }

  /**
   * 清理資源
   */
  async shutdown(): Promise<void> {
    // 等待活動操作完成
    const activeOperationIds = Array.from(this.activeOperations.keys());
    await Promise.all(
      activeOperationIds.map(id => this.waitForOperationCompletion(id, 30000))
    );

    // 關閉工作執行緒
    await Promise.all(
      Array.from(this.workers.values()).map(worker => worker.terminate())
    );

    // 關閉佇列
    await this.operationQueue.close();
    await this.queueScheduler.close();

    this.emit('shutdown');
  }
}

衝突解析引擎

// packages/kyo-core/src/services/template-engine/ConflictResolver.ts
export class ConflictResolver {
  private readonly strategies: Map<ConflictType, ConflictResolutionStrategy>;
  private readonly auditLogger: AuditLogger;

  constructor(
    dependencies: {
      auditLogger: AuditLogger;
    }
  ) {
    this.auditLogger = dependencies.auditLogger;
    this.strategies = new Map([
      ['version_conflict', new VersionConflictStrategy()],
      ['dependency_conflict', new DependencyConflictStrategy()],
      ['resource_conflict', new ResourceConflictStrategy()],
      ['business_rule_conflict', new BusinessRuleConflictStrategy()],
      ['data_integrity_conflict', new DataIntegrityConflictStrategy()]
    ]);
  }

  /**
   * 解析模板衝突
   */
  async resolveTemplateConflicts(
    conflicts: TemplateConflict[],
    resolution: ConflictResolutionConfig,
    context: TemplateEngineContext
  ): Promise<ConflictResolutionResult> {
    const results: ConflictResolutionResult = {
      resolved: [],
      unresolved: [],
      actions: [],
      warnings: []
    };

    for (const conflict of conflicts) {
      try {
        const strategy = this.strategies.get(conflict.type);
        if (!strategy) {
          results.unresolved.push({
            conflict,
            reason: `No resolution strategy for conflict type: ${conflict.type}`
          });
          continue;
        }

        const resolutionResult = await strategy.resolve(conflict, resolution, context);

        if (resolutionResult.success) {
          results.resolved.push({
            conflict,
            resolution: resolutionResult.resolution,
            actions: resolutionResult.actions
          });
          results.actions.push(...resolutionResult.actions);
        } else {
          results.unresolved.push({
            conflict,
            reason: resolutionResult.reason,
            suggestedActions: resolutionResult.suggestedActions
          });
        }

        if (resolutionResult.warnings) {
          results.warnings.push(...resolutionResult.warnings);
        }

      } catch (error) {
        await this.auditLogger.logError('conflict_resolution_failed', error, {
          ...context,
          conflict
        });

        results.unresolved.push({
          conflict,
          reason: `Resolution failed: ${error.message}`
        });
      }
    }

    // 記錄解析結果
    await this.auditLogger.logConflictResolution(results, context);

    return results;
  }

  /**
   * 預測潛在衝突
   */
  async predictConflicts(
    operation: TemplateOperation,
    context: TemplateEngineContext
  ): Promise<PredictedConflict[]> {
    const predictions: PredictedConflict[] = [];

    // 版本衝突預測
    const versionConflicts = await this.predictVersionConflicts(operation, context);
    predictions.push(...versionConflicts);

    // 依賴關係衝突預測
    const dependencyConflicts = await this.predictDependencyConflicts(operation, context);
    predictions.push(...dependencyConflicts);

    // 資源衝突預測
    const resourceConflicts = await this.predictResourceConflicts(operation, context);
    predictions.push(...resourceConflicts);

    // 業務規則衝突預測
    const businessConflicts = await this.predictBusinessRuleConflicts(operation, context);
    predictions.push(...businessConflicts);

    return predictions.sort((a, b) => b.probability - a.probability);
  }

  /**
   * 版本衝突預測
   */
  private async predictVersionConflicts(
    operation: TemplateOperation,
    context: TemplateEngineContext
  ): Promise<PredictedConflict[]> {
    const conflicts: PredictedConflict[] = [];

    if (operation.type === 'update' || operation.type === 'delete') {
      // 檢查並發修改的可能性
      const concurrentOperations = await this.getConcurrentOperations(
        operation.targetIds,
        context.tenantId
      );

      for (const concurrent of concurrentOperations) {
        if (this.hasOverlappingTargets(operation, concurrent)) {
          conflicts.push({
            type: 'version_conflict',
            probability: this.calculateConcurrencyConflictProbability(operation, concurrent),
            description: `Potential version conflict with concurrent operation ${concurrent.id}`,
            affectedResources: this.getOverlappingResources(operation, concurrent),
            suggestedMitigation: [
              'Enable optimistic locking',
              'Implement operation sequencing',
              'Use conflict-free replicated data types'
            ]
          });
        }
      }
    }

    return conflicts;
  }

  /**
   * 依賴關係衝突預測
   */
  private async predictDependencyConflicts(
    operation: TemplateOperation,
    context: TemplateEngineContext
  ): Promise<PredictedConflict[]> {
    const conflicts: PredictedConflict[] = [];

    // 檢查父子關係衝突
    if (operation.type === 'update' || operation.type === 'delete') {
      for (const targetId of operation.targetIds) {
        const dependents = await this.getTemplateDependents(targetId);
        const dependencies = await this.getTemplateDependencies(targetId);

        // 檢查是否會破壞子模板
        if (operation.type === 'delete' && dependents.length > 0) {
          conflicts.push({
            type: 'dependency_conflict',
            probability: 0.9,
            description: `Deleting template ${targetId} will orphan ${dependents.length} child templates`,
            affectedResources: dependents.map(d => d.id),
            suggestedMitigation: [
              'Cascade delete child templates',
              'Reassign child templates to different parent',
              'Convert child templates to standalone templates'
            ]
          });
        }

        // 檢查循環依賴的可能性
        if (operation.type === 'update' && operation.data.parentTemplateId) {
          const wouldCreateCycle = await this.wouldCreateDependencyCycle(
            targetId,
            operation.data.parentTemplateId,
            context
          );

          if (wouldCreateCycle) {
            conflicts.push({
              type: 'dependency_conflict',
              probability: 1.0,
              description: `Setting parent to ${operation.data.parentTemplateId} would create circular dependency`,
              affectedResources: [targetId, operation.data.parentTemplateId],
              suggestedMitigation: [
                'Choose different parent template',
                'Flatten template hierarchy',
                'Use composition instead of inheritance'
              ]
            });
          }
        }
      }
    }

    return conflicts;
  }
}

效能最佳化策略

Redis 快取層

// packages/kyo-core/src/services/template-engine/TemplateCacheManager.ts
export class TemplateCacheManager {
  private readonly redis: Redis;
  private readonly config: CacheConfiguration;
  private readonly metrics: CacheMetrics;

  constructor(redis: Redis, config: CacheConfiguration) {
    this.redis = redis;
    this.config = config;
    this.metrics = new CacheMetrics();
  }

  /**
   * 多層快取策略
   */
  async getTemplate(
    templateId: string,
    context: TemplateEngineContext
  ): Promise<CourseTemplate | null> {
    const cacheKey = this.generateTemplateKey(templateId, context.tenantId);

    try {
      // L1: 記憶體快取
      const memoryResult = this.getFromMemoryCache(cacheKey);
      if (memoryResult) {
        this.metrics.recordHit('memory');
        return memoryResult;
      }

      // L2: Redis 快取
      const redisResult = await this.redis.hgetall(cacheKey);
      if (Object.keys(redisResult).length > 0) {
        const template = this.deserializeTemplate(redisResult);
        this.setMemoryCache(cacheKey, template);
        this.metrics.recordHit('redis');
        return template;
      }

      this.metrics.recordMiss();
      return null;

    } catch (error) {
      this.metrics.recordError('cache_read', error);
      return null;
    }
  }

  /**
   * 智能預熱快取
   */
  async warmupCache(
    tenantId: string,
    strategy: WarmupStrategy = 'popular_first'
  ): Promise<WarmupResult> {
    const startTime = Date.now();
    let warmedCount = 0;
    let errors = 0;

    try {
      const templateIds = await this.getTemplateIdsForWarmup(tenantId, strategy);

      // 批次預熱
      const batches = this.chunkArray(templateIds, this.config.warmupBatchSize);

      for (const batch of batches) {
        const promises = batch.map(async (templateId) => {
          try {
            await this.preloadTemplate(templateId, tenantId);
            warmedCount++;
          } catch (error) {
            errors++;
            console.warn(`Failed to warmup template ${templateId}:`, error);
          }
        });

        await Promise.allSettled(promises);

        // 避免 Redis 過載
        if (batches.indexOf(batch) < batches.length - 1) {
          await this.delay(this.config.warmupDelayMs);
        }
      }

      return {
        totalTemplates: templateIds.length,
        warmedCount,
        errors,
        duration: Date.now() - startTime,
        strategy
      };

    } catch (error) {
      throw new Error(`Cache warmup failed: ${error.message}`);
    }
  }

  /**
   * 智能快取失效
   */
  async invalidateTemplateCache(
    templateId: string,
    tenantId: string,
    cascade: boolean = true
  ): Promise<void> {
    const keys = [this.generateTemplateKey(templateId, tenantId)];

    if (cascade) {
      // 找出所有相關的快取鍵
      const relatedKeys = await this.findRelatedCacheKeys(templateId, tenantId);
      keys.push(...relatedKeys);
    }

    // 批次刪除
    if (keys.length > 0) {
      await this.redis.del(...keys);

      // 同時清除記憶體快取
      keys.forEach(key => this.removeFromMemoryCache(key));
    }

    // 記錄失效事件
    this.metrics.recordInvalidation(templateId, keys.length);
  }

  /**
   * 快取效能監控
   */
  getMetrics(): CachePerformanceMetrics {
    return {
      hitRate: this.metrics.calculateHitRate(),
      missRate: this.metrics.calculateMissRate(),
      averageResponseTime: this.metrics.getAverageResponseTime(),
      memoryUsage: this.getMemoryCacheUsage(),
      redisUsage: this.getRedisUsageEstimate(),
      errorRate: this.metrics.getErrorRate(),
      topMissedKeys: this.metrics.getTopMissedKeys(10),
      recommendedActions: this.generateCacheRecommendations()
    };
  }

  /**
   * 產生快取建議
   */
  private generateCacheRecommendations(): CacheRecommendation[] {
    const recommendations: CacheRecommendation[] = [];
    const metrics = this.metrics;

    if (metrics.calculateHitRate() < 0.8) {
      recommendations.push({
        type: 'increase_ttl',
        priority: 'high',
        description: 'Hit rate is below 80%, consider increasing cache TTL',
        impact: 'Reduced database load and improved response times'
      });
    }

    if (metrics.getErrorRate() > 0.05) {
      recommendations.push({
        type: 'investigate_errors',
        priority: 'critical',
        description: 'Cache error rate above 5%, investigate Redis connectivity',
        impact: 'System stability and performance'
      });
    }

    const memoryUsage = this.getMemoryCacheUsage();
    if (memoryUsage > this.config.maxMemoryUsageBytes * 0.8) {
      recommendations.push({
        type: 'optimize_memory',
        priority: 'medium',
        description: 'Memory cache usage above 80%, consider LRU eviction',
        impact: 'Prevent memory leaks and maintain performance'
      });
    }

    return recommendations;
  }
}

資料庫連線池最佳化

// packages/kyo-core/src/services/database/DatabasePoolManager.ts
export class DatabasePoolManager {
  private readonly pools: Map<string, Pool> = new Map();
  private readonly config: DatabasePoolConfig;
  private readonly metrics: DatabaseMetrics;

  constructor(config: DatabasePoolConfig) {
    this.config = config;
    this.metrics = new DatabaseMetrics();
    this.initializePools();
  }

  /**
   * 智能連線池配置
   */
  private initializePools(): void {
    // 讀寫分離的連線池
    this.pools.set('write', new Pool({
      connectionString: this.config.writeDbUrl,
      max: this.config.writePool.maxConnections,
      min: this.config.writePool.minConnections,
      idleTimeoutMillis: this.config.writePool.idleTimeout,
      connectionTimeoutMillis: this.config.writePool.connectionTimeout,
      allowExitOnIdle: true,

      // 連線健康檢查
      pingTimeoutMillis: 30000,

      // 效能監控
      log: (message, level) => {
        this.metrics.recordPoolLog('write', message, level);
      }
    }));

    this.pools.set('read', new Pool({
      connectionString: this.config.readDbUrl || this.config.writeDbUrl,
      max: this.config.readPool.maxConnections,
      min: this.config.readPool.minConnections,
      idleTimeoutMillis: this.config.readPool.idleTimeout,
      connectionTimeoutMillis: this.config.readPool.connectionTimeout,
      allowExitOnIdle: true,

      // 讀取池可以有更長的連線時間
      statement_timeout: this.config.readPool.statementTimeout,
      query_timeout: this.config.readPool.queryTimeout,

      log: (message, level) => {
        this.metrics.recordPoolLog('read', message, level);
      }
    }));

    // 長時間執行查詢的專用連線池
    this.pools.set('analytics', new Pool({
      connectionString: this.config.analyticsDbUrl || this.config.readDbUrl || this.config.writeDbUrl,
      max: this.config.analyticsPool.maxConnections,
      min: 1,
      idleTimeoutMillis: this.config.analyticsPool.idleTimeout,
      connectionTimeoutMillis: this.config.analyticsPool.connectionTimeout,
      statement_timeout: 0, // 無超時限制
      query_timeout: 0,
      allowExitOnIdle: true
    }));

    // 設置連線池事件監聽
    this.pools.forEach((pool, name) => {
      pool.on('connect', (client) => {
        this.metrics.recordConnection(name, 'connected');
      });

      pool.on('remove', (client) => {
        this.metrics.recordConnection(name, 'removed');
      });

      pool.on('error', (err) => {
        this.metrics.recordError(name, err);
        console.error(`Database pool ${name} error:`, err);
      });
    });
  }

  /**
   * 智能查詢路由
   */
  async executeQuery<T = any>(
    sql: string,
    params: any[] = [],
    options: QueryOptions = {}
  ): Promise<QueryResult<T>> {
    const queryType = this.analyzeQueryType(sql);
    const poolName = this.selectOptimalPool(queryType, options);
    const pool = this.pools.get(poolName)!;

    const startTime = Date.now();
    let client: PoolClient | undefined;

    try {
      client = await pool.connect();

      // 設置查詢特定的參數
      if (options.timeout) {
        await client.query(`SET statement_timeout = ${options.timeout}`);
      }

      if (options.workMem) {
        await client.query(`SET work_mem = '${options.workMem}'`);
      }

      const result = await client.query(sql, params);

      const duration = Date.now() - startTime;
      this.metrics.recordQuery(poolName, queryType, duration, true);

      return result;

    } catch (error) {
      const duration = Date.now() - startTime;
      this.metrics.recordQuery(poolName, queryType, duration, false);

      throw this.enhanceQueryError(error, sql, params, poolName);
    } finally {
      if (client) {
        client.release();
      }
    }
  }

  /**
   * 分析查詢類型
   */
  private analyzeQueryType(sql: string): QueryType {
    const normalizedSql = sql.trim().toLowerCase();

    if (normalizedSql.startsWith('select')) {
      if (normalizedSql.includes('count(') ||
          normalizedSql.includes('sum(') ||
          normalizedSql.includes('avg(') ||
          normalizedSql.includes('group by')) {
        return 'analytics';
      }
      return 'read';
    }

    if (normalizedSql.startsWith('insert') ||
        normalizedSql.startsWith('update') ||
        normalizedSql.startsWith('delete')) {
      return 'write';
    }

    return 'other';
  }

  /**
   * 選擇最佳連線池
   */
  private selectOptimalPool(
    queryType: QueryType,
    options: QueryOptions
  ): string {
    // 強制指定連線池
    if (options.preferredPool) {
      return options.preferredPool;
    }

    // 根據查詢類型自動選擇
    switch (queryType) {
      case 'read':
        // 檢查讀取池負載
        const readPoolStats = this.metrics.getPoolStats('read');
        if (readPoolStats.activeConnections > readPoolStats.maxConnections * 0.8) {
          // 讀取池負載過高，使用寫入池分攤
          return 'write';
        }
        return 'read';

      case 'write':
        return 'write';

      case 'analytics':
        // 長時間查詢使用專用池
        if (options.expectedDuration && options.expectedDuration > 30000) {
          return 'analytics';
        }
        return 'read';

      default:
        return 'write';
    }
  }

  /**
   * 連線池健康監控
   */
  async getPoolHealth(): Promise<PoolHealthStatus> {
    const healthStatus: PoolHealthStatus = {
      pools: {},
      overall: 'healthy',
      recommendations: []
    };

    for (const [poolName, pool] of this.pools) {
      const stats = this.metrics.getPoolStats(poolName);
      const poolHealth = this.assessPoolHealth(stats);

      healthStatus.pools[poolName] = {
        status: poolHealth.status,
        activeConnections: pool.totalCount,
        idleConnections: pool.idleCount,
        waitingClients: pool.waitingCount,
        totalConnections: pool.totalCount,
        maxConnections: pool.options.max || 0,
        errorRate: stats.errorRate,
        averageQueryTime: stats.averageQueryTime,
        recommendations: poolHealth.recommendations
      };

      // 彙總建議
      healthStatus.recommendations.push(...poolHealth.recommendations);
    }

    // 計算整體健康狀態
    const poolStatuses = Object.values(healthStatus.pools).map(p => p.status);
    if (poolStatuses.some(s => s === 'critical')) {
      healthStatus.overall = 'critical';
    } else if (poolStatuses.some(s => s === 'warning')) {
      healthStatus.overall = 'warning';
    }

    return healthStatus;
  }

  /**
   * 自動調整連線池大小
   */
  async optimizePoolSizes(): Promise<PoolOptimizationResult> {
    const optimizations: PoolOptimizationResult = {
      changes: [],
      estimatedImpact: {}
    };

    for (const [poolName, pool] of this.pools) {
      const stats = this.metrics.getPoolStats(poolName);
      const recommendation = this.generatePoolSizeRecommendation(poolName, stats);

      if (recommendation) {
        // 動態調整連線池大小（PostgreSQL pool 支援）
        const currentMax = pool.options.max || 10;
        const newMax = recommendation.recommendedMax;

        if (newMax !== currentMax) {
          // 注意：實際的連線池調整需要重新創建連線池
          // 這裡只是記錄建議，實際調整需要在維護窗口執行
          optimizations.changes.push({
            poolName,
            currentMax,
            recommendedMax: newMax,
            reason: recommendation.reason,
            expectedImpact: recommendation.expectedImpact
          });
        }
      }
    }

    return optimizations;
  }
}

監控與告警系統

系統健康度監控

// packages/kyo-core/src/services/monitoring/TemplateSystemMonitor.ts
export class TemplateSystemMonitor {
  private readonly metrics: SystemMetrics;
  private readonly alertManager: AlertManager;
  private readonly healthChecks: Map<string, HealthCheck>;
  private monitoringInterval: NodeJS.Timeout | null = null;

  constructor(
    dependencies: {
      metrics: SystemMetrics;
      alertManager: AlertManager;
    }
  ) {
    this.metrics = dependencies.metrics;
    this.alertManager = dependencies.alertManager;
    this.healthChecks = new Map();
    this.initializeHealthChecks();
  }

  /**
   * 初始化健康檢查
   */
  private initializeHealthChecks(): void {
    // 資料庫健康檢查
    this.healthChecks.set('database', new DatabaseHealthCheck({
      timeout: 5000,
      retries: 3,
      severity: 'critical'
    }));

    // Redis 健康檢查
    this.healthChecks.set('redis', new RedisHealthCheck({
      timeout: 3000,
      retries: 3,
      severity: 'critical'
    }));

    // 模板引擎健康檢查
    this.healthChecks.set('template_engine', new TemplateEngineHealthCheck({
      timeout: 10000,
      retries: 2,
      severity: 'high'
    }));

    // 批次操作佇列健康檢查
    this.healthChecks.set('batch_queue', new BatchQueueHealthCheck({
      timeout: 5000,
      retries: 3,
      severity: 'high'
    }));

    // 外部服務健康檢查
    this.healthChecks.set('external_services', new ExternalServicesHealthCheck({
      timeout: 15000,
      retries: 2,
      severity: 'medium'
    }));
  }

  /**
   * 啟動系統監控
   */
  startMonitoring(intervalMs: number = 30000): void {
    if (this.monitoringInterval) {
      clearInterval(this.monitoringInterval);
    }

    this.monitoringInterval = setInterval(async () => {
      try {
        await this.performHealthChecks();
        await this.collectSystemMetrics();
        await this.evaluateAlertConditions();
      } catch (error) {
        console.error('Monitoring cycle failed:', error);
      }
    }, intervalMs);

    console.log(`Template system monitoring started (interval: ${intervalMs}ms)`);
  }

  /**
   * 執行健康檢查
   */
  private async performHealthChecks(): Promise<void> {
    const healthResults: Map<string, HealthCheckResult> = new Map();

    // 並行執行所有健康檢查
    const checkPromises = Array.from(this.healthChecks.entries()).map(
      async ([name, check]) => {
        try {
          const result = await check.execute();
          healthResults.set(name, result);
        } catch (error) {
          healthResults.set(name, {
            status: 'unhealthy',
            message: error.message,
            timestamp: new Date(),
            duration: 0,
            severity: check.severity
          });
        }
      }
    );

    await Promise.allSettled(checkPromises);

    // 處理健康檢查結果
    for (const [name, result] of healthResults) {
      this.metrics.recordHealthCheck(name, result);

      if (result.status === 'unhealthy') {
        await this.alertManager.triggerAlert({
          type: 'health_check_failed',
          severity: result.severity,
          component: name,
          message: result.message,
          timestamp: result.timestamp,
          metadata: {
            healthCheckName: name,
            duration: result.duration,
            retryCount: result.retryCount
          }
        });
      }
    }
  }

  /**
   * 收集系統指標
   */
  private async collectSystemMetrics(): Promise<void> {
    const startTime = Date.now();

    try {
      // 模板相關指標
      const templateMetrics = await this.collectTemplateMetrics();
      this.metrics.recordTemplateMetrics(templateMetrics);

      // 批次操作指標
      const batchMetrics = await this.collectBatchOperationMetrics();
      this.metrics.recordBatchMetrics(batchMetrics);

      // 效能指標
      const performanceMetrics = await this.collectPerformanceMetrics();
      this.metrics.recordPerformanceMetrics(performanceMetrics);

      // 資源使用指標
      const resourceMetrics = await this.collectResourceMetrics();
      this.metrics.recordResourceMetrics(resourceMetrics);

      const collectionDuration = Date.now() - startTime;
      this.metrics.recordMetricCollection(collectionDuration);

    } catch (error) {
      console.error('Failed to collect system metrics:', error);
      this.metrics.recordMetricCollectionError(error);
    }
  }

  /**
   * 評估告警條件
   */
  private async evaluateAlertConditions(): Promise<void> {
    const currentMetrics = this.metrics.getCurrentSnapshot();

    // 模板系統告警規則
    const alertRules: AlertRule[] = [
      {
        name: 'high_template_creation_failure_rate',
        condition: () => currentMetrics.templateCreationFailureRate > 0.1,
        severity: 'high',
        message: 'Template creation failure rate exceeds 10%'
      },
      {
        name: 'batch_operation_queue_backlog',
        condition: () => currentMetrics.batchQueueLength > 100,
        severity: 'medium',
        message: 'Batch operation queue backlog exceeds 100 items'
      },
      {
        name: 'database_connection_pool_exhaustion',
        condition: () => currentMetrics.dbPoolUtilization > 0.9,
        severity: 'critical',
        message: 'Database connection pool utilization above 90%'
      },
      {
        name: 'redis_memory_usage_high',
        condition: () => currentMetrics.redisMemoryUsage > 0.8,
        severity: 'high',
        message: 'Redis memory usage exceeds 80%'
      },
      {
        name: 'response_time_degradation',
        condition: () => currentMetrics.averageResponseTime > 2000,
        severity: 'medium',
        message: 'Average response time exceeds 2 seconds'
      }
    ];

    // 評估所有規則
    for (const rule of alertRules) {
      try {
        if (rule.condition()) {
          await this.alertManager.triggerAlert({
            type: 'metric_threshold_exceeded',
            name: rule.name,
            severity: rule.severity,
            message: rule.message,
            timestamp: new Date(),
            metadata: {
              currentMetrics: this.metrics.getRelevantMetrics(rule.name),
              threshold: rule.threshold,
              actualValue: rule.getCurrentValue?.(currentMetrics)
            }
          });
        }
      } catch (error) {
        console.error(`Failed to evaluate alert rule ${rule.name}:`, error);
      }
    }
  }

  /**
   * 生成系統報告
   */
  async generateSystemReport(
    timeRange: TimeRange,
    format: ReportFormat = 'json'
  ): Promise<SystemReport> {
    const report: SystemReport = {
      generatedAt: new Date(),
      timeRange,
      summary: await this.generateSystemSummary(timeRange),
      healthStatus: await this.getSystemHealthStatus(),
      performanceMetrics: await this.getPerformanceReport(timeRange),
      alertSummary: await this.getAlertSummary(timeRange),
      recommendations: await this.generateSystemRecommendations(),
      trendsAnalysis: await this.analyzeTrends(timeRange)
    };

    if (format === 'html') {
      return this.formatReportAsHtml(report);
    }

    return report;
  }

  /**
   * 系統建議生成
   */
  private async generateSystemRecommendations(): Promise<SystemRecommendation[]> {
    const recommendations: SystemRecommendation[] = [];
    const metrics = this.metrics.getCurrentSnapshot();

    // 效能建議
    if (metrics.averageResponseTime > 1000) {
      recommendations.push({
        category: 'performance',
        priority: 'high',
        title: 'Optimize response time',
        description: 'Average response time is above 1 second',
        suggestedActions: [
          'Review slow queries and add indexes',
          'Implement query result caching',
          'Consider database connection pooling optimization',
          'Review application logic for bottlenecks'
        ],
        estimatedImpact: 'Could reduce response time by 30-50%'
      });
    }

    // 資源使用建議
    if (metrics.dbPoolUtilization > 0.7) {
      recommendations.push({
        category: 'resource',
        priority: 'medium',
        title: 'Database connection pool scaling',
        description: 'Database connection pool utilization is above 70%',
        suggestedActions: [
          'Increase database connection pool size',
          'Implement connection lifecycle management',
          'Review query patterns for optimization',
          'Consider read replicas for scaling'
        ],
        estimatedImpact: 'Improved system stability and throughput'
      });
    }

    // 快取建議
    const cacheHitRate = metrics.cacheHitRate || 0;
    if (cacheHitRate < 0.8) {
      recommendations.push({
        category: 'caching',
        priority: 'medium',
        title: 'Improve cache efficiency',
        description: `Cache hit rate is ${(cacheHitRate * 100).toFixed(1)}%, below optimal 80%`,
        suggestedActions: [
          'Review cache key strategies',
          'Increase cache TTL for stable data',
          'Imple