import 'server-only'

import { randomBytes } from 'node:crypto'
import { createAdminClient } from '@/lib/supabase/admin'
import { sendEmail } from '@/lib/adapters/email'
import { recipientContext } from '@/lib/email/send'
import { preSendDecision } from '@/lib/domain/email-policy'
import { SUPPORT_EMAIL } from '@/lib/app-url'
import { emailPattern } from '@/lib/like'
import type { DeliveryEvent } from '@/lib/domain/zeptomail-webhook'
import {
  capabilityKey,
  findDuplicate,
  followUpDueAt,
  hasUnresolvedVariables,
  mergeStates,
  normalizeEmail,
  normalizePhone,
  ratio,
  renderPilotTemplate,
  sendBlockFor,
  sendDayKey,
  type CapabilityType,
  type ImportRow,
  type InvitationStatus,
  type PilotCampaign,
  type PilotNetworkAnalytics,
  type PilotProfile,
  type ProfileCapability,
  type SendBlock,
  type SuppressionReason,
  type EmailPerformance,
} from '@/lib/domain/pilot-network'

/**
 * Pilot Network data layer (Pilot Invitation Manager, 2026-09-14). Every
 * write goes through the service role; every admin write is audited (§26).
 * No email is delivered (answer 1): sends are recorded through the adapter.
 */

type Admin = ReturnType<typeof createAdminClient>

const PROFILE_COLUMNS =
  'id, name, company_name, email, phone, phone_digits, primary_state, related_states, account_type, claim_status, invitation_status, email_opt_in_status, sms_opt_in_status, source, source_reference, last_contacted_at, next_follow_up_at, claimed_user_id, claimed_company_id, claimed_at, admin_notes, claim_token, unsubscribe_token, created_at, updated_at'

/* ------------------------------ Availability ------------------------------ */

/** False until migration 0018 is applied — the page shows a banner instead of failing. */
export async function pilotNetworkAvailable(admin: Admin = createAdminClient()): Promise<boolean> {
  const { error } = await admin.from('pilot_profiles').select('id').limit(1)
  return !error
}

/* -------------------------------- Audit (§26) -------------------------------- */

export async function logPilotNetworkAction(params: {
  actorId?: string | null
  actorLabel: string
  action: string
  entityType: 'profile' | 'campaign' | 'template' | 'setting' | 'import'
  entityId?: string | null
  detail?: Record<string, unknown>
}, admin: Admin = createAdminClient()) {
  await admin.from('pilot_network_audit_log').insert({
    actor_id: params.actorId ?? null,
    actor_label: params.actorLabel,
    action: params.action,
    entity_type: params.entityType,
    entity_id: params.entityId ?? null,
    detail: params.detail ?? null,
  })
}

/* ------------------------------- Capabilities ------------------------------- */

export async function loadCapabilityTypes(admin: Admin = createAdminClient()): Promise<CapabilityType[]> {
  const { data } = await admin.from('pilot_capability_types').select('id, key, label, group_name, sort_order, active').order('sort_order')
  return ((data ?? []) as Array<Omit<CapabilityType, 'group'> & { group_name: CapabilityType['group'] }>).map(({ group_name, ...t }) => ({ ...t, group: group_name }))
}

async function capabilitiesFor(admin: Admin, profileIds: string[]): Promise<Map<string, ProfileCapability[]>> {
  const map = new Map<string, ProfileCapability[]>()
  if (profileIds.length === 0) return map
  const { data } = await admin
    .from('pilot_profile_capabilities')
    .select('profile_id, capability_type_id, data_status, pilot_capability_types ( key, label, group_name, sort_order )')
    .in('profile_id', profileIds)
  for (const row of (data ?? []) as unknown as Array<{ profile_id: string; capability_type_id: string; data_status: ProfileCapability['data_status']; pilot_capability_types: { key: string; label: string; group_name: ProfileCapability['group']; sort_order: number } | null }>) {
    const t = row.pilot_capability_types
    if (!t) continue
    const list = map.get(row.profile_id) ?? []
    list.push({ capability_type_id: row.capability_type_id, key: t.key, label: t.label, group: t.group_name, data_status: row.data_status })
    map.set(row.profile_id, list)
  }
  for (const list of map.values()) list.sort((a, b) => a.label.localeCompare(b.label))
  return map
}

async function suppressionsFor(admin: Admin, emails: string[]): Promise<Map<string, SuppressionReason>> {
  const map = new Map<string, SuppressionReason>()
  if (emails.length === 0) return map
  const { data } = await admin.from('pilot_suppressions').select('email, reason').in('email', emails.map(normalizeEmail))
  for (const row of (data ?? []) as Array<{ email: string; reason: SuppressionReason }>) map.set(normalizeEmail(row.email), row.reason)
  return map
}

async function hydrate(admin: Admin, rows: Record<string, unknown>[]): Promise<PilotProfile[]> {
  const ids = rows.map((r) => r.id as string)
  const [caps, sup] = await Promise.all([capabilitiesFor(admin, ids), suppressionsFor(admin, rows.map((r) => r.email as string))])
  return rows.map((r) => ({
    ...(r as unknown as PilotProfile),
    related_states: (r.related_states as string[] | null) ?? [],
    capabilities: caps.get(r.id as string) ?? [],
    suppression_reason: sup.get(normalizeEmail(r.email as string)) ?? null,
  }))
}

/* --------------------------------- Profiles --------------------------------- */

export interface ProfileFilters {
  q?: string
  state?: string
  capabilities?: string[]
  status?: InvitationStatus | ''
  page?: number
  pageSize?: number
}

export async function loadProfiles(filters: ProfileFilters, admin: Admin = createAdminClient()): Promise<{ profiles: PilotProfile[]; total: number; page: number; pageSize: number }> {
  const page = Math.max(1, filters.page ?? 1)
  const pageSize = Math.min(200, Math.max(10, filters.pageSize ?? 50))
  let query = admin.from('pilot_profiles').select(PROFILE_COLUMNS, { count: 'exact' })
  if (filters.q?.trim()) {
    const q = filters.q.trim().replace(/[%_,()]/g, ' ')
    const digits = q.replace(/\D/g, '')
    const parts = [`name.ilike.%${q}%`, `company_name.ilike.%${q}%`, `email.ilike.%${q}%`]
    if (digits.length >= 3) parts.push(`phone_digits.ilike.%${digits}%`)
    query = query.or(parts.join(','))
  }
  if (filters.state) query = query.or(`primary_state.eq.${filters.state},related_states.cs.{${filters.state}}`)
  if (filters.status) query = query.eq('invitation_status', filters.status)
  if (filters.capabilities?.length) {
    const { data: links } = await admin
      .from('pilot_profile_capabilities')
      .select('profile_id, pilot_capability_types!inner ( key )')
      .in('pilot_capability_types.key', filters.capabilities)
    const ids = [...new Set(((links ?? []) as Array<{ profile_id: string }>).map((l) => l.profile_id))]
    if (ids.length === 0) return { profiles: [], total: 0, page, pageSize }
    query = query.in('id', ids)
  }
  const from = (page - 1) * pageSize
  const { data, count } = await query.order('created_at', { ascending: false }).range(from, from + pageSize - 1)
  return { profiles: await hydrate(admin, (data ?? []) as Record<string, unknown>[]), total: count ?? 0, page, pageSize }
}

export async function loadProfile(id: string, admin: Admin = createAdminClient()): Promise<PilotProfile | null> {
  const { data } = await admin.from('pilot_profiles').select(PROFILE_COLUMNS).eq('id', id).maybeSingle()
  if (!data) return null
  const [p] = await hydrate(admin, [data as Record<string, unknown>])
  return p
}

export interface InvitationEvent {
  id: string
  profile_id: string
  campaign_id: string | null
  template_key: string | null
  kind: string
  email_status: string | null
  rendered_subject: string | null
  detail: Record<string, unknown> | null
  created_at: string
}

export interface AuditRow {
  id: string
  actor_label: string
  action: string
  entity_type: string
  entity_id: string | null
  detail: Record<string, unknown> | null
  created_at: string
}

export async function loadProfileHistory(id: string, admin: Admin = createAdminClient()): Promise<{ events: InvitationEvent[]; audit: AuditRow[] }> {
  const [ev, au] = await Promise.all([
    admin.from('pilot_invitation_events').select('*').eq('profile_id', id).order('created_at', { ascending: false }).limit(200),
    admin.from('pilot_network_audit_log').select('*').eq('entity_type', 'profile').eq('entity_id', id).order('created_at', { ascending: false }).limit(200),
  ])
  return { events: (ev.data ?? []) as InvitationEvent[], audit: (au.data ?? []) as AuditRow[] }
}

/* ------------------------------ Summary (§9) ------------------------------ */

export interface PilotNetworkSummary {
  total: number
  unclaimed: number
  approved: number
  invitations_sent: number
  claimed: number
  opted_in: number
  unsubscribed: number
  bounced_invalid: number
}

async function countWhere(admin: Admin, apply: (q: ReturnType<Admin['from']>) => unknown): Promise<number> {
  const q = admin.from('pilot_profiles')
  const res = (await (apply(q) as PromiseLike<{ count: number | null }>)) as { count: number | null }
  return res.count ?? 0
}

export async function loadSummary(admin: Admin = createAdminClient()): Promise<PilotNetworkSummary> {
  const head = { count: 'exact' as const, head: true }
  const [total, unclaimed, approved, sent, claimed, optedIn, unsub, bounced] = await Promise.all([
    countWhere(admin, (q) => q.select('id', head)),
    countWhere(admin, (q) => q.select('id', head).eq('claim_status', 'unclaimed')),
    countWhere(admin, (q) => q.select('id', head).eq('invitation_status', 'approved_for_invitation')),
    countWhere(admin, (q) => q.select('id', head).not('last_contacted_at', 'is', null)),
    countWhere(admin, (q) => q.select('id', head).eq('claim_status', 'claimed')),
    countWhere(admin, (q) => q.select('id', head).eq('email_opt_in_status', 'opted_in')),
    countWhere(admin, (q) => q.select('id', head).eq('email_opt_in_status', 'unsubscribed')),
    (async () => {
      const { count } = await admin.from('pilot_suppressions').select('id', head).in('reason', ['bounced', 'invalid_email'])
      return count ?? 0
    })(),
  ])
  return { total, unclaimed, approved, invitations_sent: sent, claimed, opted_in: optedIn, unsubscribed: unsub, bounced_invalid: bounced }
}

/* ------------------------------ Analytics (§25) ------------------------------ */

/**
 * Email performance for the pilot pipeline (Nash, 2026-09-23). Reads the
 * platform's own message log, so it reflects exactly what the provider
 * accepted and what its webhooks reported back.
 */
async function loadEmailPerformance(admin: Admin): Promise<EmailPerformance> {
  const empty: EmailPerformance = {
    available: false, attempted: 0, sent: 0, delivered: 0, opened: 0, clicked: 0, hard_bounced: 0, soft_bounced: 0, complained: 0, suppressed: 0, failed: 0, unsubscribed: 0,
    delivered_rate: null, open_rate: null, click_rate: null, bounce_rate: null, by_template: [], by_day: [],
  }
  const since = new Date(Date.now() - 30 * 86_400_000).toISOString()
  const { data, error } = await admin
    .from('email_messages')
    .select('template_key, template_version, status, sent_at, delivered_at, opened_at, clicked_at, created_at, suppressed_reason')
    .eq('campaign_type', 'pilot_recruitment')
    .limit(20_000)
  if (error) return empty
  const rows = (data ?? []) as Array<{ template_key: string | null; template_version: number | null; status: string; sent_at: string | null; delivered_at: string | null; opened_at: string | null; clicked_at: string | null; created_at: string; suppressed_reason: string | null }>
  const out: EmailPerformance = { ...empty, available: true }
  const byTemplate = new Map<string, EmailPerformance['by_template'][number]>()
  const byDay = new Map<string, EmailPerformance['by_day'][number]>()
  for (const m of rows) {
    out.attempted++
    const wasSent = !!m.sent_at || ['sent', 'delivered', 'soft_bounced', 'hard_bounced', 'complained'].includes(m.status)
    if (wasSent) out.sent++
    if (m.delivered_at) out.delivered++
    if (m.opened_at) out.opened++
    if (m.clicked_at) out.clicked++
    if (m.status === 'hard_bounced') out.hard_bounced++
    if (m.status === 'soft_bounced') out.soft_bounced++
    if (m.status === 'complained') out.complained++
    if (m.status === 'suppressed') out.suppressed++
    if (m.status === 'failed') out.failed++
    const key = `${m.template_key ?? '?'}@${m.template_version ?? 0}`
    const t = byTemplate.get(key) ?? { template_key: m.template_key ?? '?', version: m.template_version, sent: 0, delivered: 0, opened: 0, clicked: 0 }
    if (wasSent) t.sent++
    if (m.delivered_at) t.delivered++
    if (m.opened_at) t.opened++
    if (m.clicked_at) t.clicked++
    byTemplate.set(key, t)
    const day = (m.sent_at ?? m.created_at).slice(0, 10)
    if (day >= since.slice(0, 10)) {
      const d = byDay.get(day) ?? { day, sent: 0, delivered: 0, opened: 0, clicked: 0 }
      if (wasSent) d.sent++
      if (m.delivered_at) d.delivered++
      if (m.opened_at) d.opened++
      if (m.clicked_at) d.clicked++
      byDay.set(day, d)
    }
  }
  const { count: unsubs } = await admin.from('email_suppressions').select('id', { count: 'exact', head: true }).eq('reason', 'user_unsubscribe').is('overridden_at', null)
  out.unsubscribed = unsubs ?? 0
  out.delivered_rate = ratio(out.delivered, out.sent)
  out.open_rate = ratio(out.opened, out.delivered)
  out.click_rate = ratio(out.clicked, out.delivered)
  out.bounce_rate = ratio(out.hard_bounced + out.soft_bounced, out.sent)
  out.by_template = [...byTemplate.values()].sort((a, b) => b.sent - a.sent)
  out.by_day = [...byDay.values()].sort((a, b) => a.day.localeCompare(b.day))
  return out
}

export async function loadAnalytics(admin: Admin = createAdminClient()): Promise<PilotNetworkAnalytics> {
  const email = await loadEmailPerformance(admin)
  const [{ data: profiles }, { data: caps }, { data: sends }, { data: bounces }] = await Promise.all([
    admin.from('pilot_profiles').select('id, primary_state, related_states, claim_status, email_opt_in_status, last_contacted_at'),
    admin.from('pilot_profile_capabilities').select('pilot_capability_types ( label )'),
    admin.from('pilot_invitation_events').select('profile_id, kind').in('kind', ['invitation_sent', 'follow_up_sent']),
    admin.from('pilot_invitation_events').select('profile_id').eq('kind', 'bounced'),
  ])
  const rows = (profiles ?? []) as Array<{ id: string; primary_state: string; related_states: string[] | null; claim_status: string; email_opt_in_status: string; last_contacted_at: string | null }>
  const byState = new Map<string, number>()
  for (const p of rows) for (const s of new Set([p.primary_state, ...(p.related_states ?? [])])) byState.set(s, (byState.get(s) ?? 0) + 1)
  const byCap = new Map<string, number>()
  for (const c of (caps ?? []) as unknown as Array<{ pilot_capability_types: { label: string } | null }>) {
    const label = c.pilot_capability_types?.label
    if (label) byCap.set(label, (byCap.get(label) ?? 0) + 1)
  }
  const invited = new Set(((sends ?? []) as Array<{ profile_id: string }>).map((s) => s.profile_id))
  const bounced = new Set(((bounces ?? []) as Array<{ profile_id: string }>).map((s) => s.profile_id))
  const claimed = rows.filter((p) => p.claim_status === 'claimed').length
  return {
    total_imported: rows.length,
    invitations_sent: (sends ?? []).length,
    claimed,
    opted_in: rows.filter((p) => p.email_opt_in_status === 'opted_in').length,
    unsubscribed: rows.filter((p) => p.email_opt_in_status === 'unsubscribed').length,
    bounced: bounced.size,
    bounce_rate: ratio(bounced.size, invited.size),
    claim_conversion_rate: ratio(rows.filter((p) => p.claim_status === 'claimed' && invited.has(p.id)).length, invited.size),
    by_state: [...byState].map(([state, count]) => ({ state, count })).sort((a, b) => b.count - a.count || a.state.localeCompare(b.state)),
    by_capability: [...byCap].map(([label, count]) => ({ label, count })).sort((a, b) => b.count - a.count || a.label.localeCompare(b.label)),
    email,
  }
}

/* ------------------------------ Audit log view ------------------------------ */

export async function loadAuditLog(limit = 200, admin: Admin = createAdminClient()): Promise<AuditRow[]> {
  const { data } = await admin.from('pilot_network_audit_log').select('*').order('created_at', { ascending: false }).limit(limit)
  return (data ?? []) as AuditRow[]
}

/* -------------------------------- Settings -------------------------------- */

export interface PilotNetworkSettings {
  cron_enabled: boolean
  last_run_at: string | null
  last_run_summary: Record<string, unknown> | null
  updated_by: string | null
  updated_at: string | null
}

export async function loadSettings(admin: Admin = createAdminClient()): Promise<PilotNetworkSettings> {
  const { data } = await admin.from('pilot_network_settings').select('*').eq('id', 1).maybeSingle()
  return (data as PilotNetworkSettings | null) ?? { cron_enabled: false, last_run_at: null, last_run_summary: null, updated_by: null, updated_at: null }
}

/* -------------------------------- Templates -------------------------------- */

export interface PilotTemplate {
  id: string
  key: string
  name: string
  subject: string
  body: string
  cta_text: string | null
  footer: string | null
  unsubscribe_text: string | null
  active: boolean
  updated_by: string | null
  updated_at: string | null
}

export async function loadPilotTemplates(admin: Admin = createAdminClient()): Promise<PilotTemplate[]> {
  const { data } = await admin
    .from('email_templates')
    .select('id, key, name, subject, body, cta_text, footer, unsubscribe_text, active, updated_by, updated_at')
    .eq('category', 'pilot_invitation')
    .order('created_at')
  return (data ?? []) as PilotTemplate[]
}

/* --------------------------- Claim / unsubscribe links --------------------------- */

function newToken(): string {
  return randomBytes(24).toString('hex')
}

/** Answer 6: one token per profile, no expiry; created lazily, erased on claim. */
export async function ensureClaimToken(profile: PilotProfile, admin: Admin = createAdminClient()): Promise<string | null> {
  if (profile.claim_status === 'claimed') return null
  if (profile.claim_token) return profile.claim_token
  const token = newToken()
  const { error } = await admin.from('pilot_profiles').update({ claim_token: token, updated_at: new Date().toISOString() }).eq('id', profile.id).is('claim_token', null)
  if (error) return profile.claim_token
  return token
}

export function claimLink(origin: string, token: string): string {
  return `${origin}/pilot/claim/${token}`
}
export function unsubscribeLink(origin: string, token: string): string {
  return `${origin}/pilot/unsubscribe/${token}`
}

/* ------------------------------- Rendering ------------------------------- */

export async function renderForProfile(template: PilotTemplate, profile: PilotProfile, origin: string, admin: Admin = createAdminClient()) {
  const token = (await ensureClaimToken(profile, admin)) ?? 'claimed'
  return renderPilotTemplate(
    { subject: template.subject, body: template.body, cta_text: template.cta_text, footer: template.footer, unsubscribe_text: template.unsubscribe_text },
    {
      name: profile.name,
      company_name: profile.company_name,
      state: profile.primary_state,
      states: profile.related_states.length ? profile.related_states : [profile.primary_state],
      capabilities: profile.capabilities.map((c) => c.label),
      claim_profile_link: claimLink(origin, token),
      unsubscribe_link: unsubscribeLink(origin, profile.unsubscribe_token),
      support_email: SUPPORT_EMAIL,
      cta_text: template.cta_text?.trim() || 'Claim My Pilot Profile',
    },
  )
}

/* --------------------------------- Sending --------------------------------- */

export type SendOutcome = { ok: true; status: string; message_id: string | null } | { ok: false; block: SendBlock | 'template_missing' | 'template_inactive' | 'unresolved_variables' | 'delivery_failed' | 'suppressed'; error?: string }

/**
 * One invitation or follow-up to one profile: eligibility (§19), render,
 * adapter (recorded), history row (§24), profile status + follow-up date,
 * audit (§26). `kind` decides the status written.
 */
export async function sendToProfile(params: {
  profile: PilotProfile
  templateKey: string
  kind: 'invitation_sent' | 'follow_up_sent'
  campaignId?: string | null
  followUpDelayDays?: number
  requireApproval: boolean
  origin: string
  actorId?: string | null
  actorLabel: string
}, admin: Admin = createAdminClient()): Promise<SendOutcome> {
  const { profile } = params
  const block = sendBlockFor(profile, { requireApproval: params.requireApproval })
  if (block) return { ok: false, block }
  const templates = await loadPilotTemplates(admin)
  const template = templates.find((t) => t.key === params.templateKey)
  if (!template) return { ok: false, block: 'template_missing' }
  if (!template.active) return { ok: false, block: 'template_inactive' }
  const rendered = await renderForProfile(template, profile, params.origin, admin)
  if (hasUnresolvedVariables(rendered.subject) || hasUnresolvedVariables(rendered.text)) return { ok: false, block: 'unresolved_variables' }

  // Central policy (2026-09-22): a hard-bounced or globally suppressed address, or a marketing opt-out
  // recorded outside this pipeline, blocks the send here too. Pilot invitations are the updates stream.
  const ctx = await recipientContext(profile.email)
  const decision = preSendDecision({ meta: { stream: 'updates', category: 'pilot_opportunities', required: false }, templateActive: true, preferences: ctx.preferences, suppressions: ctx.suppressions, delivery: ctx.delivery, campaignType: 'pilot_recruitment' })
  if (!decision.allowed) return { ok: false, block: 'suppressed', error: decision.reason }
  const clientReference = `hha_${crypto.randomUUID().replace(/-/g, '')}`
  const result = await sendEmail({ to: profile.email, subject: rendered.subject, text: rendered.text, stream: 'updates', clientReference, tags: { kind: params.kind, template: template.key, campaign: params.campaignId ?? '' }, unsubscribeUrl: unsubscribeLink(params.origin, profile.unsubscribe_token) })
  await admin.from('email_messages').insert({
    client_reference: clientReference, recipient_email: profile.email, email_normalized: normalizeEmail(profile.email), email_stream: 'updates', agent: result.agent,
    template_key: template.key, category_key: 'pilot_opportunities', campaign_id: params.campaignId ?? null, campaign_type: 'pilot_recruitment', subject: rendered.subject,
    zepto_request_id: result.request_id, status: result.status, sent_at: result.status === 'sent' ? new Date().toISOString() : null, error: result.status === 'failed' ? result.error : null,
    actor_user_id: params.actorId ?? null, detail: { message_id: result.message_id, profile_id: profile.id, kind: params.kind },
  }).then(({ error }) => { if (error && !/does not exist|schema cache|column/i.test(error.message)) console.error('email_messages insert failed', error.message) })
  const now = new Date().toISOString()
  await admin.from('pilot_invitation_events').insert({
    profile_id: profile.id,
    campaign_id: params.campaignId ?? null,
    template_id: template.id,
    template_key: template.key,
    kind: params.kind,
    email_status: result.status,
    provider_message_id: result.message_id,
    rendered_subject: rendered.subject,
    detail: { to: profile.email, text: rendered.text, provider: result.provider, ...(result.status === 'failed' ? { error: result.error } : {}) },
  })
  if (result.status === 'failed') {
    // The provider refused it: history keeps the attempt, the profile's status does not move.
    await logPilotNetworkAction({ actorId: params.actorId, actorLabel: params.actorLabel, action: `${params.kind}_failed`, entityType: 'profile', entityId: profile.id, detail: { template: template.key, campaign: params.campaignId ?? null, error: result.error } }, admin)
    return { ok: false, block: 'delivery_failed', error: result.error }
  }
  const update: Record<string, unknown> = { last_contacted_at: now, updated_at: now, invitation_status: params.kind }
  if (params.kind === 'invitation_sent') update.next_follow_up_at = followUpDueAt(now, params.followUpDelayDays ?? 10)
  else update.next_follow_up_at = null
  await admin.from('pilot_profiles').update(update).eq('id', profile.id)
  await logPilotNetworkAction({ actorId: params.actorId, actorLabel: params.actorLabel, action: params.kind, entityType: 'profile', entityId: profile.id, detail: { template: template.key, campaign: params.campaignId ?? null, email_status: result.status } }, admin)
  return { ok: true, status: result.status, message_id: result.message_id }
}

/* -------------------------------- Campaigns -------------------------------- */

export interface CampaignWithCounts extends PilotCampaign {
  recipients: { pending: number; sent: number; follow_up_sent: number; done: number; skipped: number; total: number }
  sent_today: number
}

export async function loadCampaigns(admin: Admin = createAdminClient()): Promise<CampaignWithCounts[]> {
  const { data } = await admin.from('pilot_campaigns').select('*').order('created_at', { ascending: false })
  const campaigns = (data ?? []) as PilotCampaign[]
  if (campaigns.length === 0) return []
  const ids = campaigns.map((c) => c.id)
  const today = sendDayKey(new Date().toISOString())
  const [{ data: recs }, { data: todays }] = await Promise.all([
    admin.from('pilot_campaign_recipients').select('campaign_id, status').in('campaign_id', ids),
    admin.from('pilot_invitation_events').select('campaign_id').in('campaign_id', ids).in('kind', ['invitation_sent', 'follow_up_sent']).gte('created_at', `${today}T00:00:00.000Z`),
  ])
  return campaigns.map((c) => {
    const mine = ((recs ?? []) as Array<{ campaign_id: string; status: keyof CampaignWithCounts['recipients'] }>).filter((r) => r.campaign_id === c.id)
    const count = (s: string) => mine.filter((r) => r.status === s).length
    return {
      ...c,
      recipients: { pending: count('pending'), sent: count('sent'), follow_up_sent: count('follow_up_sent'), done: count('done'), skipped: count('skipped'), total: mine.length },
      sent_today: ((todays ?? []) as Array<{ campaign_id: string }>).filter((t) => t.campaign_id === c.id).length,
    }
  })
}

export interface RecipientPreview {
  profile: PilotProfile
  status: string
  block: SendBlock | null
}

export async function previewCampaignRecipients(campaignId: string, admin: Admin = createAdminClient()): Promise<RecipientPreview[]> {
  const { data } = await admin.from('pilot_campaign_recipients').select('profile_id, status').eq('campaign_id', campaignId)
  const recs = (data ?? []) as Array<{ profile_id: string; status: string }>
  if (recs.length === 0) return []
  const { data: rows } = await admin.from('pilot_profiles').select(PROFILE_COLUMNS).in('id', recs.map((r) => r.profile_id))
  const profiles = await hydrate(admin, (rows ?? []) as Record<string, unknown>[])
  return recs
    .map((r) => {
      const profile = profiles.find((p) => p.id === r.profile_id)
      return profile ? { profile, status: r.status, block: sendBlockFor(profile, { requireApproval: true }) } : null
    })
    .filter((x): x is RecipientPreview => !!x)
    .sort((a, b) => a.profile.name.localeCompare(b.profile.name))
}

export interface DayRunSummary {
  ran: boolean
  reason?: string
  campaigns: Array<{ id: string; name: string; initial_sent: number; follow_ups_sent: number; skipped: number; failed: number; remaining_today: number }>
}

/**
 * The daily runner (§18–§19, answer 5). Per running campaign whose start
 * date has passed: initial emails to pending recipients up to the daily
 * limit (counted per calendar day — a second call the same day sends
 * nothing more), then due follow-ups (one per recipient, ever). Skips and
 * records the reason for claimed / unsubscribed / bounced / invalid /
 * duplicate / do-not-contact profiles. `manual` bypasses the cron switch
 * (the "Run today's batch" button); the scheduler does not.
 */
export async function runPilotCampaignsDay(params: { origin: string; actorId?: string | null; actorLabel: string; manual: boolean }, admin: Admin = createAdminClient()): Promise<DayRunSummary> {
  const settings = await loadSettings(admin)
  if (!params.manual && !settings.cron_enabled) return { ran: false, reason: 'Cron is switched off', campaigns: [] }
  const today = sendDayKey(new Date().toISOString())
  const campaigns = (await loadCampaigns(admin)).filter((c) => c.status === 'running' && c.start_date <= today)
  const summary: DayRunSummary = { ran: true, campaigns: [] }

  for (const c of campaigns) {
    let remaining = Math.max(0, c.daily_send_limit - c.sent_today)
    const line = { id: c.id, name: c.name, initial_sent: 0, follow_ups_sent: 0, skipped: 0, failed: 0, remaining_today: remaining }
    const recipients = await previewCampaignRecipients(c.id, admin)

    // 1. Initial invitations.
    for (const r of recipients.filter((x) => x.status === 'pending')) {
      if (remaining <= 0) break
      const block = sendBlockFor(r.profile, { requireApproval: true })
      if (block) {
        await admin.from('pilot_campaign_recipients').update({ status: 'skipped', skip_reason: block }).eq('campaign_id', c.id).eq('profile_id', r.profile.id)
        line.skipped++
        continue
      }
      const out = await sendToProfile({ profile: r.profile, templateKey: c.template_key, kind: 'invitation_sent', campaignId: c.id, followUpDelayDays: c.follow_up_delay_days, requireApproval: true, origin: params.origin, actorId: params.actorId, actorLabel: params.actorLabel }, admin)
      if (!out.ok) {
        if (out.block === 'delivery_failed') { line.failed++; remaining--; continue } // stays pending — retried on the next run
        await admin.from('pilot_campaign_recipients').update({ status: 'skipped', skip_reason: out.block }).eq('campaign_id', c.id).eq('profile_id', r.profile.id)
        line.skipped++
        continue
      }
      const sentAt = new Date().toISOString()
      await admin
        .from('pilot_campaign_recipients')
        .update({ status: 'sent', sent_at: sentAt, follow_up_due_at: c.follow_up_template_key ? followUpDueAt(sentAt, c.follow_up_delay_days) : null })
        .eq('campaign_id', c.id)
        .eq('profile_id', r.profile.id)
      if (!c.follow_up_template_key) await admin.from('pilot_campaign_recipients').update({ status: 'done' }).eq('campaign_id', c.id).eq('profile_id', r.profile.id)
      line.initial_sent++
      remaining--
    }

    // 2. One follow-up per recipient, when due (§19).
    if (c.follow_up_template_key) {
      const { data: due } = await admin
        .from('pilot_campaign_recipients')
        .select('profile_id')
        .eq('campaign_id', c.id)
        .eq('status', 'sent')
        .lte('follow_up_due_at', new Date().toISOString())
      for (const d of (due ?? []) as Array<{ profile_id: string }>) {
        if (remaining <= 0) break
        const profile = await loadProfile(d.profile_id, admin)
        if (!profile) continue
        const block = sendBlockFor(profile, { requireApproval: false })
        if (block) {
          await admin.from('pilot_campaign_recipients').update({ status: 'skipped', skip_reason: block }).eq('campaign_id', c.id).eq('profile_id', profile.id)
          line.skipped++
          continue
        }
        const out = await sendToProfile({ profile, templateKey: c.follow_up_template_key, kind: 'follow_up_sent', campaignId: c.id, requireApproval: false, origin: params.origin, actorId: params.actorId, actorLabel: params.actorLabel }, admin)
        if (!out.ok) {
          if (out.block === 'delivery_failed') { line.failed++; remaining--; continue }
          await admin.from('pilot_campaign_recipients').update({ status: 'skipped', skip_reason: out.block }).eq('campaign_id', c.id).eq('profile_id', profile.id)
          line.skipped++
          continue
        }
        await admin.from('pilot_campaign_recipients').update({ status: 'done', follow_up_sent_at: new Date().toISOString() }).eq('campaign_id', c.id).eq('profile_id', profile.id)
        line.follow_ups_sent++
        remaining--
      }
    }

    // 3. Nothing left → completed.
    const left = await previewCampaignRecipients(c.id, admin)
    if (left.length > 0 && left.every((r) => r.status === 'done' || r.status === 'skipped')) {
      await admin.from('pilot_campaigns').update({ status: 'completed', updated_at: new Date().toISOString() }).eq('id', c.id)
      await logPilotNetworkAction({ actorId: params.actorId, actorLabel: params.actorLabel, action: 'campaign_completed', entityType: 'campaign', entityId: c.id }, admin)
    }
    line.remaining_today = remaining
    summary.campaigns.push(line)
  }

  await admin.from('pilot_network_settings').update({ last_run_at: new Date().toISOString(), last_run_summary: summary as unknown as Record<string, unknown> }).eq('id', 1)
  await logPilotNetworkAction({ actorId: params.actorId, actorLabel: params.actorLabel, action: params.manual ? 'batch_run_manual' : 'batch_run_cron', entityType: 'setting', entityId: 'daily_batch', detail: summary as unknown as Record<string, unknown> }, admin)
  return summary
}

/* ---------------------------------- Import (§27–§28) ---------------------------------- */

export interface ImportResult {
  run_id: string | null
  dry_run: boolean
  created: number
  updated: number
  skipped: number
  failed: number
  records: Array<{ row: number; email: string; action: 'created' | 'updated' | 'skipped' | 'failed'; message: string }>
}

/**
 * Accepts rows shaped {name, company?, phone?, email, state, capabilities[]}
 * (§27). Duplicate rule §28: email → phone → name + company merges into the
 * existing profile (state added, capabilities unioned) — never a second
 * profile. Unknown capability names become new inactive capability types
 * (§6). Every row is logged in import_records; dry_run touches nothing.
 */
export async function importPilotLeads(rows: ImportRow[], opts: { sourceSystem: string; sourceReference?: string | null; triggeredBy: string; actorId?: string | null; dryRun: boolean }, admin: Admin = createAdminClient()): Promise<ImportResult> {
  const result: ImportResult = { run_id: null, dry_run: opts.dryRun, created: 0, updated: 0, skipped: 0, failed: 0, records: [] }
  const { data: run } = await admin
    .from('import_runs')
    .insert({ source_system: opts.sourceSystem, scope: { rows: rows.length, source_reference: opts.sourceReference ?? null }, dry_run: opts.dryRun, triggered_by: opts.triggeredBy })
    .select('id')
    .single()
  result.run_id = run?.id ?? null

  const types = await loadCapabilityTypes(admin)
  const typeByKey = new Map(types.map((t) => [t.key, t]))
  const { data: existingRows } = await admin.from('pilot_profiles').select('id, email, phone_digits, name, company_name, related_states, primary_state')
  const existing = ((existingRows ?? []) as Array<{ id: string; email: string; phone_digits: string | null; name: string; company_name: string | null; related_states: string[] | null; primary_state: string }>).map((p) => ({ ...p, related_states: p.related_states ?? [] }))

  const record = async (row: number, email: string, action: ImportResult['records'][number]['action'], message: string, targetId?: string | null) => {
    result.records.push({ row, email, action, message })
    result[action]++
    if (result.run_id) await admin.from('import_records').insert({ run_id: result.run_id, source_table: opts.sourceSystem, source_id: `${row}:${normalizeEmail(email)}`, target_table: 'pilot_profiles', target_id: targetId ?? null, action, message })
  }

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i]
    const rowNo = i + 1
    try {
      const email = normalizeEmail(row.email ?? '')
      const state = (row.state ?? '').trim().toUpperCase()
      if (!row.name?.trim() || !email.includes('@') || !/^[A-Z]{2}$/.test(state)) {
        await record(rowNo, email, 'skipped', 'Missing name, email or two-letter state')
        continue
      }
      // Capability types: resolve, create unknown ones (inactive until reviewed).
      const typeIds: string[] = []
      for (const label of row.capabilities ?? []) {
        const key = capabilityKey(label)
        if (!key) continue
        let t = typeByKey.get(key)
        if (!t && !opts.dryRun) {
          const { data: created } = await admin.from('pilot_capability_types').insert({ key, label: label.trim(), group_name: 'position', sort_order: 900, active: false }).select('id, key, label, group_name, sort_order, active').single()
          if (created) {
            const { group_name, ...rest } = created as Omit<CapabilityType, 'group'> & { group_name: CapabilityType['group'] }
            t = { ...rest, group: group_name }
            typeByKey.set(key, t)
          }
        }
        if (t) typeIds.push(t.id)
      }

      const dup = findDuplicate({ ...row, email, state }, existing)
      if (dup) {
        const merged = mergeStates(dup.profile.related_states, state)
        if (!opts.dryRun) {
          await admin.from('pilot_profiles').update({ related_states: merged, updated_at: new Date().toISOString() }).eq('id', dup.profile.id)
          if (typeIds.length) await admin.from('pilot_profile_capabilities').upsert(typeIds.map((id) => ({ profile_id: dup.profile.id, capability_type_id: id, data_status: 'imported', source: opts.sourceSystem })), { onConflict: 'profile_id,capability_type_id', ignoreDuplicates: true })
          dup.profile.related_states = merged
        }
        await record(rowNo, email, 'updated', `Matched existing profile by ${dup.by}; states now ${merged.join(', ')}`, dup.profile.id)
        continue
      }

      if (opts.dryRun) {
        await record(rowNo, email, 'created', 'Would create')
        existing.push({ id: `dry-${rowNo}`, email, phone_digits: normalizePhone(row.phone), name: row.name, company_name: row.company ?? null, related_states: [state], primary_state: state })
        continue
      }
      const { data: created, error } = await admin
        .from('pilot_profiles')
        .insert({
          name: row.name.trim(),
          company_name: row.company?.trim() || null,
          email,
          phone: row.phone?.trim() || null,
          phone_digits: normalizePhone(row.phone),
          primary_state: state,
          related_states: [state],
          source: opts.sourceSystem,
          source_reference: opts.sourceReference ?? null,
          claim_token: newToken(),
        })
        .select('id')
        .single()
      if (error || !created) {
        await record(rowNo, email, 'failed', error?.message ?? 'Insert failed')
        continue
      }
      if (typeIds.length) await admin.from('pilot_profile_capabilities').insert(typeIds.map((id) => ({ profile_id: created.id, capability_type_id: id, data_status: 'imported', source: opts.sourceSystem })))
      existing.push({ id: created.id, email, phone_digits: normalizePhone(row.phone), name: row.name, company_name: row.company ?? null, related_states: [state], primary_state: state })
      await record(rowNo, email, 'created', 'Created', created.id)
    } catch (e) {
      await record(rowNo, row.email ?? '', 'failed', e instanceof Error ? e.message : 'Unexpected error')
    }
  }

  if (result.run_id) {
    await admin.from('import_runs').update({ status: 'completed', finished_at: new Date().toISOString(), stats: { created: result.created, updated: result.updated, skipped: result.skipped, failed: result.failed } }).eq('id', result.run_id)
  }
  await logPilotNetworkAction({ actorId: opts.actorId, actorLabel: opts.triggeredBy, action: opts.dryRun ? 'import_dry_run' : 'import_completed', entityType: 'import', entityId: result.run_id, detail: { source: opts.sourceSystem, created: result.created, updated: result.updated, skipped: result.skipped, failed: result.failed } }, admin)
  return result
}

/* ------------------------------ Claim / unsubscribe (§20–§23) ------------------------------ */

export async function loadProfileByClaimToken(token: string, admin: Admin = createAdminClient()): Promise<PilotProfile | null> {
  if (!/^[a-f0-9]{48}$/.test(token)) return null
  const { data } = await admin.from('pilot_profiles').select(PROFILE_COLUMNS).eq('claim_token', token).maybeSingle()
  if (!data) return null
  const [p] = await hydrate(admin, [data as Record<string, unknown>])
  return p
}

export async function recordClaimLinkOpened(profileId: string, admin: Admin = createAdminClient()) {
  await admin.from('pilot_invitation_events').insert({ profile_id: profileId, kind: 'opened_claim_link' })
}

export interface ClaimInput {
  account_type: 'pilot_driver' | 'pilot_company'
  name: string
  company_name: string | null
  email: string
  phone: string | null
  states: string[]
  /** Capability type keys the person confirmed or added — everything else imported is removed (§21). */
  capability_keys: string[]
  opt_in: boolean
}

/**
 * §20–§22: record the claim, the corrections, the confirmed capabilities and
 * the opt-in choice; erase the claim token (answer 6). Account creation
 * itself is the next step (answer 4) — see the claim page.
 */
export async function claimProfile(token: string, input: ClaimInput, admin: Admin = createAdminClient()): Promise<{ ok: true; profile: PilotProfile } | { ok: false; error: string }> {
  const profile = await loadProfileByClaimToken(token, admin)
  if (!profile) return { ok: false, error: 'This claim link is not valid or the profile was already claimed.' }
  const now = new Date().toISOString()
  const email = normalizeEmail(input.email)
  if (email !== normalizeEmail(profile.email)) {
    const { data: clash } = await admin.from('pilot_profiles').select('id').ilike('email', emailPattern(email)).neq('id', profile.id).maybeSingle()
    if (clash) return { ok: false, error: 'That email address belongs to another pilot profile. Contact support.' }
  }
  const states = [...new Set(input.states.map((s) => s.trim().toUpperCase()).filter((s) => /^[A-Z]{2}$/.test(s)))]
  const primary = states.includes(profile.primary_state) ? profile.primary_state : states[0] ?? profile.primary_state

  const { error } = await admin
    .from('pilot_profiles')
    .update({
      name: input.name.trim(),
      company_name: input.company_name?.trim() || null,
      email,
      phone: input.phone?.trim() || null,
      phone_digits: normalizePhone(input.phone),
      primary_state: primary,
      related_states: states.length ? states : profile.related_states,
      account_type: input.account_type,
      claim_status: 'claimed',
      invitation_status: input.opt_in ? 'opted_in' : 'claimed',
      email_opt_in_status: input.opt_in ? 'opted_in' : profile.email_opt_in_status,
      claimed_at: now,
      claim_token: null,
      next_follow_up_at: null,
      updated_at: now,
    })
    .eq('id', profile.id)
  if (error) return { ok: false, error: 'Could not save the claim. Please try again.' }

  // §21: confirmed → user_confirmed; removed → deleted; added → user_confirmed.
  const types = await loadCapabilityTypes(admin)
  const keep = new Set(input.capability_keys)
  const keepIds = types.filter((t) => keep.has(t.key)).map((t) => t.id)
  await admin.from('pilot_profile_capabilities').delete().eq('profile_id', profile.id).not('capability_type_id', 'in', `(${keepIds.length ? keepIds.join(',') : '00000000-0000-0000-0000-000000000000'})`)
  if (keepIds.length) {
    await admin.from('pilot_profile_capabilities').upsert(keepIds.map((id) => ({ profile_id: profile.id, capability_type_id: id, data_status: 'user_confirmed', confirmed_at: now, source: 'claim' })), { onConflict: 'profile_id,capability_type_id' })
  }

  await admin.from('pilot_invitation_events').insert([
    { profile_id: profile.id, kind: 'claimed', detail: { account_type: input.account_type } },
    ...(input.opt_in ? [{ profile_id: profile.id, kind: 'opted_in', detail: {} }] : []),
  ])
  await logPilotNetworkAction({ actorLabel: `${input.name.trim()} (claim page)`, action: 'profile_claimed', entityType: 'profile', entityId: profile.id, detail: { account_type: input.account_type, opted_in: input.opt_in } }, admin)
  if (input.opt_in) await logPilotNetworkAction({ actorLabel: `${input.name.trim()} (claim page)`, action: 'user_opted_in', entityType: 'profile', entityId: profile.id }, admin)
  const updated = await loadProfile(profile.id, admin)
  return { ok: true, profile: updated! }
}

export async function unsubscribeByToken(token: string, admin: Admin = createAdminClient()): Promise<{ ok: boolean; already: boolean }> {
  if (!/^[a-f0-9]{48}$/.test(token)) return { ok: false, already: false }
  const { data } = await admin.from('pilot_profiles').select('id, email, name, email_opt_in_status').eq('unsubscribe_token', token).maybeSingle()
  if (!data) return { ok: false, already: false }
  if (data.email_opt_in_status === 'unsubscribed') return { ok: true, already: true }
  const now = new Date().toISOString()
  await admin.from('pilot_profiles').update({ email_opt_in_status: 'unsubscribed', invitation_status: 'unsubscribed', next_follow_up_at: null, updated_at: now }).eq('id', data.id)
  await admin.from('pilot_suppressions').upsert({ email: normalizeEmail(data.email), reason: 'unsubscribed', profile_id: data.id, created_by: 'unsubscribe page' }, { onConflict: 'email', ignoreDuplicates: true })
  await admin.from('pilot_invitation_events').insert({ profile_id: data.id, kind: 'unsubscribed' })
  await logPilotNetworkAction({ actorLabel: `${data.name} (unsubscribe page)`, action: 'user_unsubscribed', entityType: 'profile', entityId: data.id }, admin)
  return { ok: true, already: false }
}

/* ------------------------------ Admin profile actions ------------------------------ */

export async function suppressProfile(profile: PilotProfile, reason: SuppressionReason, actor: { id?: string | null; label: string }, note?: string, admin: Admin = createAdminClient()) {
  const status: InvitationStatus = reason === 'duplicate' ? 'duplicate' : reason === 'do_not_contact' ? 'do_not_contact' : 'invalid'
  await admin.from('pilot_profiles').update({ invitation_status: status, next_follow_up_at: null, updated_at: new Date().toISOString() }).eq('id', profile.id)
  await admin.from('pilot_suppressions').upsert({ email: normalizeEmail(profile.email), reason, profile_id: profile.id, note: note ?? null, created_by: actor.label }, { onConflict: 'email' })
  await logPilotNetworkAction({ actorId: actor.id, actorLabel: actor.label, action: `profile_marked_${status}`, entityType: 'profile', entityId: profile.id, detail: { reason, note: note ?? null } }, admin)
}

/* ------------------------------ Delivery webhook (§23–§24) ------------------------------ */

/**
 * Hard bounce / complaint → suppression + `invalid` status + history row;
 * soft bounce → history row only. Unknown addresses are counted and ignored.
 */
export async function applyDeliveryEvents(events: DeliveryEvent[], payload: unknown, admin: Admin = createAdminClient()): Promise<{ matched: number; unmatched: number; suppressed: number }> {
  const result = { matched: 0, unmatched: 0, suppressed: 0 }
  for (const ev of events) {
    for (const email of ev.emails) {
      const { data } = await admin.from('pilot_profiles').select('id, name').ilike('email', emailPattern(email)).maybeSingle()
      if (!data) { result.unmatched++; continue }
      result.matched++
      const kind = ev.kind === 'complaint' ? 'complaint' : 'bounced'
      await admin.from('pilot_invitation_events').insert({ profile_id: data.id, kind, email_status: ev.raw_event, detail: { reason: ev.reason, kind: ev.kind, payload } })
      if (ev.kind === 'hardbounce' || ev.kind === 'complaint') {
        await admin.from('pilot_profiles').update({ invitation_status: 'invalid', next_follow_up_at: null, updated_at: new Date().toISOString() }).eq('id', data.id)
        await admin.from('pilot_suppressions').upsert({ email: normalizeEmail(email), reason: ev.kind === 'complaint' ? 'complaint' : 'bounced', profile_id: data.id, note: ev.reason, created_by: 'zeptomail webhook' }, { onConflict: 'email', ignoreDuplicates: true })
        await logPilotNetworkAction({ actorLabel: 'ZeptoMail webhook', action: ev.kind === 'complaint' ? 'email_complaint' : 'email_bounced', entityType: 'profile', entityId: data.id, detail: { event: ev.raw_event, reason: ev.reason } }, admin)
        result.suppressed++
      }
    }
  }
  return result
}