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,
  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,
  originalName: r.original_name,
  size: Number(r.size),
  mimeType: r.mime_type,
  url: `/uploads/${r.stored_name}`,
  createdAt: r.created_at
})

export const mapCourse = (
  r: any,
  assets: any[] = [],
  instructors: any[] = [],
  deliverables: any[] = [],
  creatorUser: any = null
) => ({
  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: assets.map(mapAsset),
  instructors: instructors.map(mapInstructor),
  deliverables: deliverables.map(mapDeliverable),
  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)
})