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 dateRangeConditions(column, { from = '', to = '' } = {}) {
  const conditions = []
  const fromDate = String(from || '').trim()
  const toDate = String(to || '').trim()
  if (fromDate) conditions.push(Prisma.sql`${column} >= ${fromDate}::date`)
  if (toDate) conditions.push(Prisma.sql`${column} < (${toDate}::date + interval '1 day')`)
  return conditions
}

function orderIdsSql(ids) {
  return Prisma.join(ids.map((id) => Prisma.sql`${id}::uuid`))
}

function serviceEntryForOrderItem(item, settlementSnapshot = {}) {
  const productType = String(item.productType || '')
  const productId = String(item.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(item.dataServiceRequestId || snapshotEntry.dataServiceRequestId || settlementSnapshot.dataServiceRequestId || '')
  const consultingRequestId = String(item.consultingRequestId || snapshotEntry.consultingRequestId || settlementSnapshot.consultingRequestId || '')
  const expertBookingId = String(item.expertBookingId || snapshotEntry.expertBookingId || settlementSnapshot.expertBookingId || '')
  const courseRegistrationId = String(item.courseRegistrationId || snapshotEntry.courseRegistrationId || settlementSnapshot.courseRegistrationId || settlementSnapshot.courseRegistrationIds?.[productId] || '')
  const eventRegistrationId = String(item.eventRegistrationId || snapshotEntry.eventRegistrationId || settlementSnapshot.eventRegistrationId || settlementSnapshot.eventRegistrationIds?.[productId] || '')
  const membershipSubscriptionId = String(item.membershipSubscriptionId || snapshotEntry.membershipSubscriptionId || settlementSnapshot.membershipSubscriptionId || settlementSnapshot.membershipSubscriptionIds?.[productId] || '')
  const fulfillmentTaskId = String(item.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(item, settlementSnapshot = {}) {
  const serviceEntry = serviceEntryForOrderItem(item, settlementSnapshot)
  return {
    productId: item.productId,
    productType: item.productType,
    titleSnapshot: item.titleSnapshot,
    unitPriceAmount: item.unitPriceAmount,
    quantity: item.quantity,
    payableAmount: item.payableAmount,
    billingCycle: item.billingCycle || 'once',
    fulfillmentStatus: settlementSnapshot.fulfillmentStatus || '',
    ...serviceEntry,
  }
}

function invoiceJson(row) {
  if (!row) return null
  return {
    id: row.id,
    invoiceNo: row.invoiceNo || '',
    invoiceType: row.invoiceType || 'normal_e_invoice',
    invoiceTypeLabel: '普通电子发票',
    orderId: row.orderId,
    orderNo: row.orderNo || '',
    settlementNo: row.settlementNo || '',
    userId: row.userId || '',
    userPhone: row.userPhone || '',
    title: row.title || '',
    taxNumber: row.taxNumber || '',
    email: row.email || '',
    amount: row.amount || 0,
    status: row.status || 'pending',
    provider: row.provider || 'manual',
    providerInvoiceId: row.providerInvoiceId || '',
    pdfFileId: row.pdfFileId || null,
    fileName: row.fileName || '',
    fileUrl: row.pdfFileId ? `/api/files/${row.pdfFileId}` : '',
    failureReason: row.failureReason || '',
    remark: row.remark || '',
    adminNote: row.adminNote || '',
    issuedBy: row.issuedBy || '',
    issuedAt: row.issuedAt,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    fileUploadedAt: row.fileUploadedAt,
  }
}

function orderJson(row, items = [], invoice = null) {
  if (!row) return null
  const settlementSnapshot = prismaJson(row.settlementSnapshot, {})
  return {
    id: row.id,
    orderNo: row.orderNo,
    settlementNo: row.settlementNo,
    userId: row.userId,
    ...(row.userPhone !== undefined ? { userPhone: row.userPhone || '' } : {}),
    status: row.status,
    items: items.map((item) => orderItemJson(item, settlementSnapshot)),
    subtotalAmount: row.subtotalAmount,
    memberDiscountAmount: row.memberDiscountAmount,
    couponDiscountAmount: row.couponDiscountAmount,
    paidAmount: row.paidAmount,
    totalAmount: row.totalAmount,
    currency: row.currency || 'CNY',
    settlementSnapshot,
    ...(invoice !== undefined ? {
      invoiceStatus: invoice?.status || '',
      invoiceApplication: invoice,
    } : {}),
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  }
}

async function loadOrderItems(orderIds) {
  if (!orderIds.length) return new Map()
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select
      oi.order_id::text as "orderId",
      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 in (${orderIdsSql(orderIds)})
    order by oi.id
  `)
  const map = new Map()
  for (const row of rows) {
    const current = map.get(row.orderId) || []
    current.push(row)
    map.set(row.orderId, current)
  }
  return map
}

async function loadLatestInvoices(orderIds) {
  if (!orderIds.length) return new Map()
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select distinct on (ia.order_id)
      ia.id::text as id,
      ia.invoice_no as "invoiceNo",
      coalesce(ia.invoice_type, 'normal_e_invoice') as "invoiceType",
      ia.order_id::text as "orderId",
      o.order_no as "orderNo",
      o.settlement_no as "settlementNo",
      o.user_id::text as "userId",
      u.phone as "userPhone",
      ia.title,
      coalesce(ia.tax_number, '') as "taxNumber",
      ia.email,
      ia.amount,
      ia.status,
      coalesce(ia.provider, 'manual') as provider,
      coalesce(ia.provider_invoice_id, '') as "providerInvoiceId",
      ia.pdf_file_id::text as "pdfFileId",
      coalesce(f.file_name, '') as "fileName",
      coalesce(ia.failure_reason, '') as "failureReason",
      coalesce(ia.remark, '') as remark,
      coalesce(ia.admin_note, '') as "adminNote",
      coalesce(ia.issued_by, '') as "issuedBy",
      ia.issued_at as "issuedAt",
      ia.created_at as "createdAt",
      ia.updated_at as "updatedAt",
      ia.file_uploaded_at as "fileUploadedAt"
    from invoice_applications ia
    join orders o on o.id = ia.order_id
    join users u on u.id = o.user_id
    left join uploaded_files f on f.id = ia.pdf_file_id
    where ia.order_id in (${orderIdsSql(orderIds)})
    order by ia.order_id, ia.created_at desc
  `)
  return new Map(rows.map((row) => [row.orderId, invoiceJson(row)]))
}

async function hydrateOrders(rows, { includeInvoice = true } = {}) {
  const orderIds = rows.map((row) => row.id).filter(Boolean)
  const [itemsByOrder, invoicesByOrder] = await Promise.all([
    loadOrderItems(orderIds),
    includeInvoice ? loadLatestInvoices(orderIds) : Promise.resolve(new Map()),
  ])
  return rows.map((row) => orderJson(
    row,
    itemsByOrder.get(row.id) || [],
    includeInvoice ? invoicesByOrder.get(row.id) || null : undefined,
  ))
}

const baseOrderSelect = Prisma.sql`
  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"
`

export async function listUserOrders(userId) {
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${baseOrderSelect}
    from orders o
    where o.user_id = ${userId}::uuid
    order by o.created_at desc
  `)
  return hydrateOrders(rows)
}

export async function findUserOrder(userId, orderKey) {
  const key = String(orderKey || '').trim()
  if (!key) return null
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${baseOrderSelect}
    from orders o
    where o.user_id = ${userId}::uuid
      and (
        o.order_no = ${key}
        or o.legacy_order_no = ${key}
        or (${isUuid(key)}::boolean and o.id = ${isUuid(key) ? key : '00000000-0000-0000-0000-000000000000'}::uuid)
      )
    limit 1
  `)
  const orders = await hydrateOrders(rows, { includeInvoice: false })
  return orders[0] || null
}

export async function findOrderById(orderId) {
  if (!isUuid(orderId)) return null
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select ${baseOrderSelect}
    from orders o
    where o.id = ${orderId}::uuid
    limit 1
  `)
  const orders = await hydrateOrders(rows, { includeInvoice: false })
  return orders[0] || null
}

function adminOrderConditions(filters = {}) {
  const conditions = []
  const status = String(filters.status || '').trim()
  const search = String(filters.search || '').trim()
  if (status) conditions.push(Prisma.sql`o.status = ${status}`)
  conditions.push(...dateRangeConditions(Prisma.sql`o.created_at`, filters))
  if (search) {
    const like = `%${search}%`
    conditions.push(Prisma.sql`(
      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 listAdminOrdersRecord(filters = {}) {
  const prisma = prismaClient()
  const conditions = adminOrderConditions(filters)
  const where = conditions.length ? Prisma.sql`where ${Prisma.join(conditions, ' and ')}` : Prisma.empty
  const rows = await prisma.$queryRaw(Prisma.sql`
    select
      ${baseOrderSelect},
      u.phone as "userPhone"
    from orders o
    join users u on u.id = o.user_id
    ${where}
    order by o.created_at desc
  `)
  return hydrateOrders(rows)
}

export async function findAdminOrderRecord(orderKey) {
  const key = String(orderKey || '').trim()
  if (!key) return null
  const prisma = prismaClient()
  const rows = await prisma.$queryRaw(Prisma.sql`
    select
      ${baseOrderSelect},
      u.phone as "userPhone"
    from orders o
    join users u on u.id = o.user_id
    where (
      (${isUuid(key)}::boolean and o.id = ${isUuid(key) ? key : '00000000-0000-0000-0000-000000000000'}::uuid)
      or o.order_no = ${key}
      or o.legacy_order_no = ${key}
      or o.settlement_no = ${key}
    )
    limit 1
  `)
  const orders = await hydrateOrders(rows)
  return orders[0] || null
}
