import "server-only";

import { createHash, randomBytes, scryptSync, timingSafeEqual } from "node:crypto";
import type { RowDataPacket } from "mysql2/promise";

import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { hasActiveGrowthPlanByOwnerEmail } from "@/lib/mail-billing-access";
import { resolveMailboxCredentialPolicy } from "@/lib/mail-external-access-policy";
import {
  assertMailcowPasswordPolicy,
  deleteMailcowNativeAppPassword,
  deleteMailcowExternalImapAppPassword,
  deleteMailcowPop3AppPassword,
  ensureMailcowExternalImapAppPassword,
  ensureMailcowNativeAppPassword,
  ensureMailcowMailbox,
  hasMailcowPop3AppPassword,
  replaceMailcowPop3AppPassword,
  replaceMailcowExternalImapAppPassword,
  setMailcowMailboxPop3Access,
} from "@/lib/mailcow";
import {
  decryptMailboxPassword,
  encryptMailboxPassword,
} from "@/lib/mailbox-remote";

type MailboxExternalAccessRow = RowDataPacket & {
  display_name: string;
  domain: string;
  email: string;
  external_access_enabled: number | null;
  external_app_password_ciphertext: string | null;
  id: number;
  legacy_app_compat_enabled: number;
  local_part: string;
  native_app_password_ciphertext: string | null;
  owner_email: string;
  password_hash: string;
  password_ciphertext: string | null;
  pop3_access_enabled: number;
  status: "active" | "disabled" | "pending_dns";
};

export type MailboxExternalAccessStatus = {
  enabled: boolean;
  legacyAppCompatibilityEnabled: boolean;
  mailbox: string;
  planAvailable: boolean;
  pop3Enabled: boolean;
  ready: boolean;
};

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

function matchesAccountPassword(password: string, storedHash: string) {
  const [salt, digest] = storedHash.split(":");
  if (!salt || !digest) return false;
  const expected = Buffer.from(digest, "hex");
  const actual = scryptSync(password, salt, 64);
  return expected.length === actual.length && timingSafeEqual(expected, actual);
}

export function createInternalMailboxCredentialPassword() {
  return `OmInternal!${randomBytes(30).toString("base64url")}9aA`;
}

async function getMailboxExternalAccessByEmail(email: string) {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<MailboxExternalAccessRow[]>(
    `
      SELECT
        m.id,
        m.email,
        m.local_part,
        m.native_app_password_ciphertext,
        m.legacy_app_compat_enabled,
        m.password_ciphertext,
        m.external_access_enabled,
        m.external_app_password_ciphertext,
        m.pop3_access_enabled,
        m.status,
        d.domain,
        u.display_name,
        u.password_hash,
        COALESCE(owner.email, u.email) AS owner_email
      FROM mailboxes m
      INNER JOIN users u ON u.id = m.user_id
      INNER JOIN domains d ON d.id = m.domain_id
      LEFT JOIN managed_team_mailboxes team
        ON LOWER(team.email) = LOWER(m.email)
       AND team.status = 'active'
      LEFT JOIN users owner ON owner.id = team.owner_user_id
      WHERE LOWER(m.email) = ?
        AND u.account_deleted_at IS NULL
      ORDER BY m.id DESC
      LIMIT 1
    `,
    [normalizeEmail(email)],
  );

  return rows[0] ?? null;
}

async function withNativeMailboxLock<T>(email: string, callback: () => Promise<T>) {
  const lockName = `om-native:${createHash("sha256")
    .update(normalizeEmail(email))
    .digest("hex")
    .slice(0, 50)}`;
  const connection = await getDbPool().getConnection();
  try {
    const [rows] = await connection.query<(RowDataPacket & { acquired: number })[]>(
      "SELECT GET_LOCK(?, 10) AS acquired",
      [lockName],
    );
    if (rows[0]?.acquired !== 1) {
      throw new Error("native-app-lock-timeout");
    }
    try {
      return await callback();
    } finally {
      await connection.query("SELECT RELEASE_LOCK(?)", [lockName]);
    }
  } finally {
    connection.release();
  }
}

export async function getOrCreateNativeMailboxAppPassword(email: string) {
  return withNativeMailboxLock(email, async () => {
    const mailbox = await getMailboxExternalAccessByEmail(email);
    if (!mailbox || mailbox.status !== "active") {
      throw new Error("mailbox-not-ready");
    }
    const existing = mailbox.native_app_password_ciphertext;
    const password = existing
      ? decryptMailboxPassword(existing)
      : createInternalMailboxCredentialPassword();
    await ensureMailcowNativeAppPassword(mailbox.email, password);
    if (!existing) {
      await getDbPool().query(
        "UPDATE mailboxes SET native_app_password_ciphertext = ?, updated_at = NOW() WHERE id = ?",
        [encryptMailboxPassword(password), mailbox.id],
      );
    }
    return password;
  });
}

export async function revokeNativeMailboxAppPassword(email: string) {
  return withNativeMailboxLock(email, async () => {
    const mailbox = await getMailboxExternalAccessByEmail(email);
    if (!mailbox) return;
    await deleteMailcowNativeAppPassword(mailbox.email);
    await getDbPool().query(
      "UPDATE mailboxes SET native_app_password_ciphertext = NULL, updated_at = NOW() WHERE id = ?",
      [mailbox.id],
    );
  });
}

export async function enableLegacyNativeAppCompatibility(email: string, accountPassword: string) {
  return withNativeMailboxLock(email, async () => {
    const mailbox = await getMailboxExternalAccessByEmail(email);
    if (!mailbox || mailbox.status !== "active") {
      throw new Error("mailbox-not-ready");
    }
    const previousPassword = mailbox.password_ciphertext
      ? decryptMailboxPassword(mailbox.password_ciphertext)
      : null;
    if (previousPassword !== accountPassword) {
      await assertMailcowPasswordPolicy(accountPassword);
      try {
        await ensureMailcowMailbox({
          displayName: mailbox.display_name,
          domain: mailbox.domain,
          localPart: mailbox.local_part,
          mailboxPassword: accountPassword,
        });
        await getDbPool().query(
          `UPDATE mailboxes
           SET password_ciphertext = ?, password_updated_at = NOW(),
               legacy_app_compat_enabled = 1, last_sync_error = NULL, updated_at = NOW()
           WHERE id = ?`,
          [encryptMailboxPassword(accountPassword), mailbox.id],
        );
      } catch (error) {
        if (previousPassword) {
          await ensureMailcowMailbox({
            displayName: mailbox.display_name,
            domain: mailbox.domain,
            localPart: mailbox.local_part,
            mailboxPassword: previousPassword,
          }).catch(() => undefined);
        }
        throw error;
      }
    } else if (mailbox.legacy_app_compat_enabled !== 1) {
      await getDbPool().query(
        "UPDATE mailboxes SET legacy_app_compat_enabled = 1, updated_at = NOW() WHERE id = ?",
        [mailbox.id],
      );
    }
  });
}

export async function disableLegacyNativeAppCompatibility(email: string) {
  return withNativeMailboxLock(email, async () => {
    const mailbox = await getMailboxExternalAccessByEmail(email);
    if (!mailbox || mailbox.legacy_app_compat_enabled !== 1) return;
    const hasSeparateExternalPassword = Boolean(mailbox.external_app_password_ciphertext);
    await replaceMailboxRemotePassword(
      mailbox,
      createInternalMailboxCredentialPassword(),
      hasSeparateExternalPassword && mailbox.external_access_enabled === 1,
      hasSeparateExternalPassword ? mailbox.external_app_password_ciphertext : null,
    );
  });
}

export async function countLegacyNativeAppMailboxes() {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<(RowDataPacket & { total: number })[]>(
    "SELECT COUNT(*) AS total FROM mailboxes WHERE legacy_app_compat_enabled = 1 AND status = 'active'",
  );
  return Number(rows[0]?.total ?? 0);
}

export async function revokeAllLegacyNativeAppCredentials() {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<(RowDataPacket & { email: string })[]>(
    "SELECT email FROM mailboxes WHERE legacy_app_compat_enabled = 1 AND status = 'active' ORDER BY id",
  );
  let revoked = 0;
  let nextIndex = 0;
  await Promise.all(Array.from({ length: Math.min(4, rows.length) }, async () => {
    while (nextIndex < rows.length) {
      const row = rows[nextIndex++];
      try {
        await disableLegacyNativeAppCompatibility(row.email);
        revoked += 1;
      } catch (error) {
        console.error("Legacy native app credential revocation failed", {
          email: row.email,
          message: error instanceof Error ? error.message : "unknown",
        });
      }
    }
  }));
  return { revoked, remaining: await countLegacyNativeAppMailboxes() };
}

async function replaceMailboxRemotePassword(
  mailbox: MailboxExternalAccessRow,
  nextPassword: string,
  externalAccessEnabled: boolean,
  externalAppPasswordCiphertext: string | null = mailbox.external_app_password_ciphertext,
) {
  const previousPassword = mailbox.password_ciphertext
    ? decryptMailboxPassword(mailbox.password_ciphertext)
    : null;

  try {
    await ensureMailcowMailbox({
      active: mailbox.status === "active",
      displayName: mailbox.display_name,
      domain: mailbox.domain,
      localPart: mailbox.local_part,
      mailboxPassword: nextPassword,
    });
    // Direct mailbox credentials never receive POP3 access. POP3 uses a
    // protocol-scoped Mailcow app password so it cannot authenticate IMAP/SMTP.
    await setMailcowMailboxPop3Access(mailbox.email, false);

    await getDbPool().query(
      `
        UPDATE mailboxes
        SET
          password_ciphertext = ?,
          password_updated_at = NOW(),
          external_access_enabled = ?,
          external_app_password_ciphertext = ?,
          legacy_app_compat_enabled = 0,
          external_access_checked_at = NOW(),
          last_sync_error = NULL,
          updated_at = NOW()
        WHERE id = ?
      `,
      [
        encryptMailboxPassword(nextPassword),
        externalAccessEnabled ? 1 : 0,
        externalAppPasswordCiphertext,
        mailbox.id,
      ],
    );
  } catch (error) {
    if (previousPassword) {
      await ensureMailcowMailbox({
        active: mailbox.status === "active",
        displayName: mailbox.display_name,
        domain: mailbox.domain,
        localPart: mailbox.local_part,
        mailboxPassword: previousPassword,
      }).catch(() => undefined);
      await setMailcowMailboxPop3Access(mailbox.email, false).catch(() => undefined);
    }

    throw error;
  }
}

async function synchronizeMailboxPop3AppAccess(mailbox: MailboxExternalAccessRow, planAvailable: boolean) {
  await setMailcowMailboxPop3Access(mailbox.email, false);

  if (!planAvailable) {
    if (mailbox.pop3_access_enabled === 1) {
      // Persist the revocation before touching Mailcow. If its API is down,
      // a later upgrade must not resurrect the old POP3 credential.
      await getDbPool().query(
        "UPDATE mailboxes SET pop3_access_enabled = 0, external_access_checked_at = NOW(), updated_at = NOW() WHERE id = ?",
        [mailbox.id],
      );
    }
    await deleteMailcowPop3AppPassword(mailbox.email);
    return false;
  }

  if (mailbox.pop3_access_enabled !== 1) {
    // Also remove a stale remote password after a failed downgrade cleanup.
    await deleteMailcowPop3AppPassword(mailbox.email);
    return false;
  }

  if (await hasMailcowPop3AppPassword(mailbox.email)) {
    return true;
  }

  await deleteMailcowPop3AppPassword(mailbox.email);
  await getDbPool().query(
    `
      UPDATE mailboxes
      SET pop3_access_enabled = 0, external_access_checked_at = NOW(), updated_at = NOW()
      WHERE id = ?
    `,
    [mailbox.id],
  );

  return false;
}

export async function synchronizeMailboxExternalAccessByEmail(
  email: string,
): Promise<MailboxExternalAccessStatus> {
  const mailbox = await getMailboxExternalAccessByEmail(email);

  if (!mailbox) {
    return {
      enabled: false,
      legacyAppCompatibilityEnabled: false,
      mailbox: normalizeEmail(email),
      planAvailable: false,
      pop3Enabled: false,
      ready: false,
    };
  }

  const planAvailable = await hasActiveGrowthPlanByOwnerEmail(mailbox.owner_email);
  const pop3Enabled = await synchronizeMailboxPop3AppAccess(mailbox, planAvailable);

  // A pre-v2 mailbox can have external access marked enabled without any
  // separate app password. In that state its primary Mailcow credential may
  // still be the account password even though the legacy flag is already off.
  const directPasswordMatchesAccount = Boolean(
    mailbox.password_ciphertext && matchesAccountPassword(
      decryptMailboxPassword(mailbox.password_ciphertext),
      mailbox.password_hash,
    ),
  );
  const externalPasswordMatchesAccount = Boolean(
    mailbox.external_app_password_ciphertext && matchesAccountPassword(
      decryptMailboxPassword(mailbox.external_app_password_ciphertext),
      mailbox.password_hash,
    ),
  );
  const credentialPolicy = resolveMailboxCredentialPolicy({
    directPasswordMatchesAccount,
    externalAccessEnabled: mailbox.external_access_enabled === 1,
    externalPasswordMatchesAccount,
    hasExternalPassword: Boolean(mailbox.external_app_password_ciphertext),
    legacyCompatibilityEnabled: mailbox.legacy_app_compat_enabled === 1,
    planAvailable,
  });
  if (credentialPolicy.disableBrokenExternal) {
    if (mailbox.legacy_app_compat_enabled === 1) {
      await disableLegacyNativeAppCompatibility(mailbox.email);
    }
    await disableMailboxExternalAccessByEmail(mailbox.email, { allowDisabled: true });
    return {
      enabled: false,
      legacyAppCompatibilityEnabled: false,
      mailbox: mailbox.email,
      planAvailable,
      pop3Enabled,
      ready: mailbox.status === "active",
    };
  }

  // The account password must never authenticate directly to IMAP/SMTP once
  // legacy compatibility is off, even if the external app password is valid.
  if (credentialPolicy.rotateDirectPassword) {
    await replaceMailboxRemotePassword(
      mailbox,
      createInternalMailboxCredentialPassword(),
      mailbox.external_access_enabled === 1 && Boolean(mailbox.external_app_password_ciphertext),
    );
  }

  if (credentialPolicy.revokeExternalForPlan) {
    // Mark disabled first, retaining the ciphertext only as a retry marker.
    // On a later upgrade the cleanup branch still deletes it instead of
    // restoring the previous external password.
    await getDbPool().query(
      `UPDATE mailboxes SET external_access_enabled = 0,
        external_access_checked_at = NOW(), updated_at = NOW() WHERE id = ?`,
      [mailbox.id],
    );
    await deleteMailcowExternalImapAppPassword(mailbox.email);
    await getDbPool().query(
      `UPDATE mailboxes SET external_app_password_ciphertext = NULL,
        external_access_checked_at = NOW(), updated_at = NOW()
       WHERE id = ?`,
      [mailbox.id],
    );
    mailbox.external_access_enabled = 0;
    mailbox.external_app_password_ciphertext = null;
  }

  if (credentialPolicy.externalAvailable && mailbox.external_app_password_ciphertext) {
    if (!mailbox.password_ciphertext) {
      await replaceMailboxRemotePassword(
        mailbox,
        createInternalMailboxCredentialPassword(),
        true,
      );
    }
    await ensureMailcowExternalImapAppPassword(
      mailbox.email,
      decryptMailboxPassword(mailbox.external_app_password_ciphertext),
      planAvailable,
    );
    await getDbPool().query(
      "UPDATE mailboxes SET external_access_checked_at = NOW() WHERE id = ?",
      [mailbox.id],
    );
    return {
      enabled: true,
      legacyAppCompatibilityEnabled: Boolean(mailbox.password_ciphertext) && mailbox.legacy_app_compat_enabled === 1,
      mailbox: mailbox.email,
      planAvailable,
      pop3Enabled,
      ready: mailbox.status === "active",
    };
  }

  // An installed pre-v2 app still authenticates to IMAP with the account
  // password. Do not silently rotate that credential while loading settings.
  if (mailbox.legacy_app_compat_enabled === 1) {
    return {
      enabled: mailbox.external_access_enabled === 1,
      legacyAppCompatibilityEnabled: true,
      mailbox: mailbox.email,
      planAvailable,
      pop3Enabled,
      ready: mailbox.status === "active",
    };
  }

  if (mailbox.external_access_enabled !== 1 && mailbox.external_app_password_ciphertext) {
    await deleteMailcowExternalImapAppPassword(mailbox.email);
    await getDbPool().query(
      "UPDATE mailboxes SET external_app_password_ciphertext = NULL WHERE id = ?",
      [mailbox.id],
    );
  }

  if (planAvailable) {
    if (mailbox.external_access_enabled === null || !mailbox.password_ciphertext) {
      await replaceMailboxRemotePassword(
        mailbox,
        createInternalMailboxCredentialPassword(),
        false,
      );

      return {
        enabled: false,
        legacyAppCompatibilityEnabled: false,
        mailbox: mailbox.email,
        planAvailable: true,
        pop3Enabled,
        ready: mailbox.status === "active",
      };
    }

    await getDbPool().query(
      `
        UPDATE mailboxes
        SET
          external_access_checked_at = NOW(),
          updated_at = NOW()
        WHERE id = ?
      `,
      [mailbox.id],
    );

    return {
      enabled: mailbox.external_access_enabled === 1,
      legacyAppCompatibilityEnabled: mailbox.legacy_app_compat_enabled === 1,
      mailbox: mailbox.email,
      planAvailable: true,
      pop3Enabled,
      ready: mailbox.status === "active",
    };
  }

  if (mailbox.external_access_enabled !== 0 || !mailbox.password_ciphertext) {
    await replaceMailboxRemotePassword(
      mailbox,
      createInternalMailboxCredentialPassword(),
      false,
      null,
    );
  } else {
    await getDbPool().query(
      "UPDATE mailboxes SET external_access_checked_at = NOW() WHERE id = ?",
      [mailbox.id],
    );
  }

  return {
    enabled: false,
    legacyAppCompatibilityEnabled: mailbox.password_ciphertext != null && mailbox.legacy_app_compat_enabled === 1 && mailbox.external_access_enabled === 0,
    mailbox: mailbox.email,
    planAvailable: false,
    pop3Enabled,
    ready: mailbox.status === "active",
  };
}

export async function setMailboxExternalAccessPasswordByEmail(
  email: string,
  password: string,
) {
  const normalizedPassword = password.trim();

  if (!normalizedPassword) {
    throw new Error("external-access-password-required");
  }

  const mailbox = await getMailboxExternalAccessByEmail(email);

  if (!mailbox || mailbox.status !== "active") {
    throw new Error("mailbox-not-ready");
  }

  const planAvailable = await hasActiveGrowthPlanByOwnerEmail(mailbox.owner_email);
  if (!planAvailable) throw new Error("growth-plan-required");
  if (matchesAccountPassword(normalizedPassword, mailbox.password_hash)) {
    throw new Error("external-password-must-differ");
  }
  await assertMailcowPasswordPolicy(normalizedPassword);
  const previousExternalPassword = mailbox.external_app_password_ciphertext
    ? decryptMailboxPassword(mailbox.external_app_password_ciphertext)
    : null;
  await replaceMailcowExternalImapAppPassword(
    mailbox.email,
    normalizedPassword,
    planAvailable,
  );
  try {
    if (mailbox.legacy_app_compat_enabled === 1) {
      await getDbPool().query(
        `UPDATE mailboxes
         SET external_access_enabled = 1, external_app_password_ciphertext = ?,
             external_access_checked_at = NOW(), last_sync_error = NULL, updated_at = NOW()
         WHERE id = ?`,
        [encryptMailboxPassword(normalizedPassword), mailbox.id],
      );
    } else {
      await replaceMailboxRemotePassword(
        mailbox,
        createInternalMailboxCredentialPassword(),
        true,
        encryptMailboxPassword(normalizedPassword),
      );
    }
  } catch (error) {
    if (previousExternalPassword) {
      await replaceMailcowExternalImapAppPassword(
        mailbox.email,
        previousExternalPassword,
        planAvailable,
      ).catch(() => undefined);
    } else {
      await deleteMailcowExternalImapAppPassword(mailbox.email).catch(() => undefined);
    }
    throw error;
  }

  return {
    enabled: true,
    mailbox: mailbox.email,
    pop3Enabled: Boolean(mailbox.pop3_access_enabled),
  };
}

export async function disableMailboxExternalAccessByEmail(
  email: string,
  options?: { allowDisabled?: boolean },
) {
  const mailbox = await getMailboxExternalAccessByEmail(email);

  if (!mailbox || (mailbox.status !== "active" &&
      !(options?.allowDisabled && mailbox.status === "disabled"))) {
    throw new Error("mailbox-not-ready");
  }

  const previousExternalPassword = mailbox.external_app_password_ciphertext
    ? decryptMailboxPassword(mailbox.external_app_password_ciphertext)
    : null;
  await deleteMailcowExternalImapAppPassword(mailbox.email);
  try {
    if (mailbox.legacy_app_compat_enabled === 1) {
      await getDbPool().query(
        `UPDATE mailboxes
         SET external_access_enabled = 0, external_app_password_ciphertext = NULL,
             external_access_checked_at = NOW(), updated_at = NOW()
         WHERE id = ?`,
        [mailbox.id],
      );
    } else {
      await replaceMailboxRemotePassword(
        mailbox,
        createInternalMailboxCredentialPassword(),
        false,
        null,
      );
    }
  } catch (error) {
    if (previousExternalPassword) {
      await replaceMailcowExternalImapAppPassword(
        mailbox.email,
        previousExternalPassword,
        await hasActiveGrowthPlanByOwnerEmail(mailbox.owner_email),
      ).catch(() => undefined);
    }
    throw error;
  }

  return {
    enabled: false,
    mailbox: mailbox.email,
    pop3Enabled: Boolean(mailbox.pop3_access_enabled),
  };
}

export async function setMailboxPop3PasswordByEmail(email: string, password: string) {
  const normalizedPassword = password.trim();

  if (!normalizedPassword) {
    throw new Error("external-access-password-required");
  }

  const mailbox = await getMailboxExternalAccessByEmail(email);

  if (!mailbox || mailbox.status !== "active") {
    throw new Error("mailbox-not-ready");
  }

  if (!await hasActiveGrowthPlanByOwnerEmail(mailbox.owner_email)) {
    throw new Error("growth-plan-required");
  }

  if (matchesAccountPassword(normalizedPassword, mailbox.password_hash)) {
    throw new Error("external-password-must-differ");
  }

  await assertMailcowPasswordPolicy(normalizedPassword);
  await setMailcowMailboxPop3Access(mailbox.email, false);

  try {
    await replaceMailcowPop3AppPassword(mailbox.email, normalizedPassword);
    await getDbPool().query(
      `
        UPDATE mailboxes
        SET pop3_access_enabled = 1, external_access_checked_at = NOW(), updated_at = NOW()
        WHERE id = ?
      `,
      [mailbox.id],
    );
  } catch (error) {
    await deleteMailcowPop3AppPassword(mailbox.email).catch(() => undefined);
    throw error;
  }

  return { enabled: true, mailbox: mailbox.email };
}

export async function disableMailboxPop3AccessByEmail(email: string) {
  const mailbox = await getMailboxExternalAccessByEmail(email);

  if (!mailbox || mailbox.status !== "active") {
    throw new Error("mailbox-not-ready");
  }

  await deleteMailcowPop3AppPassword(mailbox.email);
  await setMailcowMailboxPop3Access(mailbox.email, false);
  await getDbPool().query(
    `
      UPDATE mailboxes
      SET pop3_access_enabled = 0, external_access_checked_at = NOW(), updated_at = NOW()
      WHERE id = ?
    `,
    [mailbox.id],
  );

  return { enabled: false, mailbox: mailbox.email };
}

export async function synchronizeMailboxExternalAccessByOwnerEmail(
  ownerEmail: string,
) {
  await ensureOfficialMailSchema();
  const normalizedOwnerEmail = normalizeEmail(ownerEmail);
  const [rows] = await getDbPool().query<(RowDataPacket & { email: string; id: number })[]>(
    `
      SELECT DISTINCT m.id, m.email
      FROM mailboxes m
      INNER JOIN users u ON u.id = m.user_id
      INNER JOIN domains d ON d.id = m.domain_id
      LEFT JOIN managed_team_mailboxes team
        ON LOWER(team.email) = LOWER(m.email)
       AND team.status = 'active'
      LEFT JOIN users owner ON owner.id = team.owner_user_id
      WHERE LOWER(COALESCE(owner.email, u.email)) = ?
        AND m.status = 'active'
        AND u.account_deleted_at IS NULL
        AND d.mailcow_cleanup_at IS NULL
      ORDER BY m.id ASC
    `,
    [normalizedOwnerEmail],
  );
  const results: MailboxExternalAccessStatus[] = [];

  for (const row of rows) {
    results.push(await synchronizeMailboxExternalAccessByEmail(row.email));
  }

  return results;
}

export async function reconcileMailboxExternalAccessPolicies(options?: {
  limit?: number;
}) {
  await ensureOfficialMailSchema();
  const requestedLimit = Number(options?.limit ?? 100);
  const limit = Math.max(
    1,
    Math.min(500, Number.isFinite(requestedLimit) ? Math.floor(requestedLimit) : 100),
  );
  const [rows] = await getDbPool().query<(RowDataPacket & { email: string })[]>(
    `
      SELECT m.email
      FROM mailboxes m
      INNER JOIN users u ON u.id = m.user_id
      INNER JOIN domains d ON d.id = m.domain_id
      WHERE m.status = 'active'
        AND u.account_deleted_at IS NULL
        AND d.mailcow_cleanup_at IS NULL
      ORDER BY
        CASE WHEN m.external_access_enabled IS NULL THEN 0 ELSE 1 END,
        COALESCE(m.external_access_checked_at, '1970-01-01 00:00:00') ASC,
        m.id ASC
      LIMIT ${limit}
    `,
  );
  let disabledCount = 0;
  let enabledCount = 0;
  const failures: Array<{ email: string; error: string }> = [];

  for (const row of rows) {
    try {
      const status = await synchronizeMailboxExternalAccessByEmail(row.email);

      if (status.enabled) {
        enabledCount += 1;
      } else {
        disabledCount += 1;
      }
    } catch (error) {
      failures.push({
        email: row.email,
        error: error instanceof Error ? error.message.slice(0, 300) : "unknown-error",
      });
    }
  }

  return {
    checkedCount: rows.length,
    disabledCount,
    enabledCount,
    failures,
  };
}