import { spawn } from 'node:child_process'

// Deprecated for business runtime code. Keep this module only as the temporary
// bridge for legacy lite APIs and SQL migrations while modules move to Prisma.
const database = process.env.DATABASE_NAME || 'dlbdi_dev'
const defaultMaxBuffer = 1024 * 1024 * 100

export function sqlLiteral(value) {
  if (value === null || value === undefined) return 'null'
  if (value instanceof Date) return `'${value.toISOString().replaceAll("'", "''")}'`
  return `'${String(value).replaceAll("'", "''")}'`
}

export async function queryJson(sql) {
  const { stdout } = await runPsql(['-X', '-q', '-t', '-A', '-d', database, '-v', 'ON_ERROR_STOP=1'], sql)
  const value = stdout.trim()
  if (!value) return null
  return JSON.parse(value)
}

export async function executeSql(sql) {
  await runPsql(['-X', '-q', '-d', database, '-v', 'ON_ERROR_STOP=1'], sql)
}

function runPsql(args, sql, maxBuffer = defaultMaxBuffer) {
  return new Promise((resolve, reject) => {
    const child = spawn('psql', args, { stdio: ['pipe', 'pipe', 'pipe'] })
    const stdoutChunks = []
    const stderrChunks = []
    let stdoutSize = 0
    let stderrSize = 0
    let settled = false

    function appendChunk(chunks, chunk, sizeKey) {
      chunks.push(chunk)
      if (sizeKey === 'stdout') stdoutSize += chunk.length
      if (sizeKey === 'stderr') stderrSize += chunk.length
      if (stdoutSize + stderrSize > maxBuffer && !settled) {
        settled = true
        child.kill()
        const error = new Error(`psql output exceeded ${maxBuffer} bytes`)
        error.code = 'max_buffer_exceeded'
        reject(error)
      }
    }

    child.stdout.on('data', (chunk) => appendChunk(stdoutChunks, chunk, 'stdout'))
    child.stderr.on('data', (chunk) => appendChunk(stderrChunks, chunk, 'stderr'))
    child.on('error', (error) => {
      if (settled) return
      settled = true
      reject(error)
    })
    child.on('close', (code) => {
      if (settled) return
      const stdout = Buffer.concat(stdoutChunks).toString('utf8')
      const stderr = Buffer.concat(stderrChunks).toString('utf8')
      if (code !== 0) {
        const error = new Error(stderr || `psql exited with code ${code}`)
        error.code = code
        reject(error)
        return
      }
      settled = true
      resolve({ stdout, stderr })
    })

    child.stdin.end(sql)
  })
}

export function productJson(alias = 'p') {
  return `json_build_object(
    'id', ${alias}.id,
    'slug', ${alias}.slug,
    'type', ${alias}.type,
    'title', ${alias}.title,
    'summary', ${alias}.summary,
    'priceAmount', ${alias}.price_amount,
    'purchaseFlow', ${alias}.purchase_flow,
    'fulfillmentFlow', ${alias}.fulfillment_flow,
    'status', ${alias}.status
  )`
}

export function userJson(alias = 'u') {
  return `json_build_object(
    'id', ${alias}.id,
    'displayName', ${alias}.display_name,
    'phone', ${alias}.phone,
    'passportNo', ${alias}.passport_no,
    'wechatId', ${alias}.wechat_id,
    'contactPhone', ${alias}.contact_phone,
    'email', ${alias}.email,
    'organization', ${alias}.organization,
    'title', ${alias}.title,
    'city', ${alias}.city,
    'address', ${alias}.address,
    'industry', ${alias}.industry,
    'invoiceTitle', ${alias}.invoice_title,
    'taxNumber', ${alias}.tax_number,
    'invoiceEmail', ${alias}.invoice_email,
    'membershipLevel', ${alias}.membership_level,
    'onboardingCompleted', ${alias}.onboarding_completed,
    'status', ${alias}.status,
    'expertApplicationStatus', coalesce((
      select ea.status
      from expert_applications ea
      where ea.user_id = ${alias}.id
      order by ea.created_at desc
      limit 1
    ), ''),
    'expertProfileStatus', coalesce((
      select e.status
      from expert_profiles e
      where e.user_id = ${alias}.id
        and e.deleted_at is null
      order by e.updated_at desc
      limit 1
    ), ''),
    'lastLoginAt', ${alias}.last_login_at,
    'createdAt', ${alias}.created_at,
    'updatedAt', ${alias}.updated_at
  )`
}

export function couponJson(alias = 'c') {
  return `json_build_object(
    'id', ${alias}.id,
    'code', ${alias}.code,
    'name', ${alias}.name,
    'description', ${alias}.description,
    'discountType', ${alias}.discount_type,
    'discountValue', ${alias}.discount_value,
    'maxDiscountAmount', ${alias}.max_discount_amount,
    'minOrderAmount', ${alias}.min_order_amount,
    'productScope', coalesce(${alias}.product_scope, '{}'::jsonb),
    'userScope', coalesce(${alias}.user_scope, '{}'::jsonb),
    'usageLimits', coalesce(${alias}.usage_limits, '{}'::jsonb),
    'stackingPolicy', coalesce(${alias}.stacking_policy, '{}'::jsonb),
    'budgetAmount', ${alias}.budget_amount,
    'usedDiscountAmount', ${alias}.used_discount_amount,
    'totalQuantity', ${alias}.total_quantity,
    'usedQuantity', ${alias}.used_quantity,
    'remainingQuantity', greatest(${alias}.total_quantity - ${alias}.used_quantity, 0),
    'startsAt', ${alias}.starts_at,
    'endsAt', ${alias}.ends_at,
    'status', ${alias}.status,
    'createdAt', ${alias}.created_at,
    'updatedAt', ${alias}.updated_at
  )`
}

export function newsArticleJson(alias = 'n') {
  return `json_build_object(
    'id', ${alias}.id,
    'slug', ${alias}.slug,
    'title', ${alias}.title,
    'categoryCode', ${alias}.category_code,
    'category', ${alias}.category,
    'summary', ${alias}.summary,
    'content', ${alias}.content,
    'authorName', ${alias}.author_name,
    'coverUrl', ${alias}.cover_url,
    'status', ${alias}.status,
    'featuredPosition', ${alias}.featured_position,
    'relatedArticleIds', coalesce(to_json(${alias}.related_article_ids), '[]'::json),
    'publishedAt', ${alias}.published_at,
    'createdAt', ${alias}.created_at,
    'updatedAt', ${alias}.updated_at
  )`
}

export function expertProfileJson(alias = 'e') {
  return `json_build_object(
    'id', ${alias}.id,
    'userId', ${alias}.user_id,
    'slug', ${alias}.slug,
    'name', ${alias}.name,
    'passportNo', ${alias}.passport_no,
    'contactPhone', ${alias}.contact_phone,
    'title', ${alias}.title,
    'organization', ${alias}.organization,
    'bio', ${alias}.bio,
    'specialties', to_json(${alias}.specialties),
    'directions', coalesce(to_json(${alias}.directions), '[]'::json),
    'projectExperience', ${alias}.project_experience,
    'representativeAchievements', ${alias}.representative_achievements,
    'homepageIntro', ${alias}.homepage_intro,
    'priceAmount', ${alias}.price_amount,
    'shareRatio', ${alias}.share_ratio,
    'acceptingOrders', ${alias}.accepting_orders,
    'availabilityStatus', ${alias}.availability_status,
    'rating', ${alias}.rating,
    'consultationCount', ${alias}.consultation_count,
    'photoUrl', ${alias}.photo_url,
    'status', ${alias}.status,
    'createdAt', ${alias}.created_at,
    'updatedAt', ${alias}.updated_at
  )`
}
