import "server-only";

import { Polar } from "@polar-sh/sdk";
import type { Payment } from "@polar-sh/sdk/models/components/payment";
import type { PresentmentCurrency } from "@polar-sh/sdk/models/components/presentmentcurrency";
import { validateEvent } from "@polar-sh/sdk/webhooks";
import type { ResultSetHeader, RowDataPacket } from "mysql2/promise";
import {
  restoreManagedMembersAfterBillingRecovery,
  suspendManagedMembersForPlanDowngrade,
} from "@/lib/billing-member-access";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import {
  formatPolarPrice,
  type PolarLocalizedPrice,
} from "@/lib/billing-region";
import type { MailPlanBillingStatus } from "@/lib/mail-billing";
import { syncMailcowMailboxQuotasForOwnerUserId } from "@/lib/mail-plan-quota";
import { getMailAbsoluteUrl } from "@/lib/mail-urls";
import { hasOperationalBenefitByOwnerUserId } from "@/lib/mail-operational-benefit";
import { assertMailBillingManagementAccessByEmail } from "@/lib/toss-pay";
import { recordMarketingLifecycleEvent } from "@/lib/marketing-analytics";

const DEFAULT_POLAR_PRODUCT_ID = "409d59a4-465e-40b9-a4f9-bd398a1ef57b";
const ZERO_DECIMAL_CURRENCIES = new Set([
  "BIF", "CLP", "DJF", "GNF", "JPY", "KMF", "KRW", "MGA", "PYG",
  "RWF", "UGX", "VND", "VUV", "XAF", "XOF", "XPF",
]);

function toAnalyticsCurrencyValue(amount: number, currency: string) {
  const normalizedCurrency = currency.trim().toUpperCase();
  return ZERO_DECIMAL_CURRENCIES.has(normalizedCurrency) ? amount : amount / 100;
}

export type PolarSubscriptionStatus =
  | "active"
  | "canceled"
  | "incomplete"
  | "past_due"
  | "paused"
  | "pending"
  | "revoked";

export type PolarBillingOverview = {
  billingSeatCount: number;
  currency: string;
  entitled: boolean;
  estimatedMonthlyCost: number;
  profile: {
    customerId: string;
    status: "active";
  } | null;
  recentOrders: Array<{
    currency: string;
    id: number;
    invoiceNumber: string | null;
    orderedAt: string;
    paid: boolean;
    paidAt: string | null;
    refundedAmount: number;
    status: string;
    taxAmount: number;
    totalAmount: number;
    totalAmountLabel: string;
  }>;
  subscription: {
    cancelAtPeriodEnd: boolean;
    currentPeriodEnd: string | null;
    seats: number;
    status: PolarSubscriptionStatus;
  } | null;
  unitPrice: number;
};

type OwnerUserRow = RowDataPacket & {
  company_name: string;
  display_name: string;
  email: string;
  id: number;
  preferred_locale: string | null;
};

type CountRow = RowDataPacket & { total: number };

type PolarSubscriptionRow = RowDataPacket & {
  amount: number;
  cancel_at_period_end: number;
  current_period_end: Date | null;
  currency: string;
  polar_customer_id: string;
  polar_subscription_id: string;
  seats: number;
  status: PolarSubscriptionStatus;
};

type PolarSubscriptionRevocationRow = RowDataPacket & {
  owner_user_id: number;
  polar_subscription_id: string;
};

type PolarOrderRow = RowDataPacket & {
  currency: string;
  id: number;
  invoice_number: string | null;
  ordered_at: Date;
  paid: number;
  paid_at: Date | null;
  refunded_amount: number;
  status: string;
  tax_amount: number;
  total_amount: number;
};

type StoredPolarPaymentRow = RowDataPacket & {
  owner_user_id: number;
  polar_checkout_id: string | null;
  polar_order_id: string | null;
  polar_payment_id: string;
  status: string;
};

export type PolarPaymentSyncSummary = {
  discovered: number;
  failed: number;
  processed: number;
  skipped: number;
  statusChanged: number;
  succeeded: number;
};

function requirePolarEnv(name: "POLAR_ACCESS_TOKEN" | "POLAR_WEBHOOK_SECRET") {
  const value = process.env[name]?.trim();

  if (!value) {
    throw new Error(`${name.toLowerCase().replaceAll("_", "-")}-missing`);
  }

  return value;
}

function getPolarProductId() {
  return process.env.POLAR_PRODUCT_ID?.trim() || DEFAULT_POLAR_PRODUCT_ID;
}

function getPolarClient() {
  return new Polar({
    accessToken: requirePolarEnv("POLAR_ACCESS_TOKEN"),
    server: process.env.POLAR_ENVIRONMENT === "sandbox" ? "sandbox" : "production",
    timeoutMs: 15_000,
  });
}

function externalCustomerId(ownerUserId: number) {
  return `official-mail-owner-${ownerUserId}`;
}

function normalizePolarText(value: unknown, maxLength: number) {
  const normalized = typeof value === "string" ? value.trim() : "";
  return normalized ? normalized.slice(0, maxLength) : null;
}

async function getOwnerUserByEmail(email: string) {
  const [rows] = await getDbPool().query<OwnerUserRow[]>(
    `
      SELECT id, email, company_name, display_name, preferred_locale
      FROM users
      WHERE email = ?
      LIMIT 1
    `,
    [email.trim().toLowerCase()],
  );

  return rows[0] ?? null;
}

async function getBillingSeatCount(ownerUserId: number) {
  const [rows] = await getDbPool().query<CountRow[]>(
    `
      SELECT COUNT(*) AS total
      FROM managed_team_mailboxes
      WHERE owner_user_id = ?
        AND status = 'active'
    `,
    [ownerUserId],
  );

  return Math.max(1, Number(rows[0]?.total ?? 0) + 1);
}

function isSubscriptionRowEntitled(
  subscription: Pick<PolarSubscriptionRow, "status" | "current_period_end"> | null,
) {
  if (!subscription) {
    return false;
  }

  if (subscription.status === "active") {
    return true;
  }

  return (
    subscription.status === "canceled" &&
    Boolean(subscription.current_period_end) &&
    subscription.current_period_end!.getTime() > Date.now()
  );
}

async function hasNonPolarGrowthEntitlement(ownerUserId: number) {
  if (await hasOperationalBenefitByOwnerUserId(ownerUserId)) {
    return true;
  }

  const [rows] = await getDbPool().query<(RowDataPacket & { id: number })[]>(
    `
      SELECT id
      FROM mailbox_toss_pay_subscriptions
      WHERE owner_user_id = ?
        AND status = 'active'
        AND last_charged_at IS NOT NULL
      LIMIT 1
    `,
    [ownerUserId],
  );

  return rows.length > 0;
}

export function getPolarMailPlanBillingStatus(
  overview: Pick<PolarBillingOverview, "entitled" | "subscription"> | null,
): MailPlanBillingStatus {
  if (overview?.entitled) {
    return "active";
  }

  switch (overview?.subscription?.status) {
    case "active":
      return "active";
    case "past_due":
    case "paused":
      return "paused";
    case "canceled":
    case "revoked":
      return "cancelled";
    case "incomplete":
    case "pending":
      return "pending";
    default:
      return null;
  }
}

export async function getPolarBillingOverviewByOwnerEmail(
  email: string,
  price: Pick<PolarLocalizedPrice, "amount" | "currency">,
): Promise<PolarBillingOverview | null> {
  await ensureOfficialMailSchema();
  const owner = await getOwnerUserByEmail(email);

  if (!owner) {
    return null;
  }

  const [[subscriptionRows], billingSeatCount, [orderRows]] = await Promise.all([
    getDbPool().query<PolarSubscriptionRow[]>(
      `
        SELECT
          polar_subscription_id,
          polar_customer_id,
          status,
          currency,
          amount,
          seats,
          current_period_end,
          cancel_at_period_end
        FROM mailbox_polar_subscriptions
        WHERE owner_user_id = ?
        LIMIT 1
      `,
      [owner.id],
    ),
    getBillingSeatCount(owner.id),
    getDbPool().query<PolarOrderRow[]>(
      `
        SELECT
          id,
          status,
          paid,
          currency,
          tax_amount,
          total_amount,
          refunded_amount,
          invoice_number,
          ordered_at,
          paid_at
        FROM mailbox_polar_orders
        WHERE owner_user_id = ?
        ORDER BY ordered_at DESC, id DESC
        LIMIT 8
      `,
      [owner.id],
    ),
  ]);
  const subscription = subscriptionRows[0] ?? null;
  const currency = subscription?.currency ?? price.currency;
  const subscriptionSeats = Math.max(1, Number(subscription?.seats ?? 1));
  const unitPrice = subscription
    ? Math.round(Number(subscription.amount) / subscriptionSeats)
    : price.amount;

  return {
    billingSeatCount,
    currency,
    entitled: isSubscriptionRowEntitled(subscription),
    estimatedMonthlyCost: billingSeatCount * unitPrice,
    profile: subscription && !["incomplete", "pending", "revoked"].includes(subscription.status)
      ? { customerId: subscription.polar_customer_id, status: "active" }
      : null,
    recentOrders: orderRows.map((order) => ({
      currency: order.currency,
      id: order.id,
      invoiceNumber: order.invoice_number,
      orderedAt: order.ordered_at.toISOString(),
      paid: Boolean(order.paid),
      paidAt: order.paid_at?.toISOString() ?? null,
      refundedAmount: Number(order.refunded_amount),
      status: order.status,
      taxAmount: Number(order.tax_amount),
      totalAmount: Number(order.total_amount),
      totalAmountLabel: formatPolarPrice(
        Number(order.total_amount),
        order.currency,
        owner.preferred_locale ?? "en",
      ),
    })),
    subscription: subscription
      ? {
          cancelAtPeriodEnd: Boolean(subscription.cancel_at_period_end),
          currentPeriodEnd: subscription.current_period_end?.toISOString() ?? null,
          seats: Number(subscription.seats),
          status: subscription.status,
        }
      : null,
    unitPrice,
  };
}

export async function hasActivePolarGrowthPlanByOwnerEmail(email: string) {
  await ensureOfficialMailSchema();
  const owner = await getOwnerUserByEmail(email);

  if (!owner) {
    return false;
  }

  return hasActivePolarGrowthPlanByOwnerUserId(owner.id);
}

async function hasActivePolarGrowthPlanByOwnerUserId(ownerUserId: number) {
  const [[subscriptionRows], [orderRows]] = await Promise.all([
    getDbPool().query<PolarSubscriptionRow[]>(
    `
      SELECT status, current_period_end
      FROM mailbox_polar_subscriptions
      WHERE owner_user_id = ?
      LIMIT 1
    `,
      [ownerUserId],
    ),
    getDbPool().query<(RowDataPacket & { refunded_amount: number; total_amount: number })[]>(
      `
        SELECT total_amount, refunded_amount
        FROM mailbox_polar_orders
        WHERE owner_user_id = ?
          AND paid = 1
        ORDER BY ordered_at DESC, id DESC
        LIMIT 1
      `,
      [ownerUserId],
    ),
  ]);
  const latestOrder = orderRows[0];
  const fullyRefunded = Boolean(
    latestOrder &&
    Number(latestOrder.total_amount) > 0 &&
    Number(latestOrder.refunded_amount) >= Number(latestOrder.total_amount),
  );

  return !fullyRefunded && isSubscriptionRowEntitled(subscriptionRows[0] ?? null);
}

async function synchronizeExternalAccessAfterPolarBillingChange(ownerUserId: number) {
  const [rows] = await getDbPool().query<(RowDataPacket & { email: string })[]>(
    "SELECT email FROM users WHERE id = ? LIMIT 1",
    [ownerUserId],
  );
  const ownerEmail = rows[0]?.email;

  if (!ownerEmail) {
    return;
  }

  try {
    const { synchronizeMailboxExternalAccessByOwnerEmail } = await import(
      "@/lib/mail-external-access"
    );
    await synchronizeMailboxExternalAccessByOwnerEmail(ownerEmail);
  } catch (error) {
    console.error("[polar-billing] external access reconciliation failed", {
      error: error instanceof Error ? error.message : error,
      ownerUserId,
    });
  }
}

export function getCustomerIpAddress(headers: Pick<Headers, "get">) {
  const candidates = [
    headers.get("cf-connecting-ip"),
    headers.get("x-forwarded-for")?.split(",")[0],
    headers.get("x-real-ip"),
  ];

  for (const candidate of candidates) {
    const value = candidate?.trim();

    if (value && /^[0-9a-f:.]+$/i.test(value)) {
      return value;
    }
  }

  return undefined;
}

export async function createPolarCheckoutForOwnerEmail(input: {
  customerIpAddress?: string;
  email: string;
  locale?: string | null;
  price: Pick<PolarLocalizedPrice, "amount" | "currency" | "countryCode">;
}) {
  await ensureOfficialMailSchema();
  await assertMailBillingManagementAccessByEmail(input.email);
  const owner = await getOwnerUserByEmail(input.email);

  if (!owner) {
    throw new Error("user-not-found");
  }

  const seatCount = await getBillingSeatCount(owner.id);
  const productId = getPolarProductId();
  const checkout = await getPolarClient().checkouts.create({
    allowDiscountCodes: false,
    allowTrial: false,
    currency: input.price.currency as PresentmentCurrency,
    customerEmail: owner.email,
    customerIpAddress: input.customerIpAddress,
    customerName: owner.company_name || owner.display_name,
    externalCustomerId: externalCustomerId(owner.id),
    locale: input.locale || owner.preferred_locale || "en",
    maxSeats: seatCount,
    metadata: {
      billing_country: input.price.countryCode ?? "unknown",
      owner_user_id: owner.id,
      price_currency: input.price.currency,
    },
    minSeats: seatCount,
    prices: {
      [productId]: [
        {
          amountType: "seat_based",
          priceCurrency: input.price.currency as PresentmentCurrency,
          seatTiers: {
            seatTierType: "volume",
            tiers: [{ minSeats: 1, maxSeats: null, pricePerSeat: input.price.amount }],
          },
          taxBehavior: "exclusive",
        },
      ],
    },
    products: [productId],
    requireBillingAddress: true,
    returnUrl: getMailAbsoluteUrl("/mail?panel=setup&section=billing"),
    seats: seatCount,
    successUrl: getMailAbsoluteUrl(
      "/mail?panel=setup&section=billing&success=polar-checkout-complete&checkout_id={CHECKOUT_ID}",
    ),
  });

  return {
    checkoutId: checkout.id,
    checkoutUrl: checkout.url,
    currency: input.price.currency,
    estimatedMonthlyCost: input.price.amount * seatCount,
    seatCount,
    unitPrice: input.price.amount,
  };
}

export async function createPolarCustomerPortalForOwnerEmail(email: string) {
  await ensureOfficialMailSchema();
  await assertMailBillingManagementAccessByEmail(email);
  const owner = await getOwnerUserByEmail(email);

  if (!owner) {
    throw new Error("user-not-found");
  }

  const session = await getPolarClient().customerSessions.create({
    externalCustomerId: externalCustomerId(owner.id),
    returnUrl: getMailAbsoluteUrl("/mail?panel=setup&section=billing"),
  });

  return {
    customerPortalUrl: session.customerPortalUrl,
    expiresAt: session.expiresAt.toISOString(),
  };
}

export async function syncActivePolarSubscriptionSeatsByOwnerEmail(
  email: string,
  seatsOverride?: number,
) {
  await ensureOfficialMailSchema();
  const owner = await getOwnerUserByEmail(email);

  if (!owner) {
    throw new Error("user-not-found");
  }

  const [rows] = await getDbPool().query<PolarSubscriptionRow[]>(
    `
      SELECT polar_subscription_id, status, seats, current_period_end
      FROM mailbox_polar_subscriptions
      WHERE owner_user_id = ?
      LIMIT 1
    `,
    [owner.id],
  );
  const subscription = rows[0] ?? null;

  if (!isSubscriptionRowEntitled(subscription)) {
    return null;
  }

  const seats = Number.isInteger(seatsOverride) && Number(seatsOverride) > 0
    ? Number(seatsOverride)
    : await getBillingSeatCount(owner.id);

  if (Number(subscription!.seats) === seats) {
    return { changed: false, seats };
  }

  const updated = await getPolarClient().subscriptions.update({
    id: subscription!.polar_subscription_id,
    subscriptionUpdate: { seats },
  });

  await getDbPool().query(
    `
      UPDATE mailbox_polar_subscriptions
      SET seats = ?, amount = ?, updated_at = NOW()
      WHERE owner_user_id = ?
    `,
    [updated.seats ?? seats, updated.amount, owner.id],
  );

  return { changed: true, seats: updated.seats ?? seats };
}

export async function revokePolarSubscriptionsForDeletedAccounts(
  ownerUserIds: number[],
) {
  const normalizedOwnerUserIds = [
    ...new Set(ownerUserIds.filter((id) => Number.isSafeInteger(id) && id > 0)),
  ];

  if (normalizedOwnerUserIds.length === 0) {
    return 0;
  }

  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<PolarSubscriptionRevocationRow[]>(
    `
      SELECT owner_user_id, polar_subscription_id
      FROM mailbox_polar_subscriptions
      WHERE owner_user_id IN (${normalizedOwnerUserIds.map(() => "?").join(", ")})
        AND status IN ('active', 'canceled', 'past_due', 'paused', 'pending')
      ORDER BY owner_user_id ASC
    `,
    normalizedOwnerUserIds,
  );

  for (const row of rows) {
    await getPolarClient().subscriptions.revoke({ id: row.polar_subscription_id });
    await getDbPool().query(
      `
        UPDATE mailbox_polar_subscriptions
        SET status = 'revoked', cancel_at_period_end = 0,
            canceled_at = COALESCE(canceled_at, NOW()), ended_at = COALESCE(ended_at, NOW()),
            updated_at = NOW()
        WHERE owner_user_id = ?
          AND polar_subscription_id = ?
      `,
      [row.owner_user_id, row.polar_subscription_id],
    );
  }

  return rows.length;
}

function metadataOwnerUserId(metadata: Record<string, unknown> | undefined) {
  const value = Number(metadata?.owner_user_id);
  return Number.isInteger(value) && value > 0 ? value : null;
}

function externalIdOwnerUserId(value: string | null | undefined) {
  const match = value?.match(/^official-mail-owner-(\d+)$/);
  const ownerUserId = Number(match?.[1]);
  return Number.isInteger(ownerUserId) && ownerUserId > 0 ? ownerUserId : null;
}

async function resolveWebhookOwnerUserId(data: {
  customer?: { email?: string | null; externalId?: string | null };
  metadata?: Record<string, unknown>;
}) {
  const direct =
    metadataOwnerUserId(data.metadata) ??
    externalIdOwnerUserId(data.customer?.externalId);

  if (direct) {
    return direct;
  }

  if (!data.customer?.email) {
    return null;
  }

  const owner = await getOwnerUserByEmail(data.customer.email);
  return owner?.id ?? null;
}

async function resolveCheckoutOwnerUserId(
  checkout: Awaited<ReturnType<ReturnType<typeof getPolarClient>["checkouts"]["get"]>>,
) {
  const direct =
    metadataOwnerUserId(checkout.metadata) ??
    externalIdOwnerUserId(checkout.externalCustomerId);

  if (direct) {
    return direct;
  }

  if (!checkout.customerEmail) {
    return null;
  }

  const owner = await getOwnerUserByEmail(checkout.customerEmail);
  return owner?.id ?? null;
}

async function upsertPolarPayment(ownerUserId: number, payment: Payment) {
  const cardBrand = "methodMetadata" in payment ? payment.methodMetadata.brand : null;
  const cardLastFour = "methodMetadata" in payment ? payment.methodMetadata.last4 : null;
  const declineReason = payment.declineReason;
  const declineMessage = payment.declineMessage;

  await getDbPool().query(
    `
      INSERT INTO mailbox_polar_payments (
        owner_user_id,
        polar_payment_id,
        polar_checkout_id,
        polar_order_id,
        status,
        payment_method,
        payment_trigger,
        currency,
        amount,
        card_brand,
        card_last_four,
        decline_reason,
        decline_message,
        payment_created_at,
        payment_modified_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        status_changed_at = IF(
          status <> VALUES(status)
          OR NOT (decline_reason <=> VALUES(decline_reason))
          OR NOT (decline_message <=> VALUES(decline_message)),
          CURRENT_TIMESTAMP(3),
          status_changed_at
        ),
        owner_user_id = VALUES(owner_user_id),
        polar_checkout_id = VALUES(polar_checkout_id),
        polar_order_id = VALUES(polar_order_id),
        status = VALUES(status),
        payment_method = VALUES(payment_method),
        payment_trigger = VALUES(payment_trigger),
        currency = VALUES(currency),
        amount = VALUES(amount),
        card_brand = VALUES(card_brand),
        card_last_four = VALUES(card_last_four),
        decline_reason = VALUES(decline_reason),
        decline_message = VALUES(decline_message),
        payment_created_at = VALUES(payment_created_at),
        payment_modified_at = VALUES(payment_modified_at),
        last_synced_at = CURRENT_TIMESTAMP(3)
    `,
    [
      ownerUserId,
      payment.id,
      payment.checkoutId,
      payment.orderId,
      payment.status,
      payment.method,
      payment.trigger,
      payment.currency.toLowerCase(),
      payment.amount,
      normalizePolarText(cardBrand, 32),
      normalizePolarText(cardLastFour, 4),
      normalizePolarText(declineReason, 128),
      normalizePolarText(declineMessage, 500),
      payment.createdAt,
      payment.modifiedAt,
    ],
  );
}

export async function syncPolarPayments(): Promise<PolarPaymentSyncSummary> {
  await ensureOfficialMailSchema();
  const client = getPolarClient();
  const page = await client.payments.list({
    limit: 100,
    sorting: ["-created_at"],
  });
  const payments = page.result.items;
  const [storedRows] = await getDbPool().query<StoredPolarPaymentRow[]>(
    `
      SELECT
        owner_user_id,
        polar_checkout_id,
        polar_order_id,
        polar_payment_id,
        status
      FROM mailbox_polar_payments
      WHERE polar_payment_id IN (?)
    `,
    [payments.length > 0 ? payments.map((payment) => payment.id) : [""]],
  );
  const storedByPaymentId = new Map(storedRows.map((row) => [row.polar_payment_id, row]));
  const ownerByCheckoutId = new Map(
    storedRows
      .filter((row) => row.polar_checkout_id)
      .map((row) => [row.polar_checkout_id!, row.owner_user_id]),
  );
  const ownerByOrderId = new Map(
    storedRows
      .filter((row) => row.polar_order_id)
      .map((row) => [row.polar_order_id!, row.owner_user_id]),
  );

  if (payments.length > 0) {
    const orderIds = payments.flatMap((payment) => (payment.orderId ? [payment.orderId] : []));
    const checkoutIds = payments.flatMap((payment) => (payment.checkoutId ? [payment.checkoutId] : []));

    if (orderIds.length > 0) {
      const [orderRows] = await getDbPool().query<(RowDataPacket & {
        owner_user_id: number;
        polar_order_id: string;
      })[]>(
        `SELECT owner_user_id, polar_order_id FROM mailbox_polar_orders WHERE polar_order_id IN (?)`,
        [orderIds],
      );
      orderRows.forEach((row) => ownerByOrderId.set(row.polar_order_id, row.owner_user_id));
    }

    if (checkoutIds.length > 0) {
      const [checkoutRows] = await getDbPool().query<(RowDataPacket & {
        owner_user_id: number;
        checkout_id: string;
      })[]>(
        `
          SELECT owner_user_id, checkout_id
          FROM mailbox_polar_subscriptions
          WHERE checkout_id IN (?)
          UNION
          SELECT owner_user_id, checkout_id
          FROM mailbox_polar_orders
          WHERE checkout_id IN (?)
        `,
        [checkoutIds, checkoutIds],
      );
      checkoutRows.forEach((row) => ownerByCheckoutId.set(row.checkout_id, row.owner_user_id));
    }
  }

  const summary: PolarPaymentSyncSummary = {
    discovered: 0,
    failed: 0,
    processed: 0,
    skipped: 0,
    statusChanged: 0,
    succeeded: 0,
  };

  for (const payment of payments) {
    const stored = storedByPaymentId.get(payment.id);
    let ownerUserId = stored?.owner_user_id ?? null;

    if (!ownerUserId && payment.orderId) {
      ownerUserId = ownerByOrderId.get(payment.orderId) ?? null;
    }

    if (!ownerUserId && payment.checkoutId) {
      ownerUserId = ownerByCheckoutId.get(payment.checkoutId) ?? null;
    }

    if (!ownerUserId && payment.checkoutId) {
      try {
        const checkout = await client.checkouts.get({ id: payment.checkoutId });

        if (checkout.productId !== getPolarProductId()) {
          summary.skipped += 1;
          continue;
        }

        ownerUserId = await resolveCheckoutOwnerUserId(checkout);
        if (ownerUserId) {
          ownerByCheckoutId.set(payment.checkoutId, ownerUserId);
        }
      } catch (error) {
        console.error("polar-payment-checkout-lookup-failed", {
          checkoutId: payment.checkoutId,
          error: error instanceof Error ? error.message : String(error),
          paymentId: payment.id,
        });
      }
    }

    if (!ownerUserId) {
      summary.skipped += 1;
      continue;
    }

    await upsertPolarPayment(ownerUserId, payment);
    summary.processed += 1;

    if (!stored) {
      summary.discovered += 1;
    } else if (stored.status !== payment.status) {
      summary.statusChanged += 1;
    }

    if (payment.status === "succeeded") {
      summary.succeeded += 1;
    } else if (payment.status === "failed") {
      summary.failed += 1;
    }
  }

  return summary;
}

async function upsertPolarSubscription(
  data: Parameters<typeof resolveWebhookOwnerUserId>[0] & {
    amount: number;
    cancelAtPeriodEnd: boolean;
    canceledAt: Date | null;
    checkoutId: string | null;
    currency: string;
    currentPeriodEnd: Date;
    currentPeriodStart: Date;
    customerId: string;
    endedAt: Date | null;
    id: string;
    productId: string;
    recurringInterval: string;
    seats?: number | null;
    status: string;
  },
  eventTimestamp: Date,
) {
  if (data.productId !== getPolarProductId()) {
    return null;
  }

  const ownerUserId = await resolveWebhookOwnerUserId(data);

  if (!ownerUserId) {
    throw new Error("polar-webhook-owner-not-found");
  }

  await getDbPool().query(
    `
      INSERT INTO mailbox_polar_subscriptions (
        owner_user_id,
        polar_subscription_id,
        polar_customer_id,
        external_customer_id,
        product_id,
        checkout_id,
        status,
        currency,
        amount,
        seats,
        recurring_interval,
        current_period_start,
        current_period_end,
        cancel_at_period_end,
        canceled_at,
        ended_at,
        last_event_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        polar_subscription_id = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(polar_subscription_id), polar_subscription_id),
        polar_customer_id = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(polar_customer_id), polar_customer_id),
        external_customer_id = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(external_customer_id), external_customer_id),
        product_id = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(product_id), product_id),
        checkout_id = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(checkout_id), checkout_id),
        status = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(status), status),
        currency = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(currency), currency),
        amount = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(amount), amount),
        seats = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(seats), seats),
        recurring_interval = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(recurring_interval), recurring_interval),
        current_period_start = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(current_period_start), current_period_start),
        current_period_end = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(current_period_end), current_period_end),
        cancel_at_period_end = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(cancel_at_period_end), cancel_at_period_end),
        canceled_at = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(canceled_at), canceled_at),
        ended_at = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(ended_at), ended_at),
        updated_at = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, NOW(), updated_at),
        last_event_at = GREATEST(COALESCE(last_event_at, VALUES(last_event_at)), VALUES(last_event_at))
    `,
    [
      ownerUserId,
      data.id,
      data.customerId,
      data.customer?.externalId ?? externalCustomerId(ownerUserId),
      data.productId,
      data.checkoutId,
      data.status,
      data.currency.toLowerCase(),
      data.amount,
      data.seats ?? 1,
      data.recurringInterval,
      data.currentPeriodStart,
      data.currentPeriodEnd,
      data.cancelAtPeriodEnd ? 1 : 0,
      data.canceledAt,
      data.endedAt,
      eventTimestamp,
    ],
  );

  const polarEntitled = await hasActivePolarGrowthPlanByOwnerUserId(ownerUserId);
  const growthEntitled = polarEntitled || (await hasNonPolarGrowthEntitlement(ownerUserId));

  if (growthEntitled) {
    await restoreManagedMembersAfterBillingRecovery(ownerUserId);
    await syncMailcowMailboxQuotasForOwnerUserId(ownerUserId, "growth");
  } else {
    await suspendManagedMembersForPlanDowngrade(ownerUserId);
  }

  await synchronizeExternalAccessAfterPolarBillingChange(ownerUserId);

  return ownerUserId;
}

async function upsertPolarOrder(
  data: Parameters<typeof resolveWebhookOwnerUserId>[0] & {
    billingReason: string;
    checkoutId: string | null;
    createdAt: Date;
    currency: string;
    customerId: string;
    id: string;
    invoiceNumber: string | null;
    paid: boolean;
    productId: string | null;
    refundedAmount: number;
    status: string;
    subscriptionId: string | null;
    subtotalAmount: number;
    taxAmount: number;
    totalAmount: number;
  },
  eventTimestamp: Date,
) {
  if (data.productId !== getPolarProductId()) {
    return null;
  }

  const ownerUserId = await resolveWebhookOwnerUserId(data);

  if (!ownerUserId) {
    throw new Error("polar-webhook-owner-not-found");
  }

  await getDbPool().query(
    `
      INSERT INTO mailbox_polar_orders (
        owner_user_id,
        polar_order_id,
        polar_subscription_id,
        polar_customer_id,
        product_id,
        checkout_id,
        status,
        paid,
        currency,
        subtotal_amount,
        tax_amount,
        total_amount,
        refunded_amount,
        billing_reason,
        invoice_number,
        ordered_at,
        paid_at,
        last_event_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        status = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(status), status),
        paid = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(paid), paid),
        subtotal_amount = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(subtotal_amount), subtotal_amount),
        tax_amount = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(tax_amount), tax_amount),
        total_amount = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(total_amount), total_amount),
        refunded_amount = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(refunded_amount), refunded_amount),
        invoice_number = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(invoice_number), invoice_number),
        paid_at = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, VALUES(paid_at), paid_at),
        updated_at = IF(last_event_at IS NULL OR VALUES(last_event_at) >= last_event_at, NOW(), updated_at),
        last_event_at = GREATEST(COALESCE(last_event_at, VALUES(last_event_at)), VALUES(last_event_at))
    `,
    [
      ownerUserId,
      data.id,
      data.subscriptionId,
      data.customerId,
      data.productId,
      data.checkoutId,
      data.status,
      data.paid ? 1 : 0,
      data.currency.toLowerCase(),
      data.subtotalAmount,
      data.taxAmount,
      data.totalAmount,
      data.refundedAmount,
      data.billingReason,
      data.invoiceNumber,
      data.createdAt,
      data.paid ? eventTimestamp : null,
      eventTimestamp,
    ],
  );

  const polarEntitled = await hasActivePolarGrowthPlanByOwnerUserId(ownerUserId);
  const growthEntitled = polarEntitled || (await hasNonPolarGrowthEntitlement(ownerUserId));

  if (growthEntitled) {
    await restoreManagedMembersAfterBillingRecovery(ownerUserId);
    await syncMailcowMailboxQuotasForOwnerUserId(ownerUserId, "growth");
  } else {
    await suspendManagedMembersForPlanDowngrade(ownerUserId);
  }

  await synchronizeExternalAccessAfterPolarBillingChange(ownerUserId);

  return ownerUserId;
}

export async function processPolarWebhook(input: {
  body: string;
  headers: Pick<Headers, "get">;
}) {
  const webhookHeaders = {
    "webhook-id": input.headers.get("webhook-id") ?? "",
    "webhook-signature": input.headers.get("webhook-signature") ?? "",
    "webhook-timestamp": input.headers.get("webhook-timestamp") ?? "",
  };
  const event = validateEvent(
    input.body,
    webhookHeaders,
    requirePolarEnv("POLAR_WEBHOOK_SECRET"),
  );
  const webhookId = webhookHeaders["webhook-id"];

  if (!webhookId) {
    throw new Error("polar-webhook-id-missing");
  }

  await ensureOfficialMailSchema();
  const [insertResult] = await getDbPool().query<ResultSetHeader>(
    `
      INSERT IGNORE INTO mailbox_polar_webhook_events (
        webhook_id,
        event_type,
        status
      ) VALUES (?, ?, 'processing')
    `,
    [webhookId, event.type],
  );

  if (insertResult.affectedRows === 0) {
    const [rows] = await getDbPool().query<(RowDataPacket & { status: string })[]>(
      `SELECT status FROM mailbox_polar_webhook_events WHERE webhook_id = ? LIMIT 1`,
      [webhookId],
    );

    if (rows[0]?.status === "processed") {
      return { duplicate: true, eventType: event.type };
    }

    await getDbPool().query(
      `
        UPDATE mailbox_polar_webhook_events
        SET status = 'processing', attempt_count = attempt_count + 1, last_error = NULL
        WHERE webhook_id = ?
      `,
      [webhookId],
    );
  }

  try {
    const eventTimestamp = event.timestamp;

    switch (event.type) {
      case "subscription.created":
      case "subscription.updated":
      case "subscription.active":
      case "subscription.canceled":
      case "subscription.uncanceled":
      case "subscription.revoked":
      case "subscription.past_due": {
        const ownerUserId = await upsertPolarSubscription(event.data, eventTimestamp);
        if (event.type === "subscription.active" && ownerUserId) {
          await recordMarketingLifecycleEvent({
            eventReference: `polar-subscription:${event.data.id}`,
            eventType: "payment_method_added",
            userId: ownerUserId,
          });
          await recordMarketingLifecycleEvent({
            eventReference: `polar-growth:${event.data.id}`,
            eventType: "growth_plan_started",
            userId: ownerUserId,
          });
        }
        break;
      }
      case "order.created":
      case "order.updated":
      case "order.paid":
      case "order.refunded": {
        const ownerUserId = await upsertPolarOrder(event.data, eventTimestamp);
        if (event.type === "order.paid" && ownerUserId && event.data.paid) {
          await recordMarketingLifecycleEvent({
            currency: event.data.currency,
            eventReference: `polar:${event.data.id}`,
            eventType: "purchase_completed",
            userId: ownerUserId,
            value: toAnalyticsCurrencyValue(
              event.data.totalAmount,
              event.data.currency,
            ),
          });
        }
        break;
      }
      default:
        break;
    }

    await getDbPool().query(
      `
        UPDATE mailbox_polar_webhook_events
        SET status = 'processed', processed_at = NOW(), last_error = NULL
        WHERE webhook_id = ?
      `,
      [webhookId],
    );

    return { duplicate: false, eventType: event.type };
  } catch (error) {
    const message = error instanceof Error ? error.message : "polar-webhook-processing-failed";
    await getDbPool().query(
      `
        UPDATE mailbox_polar_webhook_events
        SET status = 'failed', last_error = ?
        WHERE webhook_id = ?
      `,
      [message.slice(0, 1000), webhookId],
    );
    throw error;
  }
}