xinghen.work/server/db.ts

import pg from 'pg'
const { Pool } = pg

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgresql://xinghen:xinghen@localhost:5432/xinghen'
})

export const mapUser = (r: any) => ({
  id: r.id,
  phone: r.phone,
  role: r.role || 'USER',
  organization: '',
  wechat: r.wechat || '',
  contactName: r.contact_name || '',
  bio: r.bio || '',
  pointsBalance: Number(r.points_balance) || 0,
  mustChangePassword: Boolean(r.must_change_password),
  createdAt: r.created_at,
  updatedAt: r.updated_at
})

export const mapAsset = (r: any) => ({
  id: r.id,
  courseId: r.course_id,
  episodeId: r.episode_id || null,
  originalName: r.original_name,
  size: Number(r.size),
  mimeType: r.mime_type,
  url: `/uploads/${r.stored_name}`,
  createdAt: r.created_at
})

export const mapInstructor = (r: any) => ({
  id: r.id,
  name: r.name,
  organization: r.organization || '',
  introduction: r.introduction || '',
  note: r.note || '',
  imageUrl: r.image_urls?.[0] || (r.image_stored_name ? `/uploads/${r.image_stored_name}` : null),
  imageUrls: r.image_urls?.length ? r.image_urls : (r.image_stored_name ? [`/uploads/${r.image_stored_name}`] : []),
  audioUrls: r.audio_urls?.length ? r.audio_urls : (r.audio?.length ? r.audio.map((a: any) => a.url) : [])
})

export const mapDeliverable = (r: any) => ({
  id: r.id,
  courseId: r.course_id,
  episodeId: r.episode_id || null,
  originalName: r.original_name,
  size: Number(r.size),
  mimeType: r.mime_type,
  url: `/uploads/${r.stored_name}`,
  createdAt: r.created_at
})

export const mapEpisode = (r: any, assets: any[] = [], deliverables: any[] = []) => ({
  id: r.id,
  courseId: r.course_id,
  episodeNumber: Number(r.episode_number) || 1,
  title: r.title,
  summary: r.summary || '',
  lectureNotes: r.lecture_notes || '',
  createdAt: r.created_at,
  updatedAt: r.updated_at,
  assets: assets.map(mapAsset),
  deliverables: deliverables.map(mapDeliverable)
})

export const mapCourse = (
  r: any,
  assets: any[] = [],
  instructors: any[] = [],
  deliverables: any[] = [],
  creatorUser: any = null,
  episodes: any[] = []
) => {
  const mappedAssets = assets.map(mapAsset)
  const mappedDeliverables = deliverables.map(mapDeliverable)

  // 为每个分集关联专属的 assets 与 deliverables
  const mappedEpisodes = episodes.map((epRow) => {
    const epAssets = assets.filter((a) => a.episode_id === epRow.id)
    const epDeliverables = deliverables.filter((d) => d.episode_id === epRow.id)
    return mapEpisode(epRow, epAssets, epDeliverables)
  })

  return {
    id: r.id,
    organizationId: r.organization_id || null,
    organizationName: r.organization_name || null,
    userId: r.user_id,
    name: r.name,
    description: r.description || '',
    status: r.status,
    productionNotes: r.production_notes || '',
    estimatedMinutes: Number(r.estimated_minutes) || 1,
    estimatedPoints: Number(r.estimated_points) || 1000,
    actualPoints: r.actual_points == null ? null : Number(r.actual_points),
    pointsCharged: Boolean(r.points_charged),
    progressEvents: (r.progress_events || []).map((event: any) => ({
      id: event.id,
      actorType: event.actor_type,
      eventType: event.event_type,
      title: event.title,
      description: event.description || '',
      metadata: event.metadata || {},
      createdAt: event.created_at
    })),
    createdAt: r.created_at,
    updatedAt: r.updated_at,
    submittedAt: r.submitted_at || null,
    completedAt: r.completed_at || null,
    assets: mappedAssets,
    episodes: mappedEpisodes,
    instructors: instructors.map(mapInstructor),
    digitalInstructorId: r.digital_instructor_id || null,
    digitalInstructorSnapshot: r.digital_instructor_snapshot || null,
    deliverables: mappedDeliverables,
    creator: creatorUser ? mapUser(creatorUser) : (r.user_phone ? {
      id: r.user_id,
      phone: r.user_phone,
      role: r.user_role || 'USER',
      organization: '',
      wechat: r.user_wechat || '',
      contactName: r.user_contact_name || '',
      bio: r.user_bio || '',
      createdAt: r.user_created_at
    } : undefined)
  }
}