import "server-only";

import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import {
  decryptMailboxPassword,
  encryptMailboxPassword,
} from "@/lib/mailbox-remote";
import {
  normalizeDomainSetupPromotionCode,
  redeemDomainSetupPromotion,
  releaseDomainSetupPromotion,
  reserveDomainSetupPromotion,
} from "@/lib/domain-setup-promotions";
import { requestDomainSetupAssistanceCharge } from "@/lib/toss-pay";
import { DOMAIN_SETUP_ASSISTANCE_CHARGE } from "@/lib/pricing";
import { sendOfficialMailNotificationEmail } from "@/lib/official-mail";

export const DOMAIN_SETUP_ASSISTANCE_FEE = DOMAIN_SETUP_ASSISTANCE_CHARGE;
export const DOMAIN_SETUP_ASSISTANCE_CONSENT_VERSION = "2026-08-06-vat";
export const DOMAIN_SETUP_ASSISTANCE_PAYMENT_CONSENT_VERSION = "2026-08-06-vat";
const CREDENTIAL_RETENTION_DAYS = 30;
const REQUEST_CONTACT_RETENTION_DAYS = 90;
const EMAIL_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/i;
const REQUEST_STATUSES = [
  "pending",
  "in_progress",
  "completed",
  "cancelled",
] as const;

export type DomainSetupAssistanceStatus = (typeof REQUEST_STATUSES)[number];
export type DomainSetupAssistancePaymentStatus =
  | "cancelled"
  | "failed"
  | "not_applicable"
  | "paid"
  | "processing"
  | "ready";

export type DomainSetupAssistanceSummary = {
  chargedAt: string | null;
  discountAmount: number;
  feeAmount: number;
  id: number;
  originalFeeAmount: number;
  paymentStatus: DomainSetupAssistancePaymentStatus;
  promotionCode: string | null;
  provider: string;
  requestedAt: string;
  status: DomainSetupAssistanceStatus;
  statusMessage: string | null;
};

export type KavenixDomainSetupAssistanceRow = DomainSetupAssistanceSummary & {
  adminNote: string | null;
  completedAt: string | null;
  credentialsAvailable: boolean;
  domain: string;
  notificationEmail: string | null;
  paymentErrorMessage: string | null;
  requesterEmail: string;
  requesterNote: string | null;
  startedAt: string | null;
};

type OwnerDomainRow = RowDataPacket & {
  domain: string | null;
  domain_id: number | null;
  user_id: number;
};

type AssistanceRow = RowDataPacket & {
  account_identifier_ciphertext: string | null;
  account_password_ciphertext: string | null;
  admin_note: string | null;
  completed_at: Date | null;
  charged_at: Date | null;
  credentials_available: number;
  domain: string;
  discount_amount: number;
  fee_amount: number;
  id: number;
  notification_email: string | null;
  payment_error_message: string | null;
  payment_status: DomainSetupAssistancePaymentStatus;
  original_fee_amount: number;
  promotion_code: string | null;
  promotion_code_id: number | null;
  provider: string;
  requested_at: Date;
  requester_email: string;
  requester_note: string | null;
  started_at: Date | null;
  status: DomainSetupAssistanceStatus;
  updated_at: Date;
  user_id: number;
};

type PreparedAssistanceRow = AssistanceRow & {
  transitioned: boolean;
};

function normalizeEmail(value: string) {
  return value.trim().toLowerCase();
}

function normalizeText(value: string, maxLength: number) {
  return value.replace(/\r\n/g, "\n").trim().slice(0, maxLength);
}

function normalizeStatus(value: string): DomainSetupAssistanceStatus {
  if (REQUEST_STATUSES.includes(value as DomainSetupAssistanceStatus)) {
    return value as DomainSetupAssistanceStatus;
  }

  throw new Error("domain-assistance-invalid-status");
}

function toIsoString(value: Date | null) {
  return value ? value.toISOString() : null;
}

async function purgeExpiredCredentials(connection?: PoolConnection) {
  const executor = connection ?? getDbPool();

  await executor.query(`
    UPDATE domain_setup_assistance_requests
    SET
      account_identifier_ciphertext = NULL,
      account_password_ciphertext = NULL,
      credentials_purged_at = COALESCE(credentials_purged_at, NOW()),
      updated_at = NOW()
    WHERE credentials_purged_at IS NULL
      AND credentials_expire_at <= NOW()
  `);

  await executor.query(
    `
      UPDATE domain_setup_assistance_requests
      SET
        notification_email = NULL,
        requester_note = NULL,
        updated_at = NOW()
      WHERE status IN ('completed', 'cancelled')
        AND COALESCE(completed_at, cancelled_at) <= DATE_SUB(NOW(), INTERVAL ? DAY)
        AND (notification_email IS NOT NULL OR requester_note IS NOT NULL)
    `,
    [REQUEST_CONTACT_RETENTION_DAYS],
  );
}

async function getOwnerDomainByEmail(ownerEmail: string) {
  const [rows] = await getDbPool().query<OwnerDomainRow[]>(
    `
      SELECT
        u.id AS user_id,
        d.id AS domain_id,
        d.domain
      FROM users u
      LEFT JOIN domains d ON d.user_id = u.id
      WHERE u.email = ?
      ORDER BY
        CASE WHEN d.domain = SUBSTRING_INDEX(u.email, '@', -1) THEN 0 ELSE 1 END,
        d.id DESC
      LIMIT 1
    `,
    [normalizeEmail(ownerEmail)],
  );
  const owner = rows[0];
  const domain = owner?.domain?.trim();

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

  if (!owner.domain_id || !domain) {
    throw new Error("domain-assistance-domain-required");
  }

  return { ...owner, domain };
}

function mapSummary(row: AssistanceRow): DomainSetupAssistanceSummary {
  return {
    chargedAt: toIsoString(row.charged_at),
    discountAmount: Number(row.discount_amount),
    feeAmount: Number(row.fee_amount),
    id: Number(row.id),
    originalFeeAmount: Number(row.original_fee_amount),
    paymentStatus: row.payment_status,
    promotionCode: row.promotion_code,
    provider: row.provider,
    requestedAt: row.requested_at.toISOString(),
    status: row.status,
    statusMessage: row.status === "cancelled" ? row.admin_note : null,
  };
}

function mapAdminRow(row: AssistanceRow): KavenixDomainSetupAssistanceRow {
  return {
    ...mapSummary(row),
    adminNote: row.admin_note,
    completedAt: toIsoString(row.completed_at),
    credentialsAvailable: row.credentials_available === 1,
    domain: row.domain,
    notificationEmail: row.notification_email,
    paymentErrorMessage: row.payment_error_message,
    requesterEmail: row.requester_email,
    requesterNote: row.requester_note,
    startedAt: toIsoString(row.started_at),
  };
}

export async function getDomainSetupAssistanceByOwnerEmail(ownerEmail: string) {
  await ensureOfficialMailSchema();
  await purgeExpiredCredentials();
  const owner = await getOwnerDomainByEmail(ownerEmail);
  const [rows] = await getDbPool().query<AssistanceRow[]>(
    `
      SELECT
        r.*,
        u.email AS requester_email,
        CASE
          WHEN r.account_identifier_ciphertext IS NOT NULL
            AND r.account_password_ciphertext IS NOT NULL
          THEN 1 ELSE 0
        END AS credentials_available
      FROM domain_setup_assistance_requests r
      INNER JOIN users u ON u.id = r.user_id
      WHERE r.user_id = ?
      ORDER BY
        CASE WHEN r.status IN ('pending', 'in_progress') THEN 0 ELSE 1 END,
        r.requested_at DESC,
        r.id DESC
      LIMIT 1
    `,
    [owner.user_id],
  );

  return rows[0] ? mapSummary(rows[0]) : null;
}

export async function createDomainSetupAssistanceRequest(
  ownerEmail: string,
  input: {
    accountIdentifier: string;
    accountPassword: string;
    consented: boolean;
    note?: string;
    notificationEmail: string;
    paymentConsented: boolean;
    promotionCode?: string;
    provider: string;
  },
) {
  await ensureOfficialMailSchema();
  const provider = normalizeText(input.provider, 80);
  const accountIdentifier = normalizeText(input.accountIdentifier, 191);
  const accountPassword = input.accountPassword.slice(0, 512);
  const note = normalizeText(input.note ?? "", 1000);
  const notificationEmail = normalizeEmail(input.notificationEmail);
  const promotionCode = normalizeDomainSetupPromotionCode(input.promotionCode);

  if (!input.consented) {
    throw new Error("domain-assistance-consent-required");
  }

  if (!input.paymentConsented) {
    throw new Error("domain-assistance-payment-consent-required");
  }

  if (!provider || !accountIdentifier || !accountPassword.trim()) {
    throw new Error("domain-assistance-required-fields");
  }

  if (notificationEmail.length > 320 || !EMAIL_PATTERN.test(notificationEmail)) {
    throw new Error("domain-assistance-invalid-notification-email");
  }

  const owner = await getOwnerDomainByEmail(ownerEmail);
  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    await purgeExpiredCredentials(connection);
    await connection.query("SELECT id FROM users WHERE id = ? FOR UPDATE", [owner.user_id]);
    const [activeRows] = await connection.query<(RowDataPacket & { id: number })[]>(
      `
        SELECT id
        FROM domain_setup_assistance_requests
        WHERE user_id = ?
          AND status IN ('pending', 'in_progress')
        ORDER BY id DESC
        LIMIT 1
        FOR UPDATE
      `,
      [owner.user_id],
    );

    if (activeRows[0]) {
      throw new Error("domain-assistance-already-requested");
    }

    const [billingProfileRows] = await connection.query<
      (RowDataPacket & {
        billing_key: string | null;
        id: number;
        pay_method: string | null;
        status: string;
      })[]
    >(
      `
        SELECT id, billing_key, pay_method, status
        FROM mailbox_toss_pay_billing_profiles
        WHERE owner_user_id = ?
        LIMIT 1
        FOR UPDATE
      `,
      [owner.user_id],
    );
    const billingProfile = billingProfileRows[0];

    if (
      !billingProfile ||
      billingProfile.status !== "active" ||
      !billingProfile.billing_key ||
      billingProfile.pay_method !== "CARD"
    ) {
      throw new Error("domain-assistance-billing-profile-required");
    }

    const [result] = await connection.query<ResultSetHeader>(
      `
        INSERT INTO domain_setup_assistance_requests (
          user_id,
          domain_id,
          domain,
          provider,
          account_identifier_ciphertext,
          account_password_ciphertext,
          notification_email,
          requester_note,
          original_fee_amount,
          discount_amount,
          fee_amount,
          promotion_code_id,
          promotion_code,
          billing_profile_id,
          payment_status,
          payment_consent_version,
          payment_consented_at,
          consent_version,
          consented_at,
          credentials_expire_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, NULL, NULL, ?, 'ready', ?, NOW(), ?, NOW(), DATE_ADD(NOW(), INTERVAL ? DAY))
      `,
      [
        owner.user_id,
        owner.domain_id,
        owner.domain,
        provider,
        encryptMailboxPassword(accountIdentifier),
        encryptMailboxPassword(accountPassword),
        notificationEmail,
        note || null,
        DOMAIN_SETUP_ASSISTANCE_FEE,
        DOMAIN_SETUP_ASSISTANCE_FEE,
        billingProfile.id,
        DOMAIN_SETUP_ASSISTANCE_PAYMENT_CONSENT_VERSION,
        DOMAIN_SETUP_ASSISTANCE_CONSENT_VERSION,
        CREDENTIAL_RETENTION_DAYS,
      ],
    );
    let finalAmount = DOMAIN_SETUP_ASSISTANCE_FEE;
    let discountAmount = 0;

    if (promotionCode) {
      const promotion = await reserveDomainSetupPromotion(connection, {
        assistanceRequestId: result.insertId,
        code: promotionCode,
        originalAmount: DOMAIN_SETUP_ASSISTANCE_FEE,
        userId: owner.user_id,
      });
      finalAmount = promotion.finalAmount;
      discountAmount = promotion.discountAmount;
      await connection.query(
        `
          UPDATE domain_setup_assistance_requests
          SET
            discount_amount = ?,
            fee_amount = ?,
            promotion_code_id = ?,
            promotion_code = ?,
            updated_at = NOW()
          WHERE id = ?
        `,
        [
          discountAmount,
          finalAmount,
          promotion.promotionCodeId,
          promotion.code,
          result.insertId,
        ],
      );
    }
    await connection.commit();

    return {
      discountAmount,
      feeAmount: finalAmount,
      id: result.insertId,
      originalFeeAmount: DOMAIN_SETUP_ASSISTANCE_FEE,
      chargedAt: null,
      paymentStatus: "ready",
      promotionCode: promotionCode || null,
      provider,
      requestedAt: new Date().toISOString(),
      status: "pending",
      statusMessage: null,
    } satisfies DomainSetupAssistanceSummary;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

export async function getKavenixDomainSetupAssistanceRequestCount() {
  await ensureOfficialMailSchema();
  await purgeExpiredCredentials();
  const [rows] = await getDbPool().query<(RowDataPacket & { total: number })[]>(
    `
      SELECT COUNT(*) AS total
      FROM domain_setup_assistance_requests
      WHERE status IN ('pending', 'in_progress')
    `,
  );

  return Number(rows[0]?.total ?? 0);
}

export async function getKavenixDomainSetupAssistanceRequests(input?: {
  domain?: string;
  query?: string;
}) {
  await ensureOfficialMailSchema();
  await purgeExpiredCredentials();
  const domain = normalizeText(input?.domain ?? "", 253).toLowerCase();
  const query = normalizeText(input?.query ?? "", 191);
  const filters: string[] = [];
  const values: unknown[] = [];

  if (domain) {
    filters.push("r.domain = ?");
    values.push(domain);
  }

  if (query) {
    filters.push("(r.domain LIKE ? OR u.email LIKE ? OR r.notification_email LIKE ? OR r.provider LIKE ?)");
    const like = `%${query}%`;
    values.push(like, like, like, like);
  }

  const [rows] = await getDbPool().query<AssistanceRow[]>(
    `
      SELECT
        r.*,
        u.email AS requester_email,
        CASE
          WHEN r.account_identifier_ciphertext IS NOT NULL
            AND r.account_password_ciphertext IS NOT NULL
          THEN 1 ELSE 0
        END AS credentials_available
      FROM domain_setup_assistance_requests r
      INNER JOIN users u ON u.id = r.user_id
      ${filters.length > 0 ? `WHERE ${filters.join(" AND ")}` : ""}
      ORDER BY
        CASE r.status
          WHEN 'pending' THEN 0
          WHEN 'in_progress' THEN 1
          WHEN 'completed' THEN 2
          ELSE 3
        END,
        r.requested_at DESC,
        r.id DESC
      LIMIT 500
    `,
    values,
  );

  return rows.map(mapAdminRow);
}

export async function getKavenixDomainSetupAssistanceCredentials(requestId: number) {
  await ensureOfficialMailSchema();
  await purgeExpiredCredentials();
  const [rows] = await getDbPool().query<AssistanceRow[]>(
    `
      SELECT
        r.*,
        u.email AS requester_email,
        CASE
          WHEN r.account_identifier_ciphertext IS NOT NULL
            AND r.account_password_ciphertext IS NOT NULL
          THEN 1 ELSE 0
        END AS credentials_available
      FROM domain_setup_assistance_requests r
      INNER JOIN users u ON u.id = r.user_id
      WHERE r.id = ?
      LIMIT 1
    `,
    [requestId],
  );
  const request = rows[0];

  if (!request) {
    throw new Error("domain-assistance-not-found");
  }

  if (!request.account_identifier_ciphertext || !request.account_password_ciphertext) {
    throw new Error("domain-assistance-credentials-purged");
  }

  return {
    accountIdentifier: decryptMailboxPassword(request.account_identifier_ciphertext),
    accountPassword: decryptMailboxPassword(request.account_password_ciphertext),
    domain: request.domain,
    provider: request.provider,
  };
}

function getAssistanceStatusNotificationCopy(
  status: DomainSetupAssistanceStatus,
) {
  if (status === "in_progress") {
    return {
      label: "설정 진행 중",
      subject: "도메인 연결 설정을 시작했습니다",
      description: "담당자가 도메인 구매처와 DNS 설정을 확인하고 있습니다.",
    };
  }

  if (status === "completed") {
    return {
      label: "설정 완료",
      subject: "도메인 연결 설정이 완료되었습니다",
      description: "도메인 연결 설정과 최종 확인을 완료했습니다.",
    };
  }

  return {
    label: "신청 취소",
    subject: "도메인 연결 설정 대행 신청이 취소되었습니다",
    description: "도메인 연결 설정 대행 신청이 취소되어 보관 중이던 관리 계정정보를 폐기했습니다.",
  };
}

export async function sendKavenixDomainSetupAssistanceStatusNotification(
  requestId: number,
  status: DomainSetupAssistanceStatus,
) {
  if (!Number.isInteger(requestId) || requestId <= 0 || status === "pending") {
    return false;
  }

  const [rows] = await getDbPool().query<AssistanceRow[]>(
    `
      SELECT
        r.*,
        u.email AS requester_email,
        0 AS credentials_available
      FROM domain_setup_assistance_requests r
      INNER JOIN users u ON u.id = r.user_id
      WHERE r.id = ? AND r.status = ?
      LIMIT 1
    `,
    [requestId, status],
  );
  const request = rows[0];

  if (!request?.notification_email) {
    return false;
  }

  const copy = getAssistanceStatusNotificationCopy(status);
  const statusMessage = request.admin_note?.replace(/\*\*/g, "").trim() || null;
  const serviceUrl = new URL(
    "/mail?panel=setup&section=domain",
    `${(process.env.NEXT_PUBLIC_MAIL_APP_URL ?? "https://mail.officialsite.kr").replace(/\/$/, "")}/`,
  ).toString();
  const bodyText = [
    copy.description,
    "",
    `도메인: ${request.domain}`,
    `처리 상태: ${copy.label}`,
    statusMessage ? "" : null,
    statusMessage,
    "",
    `진행 상태 확인: ${serviceUrl}`,
    "",
    "추가 문의는 이 메일에 답장해주세요.",
    "",
    "오피셜메일",
  ]
    .filter((line): line is string => line !== null)
    .join("\n");

  await sendOfficialMailNotificationEmail({
    bodyText,
    recipientEmail: request.notification_email,
    subject: `[오피셜메일] ${copy.subject}`,
  });
  return true;
}

export async function updateKavenixDomainSetupAssistanceRequest(
  requestId: number,
  input: { adminNote?: string; status: string },
) {
  await ensureOfficialMailSchema();
  const status = normalizeStatus(input.status);
  const adminNote = normalizeText(input.adminNote ?? "", 1000);

  if (status === "cancelled" && !adminNote) {
    throw new Error("domain-assistance-cancellation-reason-required");
  }

  if (status === "completed") {
    const preparedRequest: PreparedAssistanceRow = await (async () => {
      const connection = await getDbPool().getConnection();

      try {
        await connection.beginTransaction();
        const [rows] = await connection.query<AssistanceRow[]>(
          `
            SELECT
              r.*,
              u.email AS requester_email,
              CASE
                WHEN r.account_identifier_ciphertext IS NOT NULL
                  AND r.account_password_ciphertext IS NOT NULL
                THEN 1 ELSE 0
              END AS credentials_available
            FROM domain_setup_assistance_requests r
            INNER JOIN users u ON u.id = r.user_id
            WHERE r.id = ?
            LIMIT 1
            FOR UPDATE
          `,
          [requestId],
        );
        const request = rows[0];

        if (!request) {
          throw new Error("domain-assistance-not-found");
        }

        if (
          request.status === "completed" &&
          ['paid', 'not_applicable'].includes(request.payment_status)
        ) {
          await connection.commit();
          return { ...request, transitioned: false };
        }

        if (!['pending', 'in_progress'].includes(request.status)) {
          throw new Error("domain-assistance-invalid-transition");
        }

        if (
          request.payment_status === "processing" &&
          request.updated_at.getTime() > Date.now() - 15 * 60 * 1000
        ) {
          throw new Error("domain-assistance-payment-processing");
        }

        if (Number(request.fee_amount) === 0) {
          await connection.query(
            `
              UPDATE domain_setup_assistance_requests
              SET
                status = 'completed',
                admin_note = ?,
                started_at = COALESCE(started_at, NOW()),
                completed_at = NOW(),
                payment_status = 'not_applicable',
                payment_error_message = NULL,
                account_identifier_ciphertext = NULL,
                account_password_ciphertext = NULL,
                credentials_purged_at = NOW(),
                updated_at = NOW()
              WHERE id = ?
            `,
            [adminNote || null, requestId],
          );
          await redeemDomainSetupPromotion(connection, requestId);
          await connection.commit();
          return {
            ...request,
            admin_note: adminNote || null,
            completed_at: new Date(),
            payment_error_message: null,
            payment_status: "not_applicable" as const,
            status: "completed" as const,
            transitioned: true,
          };
        }

        await connection.query(
          `
            UPDATE domain_setup_assistance_requests
            SET
              status = 'in_progress',
              admin_note = ?,
              started_at = COALESCE(started_at, NOW()),
              payment_status = 'processing',
              payment_error_message = NULL,
              updated_at = NOW()
            WHERE id = ?
          `,
          [adminNote || null, requestId],
        );
        await connection.commit();
        return {
          ...request,
          admin_note: adminNote || null,
          payment_error_message: null,
          payment_status: "processing" as const,
          status: "in_progress" as const,
          transitioned: true,
        };
      } catch (error) {
        await connection.rollback();
        throw error;
      } finally {
        connection.release();
      }
    })();

    if (
      preparedRequest.status === "completed" &&
      ['paid', 'not_applicable'].includes(preparedRequest.payment_status)
    ) {
      return {
        chargedAt: toIsoString(preparedRequest.charged_at),
        id: requestId,
        paymentErrorMessage: null,
        paymentStatus: preparedRequest.payment_status,
        status: "completed" as const,
        transitioned: preparedRequest.transitioned,
      };
    }

    let chargeResult: Awaited<ReturnType<typeof requestDomainSetupAssistanceCharge>>;

    try {
      chargeResult = await requestDomainSetupAssistanceCharge({
        amount: Number(preparedRequest.fee_amount),
        email: preparedRequest.requester_email,
        requestId,
      });
    } catch (error) {
      const errorCode = error instanceof Error ? error.message : "";
      const detail =
        errorCode === "billing-card-required"
          ? "등록된 결제카드가 없거나 사용할 수 없습니다. 도메인 대표 관리자에게 카드 등록을 요청해주세요."
          : errorCode || "자동결제 승인 요청에 실패했습니다.";
      await getDbPool().query(
        `
          UPDATE domain_setup_assistance_requests
          SET payment_status = 'failed', payment_error_message = ?, updated_at = NOW()
          WHERE id = ? AND payment_status = 'processing'
        `,
        [detail.slice(0, 1000), requestId],
      );
      throw new Error(`domain-assistance-payment-failed:${detail}`);
    }

    if (chargeResult.status !== "success") {
      const detail = chargeResult.errorMessage || "자동결제 승인 요청에 실패했습니다.";
      await getDbPool().query(
        `
          UPDATE domain_setup_assistance_requests
          SET
            billing_charge_id = ?,
            payment_status = 'failed',
            payment_error_message = ?,
            updated_at = NOW()
          WHERE id = ? AND payment_status = 'processing'
        `,
        [chargeResult.chargeId, detail.slice(0, 1000), requestId],
      );
      throw new Error(`domain-assistance-payment-failed:${detail}`);
    }

    const chargedAt = chargeResult.approvedAt ?? new Date().toISOString();
    const connection = await getDbPool().getConnection();

    try {
      await connection.beginTransaction();
      const [rows] = await connection.query<AssistanceRow[]>(
        `SELECT * FROM domain_setup_assistance_requests WHERE id = ? LIMIT 1 FOR UPDATE`,
        [requestId],
      );
      const current = rows[0];

      if (!current) {
        throw new Error("domain-assistance-not-found");
      }

      if (current.status !== "completed") {
        if (current.status !== "in_progress" || current.payment_status !== "processing") {
          throw new Error("domain-assistance-invalid-transition");
        }

        await connection.query(
          `
            UPDATE domain_setup_assistance_requests
            SET
              status = 'completed',
              billing_charge_id = ?,
              payment_status = 'paid',
              payment_error_message = NULL,
              charged_at = ?,
              completed_at = NOW(),
              account_identifier_ciphertext = NULL,
              account_password_ciphertext = NULL,
              credentials_purged_at = NOW(),
              updated_at = NOW()
            WHERE id = ?
          `,
          [chargeResult.chargeId, new Date(chargedAt), requestId],
        );
        await redeemDomainSetupPromotion(connection, requestId);
      }

      await connection.commit();
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }

    return {
      chargedAt,
      id: requestId,
      paymentErrorMessage: null,
      paymentStatus: "paid" as const,
      status: "completed" as const,
      transitioned: true,
    };
  }

  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    const [rows] = await connection.query<AssistanceRow[]>(
      `
        SELECT
          r.*,
          u.email AS requester_email,
          CASE
            WHEN r.account_identifier_ciphertext IS NOT NULL
              AND r.account_password_ciphertext IS NOT NULL
            THEN 1 ELSE 0
          END AS credentials_available
        FROM domain_setup_assistance_requests r
        INNER JOIN users u ON u.id = r.user_id
        WHERE r.id = ?
        LIMIT 1
        FOR UPDATE
      `,
      [requestId],
    );

    if (!rows[0]) {
      throw new Error("domain-assistance-not-found");
    }

    const currentStatus = rows[0].status;
    const allowedStatuses =
      currentStatus === "pending"
        ? ["in_progress", "cancelled"]
        : currentStatus === "in_progress"
          ? ["cancelled"]
          : [];

    if (!allowedStatuses.includes(status)) {
      throw new Error("domain-assistance-invalid-transition");
    }

    if (rows[0].payment_status === "processing") {
      throw new Error("domain-assistance-payment-processing");
    }

    const shouldPurge = status === "cancelled";

    if (shouldPurge) {
      await releaseDomainSetupPromotion(connection, requestId);
    }

    await connection.query(
      `
        UPDATE domain_setup_assistance_requests
        SET
          status = ?,
          admin_note = ?,
          started_at = CASE
            WHEN ? = 'in_progress' THEN COALESCE(started_at, NOW())
            ELSE started_at
          END,
          completed_at = CASE WHEN ? = 'completed' THEN NOW() ELSE completed_at END,
          cancelled_at = CASE WHEN ? = 'cancelled' THEN NOW() ELSE cancelled_at END,
          payment_status = CASE WHEN ? = 'cancelled' THEN 'cancelled' ELSE payment_status END,
          account_identifier_ciphertext = CASE WHEN ? = 1 THEN NULL ELSE account_identifier_ciphertext END,
          account_password_ciphertext = CASE WHEN ? = 1 THEN NULL ELSE account_password_ciphertext END,
          credentials_purged_at = CASE WHEN ? = 1 THEN NOW() ELSE credentials_purged_at END,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        status,
        adminNote || null,
        status,
        status,
        status,
        status,
        shouldPurge ? 1 : 0,
        shouldPurge ? 1 : 0,
        shouldPurge ? 1 : 0,
        requestId,
      ],
    );
    await connection.commit();

    return {
      chargedAt: toIsoString(rows[0].charged_at),
      id: requestId,
      paymentErrorMessage: rows[0].payment_error_message,
      paymentStatus:
        status === "cancelled" ? ("cancelled" as const) : rows[0].payment_status,
      status,
      transitioned: true,
    };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}