import { randomUUID } from 'node:crypto'
import { Prisma } from '@prisma/client'
import { prismaJson, withPrismaTransaction } 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 checkinCode(prefix) {
  return `${prefix}${randomUUID().replaceAll('-', '').slice(0, 10).toUpperCase()}`
}

function membershipTierFromProductId(productId = '') {
  return String(productId).includes('senior') ? 'senior' : 'junior'
}

function membershipLabel(tier = '') {
  if (tier === 'senior') return '高级会员'
  if (tier === 'junior') return '初级会员'
  return '注册用户'
}

function normalizeMembershipBillingCycle(value = 'monthly') {
  return ['monthly', 'yearly', 'manual_grant'].includes(value) ? value : 'monthly'
}

function membershipCycleIntervalSql(cycle = 'monthly') {
  return cycle === 'yearly' ? Prisma.sql`interval '1 year'` : Prisma.sql`interval '1 month'`
}

function safeJson(value, fallback = {}) {
  return prismaJson(value, fallback)
}

function monthPeriodKey(date = new Date()) {
  const value = date instanceof Date ? date : new Date(date)
  return `${value.getFullYear()}-${String(value.getMonth() + 1).padStart(2, '0')}`
}

function serviceEntryForOrderItem(row, settlementSnapshot = {}) {
  const productType = String(row.productType || '')
  const productId = String(row.productId || '')
  const serviceEntries = Array.isArray(settlementSnapshot.serviceEntries) ? settlementSnapshot.serviceEntries : []
  const snapshotEntry = serviceEntries.find((entry) => (
    String(entry?.productId || '') === productId
    && (!entry?.productType || String(entry.productType) === productType)
  )) || {}
  const dataServiceRequestId = String(row.dataServiceRequestId || snapshotEntry.dataServiceRequestId || settlementSnapshot.dataServiceRequestId || '')
  const consultingRequestId = String(row.consultingRequestId || snapshotEntry.consultingRequestId || settlementSnapshot.consultingRequestId || '')
  const expertBookingId = String(row.expertBookingId || snapshotEntry.expertBookingId || settlementSnapshot.expertBookingId || '')
  const courseRegistrationId = String(row.courseRegistrationId || snapshotEntry.courseRegistrationId || settlementSnapshot.courseRegistrationId || settlementSnapshot.courseRegistrationIds?.[productId] || '')
  const eventRegistrationId = String(row.eventRegistrationId || snapshotEntry.eventRegistrationId || settlementSnapshot.eventRegistrationId || settlementSnapshot.eventRegistrationIds?.[productId] || '')
  const membershipSubscriptionId = String(row.membershipSubscriptionId || snapshotEntry.membershipSubscriptionId || settlementSnapshot.membershipSubscriptionId || settlementSnapshot.membershipSubscriptionIds?.[productId] || '')
  const fulfillmentTaskId = String(row.fulfillmentTaskId || snapshotEntry.fulfillmentTaskId || settlementSnapshot.fulfillmentTaskId || settlementSnapshot.fulfillmentTaskIds?.[productId] || '')

  if (dataServiceRequestId || productId === 'data-service-custom') {
    return {
      serviceEntryType: 'data_service',
      serviceEntryId: dataServiceRequestId,
      serviceEntryHref: dataServiceRequestId ? `/account/data/${dataServiceRequestId}` : '/account/data',
    }
  }
  if (consultingRequestId || productId === 'consulting-custom') {
    return {
      serviceEntryType: 'consulting',
      serviceEntryId: consultingRequestId,
      serviceEntryHref: consultingRequestId ? `/account/consulting/${consultingRequestId}` : '/account/consulting',
    }
  }
  if (expertBookingId || productType === 'expert') {
    return {
      serviceEntryType: 'expert',
      serviceEntryId: expertBookingId,
      serviceEntryHref: expertBookingId ? `/account/expert-bookings/${expertBookingId}` : '/account/expert-bookings',
    }
  }
  if (productType === 'membership') {
    return {
      serviceEntryType: 'membership',
      serviceEntryId: membershipSubscriptionId || productId,
      serviceEntryHref: '/account/membership',
    }
  }
  if (productType === 'course') {
    return {
      serviceEntryType: 'course',
      serviceEntryId: courseRegistrationId || productId,
      serviceEntryHref: courseRegistrationId ? `/account/courses/${courseRegistrationId}` : '/account/courses',
    }
  }
  if (productType === 'event') {
    return {
      serviceEntryType: 'event',
      serviceEntryId: eventRegistrationId || productId,
      serviceEntryHref: eventRegistrationId ? `/account/events/${eventRegistrationId}` : '/account/events',
    }
  }
  if (productType === 'agent') {
    return {
      serviceEntryType: 'agent',
      serviceEntryId: fulfillmentTaskId || productId,
      serviceEntryHref: fulfillmentTaskId ? `/account/delivery/${fulfillmentTaskId}` : '/account/agents',
    }
  }
  if (productType === 'data') {
    return {
      serviceEntryType: 'data_service',
      serviceEntryId: productId,
      serviceEntryHref: '/account/data',
    }
  }

  return {
    serviceEntryType: productType,
    serviceEntryId: productId,
    serviceEntryHref: productId ? `/products/${productId}` : '',
  }
}

function orderItemJson(row, settlementSnapshot = {}) {
  const serviceEntry = serviceEntryForOrderItem(row, settlementSnapshot)
  return {
    productId: row.productId,
    productType: row.productType,
    titleSnapshot: row.titleSnapshot,
    unitPriceAmount: Number(row.unitPriceAmount || 0),
    quantity: Number(row.quantity || 1),
    payableAmount: Number(row.payableAmount || 0),
    billingCycle: row.billingCycle || 'once',
    fulfillmentStatus: settlementSnapshot.fulfillmentStatus || '',
    ...serviceEntry,
  }
}

function orderJson(row, items = []) {
  if (!row) return null
  const settlementSnapshot = safeJson(row.settlementSnapshot, {})
  return {
    id: row.id,
    orderNo: row.orderNo,
    settlementNo: row.settlementNo,
    userId: row.userId,
    status: row.status,
    items: items.map((item) => orderItemJson(item, settlementSnapshot)),
    subtotalAmount: Number(row.subtotalAmount || 0),
    memberDiscountAmount: Number(row.memberDiscountAmount || 0),
    couponDiscountAmount: Number(row.couponDiscountAmount || 0),
    paidAmount: Number(row.paidAmount || 0),
    totalAmount: Number(row.totalAmount || 0),
    currency: row.currency || 'CNY',
    settlementPreview: settlementSnapshot.settlementPreview || null,
    settlementSnapshot,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  }
}

async function nextBusinessNoTx(tx, prefix) {
  const rows = await tx.$queryRaw(Prisma.sql`
    select next_business_no(${prefix}) as value
  `)
  return rows[0]?.value
}

async function loadOrderForGrantTx(tx, orderId) {
  const orders = await tx.$queryRaw(Prisma.sql`
    select
      o.id::text as id,
      o.order_no as "orderNo",
      o.settlement_no as "settlementNo",
      o.user_id::text as "userId",
      o.status,
      o.subtotal_amount as "subtotalAmount",
      o.member_discount_amount as "memberDiscountAmount",
      o.coupon_discount_amount as "couponDiscountAmount",
      o.paid_amount as "paidAmount",
      o.total_amount as "totalAmount",
      o.currency,
      o.settlement_snapshot as "settlementSnapshot",
      o.created_at as "createdAt",
      o.updated_at as "updatedAt"
    from orders o
    where o.id = ${orderId}::uuid
    limit 1
  `)
  const order = orders[0]
  if (!order) return null
  const items = await tx.$queryRaw(Prisma.sql`
    select
      oi.product_id as "productId",
      oi.product_type as "productType",
      oi.title_snapshot as "titleSnapshot",
      oi.unit_price_amount as "unitPriceAmount",
      oi.quantity,
      oi.payable_amount as "payableAmount",
      coalesce(oi.billing_cycle, 'once') as "billingCycle",
      cr.id::text as "courseRegistrationId",
      er.id::text as "eventRegistrationId",
      ms.id::text as "membershipSubscriptionId",
      ft.id::text as "fulfillmentTaskId"
    from order_items oi
    left join lateral (
      select id
      from course_registrations cr
      where cr.order_id = oi.order_id
        and cr.product_id = oi.product_id
        and cr.status <> 'cancelled'
      order by cr.created_at desc
      limit 1
    ) cr on true
    left join lateral (
      select id
      from event_registrations er
      where er.order_id = oi.order_id
        and er.product_id = oi.product_id
        and er.status <> 'cancelled'
      order by er.created_at desc
      limit 1
    ) er on true
    left join lateral (
      select id
      from membership_subscriptions ms
      where ms.source_order_id = oi.order_id
        and ms.product_id = oi.product_id
      order by ms.created_at desc
      limit 1
    ) ms on true
    left join lateral (
      select id
      from fulfillment_tasks ft
      where ft.order_id = oi.order_id
        and ft.product_id = oi.product_id
        and ft.type = oi.product_type
      order by ft.created_at desc
      limit 1
    ) ft on true
    where oi.order_id = ${order.id}::uuid
    order by oi.id
  `)
  return orderJson(order, items)
}

async function loadUserForGrantTx(tx, userId) {
  const rows = await tx.$queryRaw(Prisma.sql`
    select
      id::text as id,
      display_name as "displayName",
      phone,
      email,
      membership_level as "membershipLevel"
    from users
    where id = ${userId}::uuid
    limit 1
  `)
  return rows[0] || null
}

async function insertOperationalEventTx(tx, payload = {}) {
  const severity = ['info', 'warning', 'error', 'critical'].includes(payload.severity) ? payload.severity : 'warning'
  const eventType = String(payload.eventType || 'manual_event').trim().slice(0, 120) || 'manual_event'
  const resourceType = String(payload.resourceType || '').trim().slice(0, 120)
  const resourceId = String(payload.resourceId || '').trim().slice(0, 160)
  const message = String(payload.message || '').trim().slice(0, 500)
  const open = await tx.$queryRaw(Prisma.sql`
    select id
    from operational_events
    where event_type = ${eventType}
      and resource_id = ${resourceId}
      and status in ('open', 'acknowledged')
    limit 1
  `).catch(() => [])
  if (open[0]) return null
  await tx.$executeRaw(Prisma.sql`
    insert into operational_events (
      severity, event_type, resource_type, resource_id, message, payload, status
    )
    values (
      ${severity},
      ${eventType},
      ${resourceType},
      ${resourceId},
      ${message},
      ${JSON.stringify(payload.payload || {})}::jsonb,
      'open'
    )
  `).catch((error) => {
    if (!['P2010', 'P2021', 'P2022'].includes(error?.code)) throw error
  })
  return null
}

function checkoutInvoiceRequest(order) {
  const snapshot = safeJson(order?.settlementSnapshot, {})
  const invoice = snapshot.invoiceRequest || {}
  if (!invoice?.requested) return null
  const title = String(invoice.title || '').trim()
  const taxNumber = String(invoice.taxNumber || '').trim().toUpperCase()
  const email = String(invoice.email || '').trim().toLowerCase()
  const remark = String(invoice.remark || '结算页勾选自动申请发票').trim().slice(0, 500)
  if (!title || !email) return null
  return { title, taxNumber, email, remark }
}

async function createCheckoutInvoiceApplicationTx(tx, user, order) {
  const invoice = checkoutInvoiceRequest(order)
  if (!invoice) return null
  const amount = Number(order?.paidAmount || order?.totalAmount || 0)
  if (amount <= 0) return null

  const duplicate = await tx.$queryRaw(Prisma.sql`
    select invoice_no as "invoiceNo", status
    from invoice_applications
    where order_id = ${order.id}::uuid
      and status in ('pending', 'processing', 'issued', 'failed')
    order by created_at desc
    limit 1
  `)
  if (duplicate[0]) return duplicate[0]

  const invoiceNo = await nextBusinessNoTx(tx, 'INV')
  const rows = await tx.$queryRaw(Prisma.sql`
    insert into invoice_applications (
      invoice_no, invoice_type, order_id, applicant_user_id, title, tax_number,
      email, amount, status, provider, remark
    )
    values (
      ${invoiceNo},
      'normal_e_invoice',
      ${order.id}::uuid,
      ${user.id}::uuid,
      ${invoice.title},
      ${invoice.taxNumber},
      ${invoice.email},
      ${amount},
      'pending',
      'manual',
      ${invoice.remark}
    )
    returning id::text as id, invoice_no as "invoiceNo", status
  `)
  const created = rows[0] || null
  if (created) {
    await tx.$executeRaw(Prisma.sql`
      update orders
      set settlement_snapshot = coalesce(settlement_snapshot, '{}'::jsonb) || ${JSON.stringify({
        checkoutInvoiceApplication: {
          id: created.id,
          invoiceNo: created.invoiceNo,
          status: created.status,
        },
      })}::jsonb,
          updated_at = now()
      where id = ${order.id}::uuid
    `)
  }
  return created
}

async function writeMembershipEventTx(tx, {
  subscriptionId = null,
  userId,
  eventType,
  fromTier = null,
  toTier = null,
  fromStatus = null,
  toStatus = null,
  orderId = null,
  payload = {},
}) {
  await tx.$executeRaw(Prisma.sql`
    insert into membership_events (
      subscription_id, user_id, event_type, from_tier, to_tier,
      from_status, to_status, order_id, payload_json
    )
    values (
      ${subscriptionId}::uuid,
      ${userId}::uuid,
      ${eventType},
      ${fromTier},
      ${toTier},
      ${fromStatus},
      ${toStatus},
      ${orderId}::uuid,
      ${JSON.stringify(payload)}::jsonb
    )
  `)
}

async function queueNotificationMessageTx(tx, {
  userId,
  scene,
  templateKey,
  payload = {},
  channel = 'in_app',
  dedupeKey,
  status,
}) {
  const finalStatus = status || (channel === 'in_app' ? 'sent' : 'queued')
  await tx.$executeRaw(Prisma.sql`
    insert into notification_messages (
      user_id, scene, channel, template_key, payload_json, dedupe_key,
      status, scheduled_at, sent_at
    )
    values (
      ${userId}::uuid,
      ${scene},
      ${channel},
      ${templateKey},
      ${JSON.stringify(payload)}::jsonb,
      ${dedupeKey},
      ${finalStatus},
      now(),
      case when ${finalStatus} = 'sent' then now() else null end
    )
    on conflict (dedupe_key) do nothing
  `)
}

async function syncUserMembershipLevelTx(tx, userId) {
  const rows = await tx.$queryRaw(Prisma.sql`
    select coalesce((
      select tier
      from membership_subscriptions
      where user_id = ${userId}::uuid
        and status = 'active'
        and expires_at > now()
      order by case tier when 'senior' then 2 when 'junior' then 1 else 0 end desc,
        expires_at desc
      limit 1
    ), 'registered') as level
  `)
  const level = rows[0]?.level || 'registered'
  await tx.$executeRaw(Prisma.sql`
    update users
    set membership_level = ${level},
        updated_at = now()
    where id = ${userId}::uuid
      and membership_level <> ${level}
  `)
  return level
}

async function activateMembershipSubscriptionTx(tx, user, order, item) {
  const tier = membershipTierFromProductId(item.productId)
  const billingCycle = normalizeMembershipBillingCycle(item.billingCycle || 'monthly')
  const membershipChange = order.settlementSnapshot?.membershipChange?.toLevel === tier
    ? order.settlementSnapshot.membershipChange
    : null
  const existingRows = await tx.$queryRaw(Prisma.sql`
    select exists (
      select 1
      from membership_subscriptions
      where user_id = ${user.id}::uuid
        and tier = ${tier}
        and status = 'active'
        and expires_at > now()
    ) as ok
  `)
  const eventType = membershipChange?.type === 'upgrade'
    ? 'upgraded'
    : existingRows[0]?.ok ? 'renewed' : 'activated'
  const paidAmountSnapshot = Number(item.payableAmount || (Number(item.unitPriceAmount || 0) * Number(item.quantity || 1)))
  const rows = await tx.$queryRaw(Prisma.sql`
    with base as (
      select greatest(now(), coalesce(max(expires_at), now())) as starts_at
      from membership_subscriptions
      where user_id = ${user.id}::uuid
        and tier = ${tier}
        and status = 'active'
        and expires_at > now()
    ),
    inserted as (
      insert into membership_subscriptions (
        user_id, tier, product_id, billing_cycle, status,
        starts_at, expires_at, auto_renew, source_order_id, latest_renewal_order_id,
        paid_amount_snapshot, order_item_snapshot
      )
      select
        ${user.id}::uuid,
        ${tier},
        ${item.productId},
        ${billingCycle},
        'active',
        starts_at,
        starts_at + ${membershipCycleIntervalSql(billingCycle)},
        false,
        ${order.id}::uuid,
        ${order.id}::uuid,
        ${paidAmountSnapshot},
        ${JSON.stringify(item)}::jsonb
      from base
      on conflict (source_order_id, tier) do nothing
      returning
        id::text as id,
        user_id::text as "userId",
        tier,
        product_id as "productId",
        billing_cycle as "billingCycle",
        status,
        starts_at as "startsAt",
        expires_at as "expiresAt",
        auto_renew as "autoRenew",
        source_order_id::text as "sourceOrderId",
        created_at as "createdAt",
        updated_at as "updatedAt"
    )
    select * from inserted
    union all
    select
      id::text as id,
      user_id::text as "userId",
      tier,
      product_id as "productId",
      billing_cycle as "billingCycle",
      status,
      starts_at as "startsAt",
      expires_at as "expiresAt",
      auto_renew as "autoRenew",
      source_order_id::text as "sourceOrderId",
      created_at as "createdAt",
      updated_at as "updatedAt"
    from membership_subscriptions
    where source_order_id = ${order.id}::uuid
      and tier = ${tier}
    limit 1
  `)
  const subscription = rows[0]
  if (!subscription) return null

  if (eventType === 'upgraded' && Array.isArray(membershipChange?.creditedSubscriptionIds) && membershipChange.creditedSubscriptionIds.length > 0) {
    for (const creditedId of membershipChange.creditedSubscriptionIds) {
      if (!isUuid(creditedId)) continue
      await tx.$executeRaw(Prisma.sql`
        update membership_subscriptions
        set status = 'canceled',
            ended_at = now(),
            canceled_at = coalesce(canceled_at, now()),
            cancel_reason = '会员升级补差抵扣后关闭初级订阅',
            credited_to_subscription_id = ${subscription.id}::uuid,
            updated_at = now()
        where id = ${creditedId}::uuid
          and user_id = ${user.id}::uuid
          and tier = 'junior'
          and status = 'active'
          and expires_at > now()
      `)
    }
  }

  await tx.$executeRaw(Prisma.sql`
    update entitlements
    set valid_to = ${subscription.expiresAt}
    where user_id = ${user.id}::uuid
      and order_id = ${order.id}::uuid
      and product_id = ${item.productId}
      and entitlement_type = 'membership'
  `)

  await writeMembershipEventTx(tx, {
    subscriptionId: subscription.id,
    userId: user.id,
    eventType,
    fromTier: user.membershipLevel,
    toTier: tier,
    toStatus: 'active',
    orderId: order.id,
    payload: {
      orderNo: order.orderNo,
      billingCycle,
      startsAt: subscription.startsAt,
      expiresAt: subscription.expiresAt,
      membershipChange,
    },
  })

  await provisionMembershipEntitlementsTx(tx, user, subscription)

  await queueNotificationMessageTx(tx, {
    userId: user.id,
    scene: `membership_${eventType}`,
    templateKey: `membership_${eventType}`,
    dedupeKey: `membership:${subscription.id}:${eventType}`,
    payload: {
      title: eventType === 'renewed' ? `${membershipLabel(tier)}已续费` : `${membershipLabel(tier)}已开通`,
      body: `有效期至 ${new Date(subscription.expiresAt).toLocaleDateString('zh-CN')}。`,
      actionUrl: '/account/membership',
      tier,
      billingCycle,
      expiresAt: subscription.expiresAt,
    },
  })

  user.membershipLevel = await syncUserMembershipLevelTx(tx, user.id)
  return subscription
}

async function provisionMembershipEntitlementsTx(tx, user, subscription) {
  const benefits = await tx.$queryRaw(Prisma.sql`
    select
      pb.benefit_code as "benefitCode",
      pb.value_json as "valueJson"
    from membership_plan_benefits pb
    join membership_benefit_rules r on r.code = pb.benefit_code
    where pb.plan_product_id = ${subscription.productId}
      and pb.enabled = true
      and pb.status = 'active'
      and r.status = 'active'
      and r.benefit_type = 'quota'
  `)
  for (const benefit of benefits || []) {
    const value = safeJson(benefit.valueJson, {})
    const quota = Math.max(0, Number(value.quota || (subscription.tier === 'senior' ? 3 : 1)))
    if (!quota) continue
    await tx.$executeRaw(Prisma.sql`
      insert into membership_entitlement_accounts (
        user_id, subscription_id, benefit_code, period_key,
        quota_total, quota_used, valid_from, valid_to, status, metadata
      )
      values (
        ${user.id}::uuid,
        ${subscription.id}::uuid,
        ${benefit.benefitCode},
        ${monthPeriodKey(subscription.startsAt)},
        ${quota},
        0,
        ${subscription.startsAt},
        least(${subscription.expiresAt}, date_trunc('month', ${subscription.startsAt}::timestamptz) + interval '1 month'),
        'active',
        ${JSON.stringify({ source: 'membership_activation', tier: subscription.tier })}::jsonb
      )
      on conflict (user_id, subscription_id, benefit_code, period_key) do nothing
    `)
  }
}

async function redeemOrderCouponTx(tx, user, order) {
  const coupon = order.settlementSnapshot?.couponPreview
  if (!coupon?.id || !coupon.discountAmount) return
  await tx.$queryRaw(Prisma.sql`
    with inserted as (
      insert into coupon_redemptions (
        coupon_id, user_id, order_id, coupon_code_snapshot, coupon_name_snapshot,
        discount_amount, discount_base_amount, rule_snapshot
      )
      values (
        ${coupon.id}::uuid,
        ${user.id}::uuid,
        ${order.id}::uuid,
        ${coupon.code || ''},
        ${coupon.name || ''},
        ${Number(coupon.discountAmount || 0)},
        ${Number(coupon.discountBaseAmount || 0)},
        ${JSON.stringify({
          discountType: coupon.discountType,
          discountValue: coupon.discountValue,
          maxDiscountAmount: coupon.maxDiscountAmount ?? null,
          minOrderAmount: coupon.minOrderAmount || 0,
          productScope: coupon.productScope || {},
          userScope: coupon.userScope || {},
          usageLimits: coupon.usageLimits || {},
          stackingPolicy: coupon.stackingPolicy || {},
          budgetAmount: coupon.budgetAmount ?? null,
          eligibleItems: coupon.eligibleItems || [],
        })}::jsonb
      )
      on conflict (order_id) do nothing
      returning coupon_id, discount_amount
    ),
    updated as (
      update coupons
      set
        used_quantity = used_quantity + 1,
        used_discount_amount = used_discount_amount + coalesce((select discount_amount from inserted limit 1), 0),
        updated_at = now()
      where id in (select coupon_id from inserted)
      returning id
    )
    select count(*)::int from inserted
  `)
}

async function markPaidExpertBookingTx(tx, order) {
  const bookingId = order.settlementSnapshot?.expertBookingId
  if (!bookingId || !isUuid(bookingId)) return
  await tx.$executeRaw(Prisma.sql`
    update expert_bookings
    set order_id = ${order.id}::uuid,
        status = case when status = 'pending_payment' then 'pending_expert' else status end,
        hold_expires_at = null,
        updated_at = now()
    where id = ${bookingId}::uuid
  `)
}

async function markPaidConsultingRequestTx(tx, order) {
  const requestId = order.settlementSnapshot?.consultingRequestId
  if (!requestId || !isUuid(requestId)) return
  await tx.$executeRaw(Prisma.sql`
    update consulting_requests
    set order_id = ${order.id}::uuid,
        status = case
          when status in ('proposal_ready', 'pending_payment') then 'in_service'
          else status
        end,
        current_step = case
          when status in ('proposal_ready', 'pending_payment') then 5
          else current_step
        end,
        paid_at = coalesce(paid_at, now()),
        updated_at = now()
    where id = ${requestId}::uuid
  `)
}

async function markPaidDataServiceRequestTx(tx, order) {
  const requestId = order.settlementSnapshot?.dataServiceRequestId
  if (!requestId || !isUuid(requestId)) return
  await tx.$executeRaw(Prisma.sql`
    update data_service_requests
    set order_id = ${order.id}::uuid,
        status = case
          when status in ('quoted', 'pending_payment') then 'in_service'
          else status
        end,
        current_step = case
          when status in ('quoted', 'pending_payment') then 4
          else current_step
        end,
        paid_at = coalesce(paid_at, now()),
        updated_at = now()
    where id = ${requestId}::uuid
  `)
}

function buildExpertSettlementPreview(items, subtotalAmount, discountAmount) {
  const expertItems = items.filter((item) => item.productType === 'expert')
  if (!expertItems.length) return null
  const records = expertItems.map((item) => {
    const itemAmount = item.unitPriceAmount * item.quantity
    const discountShare = subtotalAmount > 0 ? Math.round(discountAmount * itemAmount / subtotalAmount) : 0
    const grossAmount = Math.max(itemAmount - discountShare, 0)
    const expertShareAmount = Math.round(grossAmount * 0.8)
    return {
      productId: item.productId,
      titleSnapshot: item.titleSnapshot,
      grossAmount,
      expertShareAmount,
      platformShareAmount: grossAmount - expertShareAmount,
      policyName: '专家咨询默认 8/2 分成',
    }
  })
  return {
    grossAmount: records.reduce((sum, item) => sum + item.grossAmount, 0),
    expertShareAmount: records.reduce((sum, item) => sum + item.expertShareAmount, 0),
    platformShareAmount: records.reduce((sum, item) => sum + item.platformShareAmount, 0),
    policyName: '专家咨询默认 8/2 分成',
    items: records,
  }
}

async function createSettlementRecordsTx(tx, user, order, items) {
  const settlementItems = order.settlementSnapshot?.settlementPreview?.items
    || buildExpertSettlementPreview(items, order.subtotalAmount, order.memberDiscountAmount + order.couponDiscountAmount)?.items
    || []
  const bookingId = order.settlementSnapshot?.expertBookingId
  const bookingRows = bookingId && isUuid(bookingId)
    ? await tx.$queryRaw(Prisma.sql`
      select
        b.id::text as id,
        b.expert_id::text as "expertId",
        e.user_id::text as "expertUserId",
        e.passport_no as "passportNo",
        e.share_ratio as "shareRatio"
      from expert_bookings b
      join expert_profiles e on e.id = b.expert_id
      where b.id = ${bookingId}::uuid
      limit 1
    `)
    : []
  const booking = bookingRows[0] || null

  for (const item of settlementItems) {
    const grossAmount = Number(item.grossAmount || 0)
    const shareRatio = Number(booking?.shareRatio ?? 80)
    const expertShareAmount = Math.round(grossAmount * shareRatio / 100)
    const settlementNo = await nextBusinessNoTx(tx, 'STL')
    const expertId = booking?.expertId || null
    const expertUserId = booking?.expertUserId || null
    await tx.$executeRaw(Prisma.sql`
      insert into settlement_records (
        settlement_no, order_id, user_id, product_id, beneficiary_type, beneficiary_ref,
        gross_amount, expert_share_amount, platform_share_amount, status, policy_snapshot,
        expert_id, beneficiary_user_id, passport_no
      )
      values (
        ${settlementNo},
        ${order.id}::uuid,
        ${user.id}::uuid,
        ${item.productId},
        'expert',
        ${booking?.expertId || item.productId},
        ${grossAmount},
        ${expertShareAmount},
        ${grossAmount - expertShareAmount},
        'pending_settlement',
        ${JSON.stringify({
          policyName: item.policyName || '专家咨询默认 8/2 分成',
          ratio: { expert: shareRatio / 100, platform: (100 - shareRatio) / 100 },
          expertBookingId: booking?.id || null,
        })}::jsonb,
        ${expertId}::uuid,
        ${expertUserId}::uuid,
        ${booking?.passportNo || ''}
      )
      on conflict (order_id, product_id) do nothing
    `)
  }
}

async function grantOrderResultsTx(tx, user, order) {
  const existingRows = await tx.$queryRaw(Prisma.sql`
    select count(*)::int as count
    from entitlements
    where order_id = ${order.id}::uuid
  `)
  if (Number(existingRows[0]?.count || 0) > 0) return

  await redeemOrderCouponTx(tx, user, order)
  await markPaidExpertBookingTx(tx, order)
  await markPaidConsultingRequestTx(tx, order)
  await markPaidDataServiceRequestTx(tx, order)
  await createSettlementRecordsTx(tx, user, order, order.items)

  const serviceEntries = []
  const courseRegistrationIds = {}
  const eventRegistrationIds = {}
  const membershipSubscriptionIds = {}
  const fulfillmentTaskIds = {}

  for (const item of order.items) {
    const remainingUses = item.productType === 'event' || item.productType === 'expert' ? 1 : null
    const entitlements = await tx.$queryRaw(Prisma.sql`
      insert into entitlements (
        user_id, product_id, order_id, entitlement_type, status, remaining_uses
      )
      values (
        ${user.id}::uuid,
        ${item.productId},
        ${order.id}::uuid,
        ${item.productType},
        'active',
        ${remainingUses}
      )
      returning id::text as id
    `)
    const entitlement = entitlements[0]

    if (item.productType === 'agent' || item.productType === 'expert') {
      const taskNo = await nextBusinessNoTx(tx, 'TSK')
      const tasks = await tx.$queryRaw(Prisma.sql`
        insert into fulfillment_tasks (
          task_no, order_id, entitlement_id, product_id, type, title, status, priority
        )
        values (
          ${taskNo},
          ${order.id}::uuid,
          ${entitlement.id}::uuid,
          ${item.productId},
          ${item.productType},
          ${item.titleSnapshot},
          'pending_material',
          'normal'
        )
        returning id::text as id
      `)
      if (tasks[0]?.id) {
        fulfillmentTaskIds[item.productId] = tasks[0].id
        serviceEntries.push({
          productId: item.productId,
          productType: item.productType,
          fulfillmentTaskId: tasks[0].id,
        })
      }
    }

    if (item.productType === 'event') {
      const registrationNo = await nextBusinessNoTx(tx, 'REG')
      await tx.$executeRaw(Prisma.sql`
        insert into event_registrations (
          registration_no, user_id, product_id, order_id, entitlement_id, checkin_code, status
        )
        values (
          ${registrationNo},
          ${user.id}::uuid,
          ${item.productId},
          ${order.id}::uuid,
          ${entitlement.id}::uuid,
          ${checkinCode('EVT')},
          'registered'
        )
        on conflict (user_id, product_id) where status <> 'cancelled' do nothing
      `)
      const registrations = await tx.$queryRaw(Prisma.sql`
        select id::text as id
        from event_registrations
        where user_id = ${user.id}::uuid
          and product_id = ${item.productId}
          and status <> 'cancelled'
        order by case when order_id = ${order.id}::uuid then 0 else 1 end,
          created_at desc
        limit 1
      `)
      if (registrations[0]?.id) {
        eventRegistrationIds[item.productId] = registrations[0].id
        serviceEntries.push({
          productId: item.productId,
          productType: item.productType,
          eventRegistrationId: registrations[0].id,
        })
      }
    }

    if (item.productType === 'course') {
      const registrationNo = await nextBusinessNoTx(tx, 'REG')
      await tx.$executeRaw(Prisma.sql`
        insert into course_registrations (
          registration_no, user_id, product_id, order_id, entitlement_id, checkin_code, status
        )
        values (
          ${registrationNo},
          ${user.id}::uuid,
          ${item.productId},
          ${order.id}::uuid,
          ${entitlement.id}::uuid,
          ${checkinCode('CRS')},
          'registered'
        )
        on conflict (user_id, product_id) where status <> 'cancelled' do nothing
      `)
      const registrations = await tx.$queryRaw(Prisma.sql`
        select id::text as id
        from course_registrations
        where user_id = ${user.id}::uuid
          and product_id = ${item.productId}
          and status <> 'cancelled'
        order by case when order_id = ${order.id}::uuid then 0 else 1 end,
          created_at desc
        limit 1
      `)
      if (registrations[0]?.id) {
        courseRegistrationIds[item.productId] = registrations[0].id
        serviceEntries.push({
          productId: item.productId,
          productType: item.productType,
          courseRegistrationId: registrations[0].id,
        })
      }
    }

    if (item.productType === 'membership') {
      const subscription = await activateMembershipSubscriptionTx(tx, user, order, item)
      if (subscription?.id) {
        membershipSubscriptionIds[item.productId] = subscription.id
        serviceEntries.push({
          productId: item.productId,
          productType: item.productType,
          membershipSubscriptionId: subscription.id,
        })
      }
    }
  }

  if (serviceEntries.length > 0) {
    await tx.$executeRaw(Prisma.sql`
      update orders
      set settlement_snapshot = coalesce(settlement_snapshot, '{}'::jsonb) || ${JSON.stringify({
        serviceEntries,
        courseRegistrationIds,
        eventRegistrationIds,
        membershipSubscriptionIds,
        fulfillmentTaskIds,
      })}::jsonb,
          updated_at = now()
      where id = ${order.id}::uuid
    `)
  }

  try {
    await createCheckoutInvoiceApplicationTx(tx, user, order)
  } catch (error) {
    await insertOperationalEventTx(tx, {
      severity: 'warning',
      eventType: 'invoice.auto_create_failed',
      resourceType: 'order',
      resourceId: order.orderNo || order.id,
      message: '订单支付成功后自动创建发票申请失败',
      payload: {
        code: error.code || 'invoice_auto_create_failed',
        message: error.message || '',
        orderId: order.id,
        userId: user.id,
      },
    })
  }
}

export async function createCheckoutOrderRecord({
  user,
  preview,
  status,
  settlementSnapshot,
  expertBookingId = null,
  dataServiceRequestId = null,
  consultingRequestId = null,
}) {
  return withPrismaTransaction(async (tx) => {
    const orderNo = await nextBusinessNoTx(tx, 'ORD')
    const settlementNo = await nextBusinessNoTx(tx, 'CKO')
    const orderRows = await tx.$queryRaw(Prisma.sql`
      insert into orders (
        order_no, settlement_no, user_id, status, subtotal_amount, member_discount_amount,
        coupon_discount_amount, paid_amount, total_amount, currency, settlement_snapshot
      )
      values (
        ${orderNo},
        ${settlementNo},
        ${user.id}::uuid,
        ${status},
        ${Number(preview.subtotalAmount || 0)},
        ${Number(preview.memberDiscountAmount || 0)},
        ${Number(preview.couponDiscountAmount || 0)},
        ${Number(preview.paidAmount || preview.totalAmount || 0)},
        ${Number(preview.totalAmount || 0)},
        ${preview.currency || 'CNY'},
        ${JSON.stringify({ ...settlementSnapshot, settlementNo })}::jsonb
      )
      returning
        id::text as id,
        order_no as "orderNo",
        settlement_no as "settlementNo",
        user_id::text as "userId",
        status,
        subtotal_amount as "subtotalAmount",
        member_discount_amount as "memberDiscountAmount",
        coupon_discount_amount as "couponDiscountAmount",
        paid_amount as "paidAmount",
        total_amount as "totalAmount",
        currency,
        settlement_snapshot as "settlementSnapshot",
        created_at as "createdAt",
        updated_at as "updatedAt"
    `)
    const inserted = orderRows[0]

    for (const item of preview.items || []) {
      await tx.$executeRaw(Prisma.sql`
        insert into order_items (
          order_id, product_id, product_type, title_snapshot,
          unit_price_amount, quantity, payable_amount, billing_cycle
        )
        values (
          ${inserted.id}::uuid,
          ${item.productId},
          ${item.productType},
          ${item.titleSnapshot},
          ${Number(item.unitPriceAmount || 0)},
          ${Number(item.quantity || 1)},
          ${Number(item.payableAmount || 0)},
          ${item.billingCycle || 'once'}
        )
      `)
    }

    if (expertBookingId) {
      await tx.$executeRaw(Prisma.sql`
        update expert_bookings
        set order_id = ${inserted.id}::uuid,
            updated_at = now()
        where id = ${expertBookingId}::uuid
      `)
    }

    if (dataServiceRequestId) {
      await tx.$executeRaw(Prisma.sql`
        update data_service_requests
        set order_id = ${inserted.id}::uuid,
            status = ${status === 'paid' ? 'in_service' : 'pending_payment'},
            current_step = ${status === 'paid' ? 4 : 3},
            confirmed_at = coalesce(confirmed_at, now()),
            paid_at = case when ${status === 'paid'} then now() else paid_at end,
            updated_at = now()
        where id = ${dataServiceRequestId}::uuid
      `)
    }

    if (consultingRequestId) {
      await tx.$executeRaw(Prisma.sql`
        update consulting_requests
        set order_id = ${inserted.id}::uuid,
            status = ${status === 'paid' ? 'in_service' : 'pending_payment'},
            current_step = ${status === 'paid' ? 5 : 4},
            confirmed_at = coalesce(confirmed_at, now()),
            paid_at = case when ${status === 'paid'} then now() else paid_at end,
            updated_at = now()
        where id = ${consultingRequestId}::uuid
      `)
    }

    if (status === 'paid') {
      const order = await loadOrderForGrantTx(tx, inserted.id)
      await grantOrderResultsTx(tx, { ...user }, order)
      return loadOrderForGrantTx(tx, inserted.id)
    }

    return orderJson(inserted, preview.items || [])
  }, { timeoutMs: 20_000 })
}

export async function createPaymentOrderRecord({ orderId, channel, amount }) {
  return withPrismaTransaction(async (tx) => {
    const paymentNo = await nextBusinessNoTx(tx, 'PAY')
    await tx.$executeRaw(Prisma.sql`
      insert into payment_orders (payment_no, order_id, provider, channel, amount, status)
      values (
        ${paymentNo},
        ${orderId}::uuid,
        ${channel === 'dev' ? 'dev' : 'wechat'},
        ${channel},
        ${Number(amount || 0)},
        'created'
      )
    `)
    return { paymentNo }
  })
}

export async function failPaymentOrderRecord(paymentNo, error = {}) {
  await withPrismaTransaction(async (tx) => {
    await tx.$executeRaw(Prisma.sql`
      update payment_orders
      set status = 'failed',
          failure_code = ${error.code || 'wechat_pay_failed'},
          failure_message = ${error.message || '微信支付请求失败'},
          response_payload = ${JSON.stringify(error.details || {})}::jsonb,
          updated_at = now()
      where payment_no = ${String(paymentNo || '')}
    `)
  })
}

export async function markWechatJsapiPaymentPaying(paymentNo, { openid, result }) {
  await withPrismaTransaction(async (tx) => {
    await tx.$executeRaw(Prisma.sql`
      update payment_orders
      set status = 'paying',
          prepay_id = ${result.prepayId},
          payer_openid = ${openid},
          request_payload = ${JSON.stringify(result.requestPayload || {})}::jsonb,
          response_payload = ${JSON.stringify(result.responsePayload || {})}::jsonb,
          updated_at = now()
      where payment_no = ${paymentNo}
    `)
  })
}

export async function markWechatNativePaymentPaying(paymentNo, { result }) {
  await withPrismaTransaction(async (tx) => {
    await tx.$executeRaw(Prisma.sql`
      update payment_orders
      set status = 'paying',
          code_url = ${result.codeUrl},
          request_payload = ${JSON.stringify(result.requestPayload || {})}::jsonb,
          response_payload = ${JSON.stringify(result.responsePayload || {})}::jsonb,
          updated_at = now()
      where payment_no = ${paymentNo}
    `)
  })
}

export async function findMiniappPaymentOpenid(userId, appId) {
  const rows = await withPrismaTransaction(async (tx) => tx.$queryRaw(Prisma.sql`
    select openid
    from user_wechat_identities
    where provider = 'miniapp'
      and app_id = ${appId}
      and user_id = ${userId}::uuid
    order by coalesce(last_login_at, updated_at, created_at) desc
    limit 1
  `))
  return rows[0]?.openid || ''
}

export async function markPaymentSucceededAndGrant(payment, options = {}) {
  const key = String(payment?.id || payment?.paymentNo || '').trim()
  if (!key) {
    const error = new Error('支付单不存在')
    error.statusCode = 404
    error.code = 'payment_not_found'
    throw error
  }
  const channelTradeNo = String(options.channelTradeNo || payment.channelTradeNo || '')
  const notifyPayload = options.notifyPayload || {}

  return withPrismaTransaction(async (tx) => {
    const condition = isUuid(payment.id)
      ? Prisma.sql`po.id = ${payment.id}::uuid`
      : Prisma.sql`po.payment_no = ${payment.paymentNo}`
    const rows = await tx.$queryRaw(Prisma.sql`
      select
        po.id::text as id,
        po.payment_no as "paymentNo",
        po.channel,
        po.amount,
        po.status,
        po.channel_trade_no as "channelTradeNo",
        po.order_id::text as "orderId",
        o.status as "orderStatus",
        o.user_id::text as "userId"
      from payment_orders po
      join orders o on o.id = po.order_id
      where ${condition}
      for update of po, o
      limit 1
    `)
    const locked = rows[0]
    if (!locked) {
      const error = new Error('支付单不存在')
      error.statusCode = 404
      error.code = 'payment_not_found'
      throw error
    }
    if (locked.orderStatus !== 'pending_payment' && locked.orderStatus !== 'paid') {
      const error = new Error('订单当前不可支付')
      error.statusCode = 409
      error.code = 'order_not_payable'
      throw error
    }

    if (locked.status !== 'succeeded') {
      await tx.$executeRaw(Prisma.sql`
        update payment_orders
        set status = 'succeeded',
            channel_trade_no = coalesce(nullif(${channelTradeNo}, ''), channel_trade_no),
            notify_payload = case
              when ${JSON.stringify(notifyPayload)}::jsonb = '{}'::jsonb then notify_payload
              else ${JSON.stringify(notifyPayload)}::jsonb
            end,
            paid_at = coalesce(paid_at, now()),
            updated_at = now()
        where id = ${locked.id}::uuid
      `)
    }

    await tx.$executeRaw(Prisma.sql`
      update orders
      set status = 'paid',
          updated_at = now()
      where id = ${locked.orderId}::uuid
        and status <> 'paid'
    `)

    await tx.$executeRaw(Prisma.sql`
      insert into payment_transactions (
        payment_order_id, channel_trade_no, amount, status, raw_payload
      )
      select
        ${locked.id}::uuid,
        ${channelTradeNo},
        ${Number(locked.amount || 0)},
        'succeeded',
        ${JSON.stringify(notifyPayload)}::jsonb
      where not exists (
        select 1
        from payment_transactions
        where payment_order_id = ${locked.id}::uuid
          and status = 'succeeded'
          and coalesce(channel_trade_no, '') = coalesce(${channelTradeNo}, '')
      )
    `)

    const [order, user] = await Promise.all([
      loadOrderForGrantTx(tx, locked.orderId),
      loadUserForGrantTx(tx, locked.userId),
    ])
    if (!order || !user) {
      const error = new Error('订单或用户不存在')
      error.statusCode = 404
      error.code = 'order_user_not_found'
      throw error
    }

    try {
      await grantOrderResultsTx(tx, user, order)
    } catch (error) {
      await insertOperationalEventTx(tx, {
        severity: 'critical',
        eventType: 'order.entitlement_grant_failed',
        resourceType: 'order',
        resourceId: order.orderNo || order.id,
        message: '订单已支付但权益发放失败',
        payload: {
          code: error.code || 'entitlement_grant_failed',
          message: error.message || '',
          orderId: order.id,
          userId: user.id,
          items: order.items,
        },
      })
      throw error
    }

    return { paymentNo: locked.paymentNo, orderId: locked.orderId }
  }, { timeoutMs: 30_000 })
}
