// Server-only per-number delivery reports. Each SMS is sent with the ref
// `C<campaign>-<index>` (see messageRef), so its status can be asked of Moolre
// later. Statuses are fetched when someone looks at a send's numbers, at most
// every 20 seconds per send and only for numbers not already final, and stored
// in campaigns.delivery.
import { and, eq, like, sql } from 'drizzle-orm'
import { db } from '../../db/index.js'
import { appSettings, campaigns } from '../../db/schema.js'
import { DELIVERY_CODES, isFinalState, recipientState, type DeliveryLabel, type RecipientState } from './delivery'
import { messageRef } from './messaging.server'
import { SMS_STATUS_BATCH, getSmsStatuses } from './moolre.server'

const LABEL_PREFIX = 'sms.dlr.'
const CACHE_MS = 30_000
const REFRESH_EVERY_MS = 20_000
// Moolre is only asked about recent sends; older reports stay as last seen.
const REFRESH_WINDOW_MS = 7 * 24 * 60 * 60_000

let cached: { at: number; labels: Map<number, DeliveryLabel> } | null = null

export async function getDeliveryLabels() {
  if (cached && Date.now() - cached.at < CACHE_MS) return cached.labels
  const rows = await db
    .select({ key: appSettings.key, value: appSettings.value })
    .from(appSettings)
    .where(like(appSettings.key, `${LABEL_PREFIX}%`))
  const labels = new Map<number, DeliveryLabel>()
  for (const r of rows) {
    const code = Number(r.key.slice(LABEL_PREFIX.length))
    if (Number.isInteger(code) && (r.value === 'delivered' || r.value === 'failed' || r.value === 'pending')) labels.set(code, r.value)
  }
  cached = { at: Date.now(), labels }
  return labels
}

// Saves (or clears, when null) the label for each Moolre status number.
export async function saveDeliveryLabels(changes: Record<number, DeliveryLabel | null>, updatedBy: string) {
  for (const [codeText, value] of Object.entries(changes)) {
    const code = Number(codeText)
    if (!DELIVERY_CODES.includes(code)) continue
    const key = `${LABEL_PREFIX}${code}`
    if (value === null) await db.delete(appSettings).where(eq(appSettings.key, key))
    else {
      await db
        .insert(appSettings)
        .values({ key, value, updatedBy })
        .onConflictDoUpdate({ target: appSettings.key, set: { value, updatedBy, updatedAt: new Date() } })
    }
  }
  cached = null
}

// How often each Moolre status number appears in recent sends, so admins can
// see which numbers need a label.
export async function recentDeliveryCodes() {
  const rows = await db.execute<{ code: string; count: string }>(sql`
    select d.value as code, count(*) as count
    from ${campaigns}, jsonb_each_text(coalesce(${campaigns.delivery}, '{}'::jsonb)) as d
    where ${campaigns.deliveryCheckedAt} > now() - interval '30 days' and d.value <> '-1'
    group by d.value
    order by count(*) desc
  `)
  const list = 'rows' in rows ? (rows.rows as { code: string; count: string }[]) : (rows as unknown as { code: string; count: string }[])
  return list.map((r) => ({ code: Number(r.code), count: Number(r.count) }))
}

type CampaignRow = typeof campaigns.$inferSelect

// Asks Moolre about the given recipient indexes (skipping final ones) and
// stores the answers. Returns the campaign's updated delivery map.
async function refreshIndexes(c: CampaignRow, indexes: number[], labels: Map<number, DeliveryLabel>) {
  const delivery = { ...(c.delivery ?? {}) }
  const sentAt = c.scheduledFor ?? c.createdAt
  const recentEnough = Date.now() - sentAt.getTime() < REFRESH_WINDOW_MS
  const due = !c.deliveryCheckedAt || Date.now() - c.deliveryCheckedAt.getTime() > REFRESH_EVERY_MS
  if (c.channel !== 'sms' || !recentEnough || !due || c.status === 'scheduled' || c.status === 'cancelled') {
    return { delivery, checkedAt: c.deliveryCheckedAt, error: null as string | null }
  }

  const toCheck = indexes.filter((i) => !isFinalState(recipientState(c, i, delivery[i], labels)) && i < c.sendProgress)
  if (toCheck.length === 0) return { delivery, checkedAt: c.deliveryCheckedAt, error: null }

  const patch: Record<string, number> = {}
  let error: string | null = null
  for (let i = 0; i < toCheck.length; i += SMS_STATUS_BATCH) {
    const chunk = toCheck.slice(i, i + SMS_STATUS_BATCH)
    try {
      const statuses = await getSmsStatuses(chunk.map((index) => messageRef(c.id, index)))
      for (const index of chunk) {
        const code = statuses.get(messageRef(c.id, index))
        if (code != null) patch[index] = code
      }
    } catch (err) {
      error = err instanceof Error ? err.message : 'Moolre did not return delivery statuses.'
      break
    }
  }
  const checkedAt = new Date()
  await db
    .update(campaigns)
    .set({
      deliveryCheckedAt: checkedAt,
      ...(Object.keys(patch).length ? { delivery: sql`coalesce(${campaigns.delivery}, '{}'::jsonb) || ${JSON.stringify(patch)}::jsonb` } : {}),
    })
    .where(eq(campaigns.id, c.id))
  Object.assign(delivery, patch)
  return { delivery, checkedAt, error }
}

export type RecipientsPage = {
  campaign: CampaignRow
  page: number
  perPage: number
  total: number
  counts: Record<RecipientState, number>
  recipients: { number: string; state: RecipientState; providerStatus: number | null }[]
  checkedAt: Date | null
  refreshError: string | null
}

// One page of a send's numbers with their delivery state, refreshing that
// page from Moolre first when due. Null when the send isn't the user's.
export async function recipientsPage(userId: string, campaignId: number, page: number, perPage: number): Promise<RecipientsPage | null> {
  const [c] = await db
    .select()
    .from(campaigns)
    .where(and(eq(campaigns.id, campaignId), eq(campaigns.userId, userId)))
  if (!c) return null
  const labels = await getDeliveryLabels()
  const start = (page - 1) * perPage
  const indexes = Array.from({ length: Math.max(0, Math.min(perPage, c.recipientCount - start)) }, (_, i) => start + i)
  const { delivery, checkedAt, error } = await refreshIndexes(c, indexes, labels)

  const counts = { scheduled: 0, cancelled: 0, queued: 0, not_sent: 0, sent: 0, delivered: 0, failed: 0, pending: 0 } as Record<RecipientState, number>
  for (let i = 0; i < c.recipientCount; i++) counts[recipientState(c, i, delivery[i], labels)]++

  return {
    campaign: c,
    page,
    perPage,
    total: c.recipientCount,
    counts,
    recipients: indexes.map((i) => {
      const code = delivery[i]
      return { number: c.recipients[i], state: recipientState(c, i, code, labels), providerStatus: code == null || code === -1 ? null : code }
    }),
    checkedAt,
    refreshError: error,
  }
}
