import { Prisma } from '@prisma/client'
import { prismaClient, prismaJson } from './prisma.mjs'

function isUuid(value) {
  return /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i.test(String(value || ''))
}

function paymentJson(row) {
  if (!row) return null
  return {
    id: row.id,
    paymentNo: row.paymentNo,
    legacyPaymentNo: row.legacyPaymentNo || null,
    orderId: row.orderId,
    ...(row.orderNo !== undefined ? { orderNo: row.orderNo || '' } : {}),
    ...(row.settlementNo !== undefined ? { settlementNo: row.settlementNo || '' } : {}),
    ...(row.userId !== undefined ? { userId: row.userId || '' } : {}),
    ...(row.userPhone !== undefined ? { userPhone: row.userPhone || '' } : {}),
    provider: row.provider || '',
    channel: row.channel,
    amount: row.amount,
    status: row.status,
    prepayId: row.prepayId || null,
    codeUrl: row.codeUrl || null,
    channelTradeNo: row.channelTradeNo || null,
    payerOpenid: row.payerOpenid || null,
    requestPayload: prismaJson(row.requestPayload, {}),
    responsePayload: prismaJson(row.responsePayload, {}),
    notifyPayload: prismaJson(row.notifyPayload, {}),
    paidAt: row.paidAt,
    closedAt: row.closedAt,
    failureCode: row.failureCode || null,
    failureMessage: row.failureMessage || null,
    expiresAt: row.expiresAt,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    ...(row.order ? { order: row.order } : {}),
    ...(row.itemSummary !== undefined ? { itemSummary: row.itemSummary || '' } : {}),
  }
}

const paymentSelect = Prisma.sql`
  po.id::text as id,
  po.payment_no as "paymentNo",
  po.legacy_payment_no as "legacyPaymentNo",
  po.order_id::text as "orderId",
  coalesce(po.provider, '') as provider,
  po.channel,
  po.amount,
  po.status,
  po.prepay_id as "prepayId",
  po.code_url as "codeUrl",
  po.channel_trade_no as "channelTradeNo",
  po.payer_openid as "payerOpenid",
  coalesce(po.request_payload, '{}'::jsonb) as "requestPayload",
  coalesce(po.response_payload, '{}'::jsonb) as "responsePayload",
  coalesce(po.notify_payload, '{}'::jsonb) as "notifyPayload",
  po.paid_at as "paidAt",
  po.closed_at as "closedAt",
  po.failure_code as "failureCode",
  po.failure_message as "failureMessage",
  po.expires_at as "expiresAt",
  po.created_at as "createdAt",
  po.updated_at as "updatedAt"
`

const orderJsonSql = Prisma.sql`
  json_build_object(
    'id', o.id,
    'orderNo', o.order_no,
    'settlementNo', o.settlement_no,
    'userId', o.user_id,
    'userPhone', u.phone,
    'status', o.status,
    'items', coalesce((
      select json_agg(json_build_object(
        'productId', oi.product_id,
        'productType', oi.product_type,
        'titleSnapshot', oi.title_snapshot,
        'unitPriceAmount', oi.unit_price_amount,
        'quantity', oi.quantity,
        'payableAmount', oi.payable_amount,
        'billingCycle', coalesce(oi.billing_cycle, 'once')
      ))
      from order_items oi
      where oi.order_id = o.id
    ), '[]'::json),
    'subtotalAmount', o.subtotal_amount,
    'memberDiscountAmount', o.member_discount_amount,
    'couponDiscountAmount', o.coupon_discount_amount,
    'paidAmount', o.paid_amount,
    'totalAmount', o.total_amount,
    'currency', o.currency,
    'settlementSnapshot', o.settlement_snapshot,
    'invoiceStatus', coalesce((
      select ia.status
      from invoice_applications ia
      where ia.order_id = o.id
      order by ia.created_at desc
      limit 1
    ), ''),
    'invoiceApplication', (
      select json_build_object(
        'id', ia.id,
        'invoiceNo', ia.invoice_no,
        'invoiceType', coalesce(ia.invoice_type, 'normal_e_invoice'),
        'invoiceTypeLabel', '普通电子发票',
        'orderId', ia.order_id,
        'orderNo', o.order_no,
        'settlementNo', o.settlement_no,
        'userId', o.user_id,
        'userPhone', u.phone,
        'title', ia.title,
        'taxNumber', coalesce(ia.tax_number, ''),
        'email', ia.email,
        'amount', ia.amount,
        'status', ia.status,
        'provider', coalesce(ia.provider, 'manual'),
        'providerInvoiceId', coalesce(ia.provider_invoice_id, ''),
        'pdfFileId', ia.pdf_file_id,
        'fileName', coalesce(f.file_name, ''),
        'fileUrl', case when ia.pdf_file_id is null then '' else '/api/files/' || ia.pdf_file_id end,
        'failureReason', coalesce(ia.failure_reason, ''),
        'remark', coalesce(ia.remark, ''),
        'adminNote', coalesce(ia.admin_note, ''),
        'issuedBy', coalesce(ia.issued_by, ''),
        'issuedAt', ia.issued_at,
        'createdAt', ia.created_at,
        'updatedAt', ia.updated_at,
        'fileUploadedAt', ia.file_uploaded_at
      )
      from invoice_applications ia
      left join uploaded_files f on f.id = ia.pdf_file_id
      where ia.order_id = o.id
      order by ia.created_at desc
      limit 1
    ),
    'createdAt', o.created_at,
    'updatedAt', o.updated_at
  )
`

export async function findPaymentByNo(paymentNo) {
  const prisma = prismaClient()
  const key = String(paymentNo || '').trim()
  if (!key) return null
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${paymentSelect}
    from payment_orders po
    where po.payment_no = ${key}
       or po.legacy_payment_no = ${key}
    limit 1
  `)
  return paymentJson(rows[0])
}

export async function findUserPaymentByNo(userId, paymentNo) {
  const prisma = prismaClient()
  const key = String(paymentNo || '').trim()
  if (!key) return null
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${paymentSelect}
    from payment_orders po
    join orders o on o.id = po.order_id
    where po.payment_no = ${key}
      and o.user_id = ${userId}::uuid
    limit 1
  `)
  return paymentJson(rows[0])
}

export async function findActivePaymentForOrderChannel(orderId, channel) {
  if (!isUuid(orderId)) return null
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${paymentSelect}
    from payment_orders po
    where po.order_id = ${orderId}::uuid
      and po.channel = ${String(channel || '')}
      and po.status in ('created', 'paying')
      and po.expires_at > now()
    order by case when po.status = 'paying' then 0 else 1 end, po.created_at desc
    limit 1
  `)
  return paymentJson(rows[0])
}

export async function expirePaymentRecord(payment) {
  if (!payment?.id || !isUuid(payment.id)) return payment
  const prisma = prismaClient()
  await prisma.$executeRaw`
    update payment_orders
    set status = 'expired',
        closed_at = coalesce(closed_at, now()),
        updated_at = now()
    where id = ${payment.id}::uuid
      and status in ('created', 'paying')
  `
  return findPaymentByNo(payment.paymentNo)
}

export async function closeUserPaymentRecord(userId, paymentNo) {
  const prisma = prismaClient()
  const key = String(paymentNo || '').trim()
  if (!isUuid(userId) || !key) return null
  const rows = await prisma.$queryRaw(Prisma.sql`
    update payment_orders po
    set status = 'closed',
        closed_at = coalesce(po.closed_at, now()),
        updated_at = now()
    from orders o
    where o.id = po.order_id
      and o.user_id = ${userId}::uuid
      and po.payment_no = ${key}
      and po.status in ('created', 'paying')
    returning ${paymentSelect}
  `)
  if (rows[0]) return paymentJson(rows[0])
  return findUserPaymentByNo(userId, key)
}

function adminPaymentConditions(filters = {}) {
  const conditions = []
  const status = String(filters.status || '').trim()
  const search = String(filters.search || '').trim()
  const from = String(filters.from || '').trim()
  const to = String(filters.to || '').trim()
  if (status) conditions.push(Prisma.sql`po.status = ${status}`)
  if (from) conditions.push(Prisma.sql`po.created_at >= ${from}::date`)
  if (to) conditions.push(Prisma.sql`po.created_at < (${to}::date + interval '1 day')`)
  if (search) {
    const like = `%${search}%`
    conditions.push(Prisma.sql`(
      po.payment_no ilike ${like}
      or po.legacy_payment_no ilike ${like}
      or o.order_no ilike ${like}
      or o.legacy_order_no ilike ${like}
      or o.settlement_no ilike ${like}
      or u.phone ilike ${like}
      or exists (
        select 1
        from order_items oi
        where oi.order_id = o.id
          and oi.title_snapshot ilike ${like}
      )
    )`)
  }
  return conditions
}

export async function listAdminPaymentRecords(filters = {}) {
  const prisma = prismaClient()
  const conditions = adminPaymentConditions(filters)
  const where = conditions.length ? Prisma.sql`where ${Prisma.join(conditions, ' and ')}` : Prisma.empty
  const rows = await prisma.$queryRaw(Prisma.sql`
    select
      ${paymentSelect},
      o.order_no as "orderNo",
      o.settlement_no as "settlementNo",
      o.user_id::text as "userId",
      u.phone as "userPhone",
      ${orderJsonSql} as "order",
      coalesce((
        select string_agg(oi.title_snapshot, ' / ' order by oi.title_snapshot)
        from order_items oi
        where oi.order_id = o.id
      ), '') as "itemSummary"
    from payment_orders po
    join orders o on o.id = po.order_id
    join users u on u.id = o.user_id
    ${where}
    order by po.created_at desc
  `)
  return rows.map(paymentJson)
}
