/**
 * Marketing campaigns CRUD + lifecycle helpers.
 *
 * The campaign row stores both the editable draft fields and a snapshot of
 * the audience rules at launch time so subsequent segment edits don't
 * retro-affect an in-flight broadcast. The dispatcher (./dispatcher.ts)
 * handles the actual send fan-out — this module only manages persistence
 * and validates the state machine.
 */

import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { ValidationError } from '@server/errors';
import { bindValue } from '@server/db/bind';
import {
  fanOutInsertDirect, normalizeRules, getSegment, previewSegmentCount,
} from './segments.service';
import { verifySmtp } from '@server/services/email/transport';
import type {
  CampaignChannel, CampaignRow, AudienceRules, CampaignStatus,
} from './types';

// ─── Row mapping ───────────────────────────────────────────────────────────

function rowToCampaign(r: Record<string, unknown>): CampaignRow {
  let templateVars: string[] = [];
  const rawTv = r.template_vars;
  if (Array.isArray(rawTv)) templateVars = rawTv.filter((x): x is string => typeof x === 'string');
  else if (typeof rawTv === 'string') {
    try {
      const parsed = JSON.parse(rawTv);
      if (Array.isArray(parsed)) templateVars = parsed.filter((x): x is string => typeof x === 'string');
    } catch { templateVars = []; }
  }
  let rulesObj: AudienceRules = {};
  const rawRules = r.audience_rules;
  if (rawRules && typeof rawRules === 'object') rulesObj = normalizeRules(rawRules);
  else if (typeof rawRules === 'string') {
    try { rulesObj = normalizeRules(JSON.parse(rawRules)); } catch { rulesObj = {}; }
  }

  return {
    id: r.id as string,
    restaurant_id: r.restaurant_id as string,
    branch_id: (r.branch_id as string | null) ?? null,
    segment_id: (r.segment_id as string | null) ?? null,
    audience_rules: rulesObj,
    name: r.name as string,
    channel: r.channel as CampaignChannel,
    subject: (r.subject as string | null) ?? null,
    body_html: (r.body_html as string | null) ?? null,
    template_name: (r.template_name as string | null) ?? null,
    template_lang: (r.template_lang as string | null) ?? null,
    template_vars: templateVars,
    status: r.status as CampaignStatus,
    scheduled_at: r.scheduled_at ? String(r.scheduled_at) : null,
    ignore_quiet_hours: Boolean(r.ignore_quiet_hours),
    audience_count: Number(r.audience_count ?? 0),
    sent_count: Number(r.sent_count ?? 0),
    failed_count: Number(r.failed_count ?? 0),
    opt_out_count: Number(r.opt_out_count ?? 0),
    created_by: (r.created_by as string | null) ?? null,
    created_at: String(r.created_at),
    updated_at: String(r.updated_at),
    started_at: r.started_at ? String(r.started_at) : null,
    completed_at: r.completed_at ? String(r.completed_at) : null,
    last_error: (r.last_error as string | null) ?? null,
    launch_group_id: (r.launch_group_id as string | null) ?? null,
    segment_deleted_at: r.segment_deleted_at ? String(r.segment_deleted_at) : null,
    max_per_minute: r.max_per_minute === null || r.max_per_minute === undefined
      ? null : Number(r.max_per_minute),
    max_per_hour: r.max_per_hour === null || r.max_per_hour === undefined
      ? null : Number(r.max_per_hour),
  };
}

const CAMPAIGN_COLS = sql`id, restaurant_id, branch_id, segment_id, audience_rules,
  name, channel, subject, body_html, template_name, template_lang, template_vars,
  status, scheduled_at, ignore_quiet_hours,
  audience_count, sent_count, failed_count, opt_out_count,
  launch_group_id, segment_deleted_at, max_per_minute, max_per_hour,
  created_by, created_at, updated_at, started_at, completed_at, last_error`;

// ─── CRUD ──────────────────────────────────────────────────────────────────

export interface CampaignDraftInput {
  name: string;
  channel: CampaignChannel;
  branchId?: string | null;
  segmentId?: string | null;
  audienceRules?: unknown;
  subject?: string | null;
  bodyHtml?: string | null;
  templateName?: string | null;
  templateLang?: string | null;
  templateVars?: unknown;
  scheduledAt?: string | Date | null;
  ignoreQuietHours?: boolean;
  createdBy?: string | null;
  /**
   * Optional grouping UUID. Used by the wizard's "Email + WhatsApp" path
   * which creates two sibling campaigns sharing one launch_group_id so the
   * report drawer can aggregate their stats into a single combined view.
   */
  launchGroupId?: string | null;
  /**
   * Per-campaign throttle controls (Task #240). null/undefined leaves the
   * campaign uncapped beyond the global per-(branch,channel) batch limit.
   * Treated as positive integers; non-positive values are coerced to null
   * so the dispatcher never gets a confusing 0 cap that would freeze sends.
   */
  maxPerMinute?: number | null;
  maxPerHour?: number | null;
}

/** Defensive clamp matched to the validator's per-field upper bound — even
 *  though the request validator rejects out-of-range values, the service is
 *  also called from internal callers (e.g. test harnesses) that bypass it. */
function normalizeThrottle(raw: unknown, max: number): number | null {
  if (raw === null || raw === undefined || raw === '') return null;
  const n = typeof raw === 'number' ? raw : Number(raw);
  if (!Number.isFinite(n) || n <= 0) return null;
  return Math.min(Math.floor(n), max);
}
const MAX_PER_MINUTE_CAP = 10_000;
const MAX_PER_HOUR_CAP   = 100_000;

function validateDraftPayload(input: CampaignDraftInput): void {
  if (!input.name?.trim()) throw new ValidationError('Campaign name is required');
  if (input.channel !== 'email' && input.channel !== 'whatsapp') {
    throw new ValidationError('Campaign channel must be "email" or "whatsapp"');
  }
}

function normalizeTemplateVars(raw: unknown): string[] {
  if (!Array.isArray(raw)) return [];
  return raw.filter((x): x is string => typeof x === 'string').slice(0, 20);
}

export interface ListCampaignsOptions {
  /**
   * When true, return only campaigns whose source audience/segment has been
   * deleted (segment_deleted_at IS NOT NULL). The campaigns list uses this
   * to power an "audience deleted" filter chip so operators can quickly
   * find rows that need to be re-linked or edited.
   */
  audienceDeleted?: boolean;
}

export async function listCampaigns(
  restaurantId: string,
  opts: ListCampaignsOptions = {},
  /**
   * When non-null, scope the result to (branch_id IS NULL OR branch_id =
   * effectiveBranchId) so an active-branch owner / pinned staff user only
   * sees their branch's campaigns plus restaurant-wide email broadcasts.
   * When null, return everything in the restaurant.
   */
  effectiveBranchId: string | null = null,
): Promise<CampaignRow[]> {
  await initDatabase();
  const onlyAudienceDeleted = opts.audienceDeleted === true;
  const branchClause = effectiveBranchId
    ? sql`AND (branch_id IS NULL OR branch_id = ${effectiveBranchId})`
    : sql``;
  const { rows } = await db.execute(sql`
    SELECT ${CAMPAIGN_COLS} FROM marketing_campaigns
    WHERE restaurant_id = ${restaurantId}
      ${onlyAudienceDeleted ? sql`AND segment_deleted_at IS NOT NULL` : sql``}
      ${branchClause}
    ORDER BY (segment_deleted_at IS NOT NULL) DESC, created_at DESC
    LIMIT 200
  `);
  return (rows as Record<string, unknown>[]).map(rowToCampaign);
}

export async function getCampaign(
  restaurantId: string,
  id: string,
  effectiveBranchId: string | null = null,
): Promise<CampaignRow | null> {
  await initDatabase();
  const branchClause = effectiveBranchId
    ? sql`AND (branch_id IS NULL OR branch_id = ${effectiveBranchId})`
    : sql``;
  const { rows } = await db.execute(sql`
    SELECT ${CAMPAIGN_COLS} FROM marketing_campaigns
    WHERE restaurant_id = ${restaurantId} AND id = ${id}
      ${branchClause}
    LIMIT 1
  `);
  const r = rows[0] as Record<string, unknown> | undefined;
  return r ? rowToCampaign(r) : null;
}

/**
 * Hard-fail if the supplied branchId doesn't belong to this restaurant.
 * Critical: campaigns store branch_id which the dispatcher later passes to
 * sendWhatsAppTemplate/sendCampaignEmail to resolve credentials. Without
 * this check a tenant could attach another tenant's branch UUID and send
 * from their WhatsApp/SMTP creds.
 */
async function ensureBranchOwned(restaurantId: string, branchId: string | null | undefined): Promise<void> {
  if (!branchId) return;
  const { rows } = await db.execute(sql`
    SELECT 1 FROM branches WHERE id = ${branchId} AND restaurant_id = ${restaurantId} LIMIT 1
  `);
  if (rows.length === 0) throw new ValidationError('Branch not found in this restaurant');
}

/**
 * Resolve the audience rules a draft will use: explicit `audienceRules`
 * win, otherwise fall back to the linked segment's rules, otherwise empty.
 */
async function resolveDraftRules(
  restaurantId: string, input: { audienceRules?: unknown; segmentId?: string | null }
): Promise<AudienceRules> {
  if (input.audienceRules !== undefined && input.audienceRules !== null) {
    return normalizeRules(input.audienceRules);
  }
  if (input.segmentId) {
    const seg = await getSegment(restaurantId, input.segmentId);
    if (seg) return seg.rules;
  }
  return {};
}

export async function createCampaign(
  restaurantId: string, input: CampaignDraftInput
): Promise<CampaignRow> {
  validateDraftPayload(input);
  await initDatabase();
  await ensureBranchOwned(restaurantId, input.branchId);

  const rules = await resolveDraftRules(restaurantId, input);
  const audienceCount = await previewSegmentCount(restaurantId, { ...rules, channel: input.channel });
  const scheduledAt = input.scheduledAt ? new Date(input.scheduledAt) : null;

  const { rows } = await db.execute(sql`
    INSERT INTO marketing_campaigns (
      restaurant_id, branch_id, segment_id, audience_rules, name, channel,
      subject, body_html, template_name, template_lang, template_vars,
      status, scheduled_at, ignore_quiet_hours,
      audience_count, created_by, launch_group_id,
      max_per_minute, max_per_hour
    ) VALUES (
      ${restaurantId}, ${input.branchId ?? null}, ${input.segmentId ?? null},
      ${JSON.stringify(rules)}::jsonb,
      ${input.name.trim()}, ${input.channel},
      ${input.subject ?? null}, ${input.bodyHtml ?? null},
      ${input.templateName ?? null}, ${input.templateLang ?? null},
      ${JSON.stringify(normalizeTemplateVars(input.templateVars))}::jsonb,
      'draft', ${scheduledAt ? scheduledAt.toISOString() : null},
      ${input.ignoreQuietHours === true},
      ${audienceCount}, ${input.createdBy ?? null}, ${input.launchGroupId ?? null},
      ${normalizeThrottle(input.maxPerMinute, MAX_PER_MINUTE_CAP)}, ${normalizeThrottle(input.maxPerHour, MAX_PER_HOUR_CAP)}
    )
    RETURNING ${CAMPAIGN_COLS}
  `);
  return rowToCampaign(rows[0] as Record<string, unknown>);
}

export async function updateCampaign(
  restaurantId: string, id: string, input: Partial<CampaignDraftInput>
): Promise<CampaignRow> {
  await initDatabase();
  const existing = await getCampaign(restaurantId, id);
  if (!existing) throw new ValidationError('Campaign not found');
  if (existing.status !== 'draft' && existing.status !== 'scheduled') {
    throw new ValidationError(`Cannot edit a campaign in status "${existing.status}"`);
  }

  const merged: CampaignDraftInput = {
    name: input.name ?? existing.name,
    channel: input.channel ?? existing.channel,
    branchId: input.branchId !== undefined ? input.branchId : existing.branch_id,
    segmentId: input.segmentId !== undefined ? input.segmentId : existing.segment_id,
    audienceRules:
      input.audienceRules !== undefined ? input.audienceRules : existing.audience_rules,
    subject: input.subject !== undefined ? input.subject : existing.subject,
    bodyHtml: input.bodyHtml !== undefined ? input.bodyHtml : existing.body_html,
    templateName: input.templateName !== undefined ? input.templateName : existing.template_name,
    templateLang: input.templateLang !== undefined ? input.templateLang : existing.template_lang,
    templateVars: input.templateVars !== undefined ? input.templateVars : existing.template_vars,
    scheduledAt: input.scheduledAt !== undefined ? input.scheduledAt : existing.scheduled_at,
    ignoreQuietHours:
      input.ignoreQuietHours !== undefined ? input.ignoreQuietHours : existing.ignore_quiet_hours,
    maxPerMinute:
      input.maxPerMinute !== undefined ? input.maxPerMinute : existing.max_per_minute,
    maxPerHour:
      input.maxPerHour !== undefined ? input.maxPerHour : existing.max_per_hour,
  };
  validateDraftPayload(merged);
  if (merged.branchId !== existing.branch_id) {
    await ensureBranchOwned(restaurantId, merged.branchId);
  }

  const rules = await resolveDraftRules(restaurantId, {
    audienceRules: merged.audienceRules,
    segmentId: merged.segmentId,
  });
  const audienceCount = await previewSegmentCount(restaurantId, { ...rules, channel: merged.channel });
  const scheduledAt = merged.scheduledAt ? new Date(merged.scheduledAt) : null;

  // Clear the "audience deleted" badge when the operator rebinds the
  // campaign to a *live* segment — at that point the snapshot is no
  // longer orphaned. We verify the new segment actually exists first so
  // a typo'd segmentId doesn't silently clear the warning. If the edit
  // leaves segmentId null, the badge stays so the operator still sees
  // they're working from a snapshot.
  let clearSegmentDeletedAt = false;
  if (
    existing.segment_deleted_at &&
    merged.segmentId &&
    merged.segmentId !== existing.segment_id
  ) {
    const seg = await getSegment(restaurantId, merged.segmentId);
    if (seg) clearSegmentDeletedAt = true;
  }

  const { rows } = await db.execute(sql`
    UPDATE marketing_campaigns SET
      name = ${merged.name.trim()},
      channel = ${merged.channel},
      branch_id = ${merged.branchId ?? null},
      segment_id = ${merged.segmentId ?? null},
      audience_rules = ${JSON.stringify(rules)}::jsonb,
      subject = ${merged.subject ?? null},
      body_html = ${merged.bodyHtml ?? null},
      template_name = ${merged.templateName ?? null},
      template_lang = ${merged.templateLang ?? null},
      template_vars = ${JSON.stringify(normalizeTemplateVars(merged.templateVars))}::jsonb,
      scheduled_at = ${scheduledAt ? scheduledAt.toISOString() : null},
      ignore_quiet_hours = ${merged.ignoreQuietHours === true},
      max_per_minute = ${normalizeThrottle(merged.maxPerMinute, MAX_PER_MINUTE_CAP)},
      max_per_hour   = ${normalizeThrottle(merged.maxPerHour, MAX_PER_HOUR_CAP)},
      audience_count = ${audienceCount},
      segment_deleted_at = ${clearSegmentDeletedAt
        ? sql`NULL`
        : sql`segment_deleted_at`},
      updated_at = now()
    WHERE restaurant_id = ${restaurantId} AND id = ${id}
    RETURNING ${CAMPAIGN_COLS}
  `);
  return rowToCampaign(rows[0] as Record<string, unknown>);
}

export async function deleteCampaign(restaurantId: string, id: string): Promise<void> {
  await initDatabase();
  const existing = await getCampaign(restaurantId, id);
  if (!existing) return;
  if (existing.status === 'sending') {
    throw new ValidationError('Cannot delete a campaign that is currently sending');
  }
  await db.execute(sql`
    DELETE FROM marketing_campaigns
    WHERE restaurant_id = ${restaurantId} AND id = ${id}
  `);
}

/**
 * Re-snapshot a segment's rules onto every still-active campaign that
 * references it (status IN draft / scheduled / sending) and stamp
 * `segment_deleted_at` so the UI can flag that the underlying audience
 * is gone. Must be called from inside the same transaction that deletes
 * the segment so the snapshot can't be lost to the `ON DELETE SET NULL`
 * cascade racing ahead.
 *
 * Completed/cancelled/failed campaigns are intentionally left untouched
 * because their reports are historical and re-stamping their snapshot
 * would mislead an operator into thinking they could resume them.
 *
 * Returns the IDs of the campaigns that were updated so the caller can
 * surface a helpful response (e.g. "auto-detached from N campaigns").
 */
export async function snapshotSegmentOntoCampaigns(
  tx: Parameters<Parameters<typeof db.transaction>[0]>[0],
  restaurantId: string,
  segmentId: string,
  rules: AudienceRules,
): Promise<string[]> {
  const { rows } = await tx.execute(sql`
    UPDATE marketing_campaigns
    SET audience_rules = ${JSON.stringify(rules)}::jsonb,
        segment_deleted_at = now(),
        updated_at = now()
    WHERE restaurant_id = ${restaurantId}
      AND segment_id = ${segmentId}
      AND status IN ('draft', 'scheduled', 'sending')
    RETURNING id
  `);
  return (rows as Array<{ id: string }>).map((r) => r.id);
}

// ─── Lifecycle ─────────────────────────────────────────────────────────────

/** Validate that the channel-specific payload is well-formed before send. */
function assertReadyForSend(c: CampaignRow): void {
  if (c.channel === 'email') {
    if (!c.subject?.trim()) throw new ValidationError('Email subject is required');
    if (!c.body_html?.trim()) throw new ValidationError('Email body is required');
  } else if (c.channel === 'whatsapp') {
    // WhatsApp credentials live on the branch row, so a campaign without a
    // branch can never resolve a sender. Fail at launch rather than at
    // dispatch time so the operator sees a clear error.
    if (!c.branch_id) throw new ValidationError('WhatsApp campaigns require a branch with WhatsApp credentials configured');
    if (!c.template_name?.trim()) throw new ValidationError('WhatsApp template name is required');
    if (!c.template_lang?.trim()) throw new ValidationError('WhatsApp template language is required');
  }
}

/**
 * Mark a draft as ready to launch. If `sendNow` is true, schedules at NOW();
 * otherwise uses the campaign's existing `scheduled_at` (must be in the
 * future). The dispatcher loop picks it up on the next tick.
 */
export async function launchCampaign(
  restaurantId: string,
  id: string,
  opts: { sendNow?: boolean; scheduledAt?: string | null } = {}
): Promise<CampaignRow> {
  await initDatabase();
  const c = await getCampaign(restaurantId, id);
  if (!c) throw new ValidationError('Campaign not found');
  if (c.status !== 'draft' && c.status !== 'scheduled') {
    throw new ValidationError(`Cannot launch a campaign in status "${c.status}"`);
  }
  assertReadyForSend(c);

  // Pre-flight SMTP credential check for email campaigns.
  // We verify before touching the campaign row so no state changes on failure
  // and the operator sees the problem immediately with a clear message.
  if (c.channel === 'email') {
    try {
      await verifySmtp(c.branch_id, restaurantId);
    } catch (err) {
      const msg = err instanceof Error ? err.message : String(err);
      throw new ValidationError(msg);
    }
  }

  let when: Date;
  if (opts.sendNow) {
    when = new Date();
  } else if (opts.scheduledAt) {
    when = new Date(opts.scheduledAt);
  } else if (c.scheduled_at) {
    when = new Date(c.scheduled_at);
  } else {
    throw new ValidationError('Schedule time is required when not sending now');
  }
  if (Number.isNaN(when.getTime())) {
    throw new ValidationError('Invalid schedule time');
  }

  const { rows } = await db.execute(sql`
    UPDATE marketing_campaigns
    SET status = 'scheduled', scheduled_at = ${when.toISOString()}, last_error = NULL, updated_at = now()
    WHERE restaurant_id = ${restaurantId} AND id = ${id}
    RETURNING ${CAMPAIGN_COLS}
  `);
  // Boot the dispatcher so we don't have to wait for the next tick interval
  // (idempotent — safe to call from a request handler).
  void import('./dispatcher').then((m) => m.startDispatcher()).catch(() => { /* */ });
  return rowToCampaign(rows[0] as Record<string, unknown>);
}

export async function cancelCampaign(restaurantId: string, id: string): Promise<CampaignRow> {
  await initDatabase();
  const c = await getCampaign(restaurantId, id);
  if (!c) throw new ValidationError('Campaign not found');
  if (c.status === 'completed' || c.status === 'cancelled') return c;

  await db.execute(sql`
    UPDATE marketing_campaigns SET status = 'cancelled', updated_at = now()
    WHERE restaurant_id = ${restaurantId} AND id = ${id}
  `);
  // Stop any queued deliveries from going out.
  await db.execute(sql`
    UPDATE campaign_deliveries SET status = 'skipped', last_error = 'campaign cancelled', updated_at = now()
    WHERE campaign_id = ${id} AND status IN ('queued','sending')
  `);
  return (await getCampaign(restaurantId, id))!;
}

// ─── Fan-out ───────────────────────────────────────────────────────────────

/**
 * Resolve the campaign's audience and insert one campaign_delivery per
 * recipient. Idempotent: the unique index (campaign_id, customer_id,
 * recipient) guarantees re-runs don't duplicate. Returns the count of
 * new rows inserted.
 */
export async function fanOutCampaign(campaignId: string): Promise<number> {
  await initDatabase();
  const { rows: cRows } = await db.execute(sql`
    SELECT ${CAMPAIGN_COLS} FROM marketing_campaigns WHERE id = ${campaignId} LIMIT 1
  `);
  const cRow = cRows[0] as Record<string, unknown> | undefined;
  if (!cRow) return 0;
  const c = rowToCampaign(cRow);
  if (c.status !== 'scheduled' && c.status !== 'sending') return 0;

  // Server-side INSERT...SELECT — never loads recipients into Node.js memory.
  // Safe for 50k+ audiences: single Postgres statement, no per-chunk round-trips.
  const { inserted, audienceCount } = await fanOutInsertDirect(
    campaignId, c.restaurant_id, c.audience_rules, c.channel,
  );

  // Move campaign into sending and snapshot the audience size.
  await db.execute(sql`
    UPDATE marketing_campaigns
    SET status = 'sending',
        audience_count = ${audienceCount},
        started_at = COALESCE(started_at, now()),
        updated_at = now()
    WHERE id = ${campaignId}
  `);
  return inserted;
}

// ─── Reporting ─────────────────────────────────────────────────────────────

export interface CampaignReport {
  campaign: CampaignRow;
  totals: {
    queued: number;
    sending: number;
    sent: number;
    failed: number;
    skipped: number;
  };
  recent: Array<{
    id: string; recipient: string; status: string; sent_at: string | null; last_error: string | null;
  }>;
}

export async function getCampaignReport(restaurantId: string, id: string): Promise<CampaignReport | null> {
  const c = await getCampaign(restaurantId, id);
  if (!c) return null;

  // When the campaign belongs to a sibling launch group (Email + WhatsApp
  // pair), aggregate every member of the group so the operator sees combined
  // totals and a merged recent-deliveries feed under either sibling row.
  let campaignIds: string[] = [id];
  if (c.launch_group_id) {
    const { rows: gRows } = await db.execute(sql`
      SELECT id FROM marketing_campaigns
      WHERE restaurant_id = ${restaurantId} AND launch_group_id = ${c.launch_group_id}
    `);
    campaignIds = (gRows as Array<{ id: string }>).map((r) => r.id);
    if (campaignIds.length === 0) campaignIds = [id];
  }

  const { rows: aggRows } = await db.execute(sql`
    SELECT status, COUNT(*)::int AS n FROM campaign_deliveries
    WHERE campaign_id = ANY(${bindValue(campaignIds)}::uuid[]) AND restaurant_id = ${restaurantId}
    GROUP BY status
  `);
  const totals = { queued: 0, sending: 0, sent: 0, failed: 0, skipped: 0 };
  for (const r of aggRows as Array<{ status: string; n: number }>) {
    if (r.status in totals) (totals as Record<string, number>)[r.status] = r.n;
  }
  const { rows: recent } = await db.execute(sql`
    SELECT id, recipient, status, sent_at, last_error
    FROM campaign_deliveries
    WHERE campaign_id = ANY(${bindValue(campaignIds)}::uuid[]) AND restaurant_id = ${restaurantId}
    ORDER BY updated_at DESC LIMIT 50
  `);
  return {
    campaign: c,
    totals,
    recent: (recent as Array<{ id: string; recipient: string; status: string; sent_at: string | null; last_error: string | null }>)
      .map((r) => ({
        id: r.id, recipient: r.recipient, status: r.status,
        sent_at: r.sent_at ? String(r.sent_at) : null,
        last_error: r.last_error,
      })),
  };
}

// ─── Delivery list (for "Failed / Skipped" report tab) ─────────────────────

export interface DeliveryListItem {
  id: string;
  campaign_id: string;
  recipient: string;
  /** 'skipped' = permanent failure (bad address, auth error, opted out)
   *  'failed'  = transient failure that exhausted all retries */
  status: 'failed' | 'skipped';
  last_error: string | null;
  attempts: number;
  updated_at: string;
}

export interface ListDeliveriesResult {
  items: DeliveryListItem[];
  total: number;
  page: number;
  pageSize: number;
}

/**
 * Paginated list of failed/skipped deliveries for a campaign (or its launch
 * group siblings). Returns null when the campaign doesn't exist.
 */
export async function listCampaignDeliveries(
  restaurantId: string,
  campaignId: string,
  opts: { page?: number; pageSize?: number } = {},
): Promise<ListDeliveriesResult | null> {
  await initDatabase();
  const c = await getCampaign(restaurantId, campaignId);
  if (!c) return null;

  let campaignIds: string[] = [campaignId];
  if (c.launch_group_id) {
    const { rows: gRows } = await db.execute(sql`
      SELECT id FROM marketing_campaigns
      WHERE restaurant_id = ${restaurantId} AND launch_group_id = ${c.launch_group_id}
    `);
    campaignIds = (gRows as Array<{ id: string }>).map((r) => r.id);
    if (campaignIds.length === 0) campaignIds = [campaignId];
  }

  const page = Math.max(1, opts.page ?? 1);
  const pageSize = Math.min(200, Math.max(1, opts.pageSize ?? 50));
  const offset = (page - 1) * pageSize;

  const { rows: countRows } = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM campaign_deliveries
    WHERE campaign_id = ANY(${bindValue(campaignIds)}::uuid[])
      AND restaurant_id = ${restaurantId}
      AND status IN ('failed', 'skipped')
  `);
  const total = Number(((countRows[0] as { n?: unknown })?.n) ?? 0);

  const { rows } = await db.execute(sql`
    SELECT id, campaign_id, recipient, status, last_error, attempts, updated_at
    FROM campaign_deliveries
    WHERE campaign_id = ANY(${bindValue(campaignIds)}::uuid[])
      AND restaurant_id = ${restaurantId}
      AND status IN ('failed', 'skipped')
    ORDER BY updated_at DESC
    LIMIT ${pageSize} OFFSET ${offset}
  `);

  return {
    items: (rows as Array<{
      id: string; campaign_id: string; recipient: string;
      status: 'failed' | 'skipped'; last_error: string | null;
      attempts: unknown; updated_at: unknown;
    }>).map((r) => ({
      id: r.id,
      campaign_id: r.campaign_id,
      recipient: r.recipient,
      status: r.status,
      last_error: r.last_error,
      attempts: Number(r.attempts),
      updated_at: String(r.updated_at),
    })),
    total,
    page,
    pageSize,
  };
}

/**
 * Full (unpaginated) export of all failed/skipped deliveries for a campaign,
 * used exclusively for CSV downloads. Deliberately has no row-count cap so
 * operators can retrieve every bad address regardless of list size.
 * Returns null when the campaign doesn't exist.
 */
export async function exportAllCampaignDeliveries(
  restaurantId: string,
  campaignId: string,
): Promise<DeliveryListItem[] | null> {
  await initDatabase();
  const c = await getCampaign(restaurantId, campaignId);
  if (!c) return null;

  let campaignIds: string[] = [campaignId];
  if (c.launch_group_id) {
    const { rows: gRows } = await db.execute(sql`
      SELECT id FROM marketing_campaigns
      WHERE restaurant_id = ${restaurantId} AND launch_group_id = ${c.launch_group_id}
    `);
    campaignIds = (gRows as Array<{ id: string }>).map((r) => r.id);
    if (campaignIds.length === 0) campaignIds = [campaignId];
  }

  const { rows } = await db.execute(sql`
    SELECT id, campaign_id, recipient, status, last_error, attempts, updated_at
    FROM campaign_deliveries
    WHERE campaign_id = ANY(${bindValue(campaignIds)}::uuid[])
      AND restaurant_id = ${restaurantId}
      AND status IN ('failed', 'skipped')
    ORDER BY updated_at DESC
  `);

  return (rows as Array<{
    id: string; campaign_id: string; recipient: string;
    status: 'failed' | 'skipped'; last_error: string | null;
    attempts: unknown; updated_at: unknown;
  }>).map((r) => ({
    id: r.id,
    campaign_id: r.campaign_id,
    recipient: r.recipient,
    status: r.status,
    last_error: r.last_error,
    attempts: Number(r.attempts),
    updated_at: String(r.updated_at),
  }));
}

/**
 * Look up other members of the same launch group as `id` so the campaigns
 * list view can collapse Email+WhatsApp sibling pairs into a single row.
 */
export async function getCampaignGroupSiblings(
  restaurantId: string, id: string,
): Promise<CampaignRow[]> {
  await initDatabase();
  const c = await getCampaign(restaurantId, id);
  if (!c || !c.launch_group_id) return c ? [c] : [];
  const { rows } = await db.execute(sql`
    SELECT ${CAMPAIGN_COLS} FROM marketing_campaigns
    WHERE restaurant_id = ${restaurantId} AND launch_group_id = ${c.launch_group_id}
    ORDER BY channel ASC
  `);
  return (rows as Record<string, unknown>[]).map(rowToCampaign);
}