import { z } from "zod";
import { AdminActivityLogRepository } from "@/internal/admin-activity-logs/admin-activity-logs.repository";
import { ConsoleUserAccessListRepository } from "@/internal/console-user-access-list/console-user-access-list.repository";
import { ConsoleUserAccessListServices } from "@/internal/console-user-access-list/console-user-access-list.services";
import { ConsoleUserRepository } from "@/internal/console-users/console-users.repository";
import { PromoCodeCampaignsRepository } from "@/internal/repository/promo-code-campaigns/promo-code-campaigns";
import { PromoCodeSignupsRepository } from "@/internal/repository/promo-code-campaigns/promo-code-signups.repo";
import { logError } from "@/lib/logger";
import { error as errorResponse, success } from "@/lib/response";
import type {
  AnalyticsQuery,
  AttachPromoCodesRequest,
  BulkDetachPromoCodesRequest,
  CampaignSignupsRequest,
  CreateCampaignRequest,
  ListCampaignsQuery,
  ReportFilterOptionsRequest,
  ReportUsersRequest,
  SignupAggregateRequest,
  SignupsSummaryRequest,
  UpdateCampaignRequest,
  UsersByPromoCodeQuery,
} from "@/v1/services/admin/promo-code-campaigns/promo-code-campaigns.schema";
import { PromoCodeCampaignsService } from "@/v1/services/admin/promo-code-campaigns/promo-code-campaigns.service";
import { PromoCodesService } from "@/v1/services/admin/promo-codes/promo-codes.service";
import type { Context } from "hono";

function svc(c: Context) {
  const db = c.get("datastore");
  return new PromoCodeCampaignsService(new PromoCodeCampaignsRepository(db));
}

function signupsRepo(c: Context) {
  const memberDb = c.get("memberDatastore");
  const mainDb = c.get("datastore");
  return new PromoCodeSignupsRepository(memberDb || mainDb, mainDb);
}

function buildAnalyticsFromRows(
  rows: Array<{
    promo_code: string;
    country: string | null;
    area: string | null;
    signups: number;
    logged_in: number;
    internal_bookings?: number;
    external_bookings?: number;
  }>,
) {
  const byPromoCode = new Map<string, number>();
  // Logged-in members per promo code, tracked alongside total signups so the
  // UI can show "Registered" next to a highlighted "Logged in" count.
  const loggedInByPromoCode = new Map<string, number>();
  const internalBookingsByPromoCode = new Map<string, number>();
  const externalBookingsByPromoCode = new Map<string, number>();
  const byPromoCodeArea = new Map<string, number>();
  const byCountryPromoCode = new Map<string, number>();
  let total = 0;
  let totalLoggedIn = 0;
  for (const row of rows) {
    const code = row.promo_code;
    const area = row.area || "Unknown";
    const country = row.country || "Unknown";
    byPromoCode.set(code, (byPromoCode.get(code) ?? 0) + row.signups);
    loggedInByPromoCode.set(
      code,
      (loggedInByPromoCode.get(code) ?? 0) + (row.logged_in ?? 0),
    );
    internalBookingsByPromoCode.set(
      code,
      (internalBookingsByPromoCode.get(code) ?? 0) +
        (row.internal_bookings ?? 0),
    );
    externalBookingsByPromoCode.set(
      code,
      (externalBookingsByPromoCode.get(code) ?? 0) +
        (row.external_bookings ?? 0),
    );
    byPromoCodeArea.set(
      `${code}::${area}`,
      (byPromoCodeArea.get(`${code}::${area}`) ?? 0) + row.signups,
    );
    byCountryPromoCode.set(
      `${country}::${code}`,
      (byCountryPromoCode.get(`${country}::${code}`) ?? 0) + row.signups,
    );
    total += row.signups;
    totalLoggedIn += row.logged_in ?? 0;
  }
  return {
    byPromoCode: Array.from(byPromoCode.entries()).map(
      ([promo_code_id, signups]) => ({
        promo_code_id,
        signups,
        logged_in: loggedInByPromoCode.get(promo_code_id) ?? 0,
        internal_bookings: internalBookingsByPromoCode.get(promo_code_id) ?? 0,
        external_bookings: externalBookingsByPromoCode.get(promo_code_id) ?? 0,
      }),
    ),
    byPromoCodeArea: Array.from(byPromoCodeArea.entries()).map(
      ([key, signups]) => {
        const [promo_code_id, area] = key.split("::");
        return { promo_code_id, area, signups };
      },
    ),
    byCountryPromoCode: Array.from(byCountryPromoCode.entries()).map(
      ([key, signups]) => {
        const [country, promo_code_id] = key.split("::");
        return { country, promo_code_id, signups };
      },
    ),
    total,
    totalLoggedIn,
  };
}

function adminEmail(c: Context) {
  return (c.get("adminEmail") as string | undefined) ?? null;
}

function handleErr(c: Context, err: any, fallback: string) {
  logError(`${fallback}:`, err);
  const status = typeof err?.status === "number" ? err.status : 400;
  return errorResponse(c, err?.message || fallback, status, err?.detail ?? err);
}

// --- Promocode Access List enforcement helpers ---
// A scope of `null` means unrestricted (super admin, or never scoped).

function accessListServices(c: Context) {
  return ConsoleUserAccessListServices({
    ConsoleUserAccessListRepository: ConsoleUserAccessListRepository(c as any),
    ConsoleUserRepository: ConsoleUserRepository(c as any),
  });
}

async function getPromoCodeAccessScope(c: Context): Promise<string[] | null> {
  const consoleUserId = c.get("consoleUserId") as string | undefined;
  if (!consoleUserId) return null;
  return accessListServices(c).GetPromoCodeAccessScope(consoleUserId);
}

async function getCampaignAccessScope(c: Context): Promise<string[] | null> {
  const consoleUserId = c.get("consoleUserId") as string | undefined;
  if (!consoleUserId) return null;
  return accessListServices(c).GetCampaignAccessScope(consoleUserId);
}

/**
 * Whether the caller may see this promo code, by code name.
 *
 * The single-resource endpoints take a code straight from the URL, so without a
 * check a restricted user could read any code simply by typing its id — their
 * access list only ever narrowed the *list* endpoints. Returns true when the
 * caller is unrestricted.
 */
async function canSeePromoCode(c: Context, codeName: string): Promise<boolean> {
  const allowed = await getAllowedPromoCodeNames(c);
  if (allowed === null) return true;
  return allowed.has(String(codeName ?? "").trim().toUpperCase());
}

/**
 * Whether the caller may see this campaign.
 *
 * Campaign-scoped endpoints have the same hole as the promo-code ones: the id
 * comes from the URL and was never checked against the caller's campaign scope.
 */
async function canSeeCampaign(c: Context, campaignId: string): Promise<boolean> {
  const allowed = await getCampaignAccessScope(c);
  if (allowed === null) return true;
  return allowed.includes(String(campaignId));
}

/**
 * Consistent refusal for an out-of-scope resource.
 *
 * 404, deliberately, not 403. The console's `coreClient` treats *every* 403 as a
 * dead session — it clears the cookie and redirects to /auth/logout — so
 * answering 403 here would sign a restricted user out instead of telling them
 * they lack access. 404 also avoids confirming that a campaign or code they
 * cannot see exists at all.
 *
 * If the 403 handling in coreClient is ever narrowed to authentication failures
 * only, this should become a 403 with the same message.
 */
function forbidden(c: Context) {
  return errorResponse(
    c,
    "Not found, or you do not have access to it.",
    404,
  );
}

// Several report endpoints key signups by promo code NAME (Updot member
// records store the human-readable code, not the id), so a restricted user's
// id-based scope needs mapping to names. Only fetches the full Updot code
// list when the caller is actually restricted — unrestricted callers (the
// overwhelming majority) pay zero extra cost.
async function getAllowedPromoCodeNames(
  c: Context,
): Promise<Set<string> | null> {
  const scope = await getPromoCodeAccessScope(c);
  if (scope === null) return null;
  const names = new Set<string>();
  if (scope.length === 0) return names;
  const allowedIds = new Set(scope);
  const promoSvc = PromoCodesService.getInstance();
  const PAGE = 500;
  const MAX_OFFSET = 5000;
  let offset = 0;
  while (true) {
    const res = await promoSvc.listPromoCodes({
      includeExpired: "true",
      limit: String(PAGE),
      offset: String(offset),
    });
    const raw = (res as any)?.data;
    const items: any[] = Array.isArray(raw)
      ? raw
      : Array.isArray(raw?.data)
        ? raw.data
        : [];
    for (const it of items) {
      const code = String(it?.code ?? it?.name ?? "").trim();
      if (!code) continue;
      const id = String(it?.id ?? it?.promoCodeId ?? it?.promo_code_id ?? code);
      if (allowedIds.has(id)) names.add(code.toUpperCase());
    }
    if (items.length < PAGE || names.size >= allowedIds.size) break;
    offset += PAGE;
    if (offset >= MAX_OFFSET) break;
  }
  return names;
}

export const listCampaignsHandler = async (c: Context) => {
  try {
    const query = c.req.query() as ListCampaignsQuery;

    // Promocode Access List: scope campaigns to what this user is explicitly
    // allowed to see (no rows for the user = unrestricted). Filtered at the DB
    // level so pagination stays accurate, unlike the external promo codes list.
    const allowedIds = await getCampaignAccessScope(c);

    const result = await svc(c).list(query, allowedIds);
    return success(c, result, "Campaigns fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to list campaigns");
  }
};

export const createCampaignHandler = async (c: Context) => {
  try {
    const body = (await c.req.json()) as CreateCampaignRequest;
    const created = await svc(c).create(body, adminEmail(c));
    return success(c, created, "Campaign created successfully", 201);
  } catch (err: any) {
    return handleErr(c, err, "Failed to create campaign");
  }
};

export const getCampaignHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const result = await svc(c).getById(id);
    return success(c, result, "Campaign fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch campaign");
  }
};

export const updateCampaignHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    // Writes must respect the access list too — otherwise a restricted user
    // could modify a campaign they are not allowed to see.
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const body = (await c.req.json()) as UpdateCampaignRequest;
    const updated = await svc(c).update(id, body, adminEmail(c));
    return success(c, updated, "Campaign updated successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to update campaign");
  }
};

export const deleteCampaignHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    // Writes must respect the access list too — otherwise a restricted user
    // could modify a campaign they are not allowed to see.
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const deleted = await svc(c).delete(id);
    return success(c, deleted, "Campaign deleted successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to delete campaign");
  }
};

export const listCampaignPromoCodesHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const codes = await svc(c).listPromoCodes(id);
    return success(c, codes, "Promo codes fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to list campaign promo codes");
  }
};

export const attachPromoCodesHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    // Writes must respect the access list too — otherwise a restricted user
    // could modify a campaign they are not allowed to see.
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const body = (await c.req.json()) as AttachPromoCodesRequest;
    const updated = await svc(c).attachPromoCodes(id, body, adminEmail(c));
    return success(c, updated, "Promo codes attached successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to attach promo codes");
  }
};

export const detachPromoCodeHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    const promoCodeId = c.req.param("promoCodeId");
    if (!id || !promoCodeId)
      return errorResponse(
        c,
        "Campaign id and promo code id are required",
        400,
      );
    // Writes must respect the access list too — otherwise a restricted user
    // could modify a campaign they are not allowed to see.
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const detached = await svc(c).detachPromoCode(
      id,
      promoCodeId,
      adminEmail(c),
    );
    return success(c, detached, "Promo code detached successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to detach promo code");
  }
};

// Bulk detach — one request, one DELETE, one activity-log entry.
export const bulkDetachPromoCodesHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    // Writes must respect the access list too — otherwise a restricted user
    // could modify a campaign they are not allowed to see.
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const body = (await c.req.json()) as BulkDetachPromoCodesRequest;
    const detached = await svc(c).detachPromoCodes(
      id,
      body.promo_code_ids,
      adminEmail(c),
    );
    return success(c, detached, "Promo codes detached successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to detach promo codes");
  }
};

export const dashboardOverviewHandler = async (c: Context) => {
  try {
    const overview = await svc(c).dashboardOverview();
    /*
     * Scoped after the fact rather than in SQL: the overview is a small
     * per-campaign summary, so filtering rows is simpler than threading a scope
     * through the service — and without it a restricted user saw totals for every
     * campaign, including ones their access list excludes.
     */
    const allowed = await getCampaignAccessScope(c);
    const scoped =
      allowed === null || !Array.isArray(overview)
        ? overview
        : (overview as any[]).filter((row) =>
            allowed.includes(String(row?.campaign_id ?? row?.id ?? "")),
          );
    return success(c, scoped, "Dashboard overview fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch dashboard overview");
  }
};

export const campaignAnalyticsHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const query = c.req.query() as AnalyticsQuery;
    const codes = await svc(c).listPromoCodes(id);
    // Look up signups by promo code NAME (the human-readable code, e.g.
    // "SOCIALKARMA") because `members.signup_promo_code` stores the string.
    const codeNames = codes.map((m) => m.promo_code_name);
    const repo = signupsRepo(c);
    const [rows, byDay] = await Promise.all([
      repo.aggregateSignups({
        promoCodes: codeNames,
        from: query.from,
        to: query.to,
      }),
      repo.aggregateSignupsByDay({
        promoCodes: codeNames,
        from: query.from,
        to: query.to,
      }),
    ]);
    // Map promo_code (name, uppercased by repo) back to the campaign-map
    // promo_code_id so the frontend can correlate slices to the campaign's
    // promo_codes list. Compare on uppercase keys to match the repo's output.
    const upperNameToId = new Map(
      codes.map(
        (m) => [m.promo_code_name.toUpperCase(), m.promo_code_id] as const,
      ),
    );
    const remapped = rows.map((r) => ({
      ...r,
      promo_code: upperNameToId.get(r.promo_code.toUpperCase()) ?? r.promo_code,
    }));
    const analytics = { ...buildAnalyticsFromRows(remapped), byDay };
    return success(
      c,
      { promo_codes: codes, analytics },
      "Campaign analytics fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch campaign analytics");
  }
};

// Aggregated analytics for the "Member Referrals" section. There is no campaign
// row — the code set is EVERY member-referral code (Updot `onlyMemberReferralCodes`).
// We enumerate those codes (paginated proxy), then run the SAME single-query
// aggregation used by campaignAnalyticsHandler, and return the identical shape so
// the existing analytics card renders unchanged.
export const memberReferralAnalyticsHandler = async (c: Context) => {
  try {
    const query = c.req.query() as AnalyticsQuery;
    const promoSvc = PromoCodesService.getInstance();
    // Larger page size → fewer sequential proxy round-trips when the referral
    // set is large (the dominant cost of this endpoint).
    const PAGE = 500;
    const codes: Array<{ promo_code_id: string; promo_code_name: string }> = [];
    let offset = 0;
    while (true) {
      const res = await promoSvc.listPromoCodes({
        onlyMemberReferralCodes: "true",
        includeExpired: "true",
        limit: String(PAGE),
        offset: String(offset),
      });
      const raw = (res as any)?.data;
      const items: any[] = Array.isArray(raw)
        ? raw
        : Array.isArray(raw?.data)
          ? raw.data
          : [];
      for (const it of items) {
        const code = String(it?.code ?? it?.name ?? "").trim();
        if (!code) continue;
        const id = String(
          it?.id ?? it?.promoCodeId ?? it?.promo_code_id ?? code,
        );
        codes.push({ promo_code_id: id, promo_code_name: code });
      }
      if (items.length < PAGE) break;
      offset += PAGE;
    }

    // Promocode Access List: scope to promo codes this user is explicitly
    // allowed to see (no rows for the user = unrestricted).
    const promoScope = await getPromoCodeAccessScope(c);
    const scopedCodes =
      promoScope === null
        ? codes
        : codes.filter((m) => promoScope.includes(m.promo_code_id));

    if (scopedCodes.length === 0) {
      return success(
        c,
        {
          promo_codes: [],
          analytics: { ...buildAnalyticsFromRows([]), byDay: [] },
        },
        "Member referral analytics fetched successfully",
        200,
      );
    }

    const codeNames = scopedCodes.map((m) => m.promo_code_name);
    const repo = signupsRepo(c);
    const [rows, byDay] = await Promise.all([
      repo.aggregateSignups({
        promoCodes: codeNames,
        from: query.from,
        to: query.to,
      }),
      repo.aggregateSignupsByDay({
        promoCodes: codeNames,
        from: query.from,
        to: query.to,
      }),
    ]);
    // Remap repo's uppercased code back to our promo_code_id so the frontend can
    // correlate analytics rows to the promo_codes list (same as campaigns).
    const upperNameToId = new Map(
      scopedCodes.map(
        (m) => [m.promo_code_name.toUpperCase(), m.promo_code_id] as const,
      ),
    );
    const remapped = rows.map((r) => ({
      ...r,
      promo_code: upperNameToId.get(r.promo_code.toUpperCase()) ?? r.promo_code,
    }));
    const analytics = { ...buildAnalyticsFromRows(remapped), byDay };
    return success(
      c,
      { promo_codes: scopedCodes, analytics },
      "Member referral analytics fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch member referral analytics");
  }
};

// Aggregated analytics across ALL regular (non-member-referral) promo codes —
// the "Karma Subito Promo Code" universe. Mirrors memberReferralAnalyticsHandler
// but fetches the code list with onlyPromoCodes=true instead.
export const promoCodesAnalyticsHandler = async (c: Context) => {
  try {
    const query = c.req.query() as AnalyticsQuery;
    const promoSvc = PromoCodesService.getInstance();
    const PAGE = 500;
    const codes: Array<{ promo_code_id: string; promo_code_name: string }> = [];
    let offset = 0;
    while (true) {
      const res = await promoSvc.listPromoCodes({
        onlyPromoCodes: "true",
        includeExpired: "true",
        limit: String(PAGE),
        offset: String(offset),
      });
      const raw = (res as any)?.data;
      const items: any[] = Array.isArray(raw)
        ? raw
        : Array.isArray(raw?.data)
          ? raw.data
          : [];
      for (const it of items) {
        const code = String(it?.code ?? it?.name ?? "").trim();
        if (!code) continue;
        const id = String(
          it?.id ?? it?.promoCodeId ?? it?.promo_code_id ?? code,
        );
        codes.push({ promo_code_id: id, promo_code_name: code });
      }
      if (items.length < PAGE) break;
      offset += PAGE;
    }

    // Promocode Access List: scope to promo codes this user is explicitly
    // allowed to see (no rows for the user = unrestricted).
    const promoScope = await getPromoCodeAccessScope(c);
    const scopedCodes =
      promoScope === null
        ? codes
        : codes.filter((m) => promoScope.includes(m.promo_code_id));

    if (scopedCodes.length === 0) {
      return success(
        c,
        {
          promo_codes: [],
          analytics: { ...buildAnalyticsFromRows([]), byDay: [] },
        },
        "Promo code analytics fetched successfully",
        200,
      );
    }

    const codeNames = scopedCodes.map((m) => m.promo_code_name);
    const repo = signupsRepo(c);
    const [rows, byDay] = await Promise.all([
      repo.aggregateSignups({
        promoCodes: codeNames,
        from: query.from,
        to: query.to,
      }),
      repo.aggregateSignupsByDay({
        promoCodes: codeNames,
        from: query.from,
        to: query.to,
      }),
    ]);
    const upperNameToId = new Map(
      scopedCodes.map(
        (m) => [m.promo_code_name.toUpperCase(), m.promo_code_id] as const,
      ),
    );
    const remapped = rows.map((r) => ({
      ...r,
      promo_code: upperNameToId.get(r.promo_code.toUpperCase()) ?? r.promo_code,
    }));
    const analytics = { ...buildAnalyticsFromRows(remapped), byDay };
    return success(
      c,
      { promo_codes: scopedCodes, analytics },
      "Promo code analytics fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch promo code analytics");
  }
};

async function resolvePromoCodeParam(
  c: Context,
  param: string,
): Promise<string> {
  const raw = String(param || "").trim();
  if (!raw) return raw;
  const isUuid =
    /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(raw);
  if (isUuid) {
    const db = c.get("datastore");
    if (db) {
      const row = await db
        .selectFrom("promo_code_campaign_map")
        .select("promo_code_name")
        .where("promo_code_id", "=", raw)
        .executeTakeFirst();
      if (row?.promo_code_name) return row.promo_code_name;
    }
  }
  return raw;
}

async function resolveCodeList(c: Context, codes: string[]): Promise<string[]> {
  if (!codes.length) return [];
  const db = c.get("datastore");
  const result: string[] = [];
  const uuids: string[] = [];
  const uuidRegex =
    /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
  for (const item of codes) {
    const raw = String(item || "").trim();
    if (!raw) continue;
    if (uuidRegex.test(raw)) {
      uuids.push(raw);
    } else {
      result.push(raw);
    }
  }
  if (uuids.length > 0 && db) {
    const rows = await db
      .selectFrom("promo_code_campaign_map")
      .select(["promo_code_id", "promo_code_name"])
      .where("promo_code_id", "in", uuids)
      .execute();
    for (const r of rows) {
      if (r.promo_code_name) result.push(r.promo_code_name);
    }
  }
  return Array.from(new Set(result));
}

export const promoCodeAnalyticsHandler = async (c: Context) => {
  try {
    const rawParam = c.req.param("promoCodeId");
    if (!rawParam) return errorResponse(c, "Promo code id is required", 400);
    const promoCodeId = await resolvePromoCodeParam(c, rawParam);
    if (!(await canSeePromoCode(c, promoCodeId))) return forbidden(c);
    const query = c.req.query() as AnalyticsQuery;
    const repo = signupsRepo(c);
    const [rows, byDay] = await Promise.all([
      repo.aggregateSignups({
        promoCodes: [promoCodeId],
        from: query.from,
        to: query.to,
      }),
      repo.aggregateSignupsByDay({
        promoCodes: [promoCodeId],
        from: query.from,
        to: query.to,
      }),
    ]);
    const analytics = { ...buildAnalyticsFromRows(rows), byDay };
    return success(
      c,
      analytics,
      "Promo code analytics fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch promo code analytics");
  }
};

export const promoCodeUsersHandler = async (c: Context) => {
  try {
    const rawParam = c.req.param("promoCodeId");
    if (!rawParam) return errorResponse(c, "Promo code id is required", 400);
    const promoCodeId = await resolvePromoCodeParam(c, rawParam);
    // Member PII — the access list has to apply here, not only to the list views.
    if (!(await canSeePromoCode(c, promoCodeId))) return forbidden(c);
    const query = c.req.query() as UsersByPromoCodeQuery & AnalyticsQuery;
    const page = Math.max(1, parseInt(query.page ?? "1", 10) || 1);
    const pageSize = Math.min(
      100,
      Math.max(1, parseInt(query.pageSize ?? "15", 10) || 15),
    );
    const { items, total } = await signupsRepo(c).listUsersByPromoCode({
      promoCode: promoCodeId,
      page,
      pageSize,
      search: query.search,
      from: query.from,
      to: query.to,
      loggedIn: query.loggedIn === "true" ? true : undefined,
    });
    return success(
      c,
      {
        items,
        pagination: {
          page,
          pageSize,
          total,
          totalPages: Math.max(1, Math.ceil(total / pageSize)),
        },
      },
      "Users fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch users for promo code");
  }
};

// Combined, paginated user list. With no codes it returns ALL promo-code
// signups (the default "all members" view); with codes it filters to them.
/**
 * Every promo code attached to a campaign.
 *
 * The campaigns dashboard only ever counts codes that belong to a campaign, so a
 * drill-down that defaulted to "all promo codes" disagreed with the badge it was
 * opened from — 59 against 53, the gap being auto-generated referral codes with
 * no campaign. Passing `scope: "campaigns"` reproduces the dashboard's scope
 * without shipping a hundred code names through the URL.
 */
async function getCampaignAttachedCodes(c: Context): Promise<string[]> {
  const db = c.get("datastore");
  if (!db) return [];
  const rows = await db
    .selectFrom("promo_code_campaign_map")
    .select("promo_code_name")
    .distinct()
    .execute();
  return rows
    .map((r: any) => String(r.promo_code_name ?? "").trim())
    .filter(Boolean);
}

/**
 * Aggregates for the promo-code bookings page.
 *
 * Same access-list handling as the row listing: an empty `codes` means "every
 * code" to the repo, so a restricted user's request is replaced by their
 * allow-list rather than forwarded.
 */
export const promoCodeBookingsAnalyticsHandler = async (c: Context) => {
  try {
    const body = (await c.req.json().catch(() => null)) as {
      codes?: unknown[];
      scope?: string;
      from?: string;
      to?: string;
      statuses?: unknown[];
      entities?: unknown[];
    } | null;
    if (!body) return errorResponse(c, "Expected a JSON body", 400);

    const rawCodes = (body.codes ?? []).map((s) => String(s)).filter(Boolean);
    let codes = await resolveCodeList(c, rawCodes);
    // No explicit codes + campaigns scope => every campaign-attached code, which
    // is what the dashboard's own figures cover.
    if (codes.length === 0 && body.scope === "campaigns") {
      codes = await getCampaignAttachedCodes(c);
    }

    const allowedNames = await getAllowedPromoCodeNames(c);
    if (allowedNames !== null) {
      codes =
        codes.length > 0
          ? codes.filter((code) => allowedNames.has(code.toUpperCase()))
          : Array.from(allowedNames);
    }

    const data = await signupsRepo(c).aggregateBookings({
      promoCodes: codes,
      from: body.from,
      to: body.to,
      statuses: (body.statuses ?? []).map(String).filter(Boolean),
      entities: (body.entities ?? []).map(String).filter(Boolean),
    });
    return success(c, data, "Booking analytics fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch booking analytics");
  }
};

/**
 * The individual bookings behind a Bookings Analytics slice.
 *
 * POST rather than GET because the campaigns dashboard drills in across every
 * code currently in scope — well over a hundred names, which would not survive a
 * query string.
 *
 * `kind` is required. The repo's internal and curated predicates can both match
 * one booking, so a combined list could not reconcile with either slice.
 */
export const promoCodeBookingsHandler = async (c: Context) => {
  try {
    const body = (await c.req.json().catch(() => null)) as {
      codes?: unknown[];
      scope?: string;
      kind?: string;
      from?: string;
      to?: string;
      statuses?: unknown[];
      entities?: unknown[];
      page?: number;
      pageSize?: number;
    } | null;
    if (!body) return errorResponse(c, "Expected a JSON body", 400);

    const kind = body.kind === "curated" ? "curated" : "internal";
    const page = Math.max(1, Number(body.page ?? 1) || 1);
    const pageSize = Math.min(200, Math.max(1, Number(body.pageSize ?? 25) || 25));

    const rawCodes = (body.codes ?? []).map((s) => String(s)).filter(Boolean);
    let codes = await resolveCodeList(c, rawCodes);
    // No explicit codes + campaigns scope => every campaign-attached code, which
    // is what the dashboard's own figures cover.
    if (codes.length === 0 && body.scope === "campaigns") {
      codes = await getCampaignAttachedCodes(c);
    }

    /*
     * Promocode Access List, mirroring reportUsersHandler.
     *
     * An empty `codes` array means "every promo code" to the repo, so a
     * restricted user's request must be replaced by their explicit allow-list
     * rather than forwarded — otherwise they could read bookings belonging to
     * codes they have no access to.
     */
    const allowedNames = await getAllowedPromoCodeNames(c);
    if (allowedNames !== null) {
      codes =
        codes.length > 0
          ? codes.filter((code) => allowedNames.has(code.toUpperCase()))
          : Array.from(allowedNames);
      if (codes.length === 0) {
        return success(
          c,
          { items: [], pagination: { page, pageSize, total: 0, totalPages: 1 } },
          "Bookings fetched successfully",
          200,
        );
      }
    }

    const { items, total } = await signupsRepo(c).listBookingsByPromoCodes({
      promoCodes: codes,
      kind,
      from: body.from,
      to: body.to,
      statuses: (body.statuses ?? []).map(String).filter(Boolean),
      entities: (body.entities ?? []).map(String).filter(Boolean),
      page,
      pageSize,
    });

    return success(
      c,
      {
        items,
        pagination: {
          page,
          pageSize,
          total,
          totalPages: Math.max(1, Math.ceil(total / pageSize)),
        },
      },
      "Bookings fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch bookings for promo codes");
  }
};

/**
 * The account type a non-super-admin's reports are locked to.
 *
 * Everyone below super admin sees only guest-membership members. That is the promo
 * population — anyone on another membership has been upgraded out of it — and it is the
 * one account type promo reporting is about.
 *
 * Named rather than inlined because it is enforced in two places that must agree: the
 * report query itself, and the filter options that populate the picker. If they
 * disagreed, the dropdown would offer a value the report refuses to honour.
 */
const DEFAULT_ACCOUNT_TYPE = "KARMA CLUB GUEST MEMBERSHIP";

/**
 * The account types this caller may report on, or `null` for no restriction.
 *
 * Enforced here rather than only hidden in the console. The console does hide the
 * control for non-super-admins, but a hidden control is an affordance, not a
 * permission — the request can still be made by hand, and this is what refuses it.
 */
function allowedAccountTypes(c: Context): string[] | null {
  return c.get("isAdminConsoleSuperAdmin") === true
    ? null
    : [DEFAULT_ACCOUNT_TYPE];
}

/**
 * The account types a report should actually be scoped to.
 *
 * A super admin's own choice, or nothing at all. Everyone else gets the guest
 * membership regardless of what they sent — their request's list is intersected with
 * what they are allowed, so asking for another type returns nothing for it rather than
 * silently widening the report.
 */
function reportAccountType(
  c: Context,
  asked: unknown,
): string[] | undefined {
  const allowed = allowedAccountTypes(c);
  const requested =
    Array.isArray(asked) && asked.length > 0
      ? (asked as string[])
      : undefined;
  if (allowed === null) return requested;
  return requested
    ? requested.filter((t) => allowed.includes(t))
    : allowed;
}

export const reportUsersHandler = async (c: Context) => {
  try {
    const body = (await c.req.json()) as ReportUsersRequest;
    let rawCodes = (body.codes ?? []).map((s) => String(s)).filter(Boolean);
    let codes = await resolveCodeList(c, rawCodes);
    const page = Math.max(1, body.page ?? 1);
    const pageSize = Math.min(1000, Math.max(1, body.pageSize ?? 25));

    // Promocode Access List: an empty `codes` array means "all promo-code
    // signups" to the repo below, so a restricted user's empty/unfiltered
    // request must be substituted with their explicit allow-list — never
    // forwarded as-is, or they'd see everyone's signups.
    const allowedNames = await getAllowedPromoCodeNames(c);
    if (allowedNames !== null) {
      codes =
        codes.length > 0
          ? codes.filter((code) => allowedNames.has(code.toUpperCase()))
          : Array.from(allowedNames);
      if (codes.length === 0) {
        return success(
          c,
          {
            items: [],
            pagination: { page, pageSize, total: 0, totalPages: 1 },
          },
          "Report users fetched successfully",
          200,
        );
      }
    }

    const { items, total } = await signupsRepo(c).listUsersByPromoCodes({
      promoCodes: codes,
      page,
      pageSize,
      search: body.search,
      loggedIn: body.loggedIn === true ? true : undefined,
      from: body.from,
      to: body.to,
      country:
        Array.isArray(body.country) && body.country.length > 0
          ? body.country
          : undefined,
      status: body.status,
      // Which contact bucket to list — the Contact Completeness drill-down.
      contact: body.contact,
      // logged_in | pending — the Logged In drill-down.
      loginStatus: body.loginStatus,
      // The "No Country Info" area, which no country name can select.
      countryMissing: body.countryMissing,
      nationality:
        Array.isArray(body.nationality) && body.nationality.length > 0
          ? body.nationality
          : undefined,
      /*
       * A super admin may filter by any account type, or none. Everyone else is locked
       * to the guest membership: their request's own `accountType` is intersected with
       * what they are allowed, so asking for another type returns nothing for it rather
       * than silently widening.
       */
      accountType: reportAccountType(c, body.accountType),
      /*
       * Only an explicit account-type choice reaches past the upgraded-contract
       * exclusion, and only a super admin can make one.
       *
       * Without this the choice could not work: the exclusion already pins the account
       * type to the guest membership, so asking for KARMA CLUB while it applied would
       * return nothing. With no choice made the exclusion stands, which keeps an
       * unfiltered report equal to the dashboards rather than quietly wider.
       */
      includeUpgraded:
        allowedAccountTypes(c) === null &&
        Array.isArray(body.accountType) &&
        body.accountType.length > 0,
      dobFrom: body.dobFrom,
      dobTo: body.dobTo,
    });
    return success(
      c,
      {
        items,
        pagination: {
          page,
          pageSize,
          total,
          totalPages: Math.max(1, Math.ceil(total / pageSize)),
        },
      },
      "Report users fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch report users");
  }
};

export const reportFilterOptionsHandler = async (c: Context) => {
  try {
    const body = (await c.req.json()) as ReportFilterOptionsRequest;
    let rawCodes = (body.codes ?? []).map((s) => String(s)).filter(Boolean);
    let codes = await resolveCodeList(c, rawCodes);

    const allowedNames = await getAllowedPromoCodeNames(c);
    if (allowedNames !== null) {
      codes =
        codes.length > 0
          ? codes.filter((code) => allowedNames.has(code.toUpperCase()))
          : Array.from(allowedNames);
      if (codes.length === 0) {
        return success(
          c,
          { countries: [], nationalities: [], accountTypes: [] },
          "Report filter options fetched successfully",
          200,
        );
      }
    }

    const options = await signupsRepo(c).getFilterOptions({
      promoCodes: codes,
      from: body.from,
      to: body.to,
      /*
       * A super admin gets the complete account-type list.
       *
       * With the upgraded-contract exclusion applied the list collapses to the single
       * guest membership — every promo signup still in scope has that type by
       * definition — which left the picker with one useless option. Anyone else has
       * their options narrowed below anyway, so opting in changes nothing for them.
       */
      includeUpgraded: allowedAccountTypes(c) === null,
    });

    /*
     * A non-super-admin is offered only the account type they are allowed. The console
     * hides the picker for them anyway, but returning the full list here would let a
     * hidden control — or a hand-made request — appear to offer choices the report
     * would then refuse.
     */
    const allowed = allowedAccountTypes(c);
    const scopedOptions =
      allowed === null
        ? options
        : {
            ...options,
            accountTypes: (options.accountTypes ?? []).filter((t: string) =>
              allowed.includes(t),
            ),
          };

    return success(
      c,
      scopedOptions,
      "Report filter options fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch report filter options");
  }
};

// Campaign-scoped change history. Gated by promo-code-campaigns:read (NOT the
// global admin-logs permission) so anyone who can read campaigns can view a
// campaign's logs without being logged out. Reuses the activity-log store,
// filtered to this campaign's API path.
export const campaignLogsHandler = async (c: Context) => {
  try {
    const id = c.req.param("id");
    if (!id) return errorResponse(c, "Campaign id is required", 400);
    if (!(await canSeeCampaign(c, id))) return forbidden(c);
    const query = c.req.query();
    const limit = query.limit ? parseInt(query.limit, 10) : 15;
    const offset = query.offset ? parseInt(query.offset, 10) : 0;
    const db = c.get("datastore");
    const repo = AdminActivityLogRepository(db);
    const result = await repo.searchLogs({
      search: `admin-console/promo-code-campaigns/${id}`,
      method: ["POST", "PUT", "PATCH", "DELETE"],
      limit,
      offset,
    });
    return success(c, result, "Campaign logs fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch campaign logs");
  }
};

export const campaignsForPromoCodeHandler = async (c: Context) => {
  try {
    const code = c.req.param("code");
    if (!code) return errorResponse(c, "Promo code is required", 400);
    const repo = new PromoCodeCampaignsRepository(c.get("datastore"));
    const items = await repo.findCampaignsForPromoCodeName(code);
    return success(c, items, "Campaigns fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch campaigns for promo code");
  }
};

export const signupsSummaryHandler = async (c: Context) => {
  try {
    const body = (await c.req.json()) as SignupsSummaryRequest;
    let codes = (body.codes ?? []).map((s) => String(s)).filter(Boolean);
    const allowedNames = await getAllowedPromoCodeNames(c);
    if (allowedNames !== null) {
      codes = codes.filter((code) => allowedNames.has(code.toUpperCase()));
    }
    if (codes.length === 0) {
      return success(c, [], "No codes provided", 200);
    }
    const rows = await signupsRepo(c).aggregateSignups({
      promoCodes: codes,
      from: body.from,
      to: body.to,
    });
    const map = new Map<string, number>();
    for (const r of rows) {
      const code = String(r.promo_code).toUpperCase();
      map.set(code, (map.get(code) ?? 0) + Number(r.signups || 0));
    }
    const result = codes.map((c) => ({
      code: c,
      signups: map.get(String(c).toUpperCase()) ?? 0,
    }));
    return success(c, result, "Signups summary fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch signups summary");
  }
};

// Per-code, per-country signup aggregate for a window. The Promo Codes report
// uses this for window-aware Registered / Logged-In counts, to populate the
// country filter options, and to filter to "codes with signups in window".
export const signupAggregateHandler = async (c: Context) => {
  try {
    const body = (await c.req.json()) as SignupAggregateRequest;
    let codes = (body.codes ?? []).map((s) => String(s)).filter(Boolean);
    const allowedNames = await getAllowedPromoCodeNames(c);
    if (allowedNames !== null) {
      codes = codes.filter((code) => allowedNames.has(code.toUpperCase()));
    }
    if (codes.length === 0) {
      return success(c, [], "No codes provided", 200);
    }
    const rows = await signupsRepo(c).aggregateSignups({
      promoCodes: codes,
      from: body.from,
      to: body.to,
      country:
        Array.isArray(body.country) && body.country.length > 0
          ? body.country
          : undefined,
    });
    const result = rows.map((r) => ({
      code: r.promo_code,
      country: r.country,
      signups: r.signups,
      loggedIn: r.logged_in,
    }));
    return success(c, result, "Signup aggregate fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch signup aggregate");
  }
};

// Per-campaign signup totals for a window (+ optional country). Reads every
// campaign→code mapping in one query, aggregates signups across all those codes
// in one Updot query, then sums per campaign. A code in multiple campaigns
// counts toward each. Also returns the distinct country list (pre-filter) so
// the campaign report can offer a country filter.
export const campaignSignupsHandler = async (c: Context) => {
  try {
    const body = (await c.req.json()) as CampaignSignupsRequest;
    const country =
      Array.isArray(body.country) && body.country.length > 0
        ? body.country
        : undefined;

    const allMaps = await svc(c).listAllPromoCodeMaps();

    // Promocode Access List: drop campaigns this user isn't explicitly
    // allowed to see (no rows for the user = unrestricted). Output here is
    // per-campaign totals, so campaign-level scoping is the relevant boundary.
    const campaignScope = await getCampaignAccessScope(c);
    const maps =
      campaignScope === null
        ? allMaps
        : allMaps.filter((m) => campaignScope.includes(m.campaign_id));

    if (maps.length === 0) {
      return success(c, { items: [], countries: [] }, "No campaigns", 200);
    }
    // UPPER(code) → set of campaign ids it belongs to.
    const codeToCampaigns = new Map<string, Set<string>>();
    for (const m of maps) {
      const key = String(m.promo_code_name).toUpperCase();
      if (!codeToCampaigns.has(key)) codeToCampaigns.set(key, new Set());
      codeToCampaigns.get(key)!.add(m.campaign_id);
    }
    const codeNames = Array.from(new Set(maps.map((m) => m.promo_code_name)));

    const rows = await signupsRepo(c).aggregateSignups({
      promoCodes: codeNames,
      from: body.from,
      to: body.to,
    });

    // Distinct countries (computed before the country filter, for options).
    const countries = Array.from(
      new Set(
        rows.map((r) => (r.country ?? "").trim()).filter((x) => x.length > 0),
      ),
    ).sort();

    const totals = new Map<
      string,
      {
        signups: number;
        loggedIn: number;
        internalBookings: number;
        externalBookings: number;
      }
    >();
    for (const r of rows) {
      if (country && !(r.country && country.includes(r.country))) continue;
      const ids = codeToCampaigns.get(String(r.promo_code).toUpperCase());
      if (!ids) continue;
      for (const id of ids) {
        const cur = totals.get(id) ?? {
          signups: 0,
          loggedIn: 0,
          internalBookings: 0,
          externalBookings: 0,
        };
        cur.signups += Number(r.signups || 0);
        cur.loggedIn += Number(r.logged_in || 0);
        cur.internalBookings += Number(r.internal_bookings || 0);
        cur.externalBookings += Number(r.external_bookings || 0);
        totals.set(id, cur);
      }
    }
    const items = Array.from(totals, ([campaignId, v]) => ({
      campaignId,
      signups: v.signups,
      loggedIn: v.loggedIn,
      internalBookings: v.internalBookings,
      externalBookings: v.externalBookings,
    }));

    /*
     * Per-code totals, in addition to the per-campaign roll-up above.
     *
     * Reward allocation is `reward value × logged-in members`, and the reward
     * value is per promo code — so a campaign-level loggedIn cannot be used
     * unless every code in the campaign happens to carry the same reward. The
     * rows are already grouped by code, so this exposes what was being collapsed
     * away rather than querying anything new.
     */
    const byCodeTotals = new Map<
      string,
      {
        promoCode: string;
        campaignIds: string[];
        signups: number;
        loggedIn: number;
      }
    >();
    for (const r of rows) {
      if (country && !(r.country && country.includes(r.country))) continue;
      const code = String(r.promo_code || "").toUpperCase();
      if (!code) continue;
      const ids = codeToCampaigns.get(code);
      if (!ids) continue;
      const cur = byCodeTotals.get(code) ?? {
        promoCode: code,
        campaignIds: Array.from(ids),
        signups: 0,
        loggedIn: 0,
      };
      cur.signups += Number(r.signups || 0);
      cur.loggedIn += Number(r.logged_in || 0);
      byCodeTotals.set(code, cur);
    }
    const byPromoCode = Array.from(byCodeTotals.values());

    /*
     * Contact completeness, rolled up across the codes in scope.
     *
     * Same promoCodes/from/to/country as the signup aggregate above, so the
     * bucket total reconciles with the signup total rather than describing a
     * different population. Country is passed to the query instead of being
     * filtered afterwards because the buckets are already aggregated per code —
     * there is no per-country row left to filter on this side.
     */
    const contactRows = await signupsRepo(c).aggregateContactCompleteness({
      promoCodes: codeNames,
      from: body.from,
      to: body.to,
      country,
    });
    const contactCompleteness = contactRows.reduce(
      (acc, r) => {
        // Only codes attached to a campaign the caller can see.
        if (!codeToCampaigns.has(String(r.promo_code).toUpperCase()))
          return acc;
        acc.phoneAndEmail += r.phone_and_email;
        acc.phoneOnly += r.phone_only;
        acc.emailOnly += r.email_only;
        acc.neither += r.neither;
        acc.total += r.total;
        return acc;
      },
      {
        phoneAndEmail: 0,
        phoneOnly: 0,
        emailOnly: 0,
        neither: 0,
        total: 0,
      },
    );

    const byCountryCampaignTotals = new Map<
      string,
      { country: string; campaignId: string; signups: number }
    >();
    for (const r of rows) {
      const rowCountry = (r.country ?? "").trim();
      if (!rowCountry) continue;
      if (country && !country.includes(rowCountry)) continue;
      const ids = codeToCampaigns.get(String(r.promo_code).toUpperCase());
      if (!ids) continue;
      for (const id of ids) {
        const key = `${rowCountry}::${id}`;
        const cur = byCountryCampaignTotals.get(key) ?? {
          country: rowCountry,
          campaignId: id,
          signups: 0,
        };
        cur.signups += Number(r.signups || 0);
        byCountryCampaignTotals.set(key, cur);
      }
    }
    const byCountryCampaign = Array.from(byCountryCampaignTotals.values());

    /*
     * Per-country totals with no campaign attribution, plus the no-country
     * remainder and the overall split.
     *
     * `byCountryCampaign` above fans each row out across every campaign its code
     * belongs to, so a code mapped to two campaigns contributes twice — correct for
     * a per-campaign chart, wrong for anything that counts members. The signup
     * drill-down lists distinct members, so it needs this: grouped by country only,
     * each aggregate row counted once.
     *
     * The country is passed through exactly as the column holds it, untrimmed. The
     * drill-down sends these strings straight back as `country = ANY(...)`, which is
     * an exact match, so tidying them here would produce a country whose count and
     * member list disagree.
     *
     * `noCountry` is counted rather than left to be derived: subtracting the placed
     * total from the overall one only happens to work while no other row is being
     * dropped, and that is not a property worth depending on.
     */
    const byCountryTotals = new Map<
      string,
      { country: string; signups: number; loggedIn: number }
    >();
    const signupTotals = { signups: 0, loggedIn: 0, noCountry: 0 };
    for (const r of rows) {
      // Same scoping as every other roll-up here: codes outside the user's
      // campaigns are skipped, then the country filter applies.
      if (!codeToCampaigns.has(String(r.promo_code).toUpperCase())) continue;
      const raw = r.country ?? "";
      if (country && !country.includes(raw)) continue;
      const signups = Number(r.signups || 0);
      const loggedIn = Number(r.logged_in || 0);
      signupTotals.signups += signups;
      signupTotals.loggedIn += loggedIn;
      if (raw.trim() === "") {
        signupTotals.noCountry += signups;
        continue;
      }
      const cur = byCountryTotals.get(raw) ?? {
        country: raw,
        signups: 0,
        loggedIn: 0,
      };
      cur.signups += signups;
      cur.loggedIn += loggedIn;
      byCountryTotals.set(raw, cur);
    }
    const byCountry = Array.from(byCountryTotals.values());

    return success(
      c,
      {
        items,
        countries,
        byCountryCampaign,
        byCountry,
        signupTotals,
        byPromoCode,
        contactCompleteness,
      },
      "Campaign signups fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch campaign signups");
  }
};

// Full code→campaign mapping in one query. Powers the Members report's Campaign
// filter + column, correlating Updot members (by promo code) to OUR campaigns.
export const codeCampaignMapHandler = async (c: Context) => {
  try {
    const allRows = await svc(c).listCodeCampaignMap();

    // Promocode Access List: a mapping row is visible only if the user is
    // allowed to see BOTH the campaign and the promo code it names (no rows
    // for the user on a dimension = unrestricted on that dimension).
    const [campaignScope, promoScope] = await Promise.all([
      getCampaignAccessScope(c),
      getPromoCodeAccessScope(c),
    ]);
    const rows =
      campaignScope === null && promoScope === null
        ? allRows
        : allRows.filter(
            (r) =>
              (campaignScope === null ||
                campaignScope.includes(r.campaign_id)) &&
              (promoScope === null || promoScope.includes(r.promo_code_id)),
          );

    return success(c, rows, "Code campaign map fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch code campaign map");
  }
};

// Unfiltered campaign list for the Access List picker — the admin
// configuring someone's grants needs to see every campaign, regardless of
// the viewer's own or the target's access scope (unlike listCampaignsHandler,
// which scopes results to the *viewer's* own campaign access).
export const listAllCampaignsForAccessListHandler = async (c: Context) => {
  try {
    const items = await svc(c).dashboardOverview();
    return success(c, items, "Campaigns fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch campaigns");
  }
};

// Full, UNFILTERED campaign→promo-code mapping for the Access List's own
// picker — the admin configuring someone's grants needs to see every
// campaign's codes to auto-grant them together, regardless of the admin's
// own (or the target user's) access scope. Deliberately does not apply the
// dual-scope filter that codeCampaignMapHandler uses for Reports.
export const campaignPromoCodeMapHandler = async (c: Context) => {
  try {
    const rows = await svc(c).listCodeCampaignMap();
    return success(
      c,
      rows,
      "Campaign promo code map fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch campaign promo code map");
  }
};

// Deleting a promo code in Updot doesn't notify this app, so
// promo_code_campaign_map can accumulate stale rows pointing at codes that
// no longer exist. Detaches any such orphaned mapping. Safe to re-run anytime
// (also runs on a schedule — see initializeBackgroundServices).
export const reconcileOrphanedPromoCodesHandler = async (c: Context) => {
  try {
    const result = await svc(c).reconcileOrphanedPromoCodes();
    return success(
      c,
      result,
      result.orphaned > 0
        ? `Detached ${result.orphaned} orphaned promo-code mapping(s)`
        : "No orphaned promo-code mappings found",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to reconcile orphaned promo codes");
  }
};

// Powers the Promocode Access List picker: every live Updot promo code plus
// every code ever attached to a campaign locally, even if since deleted
// upstream (isLive: false) — so historical codes stay assignable.
export const listAllKnownPromoCodesHandler = async (c: Context) => {
  try {
    const items = await svc(c).listAllKnownPromoCodes();
    return success(c, items, "Promo codes fetched successfully", 200);
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch promo codes");
  }
};

// Returns every row in the `countries` table (id + name + code) sorted
// alphabetically by name. Used by the Campaigns listing page to populate the
// CountryMultiSelect from the DB instead of from a static JSON file, and by the
// promo code form, which submits the record `id` (uuid) in countryRecordIDs.
export const listCountriesHandler = async (c: Context) => {
  try {
    const memberDb = c.get("memberDatastore");
    const rows = await memberDb
      .selectFrom("countries" as any)
      .select(["id", "name", "code"] as any)
      .orderBy("name" as any, "asc")
      .execute();
    return success(c, rows, "Countries fetched", 200);
  } catch (err: any) {
    logError(err, "Failed to list countries");
    return errorResponse(c, err?.message || "Failed to list countries", 500);
  }
};

export const memberPointsHandler = async (c: Context) => {
  try {
    const membershipNumber = c.req.param("membershipNumber");
    if (!membershipNumber)
      return errorResponse(c, "Membership number is required", 400);

    const { getMemberPoints } = await import("@/viewpoint");

    let pointsArray: any[] = [];
    try {
      const indiaPoints = await getMemberPoints(membershipNumber, 3208);
      if (Array.isArray(indiaPoints)) pointsArray.push(...indiaPoints);
    } catch {
      /* ignore if not found in India */
    }
    try {
      const nonIndiaPoints = await getMemberPoints(membershipNumber, 3206);
      if (Array.isArray(nonIndiaPoints)) pointsArray.push(...nonIndiaPoints);
    } catch {
      /* ignore if not found in Non-India */
    }

    const totalBalance = pointsArray.reduce(
      (acc, curr) => acc + (Number(curr.Balance) || 0),
      0,
    );
    const totalValue = pointsArray.reduce(
      (acc, curr) => acc + (Number(curr.Value) || 0),
      0,
    );

    return success(
      c,
      {
        membershipNumber,
        balance: totalBalance,
        allocated: totalValue,
        entitlements: pointsArray,
      },
      "Member points fetched successfully",
      200,
    );
  } catch (err: any) {
    return handleErr(c, err, "Failed to fetch member points");
  }
};