import { string } from 'alga-js'
import refreshTokenHelper from "../../../utils/refreshTokenHelper"
import forceLogoutHelper from "../../../utils/forceLogoutHelper"
import errorHandlingHelper from "../../../utils/errorHandlingHelper"
import fetchHelper from "../../../utils/fetchHelper"
import {
  getAmazonAccessToken,
  resolveMarketplaceId,
  listAllOrders
} from "../../../utils/amazonSpApi"

// READ-ONLY reconciliation: pull every Amazon order in a date window and match
// each to an internal c_order so staff can spot orders that were never imported,
// totals that drifted, and matched orders that still lack an invoice. Writes
// nothing — only GETs c_order / c_invoice for the indicators.

type Matched = {
  id: number
  documentNo: string
  grandTotal: number
  isInvoiced: boolean
  docStatus: string
  currency: string
  dateOrdered: string
  dateAcct: string
  matchField: string
}

// Match Amazon order ids to internal c_order records. Looks up by amazon_order_id
// first (the canonical field), then ExternalOrderId for whatever is still missing.
const matchOrders = async (event: any, token: any, orderIds: string[]) => {
  const map: Record<string, Matched> = {}
  const ids = [...new Set(orderIds.filter(Boolean))]
  if (!ids.length) return { map, truncated: false }

  const CAP = 8000
  const truncated = ids.length > CAP
  const work = truncated ? ids.slice(0, CAP) : ids

  const toMatched = (rec: any, field: string): Matched => ({
    id: rec.id ?? rec.C_Order_ID,
    documentNo: rec.DocumentNo || '',
    grandTotal: typeof rec.GrandTotal === 'number' ? rec.GrandTotal : Number(rec.GrandTotal || 0),
    isInvoiced: rec.IsInvoiced === true || rec.IsInvoiced === 'true' || rec.IsInvoiced === 'Y',
    docStatus: rec.DocStatus?.id || rec.DocStatus?.identifier || rec.DocStatus || '',
    currency: rec.C_Currency_ID?.identifier || rec.C_Currency_ID?.ISO_Code || '',
    dateOrdered: rec.DateOrdered || '',
    dateAcct: rec.DateAcct || '',
    matchField: field
  })

  const lookup = async (field: string, pending: string[]) => {
    const CHUNK = 45
    for (let i = 0; i < pending.length; i += CHUNK) {
      const chunk = pending.slice(i, i + CHUNK)
      const filter = chunk.map(id => `${field} eq '${String(id).replace(/'/g, "''")}'`).join(' OR ')
      const res: any = await fetchHelper(
        event,
        `models/c_order?$filter=${string.urlEncode(filter)}&$select=${string.urlEncode('C_Order_ID,DocumentNo,GrandTotal,IsInvoiced,DocStatus,C_Currency_ID,DateOrdered,DateAcct,amazon_order_id,ExternalOrderId')}&$top=200`,
        'GET',
        token,
        null
      )
      for (const rec of (res?.records || [])) {
        // Register under both ids so a hit on either field resolves the order.
        if (rec.amazon_order_id) map[String(rec.amazon_order_id)] = toMatched(rec, 'amazon_order_id')
        if (rec.ExternalOrderId) map[String(rec.ExternalOrderId)] = toMatched(rec, 'ExternalOrderId')
      }
    }
  }

  await lookup('amazon_order_id', work)
  const stillMissing = work.filter(id => !map[id])
  if (stillMissing.length) await lookup('ExternalOrderId', stillMissing)

  return { map, truncated }
}

type InvoiceRef = { invoiceId: number; documentNo: string; grandTotal: number; docStatus: string }

// For matched orders, find any linked c_invoice so we can show the invoice no/link.
// Prefers a Completed/Closed invoice over a draft when an order has several.
const lookupInvoices = async (event: any, token: any, internalOrderIds: number[]) => {
  const map: Record<number, InvoiceRef> = {}
  const ids = [...new Set(internalOrderIds.filter(Boolean))]
  if (!ids.length) return map

  const CHUNK = 45
  for (let i = 0; i < ids.length; i += CHUNK) {
    const chunk = ids.slice(i, i + CHUNK)
    // Only live invoices count as "invoice exists" — exclude voided (VO) /
    // reversed (RE) ones, which iDempiere keeps IsActive=true. Whitelist matches
    // invoices/so.get.ts (DR = draft, CO = completed, CL = closed).
    const filter = chunk
      .map(id => `((C_Order_ID eq ${id}) and (DocStatus eq 'CO' or DocStatus eq 'CL' or DocStatus eq 'DR'))`)
      .join(' OR ')
    const res: any = await fetchHelper(
      event,
      `models/c_invoice?$filter=${string.urlEncode(filter)}&$select=${string.urlEncode('C_Invoice_ID,DocumentNo,GrandTotal,DocStatus,C_Order_ID')}&$top=400`,
      'GET',
      token,
      null
    )
    for (const rec of (res?.records || [])) {
      const oid = rec.C_Order_ID?.id ?? rec.C_Order_ID
      if (oid == null) continue
      const docStatus = rec.DocStatus?.id || rec.DocStatus?.identifier || rec.DocStatus || ''
      const ref: InvoiceRef = {
        invoiceId: rec.id ?? rec.C_Invoice_ID,
        documentNo: rec.DocumentNo || '',
        grandTotal: typeof rec.GrandTotal === 'number' ? rec.GrandTotal : Number(rec.GrandTotal || 0),
        docStatus
      }
      const existing = map[oid]
      // Tolerant to both the value code (CO/CL) and the display text iDempiere
      // may return for the DocStatus reference field.
      const isFinal = (s: string) => {
        const u = String(s || '').toUpperCase()
        return u === 'CO' || u === 'CL' || u.includes('COMPLET') || u.includes('CLOSED')
      }
      if (!existing || (!isFinal(existing.docStatus) && isFinal(docStatus))) {
        map[oid] = ref
      }
    }
  }
  return map
}

const round2 = (n: number) => Math.round(n * 100) / 100

// Calendar day (UTC, YYYY-MM-DD) of an ISO date/datetime. Amazon purchaseDate is
// UTC ("…Z"); iDempiere DateAcct is a plain date — both reduce to the leading day.
const dayOf = (s: string | null | undefined): string => {
  if (!s) return ''
  const str = String(s)
  const m = str.match(/^(\d{4}-\d{2}-\d{2})/)
  if (m) return m[1]
  const d = new Date(str)
  return isNaN(d.getTime()) ? '' : d.toISOString().slice(0, 10)
}

const handleFunc = async (event: any, authToken: any = null) => {
  const token = authToken ?? await getTokenHelper(event)
  const query = getQuery(event)
  const orderSourceId = query.orderSourceId

  if (!orderSourceId) return { status: 400, message: 'orderSourceId is required' }

  // --- Resolve & validate the Amazon order source -------------------------
  const orderSource: any = await fetchHelper(event, `models/c_ordersource/${orderSourceId}`, 'GET', token, null)
  if (!orderSource) return { status: 404, message: 'Order source not found' }

  const marketplaceIdentifier = String(orderSource?.Marketplace?.identifier || '').toLowerCase()
  const isAmazon = marketplaceIdentifier === 'amazon' || String(orderSource?.Marketplace?.id) === '4' || String(orderSource?.marketplace) === '4'
  if (!isAmazon) return { status: 400, message: `Order source "${orderSource?.Name || orderSourceId}" is not an Amazon marketplace` }

  if (!orderSource?.marketplace_key || !orderSource?.marketplace_secret || !orderSource?.marketplace_token) {
    return { status: 400, message: 'Order source is missing Amazon SP-API credentials' }
  }

  const marketplaceId = resolveMarketplaceId(orderSource?.Marketplace?.identifier, orderSource?.Description)
  if (!marketplaceId) return { status: 400, message: 'Could not resolve an Amazon marketplaceId from the order source' }

  // --- Compute the created-date window (year, or year+month) ---------------
  const now = new Date()
  const year = Number(query.year) || now.getUTCFullYear()
  const monthRaw = query.month != null && query.month !== '' ? Number(query.month) : null
  const month = monthRaw && monthRaw >= 1 && monthRaw <= 12 ? monthRaw : null

  let rangeStart: Date
  let rangeEnd: Date
  if (month) {
    rangeStart = new Date(Date.UTC(year, month - 1, 1, 0, 0, 0))
    rangeEnd = new Date(Date.UTC(year, month, 1, 0, 0, 0)) // exclusive: first of next month
  } else {
    rangeStart = new Date(Date.UTC(year, 0, 1, 0, 0, 0))
    rangeEnd = new Date(Date.UTC(year + 1, 0, 1, 0, 0, 0))
  }

  // Amazon requires CreatedBefore to be no later than 2 minutes before now.
  const cap = new Date(now.getTime() - 2 * 60 * 1000)
  if (rangeEnd > cap) rangeEnd = cap
  if (rangeStart >= cap) {
    return {
      status: 200,
      period: { year, month, createdAfter: rangeStart.toISOString(), createdBefore: cap.toISOString(), future: true },
      summary: emptySummary(),
      rows: []
    }
  }

  const createdAfter = rangeStart.toISOString()
  const createdBefore = rangeEnd.toISOString()

  const accessToken = await getAmazonAccessToken(
    orderSource.marketplace_key,
    orderSource.marketplace_secret,
    orderSource.marketplace_token
  )

  // --- Pull every Amazon order in the window -------------------------------
  const { orders, truncated: amazonTruncated, throttled, pages } =
    await listAllOrders(accessToken, marketplaceId, { createdAfter, createdBefore })

  // --- Match to internal orders --------------------------------------------
  const distinct = [...new Set(orders.map(o => o.amazonOrderId).filter(Boolean))]
  const { map, truncated: matchTruncated } = await matchOrders(event, token, distinct)

  // --- Invoice indicators for matched orders (best-effort) -----------------
  const matchedInternalIds: number[] = []
  for (const id of distinct) {
    const m = map[id]
    if (m?.id) matchedInternalIds.push(m.id)
  }
  let invoiceMap: Record<number, InvoiceRef> = {}
  let invoiceError: string | null = null
  try {
    invoiceMap = await lookupInvoices(event, token, matchedInternalIds)
  } catch (e: any) {
    invoiceError = e?.message || 'Invoice lookup failed'
  }

  // --- Build rows -----------------------------------------------------------
  const rows = orders.map(o => {
    const m = map[o.amazonOrderId] || null
    const matched = !!m
    const inv = m?.id ? invoiceMap[m.id] : null
    const invoiceExists = matched && (m.isInvoiced || !!inv)

    const amazonTotal = o.orderTotalAmount
    const idempiereTotal = m ? m.grandTotal : null

    let amountDiff: number | null = null
    let amountMismatch = false
    if (matched && amazonTotal != null && idempiereTotal != null) {
      amountDiff = round2(amazonTotal - idempiereTotal)
      amountMismatch = Math.abs(amountDiff) >= 0.01
    }

    const currencyMismatch = !!(matched && o.currency && m!.currency &&
      String(o.currency).toUpperCase() !== String(m!.currency).toUpperCase())

    const isCanceled = String(o.orderStatus).toLowerCase() === 'canceled'

    // Date check: Amazon purchase date vs the iDempiere accounted date (DateAcct).
    const purchaseDay = dayOf(o.purchaseDate)
    const acctDay = matched ? dayOf(m!.dateAcct) : ''
    let dateMismatch = false
    let dateDiffDays: number | null = null
    if (matched && purchaseDay && acctDay) {
      dateMismatch = purchaseDay !== acctDay
      dateDiffDays = Math.round(
        (new Date(purchaseDay + 'T00:00:00Z').getTime() - new Date(acctDay + 'T00:00:00Z').getTime()) / 86400000
      )
    }

    return {
      amazonOrderId: o.amazonOrderId,
      purchaseDate: o.purchaseDate,
      orderStatus: o.orderStatus,
      isCanceled,
      salesChannel: o.salesChannel,
      fulfillmentChannel: o.fulfillmentChannel,
      amazonTotal,
      amazonCurrency: o.currency,
      matched,
      matchField: m?.matchField || '',
      internalOrderId: m?.id || null,
      internalDocumentNo: m?.documentNo || '',
      idempiereTotal,
      idempiereCurrency: m?.currency || '',
      docStatus: m?.docStatus || '',
      idempiereDateOrdered: m?.dateOrdered || '',
      idempiereDateAcct: m?.dateAcct || '',
      dateMismatch,
      dateDiffDays,
      amountDiff,
      amountMismatch,
      currencyMismatch,
      invoiceExists,
      invoiceId: inv?.invoiceId || null,
      invoiceDocumentNo: inv?.documentNo || '',
      invoiceTotal: inv?.grandTotal ?? null,
      invoiceDocStatus: inv?.docStatus || ''
    }
  })

  // --- Summary KPIs ---------------------------------------------------------
  const matchedRows = rows.filter(r => r.matched)
  const missingRows = rows.filter(r => !r.matched)
  const missingCanceled = missingRows.filter(r => r.isCanceled).length
  const matchedCount = matchedRows.length
  const invoicedCount = matchedRows.filter(r => r.invoiceExists).length

  const summary = {
    totalOrders: rows.length,
    distinctOrders: distinct.length,
    matchedCount,
    missingCount: missingRows.length,
    missingActive: missingRows.length - missingCanceled,
    missingCanceled,
    mismatchCount: rows.filter(r => r.amountMismatch).length,
    currencyMismatchCount: rows.filter(r => r.currencyMismatch).length,
    dateMismatchCount: rows.filter(r => r.dateMismatch).length,
    invoicedCount,
    notInvoicedCount: matchedCount - invoicedCount,
    totalAmazonAmount: round2(rows.reduce((s, r) => s + (r.amazonTotal || 0), 0)),
    totalIdempiereAmount: round2(matchedRows.reduce((s, r) => s + (r.idempiereTotal || 0), 0)),
    currency: rows.find(r => r.amazonCurrency)?.amazonCurrency || 'EUR',
    amazonTruncated,
    matchTruncated,
    throttled,
    pages,
    invoiceError
  }

  return {
    status: 200,
    period: { year, month, createdAfter, createdBefore },
    summary,
    rows
  }
}

const emptySummary = () => ({
  totalOrders: 0, distinctOrders: 0, matchedCount: 0, missingCount: 0, missingActive: 0,
  missingCanceled: 0, mismatchCount: 0, currencyMismatchCount: 0, dateMismatchCount: 0, invoicedCount: 0,
  notInvoicedCount: 0, totalAmazonAmount: 0, totalIdempiereAmount: 0, currency: 'EUR',
  amazonTruncated: false, matchTruncated: false, throttled: false, pages: 0, invoiceError: null
})

export default defineEventHandler(async (event) => {
  let data: any = {}

  try {
    data = await handleFunc(event)
  } catch (err: any) {
    // SP-API errors carry a status — surface them verbatim instead of treating
    // them as an iDempiere auth failure.
    if (err?.status && err?.message) {
      return { status: err.status, message: err.message, spapi: err.spapi }
    }
    try {
      let authToken: any = await refreshTokenHelper(event)
      data = await handleFunc(event, authToken)
    } catch (error: any) {
      data = errorHandlingHelper(err?.data ?? err, error?.data ?? error)
      forceLogoutHelper(event, data)
    }
  }

  return data
})