經過 Day 12 的會員管理服務實作，我們已經建立了完整的會員 CRUD 與查詢系統。今天我們要實作教練管理與排課服務 (Trainer Service)，這是健身房 SaaS 的另一個核心微服務。
教練管理不只是簡單的人員管理，還包含複雜的排課邏輯、專業技能認證、薪資計算、教學評價等業務功能。我們將建立一個高度彈性的教練服務系統，支援多樣化的健身房營運需求。
// 教練角色與權限模型
interface TrainerRole {
id: string;
name: string;
level: 'intern' | 'junior' | 'senior' | 'lead' | 'master';
specializations: TrainerSpecialization[];
permissions: TrainerPermission[];
hourlyRate: {
base: number;
premium: number; // 熱門時段加成
groupClass: number; // 團體課程費率
personalTraining: number; // 個人訓練費率
};
}
interface TrainerSpecialization {
category: 'strength' | 'cardio' | 'yoga' | 'pilates' | 'dance' | 'martial_arts' | 'rehabilitation';
subCategory: string; // 如：Hatha Yoga、拳擊、復健運動
certificationLevel: 'basic' | 'intermediate' | 'advanced' | 'master';
certificationBody: string; // 認證機構
certificationNumber: string;
expiryDate: Date;
isActive: boolean;
}
type TrainerPermission =
| 'schedule_classes' // 安排課程
| 'modify_schedule' // 修改課表
| 'access_member_data' // 存取會員資料
| 'create_workout_plans' // 建立訓練計畫
| 'manage_equipment' // 管理器材
| 'view_analytics' // 查看分析報告
| 'mentor_junior_trainers'; // 指導新進教練
// 排課系統業務邏輯
interface SchedulingRequirements {
// 時間約束
timeConstraints: {
workingHours: { start: string; end: string; };
availableDays: number[]; // 0-6 (週日到週六)
breakTime: number; // 課程間休息時間 (分鐘)
maxConsecutiveHours: number; // 連續授課上限
preferredTimeSlots: TimeSlot[];
};
// 容量限制
capacityLimits: {
maxStudentsPerClass: number;
maxClassesPerDay: number;
maxClassesPerWeek: number;
minStudentsToRun: number; // 最少開班人數
};
// 技能匹配
skillMatching: {
requiredCertifications: string[];
experienceLevel: 'beginner' | 'intermediate' | 'advanced';
equipmentProficiency: string[];
};
// 商業邏輯
businessRules: {
substitutePolicy: 'allowed' | 'restricted' | 'forbidden';
cancellationDeadline: number; // 取消課程的最短時間 (小時)
reschedulingRules: ReschedulingRule[];
paymentTriggers: PaymentTrigger[];
};
}
// packages/kyo-core/src/services/trainer-service.ts
import { TenantConnectionManager } from '../database/tenant-connection';
import { KyoError } from '@kyong/kyo-types';
export class TrainerService {
constructor(private connectionManager: TenantConnectionManager) {}
// 教練管理核心方法
async createTrainer(tenantId: string, trainerData: CreateTrainerRequest): Promise<Trainer> {
const pool = await this.connectionManager.getConnection(tenantId);
try {
await pool.query('BEGIN');
// 1. 建立教練基本資料
const trainer = await this.insertTrainerBasicInfo(pool, trainerData);
// 2. 建立專業認證記錄
await this.insertTrainerCertifications(pool, trainer.id, trainerData.certifications);
// 3. 設定可用時間
await this.insertTrainerAvailability(pool, trainer.id, trainerData.availability);
// 4. 初始化薪資設定
await this.insertTrainerCompensation(pool, trainer.id, trainerData.compensation);
await pool.query('COMMIT');
// 5. 觸發事件 (用於其他服務)
await this.publishTrainerCreatedEvent(tenantId, trainer);
return trainer;
} catch (error) {
await pool.query('ROLLBACK');
throw new KyoError('E_TRAINER_CREATE_FAILED', `Failed to create trainer: ${error.message}`, 500);
}
}
async searchTrainers(tenantId: string, params: TrainerSearchParams): Promise<TrainerSearchResult> {
const pool = await this.connectionManager.getConnection(tenantId);
// 建構複雜查詢條件
const queryBuilder = new TrainerQueryBuilder(params);
const { query, values } = queryBuilder.build();
const result = await pool.query(query, values);
return {
trainers: result.rows.map(row => this.mapRowToTrainer(row)),
pagination: await this.calculatePagination(pool, params),
aggregations: await this.calculateTrainerStats(pool, params),
};
}
// 排課相關方法
async getTrainerAvailability(
tenantId: string,
trainerId: string,
dateRange: { start: Date; end: Date }
): Promise<TrainerAvailability[]> {
const pool = await this.connectionManager.getConnection(tenantId);
// 查詢基礎可用時間
const baseAvailability = await this.getBaseAvailability(pool, trainerId, dateRange);
// 查詢已排課程
const existingClasses = await this.getExistingClasses(pool, trainerId, dateRange);
// 查詢請假記錄
const leaveRecords = await this.getLeaveRecords(pool, trainerId, dateRange);
// 計算實際可用時間
return this.calculateAvailableSlots(baseAvailability, existingClasses, leaveRecords);
}
async scheduleClass(tenantId: string, scheduleRequest: ScheduleClassRequest): Promise<ScheduledClass> {
const pool = await this.connectionManager.getConnection(tenantId);
try {
await pool.query('BEGIN');
// 1. 驗證教練可用性
await this.validateTrainerAvailability(pool, scheduleRequest);
// 2. 驗證教練技能匹配
await this.validateTrainerSkills(pool, scheduleRequest);
// 3. 檢查容量限制
await this.validateCapacityLimits(pool, scheduleRequest);
// 4. 建立課程排程
const scheduledClass = await this.insertScheduledClass(pool, scheduleRequest);
// 5. 更新教練工作負荷
await this.updateTrainerWorkload(pool, scheduleRequest.trainerId, scheduleRequest.duration);
await pool.query('COMMIT');
// 6. 發送通知事件
await this.publishClassScheduledEvent(tenantId, scheduledClass);
return scheduledClass;
} catch (error) {
await pool.query('ROLLBACK');
throw error;
}
}
// 高級功能：智能排課建議
async getSchedulingSuggestions(
tenantId: string,
requirements: SchedulingRequirements
): Promise<SchedulingSuggestion[]> {
const pool = await this.connectionManager.getConnection(tenantId);
// 1. 找出符合技能要求的教練
const qualifiedTrainers = await this.findQualifiedTrainers(pool, requirements);
// 2. 分析每位教練的可用時間
const suggestions: SchedulingSuggestion[] = [];
for (const trainer of qualifiedTrainers) {
const availability = await this.getTrainerAvailability(
tenantId,
trainer.id,
requirements.dateRange
);
// 3. 使用演算法找出最佳時段
const optimalSlots = await this.findOptimalTimeSlots(
trainer,
availability,
requirements
);
suggestions.push({
trainer,
suggestedSlots: optimalSlots,
confidence: this.calculateConfidence(trainer, requirements),
estimatedRevenue: this.calculateEstimatedRevenue(optimalSlots, trainer.hourlyRate),
});
}
// 4. 按照綜合評分排序
return suggestions.sort((a, b) => b.confidence - a.confidence);
}
// 教練效能分析
async getTrainerPerformanceMetrics(
tenantId: string,
trainerId: string,
period: { start: Date; end: Date }
): Promise<TrainerPerformanceMetrics> {
const pool = await this.connectionManager.getConnection(tenantId);
const [
classStats,
memberSatisfaction,
revenueContribution,
attendanceRates,
skillDevelopment
] = await Promise.all([
this.getClassStatistics(pool, trainerId, period),
this.getMemberSatisfactionScore(pool, trainerId, period),
this.getRevenueContribution(pool, trainerId, period),
this.getAttendanceRates(pool, trainerId, period),
this.getSkillDevelopmentProgress(pool, trainerId, period),
]);
return {
trainerId,
period,
summary: {
totalClassesTaught: classStats.total,
averageClassSize: classStats.averageSize,
satisfactionScore: memberSatisfaction.averageScore,
revenueGenerated: revenueContribution.total,
attendanceRate: attendanceRates.average,
skillImprovements: skillDevelopment.newCertifications.length,
},
detailed: {
classBreakdown: classStats.breakdown,
satisfactionTrends: memberSatisfaction.trends,
revenueByService: revenueContribution.breakdown,
attendancePatterns: attendanceRates.patterns,
certificationProgress: skillDevelopment.progress,
},
recommendations: await this.generatePerformanceRecommendations(
classStats,
memberSatisfaction,
revenueContribution
),
};
}
private async findOptimalTimeSlots(
trainer: Trainer,
availability: TrainerAvailability[],
requirements: SchedulingRequirements
): Promise<OptimalTimeSlot[]> {
// 智能排課演算法
const algorithm = new SchedulingOptimizationAlgorithm({
trainer,
availability,
requirements,
weightFactors: {
trainerPreference: 0.3, // 教練偏好時段
memberDemand: 0.4, // 會員需求熱度
revenueOptimization: 0.2, // 收益最佳化
resourceUtilization: 0.1, // 資源利用率
},
});
return await algorithm.findOptimalSlots();
}
}
// packages/kyo-core/src/algorithms/scheduling-optimization.ts
export class SchedulingOptimizationAlgorithm {
constructor(private config: SchedulingConfig) {}
async findOptimalSlots(): Promise<OptimalTimeSlot[]> {
// 1. 預處理：建立時間網格
const timeGrid = this.createTimeGrid();
// 2. 分析歷史數據
const historicalData = await this.analyzeHistoricalDemand();
// 3. 計算每個時段的綜合評分
const scoredSlots = timeGrid.map(slot => ({
...slot,
score: this.calculateSlotScore(slot, historicalData),
}));
// 4. 使用遺傳演算法或動態規劃找出最佳組合
return this.optimizeSchedule(scoredSlots);
}
private calculateSlotScore(
slot: TimeSlot,
historicalData: HistoricalDemandData
): number {
const weights = this.config.weightFactors;
// 教練偏好評分
const trainerPreferenceScore = this.calculateTrainerPreference(slot);
// 會員需求評分
const memberDemandScore = this.calculateMemberDemand(slot, historicalData);
// 收益評分
const revenueScore = this.calculateRevenueScore(slot);
// 資源利用率評分
const utilizationScore = this.calculateUtilizationScore(slot);
return (
trainerPreferenceScore * weights.trainerPreference +
memberDemandScore * weights.memberDemand +
revenueScore * weights.revenueOptimization +
utilizationScore * weights.resourceUtilization
);
}
private calculateMemberDemand(
slot: TimeSlot,
historicalData: HistoricalDemandData
): number {
// 分析歷史數據中該時段的會員需求
const dayOfWeek = slot.startTime.getDay();
const hourOfDay = slot.startTime.getHours();
const historicalDemand = historicalData.getDemand(dayOfWeek, hourOfDay);
const seasonalFactor = this.getSeasonalFactor(slot.startTime);
const trendFactor = this.getTrendFactor(slot.startTime);
return historicalDemand * seasonalFactor * trendFactor;
}
private optimizeSchedule(scoredSlots: ScoredTimeSlot[]): OptimalTimeSlot[] {
// 使用動態規劃或遺傳演算法進行最佳化
const optimizer = new GeneticAlgorithmOptimizer({
population: 100,
generations: 50,
mutationRate: 0.1,
crossoverRate: 0.8,
});
return optimizer.optimize(scoredSlots, this.config.constraints);
}
}
// 教練薪資計算系統
export class TrainerCompensationService {
async calculateTrainerPay(
tenantId: string,
trainerId: string,
period: PayPeriod
): Promise<TrainerPayCalculation> {
const pool = await this.connectionManager.getConnection(tenantId);
// 1. 取得教練的薪資設定
const compensationConfig = await this.getTrainerCompensationConfig(pool, trainerId);
// 2. 取得期間內的工作記錄
const workRecords = await this.getWorkRecords(pool, trainerId, period);
// 3. 計算基本薪資
const basePay = this.calculateBasePay(workRecords, compensationConfig);
// 4. 計算績效獎金
const performanceBonus = await this.calculatePerformanceBonus(
pool,
trainerId,
period,
compensationConfig
);
// 5. 計算加班費
const overtimePay = this.calculateOvertimePay(workRecords, compensationConfig);
// 6. 計算扣除項目
const deductions = await this.calculateDeductions(pool, trainerId, period);
const totalPay = basePay + performanceBonus + overtimePay - deductions;
return {
trainerId,
period,
breakdown: {
basePay,
performanceBonus,
overtimePay,
deductions,
totalPay,
},
workSummary: {
totalHours: workRecords.reduce((sum, record) => sum + record.hours, 0),
classesCount: workRecords.filter(r => r.type === 'class').length,
personalTrainingCount: workRecords.filter(r => r.type === 'personal').length,
overtimeHours: workRecords.reduce((sum, record) => sum + (record.overtimeHours || 0), 0),
},
taxInformation: await this.calculateTaxes(totalPay, compensationConfig.taxSettings),
};
}
private async calculatePerformanceBonus(
pool: any,
trainerId: string,
period: PayPeriod,
config: CompensationConfig
): Promise<number> {
const performanceMetrics = await this.getPerformanceMetrics(pool, trainerId, period);
let bonus = 0;
// 會員滿意度獎金
if (performanceMetrics.satisfactionScore >= config.bonusThresholds.satisfaction) {
bonus += config.bonusRates.satisfactionBonus;
}
// 出席率獎金
if (performanceMetrics.attendanceRate >= config.bonusThresholds.attendance) {
bonus += config.bonusRates.attendanceBonus;
}
// 新會員推薦獎金
bonus += performanceMetrics.newMemberReferrals * config.bonusRates.referralBonus;
// 課程創新獎金
if (performanceMetrics.newClassesCreated > 0) {
bonus += performanceMetrics.newClassesCreated * config.bonusRates.innovationBonus;
}
return bonus;
}
}
// 教練評價管理
export class TrainerReviewService {
async submitTrainerReview(
tenantId: string,
reviewData: TrainerReviewRequest
): Promise<TrainerReview> {
const pool = await this.connectionManager.getConnection(tenantId);
try {
await pool.query('BEGIN');
// 1. 驗證評價者權限 (只有上過課的會員可以評價)
await this.validateReviewerEligibility(pool, reviewData);
// 2. 檢查重複評價 (同一課程只能評價一次)
await this.checkDuplicateReview(pool, reviewData);
// 3. 儲存評價
const review = await this.insertReview(pool, reviewData);
// 4. 更新教練總評分
await this.updateTrainerOverallRating(pool, reviewData.trainerId);
// 5. 觸發通知事件
await this.notifyTrainerAndManagement(tenantId, review);
await pool.query('COMMIT');
return review;
} catch (error) {
await pool.query('ROLLBACK');
throw error;
}
}
async getTrainerReviewAnalytics(
tenantId: string,
trainerId: string,
period?: { start: Date; end: Date }
): Promise<TrainerReviewAnalytics> {
const pool = await this.connectionManager.getConnection(tenantId);
const [
overallStats,
categoryBreakdown,
sentimentAnalysis,
trends,
competitorComparison
] = await Promise.all([
this.getOverallReviewStats(pool, trainerId, period),
this.getReviewCategoryBreakdown(pool, trainerId, period),
this.analyzeSentiment(pool, trainerId, period),
this.getReviewTrends(pool, trainerId, period),
this.getCompetitorComparison(pool, trainerId, period),
]);
return {
summary: {
totalReviews: overallStats.count,
averageRating: overallStats.averageRating,
recommendationRate: overallStats.recommendationRate,
responseRate: overallStats.responseRate,
},
breakdown: categoryBreakdown,
sentiment: sentimentAnalysis,
trends,
benchmarking: competitorComparison,
actionableInsights: await this.generateActionableInsights(
overallStats,
categoryBreakdown,
sentimentAnalysis
),
};
}
private async analyzeSentiment(
pool: any,
trainerId: string,
period?: { start: Date; end: Date }
): Promise<SentimentAnalysis> {
// 使用 NLP 分析評論情感
const reviews = await this.getReviewTexts(pool, trainerId, period);
const sentimentScores = await Promise.all(
reviews.map(review => this.analyzeTextSentiment(review.comment))
);
const positive = sentimentScores.filter(score => score > 0.1).length;
const neutral = sentimentScores.filter(score => score >= -0.1 && score <= 0.1).length;
const negative = sentimentScores.filter(score => score < -0.1).length;
// 提取關鍵詞和主題
const keywords = await this.extractKeywords(reviews.map(r => r.comment));
const topics = await this.extractTopics(reviews.map(r => r.comment));
return {
distribution: {
positive: positive / sentimentScores.length,
neutral: neutral / sentimentScores.length,
negative: negative / sentimentScores.length,
},
keyPositives: keywords.positive,
keyNegatives: keywords.negative,
commonTopics: topics,
overallSentiment: sentimentScores.reduce((sum, score) => sum + score, 0) / sentimentScores.length,
};
}
}
// apps/kyo-otp-service/src/routes/trainer-routes.ts
export async function trainerRoutes(fastify: FastifyInstance) {
// 教練 CRUD 操作
fastify.post('/trainers', {
schema: {
body: CreateTrainerRequestSchema,
response: { 201: TrainerSchema },
},
preHandler: [fastify.authenticate, fastify.authorize(['admin', 'manager'])],
}, async (request) => {
const trainerData = request.body as CreateTrainerRequest;
return await trainerService.createTrainer(request.tenantId, trainerData);
});
fastify.get('/trainers', {
schema: {
querystring: TrainerSearchParamsSchema,
response: { 200: TrainerSearchResultSchema },
},
preHandler: [fastify.authenticate],
}, async (request) => {
const params = request.query as TrainerSearchParams;
return await trainerService.searchTrainers(request.tenantId, params);
});
// 排課相關 API
fastify.get('/trainers/:id/availability', {
schema: {
params: Type.Object({ id: Type.String() }),
querystring: Type.Object({
start: Type.String({ format: 'date-time' }),
end: Type.String({ format: 'date-time' }),
}),
response: { 200: Type.Array(TrainerAvailabilitySchema) },
},
preHandler: [fastify.authenticate],
}, async (request) => {
const { id } = request.params as { id: string };
const { start, end } = request.query as { start: string; end: string };
return await trainerService.getTrainerAvailability(
request.tenantId,
id,
{ start: new Date(start), end: new Date(end) }
);
});
fastify.post('/trainers/schedule', {
schema: {
body: ScheduleClassRequestSchema,
response: { 201: ScheduledClassSchema },
},
preHandler: [fastify.authenticate, fastify.authorize(['admin', 'manager', 'staff'])],
}, async (request) => {
const scheduleRequest = request.body as ScheduleClassRequest;
return await trainerService.scheduleClass(request.tenantId, scheduleRequest);
});
// 智能排課建議
fastify.post('/trainers/scheduling-suggestions', {
schema: {
body: SchedulingRequirementsSchema,
response: { 200: Type.Array(SchedulingSuggestionSchema) },
},
preHandler: [fastify.authenticate, fastify.authorize(['admin', 'manager'])],
}, async (request) => {
const requirements = request.body as SchedulingRequirements;
return await trainerService.getSchedulingSuggestions(request.tenantId, requirements);
});
// 績效分析
fastify.get('/trainers/:id/performance', {
schema: {
params: Type.Object({ id: Type.String() }),
querystring: Type.Object({
start: Type.String({ format: 'date-time' }),
end: Type.String({ format: 'date-time' }),
}),
response: { 200: TrainerPerformanceMetricsSchema },
},
preHandler: [fastify.authenticate, fastify.authorize(['admin', 'manager'])],
}, async (request) => {
const { id } = request.params as { id: string };
const { start, end } = request.query as { start: string; end: string };
return await trainerService.getTrainerPerformanceMetrics(
request.tenantId,
id,
{ start: new Date(start), end: new Date(end) }
);
});
// 教練評價系統
fastify.post('/trainers/:id/reviews', {
schema: {
params: Type.Object({ id: Type.String() }),
body: TrainerReviewRequestSchema,
response: { 201: TrainerReviewSchema },
},
preHandler: [fastify.authenticate],
}, async (request) => {
const { id } = request.params as { id: string };
const reviewData = { ...request.body, trainerId: id } as TrainerReviewRequest;
return await trainerReviewService.submitTrainerReview(request.tenantId, reviewData);
});
fastify.get('/trainers/:id/reviews/analytics', {
schema: {
params: Type.Object({ id: Type.String() }),
querystring: Type.Optional(Type.Object({
start: Type.String({ format: 'date-time' }),
end: Type.String({ format: 'date-time' }),
})),
response: { 200: TrainerReviewAnalyticsSchema },
},
preHandler: [fastify.authenticate, fastify.authorize(['admin', 'manager'])],
}, async (request) => {
const { id } = request.params as { id: string };
const query = request.query as { start?: string; end?: string };
const period = query.start && query.end ? {
start: new Date(query.start),
end: new Date(query.end),
} : undefined;
return await trainerReviewService.getTrainerReviewAnalytics(request.tenantId, id, period);
});
}
-- 教練基本資料表
CREATE TABLE trainers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL,
employee_id VARCHAR(20) UNIQUE NOT NULL,
-- 個人資訊
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
phone VARCHAR(20),
date_of_birth DATE,
gender VARCHAR(10),
-- 職業資訊
hire_date DATE NOT NULL,
employment_status VARCHAR(20) DEFAULT 'active',
employment_type VARCHAR(20) DEFAULT 'full_time', -- full_time, part_time, contract
trainer_level VARCHAR(20) DEFAULT 'junior',
-- 聯絡與緊急資訊
address JSONB,
emergency_contact JSONB,
-- 系統資訊
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
created_by UUID,
is_active BOOLEAN DEFAULT true
);
-- 教練專業認證表
CREATE TABLE trainer_certifications (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
trainer_id UUID NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
certification_name VARCHAR(100) NOT NULL,
certification_body VARCHAR(100) NOT NULL,
certification_number VARCHAR(50),
category VARCHAR(50) NOT NULL, -- strength, cardio, yoga, etc.
sub_category VARCHAR(50),
level VARCHAR(20) NOT NULL, -- basic, intermediate, advanced, master
issue_date DATE NOT NULL,
expiry_date DATE,
is_lifetime BOOLEAN DEFAULT false,
is_active BOOLEAN DEFAULT true,
-- 認證文件
certificate_document_url TEXT,
verification_status VARCHAR(20) DEFAULT 'pending', -- pending, verified, expired
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- 教練可用時間表
CREATE TABLE trainer_availability (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
trainer_id UUID NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
day_of_week INTEGER NOT NULL CHECK (day_of_week >= 0 AND day_of_week <= 6),
start_time TIME NOT NULL,
end_time TIME NOT NULL,
-- 特殊日期覆蓋
specific_date DATE, -- 如果設定，則覆蓋 day_of_week
is_available BOOLEAN DEFAULT true,
-- 生效期間
effective_start_date DATE DEFAULT CURRENT_DATE,
effective_end_date DATE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT valid_time_range CHECK (start_time < end_time)
);
-- 教練薪資設定表
CREATE TABLE trainer_compensation (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
trainer_id UUID NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
-- 基本薪資
base_hourly_rate DECIMAL(10,2) NOT NULL,
premium_hourly_rate DECIMAL(10,2), -- 熱門時段加成
group_class_rate DECIMAL(10,2),
personal_training_rate DECIMAL(10,2),
-- 獎金設定
performance_bonus_eligible BOOLEAN DEFAULT true,
commission_rate DECIMAL(5,4), -- 銷售佣金比例
-- 生效期間
effective_start_date DATE NOT NULL,
effective_end_date DATE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
created_by UUID
);
-- 課程排程表
CREATE TABLE scheduled_classes (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL,
trainer_id UUID NOT NULL REFERENCES trainers(id),
-- 課程資訊
class_name VARCHAR(100) NOT NULL,
class_type VARCHAR(50) NOT NULL, -- group, personal, workshop
description TEXT,
-- 時間安排
scheduled_date DATE NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
duration_minutes INTEGER NOT NULL,
-- 容量管理
max_participants INTEGER NOT NULL,
min_participants INTEGER DEFAULT 1,
current_participants INTEGER DEFAULT 0,
waitlist_count INTEGER DEFAULT 0,
-- 地點與設備
room_id UUID,
required_equipment JSONB,
-- 狀態管理
status VARCHAR(20) DEFAULT 'scheduled', -- scheduled, confirmed, cancelled, completed
cancellation_reason TEXT,
-- 商業資訊
price_per_person DECIMAL(10,2),
total_revenue DECIMAL(10,2),
trainer_payment DECIMAL(10,2),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- 教練評價表
CREATE TABLE trainer_reviews (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL,
trainer_id UUID NOT NULL REFERENCES trainers(id),
member_id UUID NOT NULL,
class_id UUID REFERENCES scheduled_classes(id),
-- 評分細項
overall_rating INTEGER NOT NULL CHECK (overall_rating >= 1 AND overall_rating <= 5),
teaching_quality INTEGER CHECK (teaching_quality >= 1 AND teaching_quality <= 5),
professionalism INTEGER CHECK (professionalism >= 1 AND professionalism <= 5),
motivation INTEGER CHECK (motivation >= 1 AND motivation <= 5),
knowledge INTEGER CHECK (knowledge >= 1 AND knowledge <= 5),
-- 文字評論
written_review TEXT,
pros TEXT,
cons TEXT,
-- 推薦
would_recommend BOOLEAN,
-- 狀態
is_anonymous BOOLEAN DEFAULT false,
is_public BOOLEAN DEFAULT true,
is_verified BOOLEAN DEFAULT false,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- 防止重複評價 (同一會員對同一課程只能評價一次)
UNIQUE(member_id, class_id)
);
-- 建立索引
CREATE INDEX idx_trainers_tenant_id ON trainers(tenant_id);
CREATE INDEX idx_trainers_employee_id ON trainers(employee_id);
CREATE INDEX idx_trainers_status ON trainers(employment_status, is_active);
CREATE INDEX idx_trainer_certifications_trainer_id ON trainer_certifications(trainer_id);
CREATE INDEX idx_trainer_certifications_category ON trainer_certifications(category, is_active);
CREATE INDEX idx_trainer_certifications_expiry ON trainer_certifications(expiry_date) WHERE expiry_date IS NOT NULL;
CREATE INDEX idx_trainer_availability_trainer_id ON trainer_availability(trainer_id);
CREATE INDEX idx_trainer_availability_day_time ON trainer_availability(day_of_week, start_time, end_time);
CREATE INDEX idx_trainer_availability_date ON trainer_availability(specific_date) WHERE specific_date IS NOT NULL;
CREATE INDEX idx_scheduled_classes_trainer_date ON scheduled_classes(trainer_id, scheduled_date);
CREATE INDEX idx_scheduled_classes_tenant_status ON scheduled_classes(tenant_id, status);
CREATE INDEX idx_scheduled_classes_date_time ON scheduled_classes(scheduled_date, start_time);
CREATE INDEX idx_trainer_reviews_trainer_id ON trainer_reviews(trainer_id);
CREATE INDEX idx_trainer_reviews_rating ON trainer_reviews(overall_rating);
CREATE INDEX idx_trainer_reviews_created_at ON trainer_reviews(created_at);
// 查詢優化與快取策略
export class TrainerServiceOptimizer {
// 1. 使用 Redis 快取熱門查詢
async getCachedTrainerAvailability(
tenantId: string,
trainerId: string,
date: string
): Promise<TrainerAvailability[] | null> {
const cacheKey = `trainer:${tenantId}:${trainerId}:availability:${date}`;
const cached = await this.redis.get(cacheKey);
if (cached) {
return JSON.parse(cached);
}
return null;
}
async setCachedTrainerAvailability(
tenantId: string,
trainerId: string,
date: string,
availability: TrainerAvailability[]
): Promise<void> {
const cacheKey = `trainer:${tenantId}:${trainerId}:availability:${date}`;
await this.redis.setex(cacheKey, 3600, JSON.stringify(availability)); // 1小時快取
}
// 2. 批次處理排課衝突檢查
async batchValidateSchedulingConflicts(
scheduleRequests: ScheduleClassRequest[]
): Promise<ValidationResult[]> {
// 將同一教練的請求分組
const groupedByTrainer = groupBy(scheduleRequests, 'trainerId');
const validationPromises = Object.entries(groupedByTrainer).map(
async ([trainerId, requests]) => {
return await this.validateTrainerScheduleConflicts(trainerId, requests);
}
);
const results = await Promise.all(validationPromises);
return results.flat();
}
// 3. 智能預加載
async preloadTrainerData(tenantId: string, trainerIds: string[]): Promise<void> {
// 預加載教練基本資料、認證、可用時間
const preloadPromises = [
this.preloadTrainerBasicInfo(tenantId, trainerIds),
this.preloadTrainerCertifications(tenantId, trainerIds),
this.preloadTrainerAvailability(tenantId, trainerIds),
];
await Promise.all(preloadPromises);
}
}
我們今天建立了完整的教練管理與排課服務：
iThome鐵人賽
看影片追技術