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: r.organization || '',
  wechat: r.wechat || '',
  contactName: r.contact_name || '',
  bio: r.bio || '',
  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 || '',
  imageUrl: r.image_stored_name ? `/uploads/${r.image_stored_name}` : null
})

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,
    userId: r.user_id,
    name: r.name,
    category: r.category || '',
    audience: r.audience || '',
    description: r.description || '',
    status: r.status,
    productionNotes: r.production_notes || '',
    createdAt: r.created_at,
    submittedAt: r.submitted_at || null,
    completedAt: r.completed_at || null,
    assets: mappedAssets,
    episodes: mappedEpisodes,
    instructors: instructors.map(mapInstructor),
    deliverables: mappedDeliverables,
    creator: creatorUser ? mapUser(creatorUser) : (r.user_phone ? {
      id: r.user_id,
      phone: r.user_phone,
      role: r.user_role || 'USER',
      organization: r.user_organization || '',
      wechat: r.user_wechat || '',
      contactName: r.user_contact_name || '',
      bio: r.user_bio || '',
      createdAt: r.user_created_at
    } : undefined)
  }
}