import "server-only";

import { randomBytes } from "node:crypto";
import path from "node:path";
import mysql, { type PoolConnection, type RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { LARGE_ATTACHMENT_DOWNLOAD_LIMIT } from "@/lib/mail-attachment-policy";
import {
  deleteObjectFromObjectStorage,
  readObjectFromObjectStorage,
  uploadPrivateBufferToObjectStorage,
} from "@/lib/object-storage";
import {
  activateMailboxStorageObject,
  reserveMailboxStorageObject,
  scheduleMailboxStorageObjectDeletion,
} from "@/lib/mailbox-storage-registry";
import {
  getMailPlanQuotaTierForOwnerUserId,
  type MailPlanQuotaTier,
} from "@/lib/mail-plan-quota";

export type MailboxFileDrawerFilter = "all" | "file" | "image";
export type MailboxFileShareAccess = "owner" | "public";

export type MailboxFileDrawerItem = {
  createdAt: string;
  direction: "inbound" | "outbound" | "uploaded";
  downloadUrl: string;
  expiresAt: string | null;
  id: string;
  isImage: boolean;
  mimeType: string;
  name: string;
  previewUrl: string | null;
  shareAccess: MailboxFileShareAccess;
  shareEnabled: boolean;
  shareUrl: string | null;
  sizeBytes: number;
  source: "mail" | "upload";
  subject: string | null;
};

export type MailboxFileDrawerSnapshot = {
  generatedAt: string;
  hasMore: boolean;
  items: MailboxFileDrawerItem[];
  mailAttachmentBytes: number;
  mailAttachmentCount: number;
  manualFileBytes: number;
  manualFileCount: number;
  page: number;
  pageSize: number;
  quotaBytes: number | null;
  quotaIsUnlimited: boolean;
  quotaUsagePercent: number | null;
  quotaUsedBytes: number | null;
  totalCount: number;
};

type MailboxContextRow = RowDataPacket & {
  mailbox_email: string;
  mailbox_id: number;
  owner_email: string;
  owner_user_id: number;
  plan_owner_user_id: number;
};

type DrawerItemRow = RowDataPacket & {
  attachment_index: number | null;
  created_at: Date;
  direction: "inbound" | "outbound" | "uploaded";
  expires_at: Date | null;
  item_id: number | string;
  item_type: "large" | "received" | "upload";
  mailbox_message_id: number | null;
  mime_type: string;
  original_name: string;
  share_access: MailboxFileShareAccess;
  share_enabled: number;
  share_token: string | null;
  size_bytes: number;
  subject: string | null;
};

type DrawerCountRow = RowDataPacket & {
  item_count: number;
  published_count?: number;
  size_bytes: number;
};

type ManualFileRow = RowDataPacket & {
  id: number;
  file_token: string;
  mime_type: string;
  original_name: string;
  owner_user_id: number;
  share_access: MailboxFileShareAccess;
  share_enabled: number;
  share_token: string | null;
  size_bytes: number;
  storage_key: string;
};

type SharedFileRow = ManualFileRow & {
  owner_email: string;
};

type AttachmentStorageRow = RowDataPacket & {
  attachment_row_id: number;
  owner_user_id: number;
  storage_key: string;
};

type StorageDeleteJobRow = RowDataPacket & {
  attempt_count: number;
  delete_after_at: Date | null;
  id: number;
  lifecycle_status: "reserved" | "active" | "unlinked" | "deleting" | "deleted" | null;
  source_kind: "mail_attachment" | "manual_file";
  source_ref: string;
  storage_object_id: number | null;
  storage_key: string;
};

type UnlinkedStorageObjectRow = RowDataPacket & {
  delete_after_at: Date;
  owner_user_id_snapshot: number;
  source_kind: "mail_attachment" | "manual_file";
  source_ref: string;
  storage_key: string;
};

const DEFAULT_FILE_MAX_BYTES = 25 * 1024 * 1024;
const GIB_BYTES = 1024 * 1024 * 1024;
const DEFAULT_FREE_FILE_DRAWER_QUOTA_BYTES = 2 * GIB_BYTES;
const DEFAULT_GROWTH_FILE_DRAWER_QUOTA_BYTES = 10 * GIB_BYTES;
const DEFAULT_BUSINESS_FILE_DRAWER_QUOTA_BYTES = 100 * GIB_BYTES;
const DEFAULT_PAGE_SIZE = 60;
const MAX_PAGE_SIZE = 120;
const MAX_BULK_DELETE_ITEMS = 2_000;

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

function normalizeFilename(filename: string) {
  const cleaned = filename
    .trim()
    .replace(/[<>:"/\\|?*\u0000-\u001f]+/g, " ")
    .replace(/\s+/g, " ")
    .replace(/^\.+/, "")
    .trim();
  return (cleaned || "file").slice(0, 255);
}

function normalizeMimeType(value: string) {
  return (value.trim().toLowerCase() || "application/octet-stream").slice(0, 191);
}

function readFileMaxBytes() {
  const value = Number(process.env.MAIL_FILE_DRAWER_MAX_BYTES ?? DEFAULT_FILE_MAX_BYTES);
  return Number.isFinite(value) && value > 0 ? Math.floor(value) : DEFAULT_FILE_MAX_BYTES;
}

function readPositiveByteLimit(value: string | undefined, fallback: number) {
  const parsed = Number(value ?? fallback);
  return Number.isFinite(parsed) && parsed > 0 ? Math.floor(parsed) : fallback;
}

export function getFileDrawerQuotaBytesForTier(tier: MailPlanQuotaTier) {
  if (tier === "business") {
    return readPositiveByteLimit(
      process.env.MAIL_FILE_DRAWER_BUSINESS_QUOTA_BYTES,
      DEFAULT_BUSINESS_FILE_DRAWER_QUOTA_BYTES,
    );
  }

  if (tier === "growth") {
    return readPositiveByteLimit(
      process.env.MAIL_FILE_DRAWER_GROWTH_QUOTA_BYTES ??
        process.env.MAIL_FILE_DRAWER_QUOTA_BYTES,
      DEFAULT_GROWTH_FILE_DRAWER_QUOTA_BYTES,
    );
  }

  return readPositiveByteLimit(
    process.env.MAIL_FILE_DRAWER_FREE_QUOTA_BYTES,
    DEFAULT_FREE_FILE_DRAWER_QUOTA_BYTES,
  );
}

export class MailboxFileDrawerQuotaExceededError extends Error {
  constructor(readonly quotaBytes: number) {
    super("file-drawer-quota-exceeded");
    this.name = "MailboxFileDrawerQuotaExceededError";
  }
}

function buildStorageKey(userId: number, token: string, filename: string) {
  const now = new Date();
  const extension = path.extname(filename).toLowerCase().replace(/[^.a-z0-9]/g, "").slice(0, 16);
  return [
    process.env.MAIL_FILE_DRAWER_STORAGE_PREFIX?.trim() || "official-mail/file-drawer",
    String(userId),
    String(now.getUTCFullYear()),
    String(now.getUTCMonth() + 1).padStart(2, "0"),
    `${token}${extension}`,
  ].join("/");
}

function toIsoString(value: Date) {
  return (value instanceof Date ? value : new Date(value)).toISOString();
}

async function getMailboxContextByEmail(email: string) {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<MailboxContextRow[]>(
    `
      SELECT
        u.id AS owner_user_id,
        COALESCE(mtm.owner_user_id, u.id) AS plan_owner_user_id,
        LOWER(u.email) AS owner_email,
        m.id AS mailbox_id,
        LOWER(m.email) AS mailbox_email
      FROM users u
      INNER JOIN mailboxes m ON m.user_id = u.id
      LEFT JOIN managed_team_mailboxes mtm
        ON mtm.email = LOWER(u.email) AND mtm.status = 'active'
      WHERE LOWER(u.email) = ?
        AND m.status <> 'disabled'
      ORDER BY m.id DESC
      LIMIT 1
    `,
    [normalizeEmail(email)],
  );
  return rows[0] ?? null;
}

function normalizeFilter(value: string | null | undefined): MailboxFileDrawerFilter {
  return value === "image" || value === "file" ? value : "all";
}

function buildFilterSql(filter: MailboxFileDrawerFilter, column: string) {
  if (filter === "image") return `AND LOWER(${column}) LIKE 'image/%'`;
  if (filter === "file") return `AND LOWER(${column}) NOT LIKE 'image/%'`;
  return "";
}

function buildSearchSql(query: string, columns: string[]) {
  if (!query) return { params: [] as string[], sql: "" };
  const pattern = `%${query.toLowerCase()}%`;
  return {
    params: columns.map(() => pattern),
    sql: `AND (${columns.map((column) => `LOWER(${column}) LIKE ?`).join(" OR ")})`,
  };
}

function mapUploadItem(row: DrawerItemRow): MailboxFileDrawerItem {
  if (row.item_type === "received" && row.mailbox_message_id !== null && row.attachment_index !== null) {
    const params = new URLSearchParams({
      message: String(row.mailbox_message_id),
      index: String(row.attachment_index),
    });
    const downloadUrl = `/api/mailbox/attachment?${params}`;
    const canPreview = row.mime_type.toLowerCase().startsWith("image/") || row.mime_type.toLowerCase() === "application/pdf";
    return {
      createdAt: toIsoString(row.created_at),
      direction: "inbound",
      downloadUrl,
      expiresAt: null,
      id: `received_${row.item_id}`,
      isImage: row.mime_type.toLowerCase().startsWith("image/"),
      mimeType: row.mime_type,
      name: row.original_name,
      previewUrl: canPreview ? `${downloadUrl}&mode=inline` : null,
      shareAccess: "owner",
      shareEnabled: false,
      shareUrl: null,
      sizeBytes: Number(row.size_bytes),
      source: "mail",
      subject: row.subject,
    };
  }
  if (row.item_type === "large") {
    const token = String(row.item_id);
    const downloadUrl = `/api/mailbox/large-attachments/${token}`;
    return {
      createdAt: toIsoString(row.created_at),
      direction: "outbound",
      downloadUrl,
      expiresAt: row.expires_at ? toIsoString(row.expires_at) : null,
      id: `large_${token}`,
      isImage: row.mime_type.toLowerCase().startsWith("image/"),
      mimeType: row.mime_type,
      name: row.original_name,
      previewUrl: null,
      shareAccess: "public",
      shareEnabled: true,
      shareUrl: downloadUrl,
      sizeBytes: Number(row.size_bytes),
      source: "mail",
      subject: row.subject,
    };
  }
  const id = `upload_${String(row.item_id)}`;
  const canPreview = row.mime_type.toLowerCase().startsWith("image/") || row.mime_type.toLowerCase() === "application/pdf";
  return {
    createdAt: toIsoString(row.created_at),
    direction: "uploaded",
    downloadUrl: `/api/mailbox/files/${id}?mode=download`,
    expiresAt: null,
    id,
    isImage: row.mime_type.toLowerCase().startsWith("image/"),
    mimeType: row.mime_type,
    name: row.original_name,
    previewUrl: canPreview ? `/api/mailbox/files/${id}?mode=inline` : null,
    shareAccess: row.share_access === "public" ? "public" : "owner",
    shareEnabled: Boolean(row.share_enabled && row.share_token),
    shareUrl: row.share_enabled && row.share_token
      ? `/api/mailbox/files/shared/${row.share_token}`
      : null,
    sizeBytes: Number(row.size_bytes),
    source: "upload",
    subject: null,
  };
}

export async function getMailboxDrawerManualUsageByUserId(ownerUserId: number) {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<DrawerCountRow[]>(
    `
      SELECT COUNT(*) AS item_count, COALESCE(SUM(size_bytes), 0) AS size_bytes
      FROM mailbox_file_assets
      WHERE owner_user_id = ?
        AND storage_status = 'active'
    `,
    [ownerUserId],
  );
  return {
    count: Number(rows[0]?.item_count ?? 0),
    sizeBytes: Number(rows[0]?.size_bytes ?? 0),
  };
}

export async function getMailboxFileDrawerSnapshotByEmail(input: {
  email: string;
  filter?: string | null;
  page?: number;
  pageSize?: number;
  query?: string | null;
}): Promise<MailboxFileDrawerSnapshot | null> {
  const context = await getMailboxContextByEmail(input.email);
  if (!context) return null;

  const filter = normalizeFilter(input.filter);
  const query = String(input.query ?? "").trim().slice(0, 120);
  const page = Math.max(1, Math.floor(input.page ?? 1));
  const pageSize = Math.max(1, Math.min(MAX_PAGE_SIZE, Math.floor(input.pageSize ?? DEFAULT_PAGE_SIZE)));
  const offset = (page - 1) * pageSize;
  const uploadSearch = buildSearchSql(query, ["mfa.original_name"]);
  const largeSearch = buildSearchSql(query, ["cua.original_name"]);
  const receivedSearch = buildSearchSql(query, ["mma.original_name"]);
  const pool = getDbPool();

  const [itemRows, uploadCountRows, largeCountRows, receivedCountRows, totalManualUsageRows, largeUsageRows, planTier] = await Promise.all([
    pool.query<DrawerItemRow[]>(
      `
        SELECT * FROM (
          SELECT mfa.file_token AS item_id, 'upload' AS item_type,
            NULL AS attachment_index, NULL AS mailbox_message_id,
            mfa.original_name, mfa.mime_type, mfa.size_bytes, mfa.created_at,
            NULL AS expires_at,
            mfa.share_enabled, mfa.share_access, mfa.share_token,
            'uploaded' AS direction, NULL AS subject
          FROM mailbox_file_assets mfa
          WHERE mfa.owner_user_id = ? AND mfa.storage_status = 'active'
            ${buildFilterSql(filter, "mfa.mime_type")}
            ${uploadSearch.sql}
          UNION ALL
          SELECT cua.temp_token AS item_id, 'large' AS item_type,
            NULL AS attachment_index, cua.mailbox_message_id,
            cua.original_name, cua.mime_type, cua.size_bytes, cua.created_at,
            cua.expires_at,
            1 AS share_enabled, 'public' AS share_access, cua.temp_token AS share_token,
            'outbound' AS direction, mm.subject
          FROM mailbox_uploaded_assets cua
          LEFT JOIN mailbox_messages mm ON mm.id = cua.mailbox_message_id
          WHERE cua.owner_user_id = ? AND cua.large_attachment = 1
            AND cua.storage_status = 'finalized' AND cua.published_at IS NOT NULL
            AND cua.expires_at > NOW()
            AND cua.download_count < ?
            ${buildFilterSql(filter, "cua.mime_type")}
            ${largeSearch.sql}
          UNION ALL
          SELECT CONCAT(mma.mailbox_message_id, '_', mma.attachment_index) AS item_id,
            'received' AS item_type, mma.attachment_index, mma.mailbox_message_id,
            mma.original_name, mma.mime_type, mma.size_bytes, mm.received_at AS created_at,
            NULL AS expires_at,
            0 AS share_enabled, 'owner' AS share_access, NULL AS share_token,
            'inbound' AS direction, mm.subject
          FROM mailbox_message_attachments mma
          INNER JOIN mailbox_messages mm ON mm.id = mma.mailbox_message_id
          WHERE mm.mailbox_id = ? AND mm.direction = 'inbound'
            AND mma.content_disposition = 'attachment'
            ${buildFilterSql(filter, "mma.mime_type")}
            ${receivedSearch.sql}
        ) drawer_items
        ORDER BY created_at DESC, item_id DESC
        LIMIT ? OFFSET ?
      `,
      [context.owner_user_id, ...uploadSearch.params,
        context.owner_user_id, LARGE_ATTACHMENT_DOWNLOAD_LIMIT, ...largeSearch.params,
        context.mailbox_id, ...receivedSearch.params,
        pageSize, offset],
    ).then(([rows]) => rows),
    pool.query<DrawerCountRow[]>(
      `
        SELECT COUNT(*) AS item_count, COALESCE(SUM(mfa.size_bytes), 0) AS size_bytes
        FROM mailbox_file_assets mfa
        WHERE mfa.owner_user_id = ?
          AND mfa.storage_status = 'active'
          ${buildFilterSql(filter, "mfa.mime_type")}
          ${uploadSearch.sql}
      `,
      [context.owner_user_id, ...uploadSearch.params],
    ).then(([rows]) => rows),
    pool.query<DrawerCountRow[]>(
      `SELECT COUNT(*) AS item_count, COALESCE(SUM(cua.size_bytes), 0) AS size_bytes
       FROM mailbox_uploaded_assets cua
       WHERE cua.owner_user_id = ? AND cua.large_attachment = 1
         AND cua.storage_status = 'finalized' AND cua.published_at IS NOT NULL
         AND cua.expires_at > NOW() AND cua.download_count < ?
         ${buildFilterSql(filter, "cua.mime_type")}
         ${largeSearch.sql}`,
      [context.owner_user_id, LARGE_ATTACHMENT_DOWNLOAD_LIMIT, ...largeSearch.params],
    ).then(([rows]) => rows),
    pool.query<DrawerCountRow[]>(
      `SELECT COUNT(*) AS item_count, COALESCE(SUM(mma.size_bytes), 0) AS size_bytes
       FROM mailbox_message_attachments mma
       INNER JOIN mailbox_messages mm ON mm.id = mma.mailbox_message_id
       WHERE mm.mailbox_id = ? AND mm.direction = 'inbound'
         AND mma.content_disposition = 'attachment'
         ${buildFilterSql(filter, "mma.mime_type")}
         ${receivedSearch.sql}`,
      [context.mailbox_id, ...receivedSearch.params],
    ).then(([rows]) => rows),
    pool.query<DrawerCountRow[]>(
      `
        SELECT COUNT(*) AS item_count, COALESCE(SUM(size_bytes), 0) AS size_bytes
        FROM mailbox_file_assets
        WHERE owner_user_id = ?
          AND storage_status = 'active'
      `,
      [context.owner_user_id],
    ).then(([rows]) => rows),
    pool.query<DrawerCountRow[]>(
      `SELECT COUNT(*) AS item_count,
         COALESCE(SUM(size_bytes), 0) AS size_bytes,
         COALESCE(SUM(storage_status = 'finalized' AND published_at IS NOT NULL), 0) AS published_count
       FROM mailbox_uploaded_assets
       WHERE owner_user_id = ? AND large_attachment = 1
         AND storage_status IN ('temporary', 'finalized')
         AND expires_at > NOW() AND download_count < ?`,
      [context.owner_user_id, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
    ).then(([rows]) => rows),
    getMailPlanQuotaTierForOwnerUserId(context.plan_owner_user_id),
  ]);

  const uploadCount = Number(uploadCountRows[0]?.item_count ?? 0);
  const largeCount = Number(largeCountRows[0]?.item_count ?? 0);
  const receivedCount = Number(receivedCountRows[0]?.item_count ?? 0);
  const manualFileBytes = Number(totalManualUsageRows[0]?.size_bytes ?? 0);
  const largeAttachmentBytes = Number(largeUsageRows[0]?.size_bytes ?? 0);
  const usedBytes = manualFileBytes + largeAttachmentBytes;
  const quotaBytes = getFileDrawerQuotaBytesForTier(planTier);
  const items = itemRows.map(mapUploadItem);

  return {
    generatedAt: new Date().toISOString(),
    hasMore: offset + items.length < uploadCount + largeCount + receivedCount,
    items,
    mailAttachmentBytes: largeAttachmentBytes,
    mailAttachmentCount: Number(largeUsageRows[0]?.published_count ?? 0),
    manualFileBytes,
    manualFileCount: Number(totalManualUsageRows[0]?.item_count ?? 0),
    page,
    pageSize,
    quotaBytes,
    quotaIsUnlimited: false,
    quotaUsagePercent: Math.min(100, Math.round((usedBytes / quotaBytes) * 10_000) / 100),
    quotaUsedBytes: usedBytes,
    totalCount: uploadCount + largeCount + receivedCount,
  };
}

async function enqueueStorageDelete(
  connection: PoolConnection,
  input: {
    ownerUserId: number;
    sourceKind: "mail_attachment" | "manual_file";
    sourceRef: string;
    storageKey: string;
    reason: string;
  },
) {
  await scheduleMailboxStorageObjectDeletion(connection, input);
}

export async function enqueueMailboxAttachmentStorageDeletesByMessageIds(
  connection: PoolConnection,
  messageIds: number[],
) {
  const normalizedMessageIds = [...new Set(messageIds.filter((id) => Number.isInteger(id) && id > 0))];

  if (normalizedMessageIds.length === 0) return 0;

  let enqueuedCount = 0;
  for (let offset = 0; offset < normalizedMessageIds.length; offset += 500) {
    const chunk = normalizedMessageIds.slice(offset, offset + 500);
    const [rows] = await connection.query<AttachmentStorageRow[]>(
      `
        SELECT
          mma.id AS attachment_row_id,
          u.id AS owner_user_id,
          mma.storage_key
        FROM mailbox_message_attachments mma
        INNER JOIN mailbox_messages mm ON mm.id = mma.mailbox_message_id
        INNER JOIN mailboxes mb ON mb.id = mm.mailbox_id
        INNER JOIN users u ON u.id = mb.user_id
        WHERE mma.mailbox_message_id IN (${chunk.map(() => "?").join(", ")})
          AND mma.storage_key IS NOT NULL
          AND mma.storage_key <> ''
      `,
      chunk,
    );

    for (const row of rows) {
      await enqueueStorageDelete(connection, {
        ownerUserId: row.owner_user_id,
        sourceKind: "mail_attachment",
        sourceRef: String(row.attachment_row_id),
        storageKey: row.storage_key,
        reason: "message-unlinked",
      });
      enqueuedCount += 1;
    }
  }

  return enqueuedCount;
}

export async function markMailboxDrawerManualFilesDeletedForOwner(
  connection: PoolConnection,
  ownerUserId: number,
) {
  const [rows] = await connection.query<ManualFileRow[]>(
    `
      SELECT id, owner_user_id, file_token, original_name, mime_type, size_bytes, storage_key
      FROM mailbox_file_assets
      WHERE owner_user_id = ?
        AND storage_status <> 'deleted'
      ORDER BY id ASC
      FOR UPDATE
    `,
    [ownerUserId],
  );

  if (rows.length === 0) return 0;

  await connection.query(
    `
      UPDATE mailbox_file_assets
      SET storage_status = 'deleted', share_enabled = 0, share_token = NULL,
          share_updated_at = NOW(), deleted_at = COALESCE(deleted_at, NOW()), updated_at = NOW()
      WHERE owner_user_id = ?
        AND storage_status <> 'deleted'
    `,
    [ownerUserId],
  );

  for (const row of rows) {
    await enqueueStorageDelete(connection, {
      ownerUserId,
      sourceKind: "manual_file",
      sourceRef: String(row.id),
      storageKey: row.storage_key,
      reason: "owner-deleted",
    });
  }

  return rows.length;
}

async function reserveManualFile(input: {
  context: MailboxContextRow;
  filename: string;
  mimeType: string;
  quotaBytes: number;
  sizeBytes: number;
}) {
  const token = randomBytes(24).toString("hex");
  const storageKey = buildStorageKey(input.context.owner_user_id, token, input.filename);
  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    await connection.query("SELECT id FROM users WHERE id = ? FOR UPDATE", [input.context.owner_user_id]);
    const [usageRows] = await connection.query<DrawerCountRow[]>(
      `
        SELECT
          (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_file_assets
           WHERE owner_user_id = ? AND storage_status IN ('uploading', 'active')) +
          (SELECT COALESCE(SUM(size_bytes), 0) FROM mailbox_uploaded_assets
           WHERE owner_user_id = ? AND large_attachment = 1
             AND storage_status IN ('temporary', 'finalized') AND expires_at > NOW()
             AND download_count < ?) AS size_bytes
      `,
      [input.context.owner_user_id, input.context.owner_user_id, LARGE_ATTACHMENT_DOWNLOAD_LIMIT],
    );
    const reservedBytes = Number(usageRows[0]?.size_bytes ?? 0);

    if (reservedBytes + input.sizeBytes > input.quotaBytes) {
      throw new MailboxFileDrawerQuotaExceededError(input.quotaBytes);
    }

    const [result] = await connection.query<mysql.ResultSetHeader>(
      `
        INSERT INTO mailbox_file_assets (
          owner_user_id,
          mailbox_id,
          file_token,
          original_name,
          mime_type,
          size_bytes,
          storage_status,
          storage_key
        ) VALUES (?, ?, ?, ?, ?, ?, 'uploading', ?)
      `,
      [
        input.context.owner_user_id,
        input.context.mailbox_id,
        token,
        input.filename,
        input.mimeType,
        input.sizeBytes,
        storageKey,
      ],
    );
    await reserveMailboxStorageObject(connection, {
      mailboxEmail: input.context.mailbox_email,
      mailboxId: input.context.mailbox_id,
      mimeType: input.mimeType,
      originalName: input.filename,
      ownerEmail: input.context.owner_email,
      ownerUserId: input.context.owner_user_id,
      sizeBytes: input.sizeBytes,
      sourceKind: "manual_file",
      sourceRef: String(result.insertId),
      storageKey,
    });
    await connection.commit();
    return { id: Number(result.insertId), storageKey, token };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

export async function uploadMailboxDrawerFilesForUserEmail(input: {
  email: string;
  files: Array<{ body: Buffer; filename: string; mimeType: string; sizeBytes: number }>;
}) {
  const context = await getMailboxContextByEmail(input.email);
  if (!context) throw new Error("file-drawer-mailbox-not-found");
  const maxBytes = readFileMaxBytes();
  const planTier = await getMailPlanQuotaTierForOwnerUserId(context.plan_owner_user_id);
  const quotaBytes = getFileDrawerQuotaBytesForTier(planTier);
  const uploadedItems: MailboxFileDrawerItem[] = [];

  for (const file of input.files) {
    const filename = normalizeFilename(file.filename);
    const mimeType = normalizeMimeType(file.mimeType);

    if (file.sizeBytes < 1 || file.body.length < 1) throw new Error("file-drawer-empty");
    if (file.sizeBytes > maxBytes || file.body.length > maxBytes) throw new Error("file-drawer-too-large");

    const reservation = await reserveManualFile({
      context,
      filename,
      mimeType,
      quotaBytes,
      sizeBytes: file.sizeBytes,
    });

    try {
      const stored = await uploadPrivateBufferToObjectStorage({
        body: file.body,
        cacheControl: "private, max-age=31536000, immutable",
        contentType: mimeType,
        key: reservation.storageKey,
      });
      const connection = await getDbPool().getConnection();
      try {
        await connection.beginTransaction();
        const [activated] = await connection.query<mysql.ResultSetHeader>(
          `
            UPDATE mailbox_file_assets
            SET storage_status = 'active', storage_bucket = ?, activated_at = NOW(), updated_at = NOW()
            WHERE id = ? AND storage_status = 'uploading'
          `,
          [stored.bucketName, reservation.id],
        );
        if (activated.affectedRows !== 1) throw new Error("file-drawer-reservation-lost");
        await activateMailboxStorageObject(connection, {
          storageBucket: stored.bucketName,
          storageKey: reservation.storageKey,
        });
        await connection.commit();
      } catch (error) {
        await connection.rollback();
        throw error;
      } finally {
        connection.release();
      }
      uploadedItems.push({
        createdAt: new Date().toISOString(),
        direction: "uploaded",
        downloadUrl: `/api/mailbox/files/upload_${reservation.token}?mode=download`,
        expiresAt: null,
        id: `upload_${reservation.token}`,
        isImage: mimeType.startsWith("image/"),
        mimeType,
        name: filename,
        previewUrl: mimeType.startsWith("image/")
          ? `/api/mailbox/files/upload_${reservation.token}?mode=inline`
          : null,
        shareAccess: "owner",
        shareEnabled: false,
        shareUrl: null,
        sizeBytes: file.sizeBytes,
        source: "upload",
        subject: null,
      });
    } catch (error) {
      const connection = await getDbPool().getConnection();
      try {
        await connection.beginTransaction();
        await connection.query(
          `UPDATE mailbox_file_assets SET storage_status = 'deleted', deleted_at = NOW(), updated_at = NOW() WHERE id = ?`,
          [reservation.id],
        );
        await enqueueStorageDelete(connection, {
          ownerUserId: context.owner_user_id,
          sourceKind: "manual_file",
          sourceRef: String(reservation.id),
          storageKey: reservation.storageKey,
          reason: "upload-failed",
        });
        await connection.commit();
      } catch {
        await connection.rollback();
      } finally {
        connection.release();
      }
      throw error;
    }
  }

  return uploadedItems;
}

export async function getMailboxDrawerUploadPayloadByEmail(email: string, token: string) {
  const context = await getMailboxContextByEmail(email);
  if (!context || !/^[a-f0-9]{48}$/.test(token)) return null;
  const [rows] = await getDbPool().query<ManualFileRow[]>(
    `
      SELECT id, owner_user_id, file_token, original_name, mime_type, size_bytes, storage_key
      FROM mailbox_file_assets
      WHERE owner_user_id = ?
        AND file_token = ?
        AND storage_status = 'active'
      LIMIT 1
    `,
    [context.owner_user_id, token],
  );
  const row = rows[0];
  if (!row) return null;
  const object = await readObjectFromObjectStorage({ key: row.storage_key });
  if (!object?.body) return null;
  return {
    body: object.body,
    filename: row.original_name,
    mimeType: object.contentType || row.mime_type,
  };
}

function normalizeUploadItemToken(itemId: string) {
  const token = itemId.startsWith("upload_") ? itemId.slice("upload_".length) : "";
  return /^[a-f0-9]{48}$/.test(token) ? token : null;
}

function buildFileShareUrl(token: string) {
  return `/api/mailbox/files/shared/${token}`;
}

export async function getMailboxDrawerFileShareByEmail(email: string, itemId: string) {
  const context = await getMailboxContextByEmail(email);
  const fileToken = normalizeUploadItemToken(itemId);
  if (!context || !fileToken) return null;

  const [rows] = await getDbPool().query<ManualFileRow[]>(
    `
      SELECT id, owner_user_id, file_token, original_name, mime_type, size_bytes, storage_key,
             share_enabled, share_access, share_token
      FROM mailbox_file_assets
      WHERE owner_user_id = ? AND file_token = ? AND storage_status = 'active'
      LIMIT 1
    `,
    [context.owner_user_id, fileToken],
  );
  const row = rows[0];
  if (!row) return null;
  const enabled = Boolean(row.share_enabled && row.share_token);
  return {
    access: row.share_access === "public" ? "public" as const : "owner" as const,
    enabled,
    url: enabled && row.share_token ? buildFileShareUrl(row.share_token) : null,
  };
}

export async function updateMailboxDrawerFileShareByEmail(input: {
  access: MailboxFileShareAccess;
  email: string;
  enabled: boolean;
  itemId: string;
}) {
  const context = await getMailboxContextByEmail(input.email);
  const fileToken = normalizeUploadItemToken(input.itemId);
  if (!context || !fileToken) return null;
  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    const [rows] = await connection.query<ManualFileRow[]>(
      `
        SELECT id, owner_user_id, file_token, original_name, mime_type, size_bytes, storage_key,
               share_enabled, share_access, share_token
        FROM mailbox_file_assets
        WHERE owner_user_id = ? AND file_token = ? AND storage_status = 'active'
        LIMIT 1
        FOR UPDATE
      `,
      [context.owner_user_id, fileToken],
    );
    const row = rows[0];
    if (!row) {
      await connection.rollback();
      return null;
    }

    if (!input.enabled) {
      await connection.query(
        `
          UPDATE mailbox_file_assets
          SET share_enabled = 0, share_token = NULL, share_updated_at = NOW(), updated_at = NOW()
          WHERE id = ?
        `,
        [row.id],
      );
      await connection.commit();
      return { access: input.access, enabled: false, url: null };
    }

    const shareToken = randomBytes(32).toString("hex");
    await connection.query(
      `
        UPDATE mailbox_file_assets
        SET share_enabled = 1, share_access = ?, share_token = ?,
            share_updated_at = NOW(), updated_at = NOW()
        WHERE id = ?
      `,
      [input.access, shareToken, row.id],
    );
    await connection.commit();
    return {
      access: input.access,
      enabled: true,
      url: buildFileShareUrl(shareToken),
    };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

export async function getMailboxDrawerSharedPayload(input: {
  requesterEmail?: string | null;
  token: string;
}) {
  if (!/^[a-f0-9]{64}$/.test(input.token)) return null;
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<SharedFileRow[]>(
    `
      SELECT mfa.id, mfa.owner_user_id, mfa.file_token, mfa.original_name, mfa.mime_type,
             mfa.size_bytes, mfa.storage_key, mfa.share_enabled, mfa.share_access,
             mfa.share_token, LOWER(u.email) AS owner_email
      FROM mailbox_file_assets mfa
      INNER JOIN users u ON u.id = mfa.owner_user_id
      WHERE mfa.share_enabled = 1
        AND mfa.share_token = ?
        AND mfa.storage_status = 'active'
      LIMIT 1
    `,
    [input.token],
  );
  const row = rows[0];
  if (!row) return null;
  if (row.share_access === "owner" && normalizeEmail(input.requesterEmail ?? "") !== row.owner_email) {
    return null;
  }

  const object = await readObjectFromObjectStorage({ key: row.storage_key });
  if (!object?.body) return null;
  return {
    body: object.body,
    filename: row.original_name,
    mimeType: object.contentType || row.mime_type,
  };
}

export async function deleteMailboxDrawerItemByEmail(email: string, itemId: string) {
  const context = await getMailboxContextByEmail(email);
  const largeToken = itemId.startsWith("large_") ? itemId.slice("large_".length) : "";
  if (context && /^[a-f0-9]{48}$/.test(largeToken)) {
    const [result] = await getDbPool().query<mysql.ResultSetHeader>(
      `UPDATE mailbox_uploaded_assets
       SET storage_status = 'deleted', deleted_at = NOW(), updated_at = NOW()
       WHERE owner_user_id = ? AND temp_token = ? AND large_attachment = 1
         AND storage_status = 'finalized' AND published_at IS NOT NULL`,
      [context.owner_user_id, largeToken],
    );
    // The compose-upload cleanup job removes its private object after deletion.
    return result.affectedRows === 1;
  }
  const token = itemId.startsWith("upload_") ? itemId.slice("upload_".length) : "";
  if (!context || !/^[a-f0-9]{48}$/.test(token)) return false;
  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    const [rows] = await connection.query<ManualFileRow[]>(
      `
        SELECT id, owner_user_id, file_token, original_name, mime_type, size_bytes, storage_key,
               share_enabled, share_access, share_token
        FROM mailbox_file_assets
        WHERE owner_user_id = ? AND file_token = ? AND storage_status = 'active'
        LIMIT 1
        FOR UPDATE
      `,
      [context.owner_user_id, token],
    );
    const row = rows[0];
    if (!row) {
      await connection.rollback();
      return false;
    }
    await connection.query(
      `
        UPDATE mailbox_file_assets
        SET storage_status = 'deleted', share_enabled = 0, share_token = NULL,
            share_updated_at = NOW(), deleted_at = COALESCE(deleted_at, NOW()), updated_at = NOW()
        WHERE id = ?
      `,
      [row.id],
    );
    await enqueueStorageDelete(connection, {
      ownerUserId: context.owner_user_id,
      sourceKind: "manual_file",
      sourceRef: String(row.id),
      storageKey: row.storage_key,
      reason: "user-delete",
    });

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

export async function deleteMailboxDrawerItemsByEmail(email: string, itemIds: string[]) {
  const normalizedItemIds = [...new Set(
    itemIds.map((itemId) => itemId.trim()).filter(Boolean),
  )].slice(0, MAX_BULK_DELETE_ITEMS);
  let deletedCount = 0;

  for (const itemId of normalizedItemIds) {
    if (await deleteMailboxDrawerItemByEmail(email, itemId)) deletedCount += 1;
  }

  return deletedCount;
}

export async function deleteAllMailboxDrawerItemsByEmail(email: string) {
  const context = await getMailboxContextByEmail(email);
  if (!context) return null;
  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    const [manualRows] = await connection.query<ManualFileRow[]>(
      `
        SELECT id, owner_user_id, file_token, original_name, mime_type, size_bytes, storage_key,
               share_enabled, share_access, share_token
        FROM mailbox_file_assets
        WHERE owner_user_id = ?
          AND storage_status = 'active'
        FOR UPDATE
      `,
      [context.owner_user_id],
    );
    const [largeRows] = await connection.query<(RowDataPacket & { id: number })[]>(
      `SELECT id FROM mailbox_uploaded_assets
       WHERE owner_user_id = ? AND large_attachment = 1
         AND storage_status = 'finalized' AND published_at IS NOT NULL
         AND expires_at > NOW()
       FOR UPDATE`,
      [context.owner_user_id],
    );

    if (manualRows.length > 0) {
      await connection.query(
        `
          UPDATE mailbox_file_assets
          SET storage_status = 'deleted',
              share_enabled = 0,
              share_token = NULL,
              share_updated_at = NOW(),
              deleted_at = COALESCE(deleted_at, NOW()),
              updated_at = NOW()
          WHERE owner_user_id = ?
            AND storage_status = 'active'
        `,
        [context.owner_user_id],
      );
    }

    for (const row of manualRows) {
      await enqueueStorageDelete(connection, {
        ownerUserId: context.owner_user_id,
        sourceKind: "manual_file",
        sourceRef: String(row.id),
        storageKey: row.storage_key,
        reason: "user-delete-all",
      });
    }

    if (largeRows.length > 0) {
      await connection.query(
        `UPDATE mailbox_uploaded_assets
         SET storage_status = 'deleted', deleted_at = NOW(), updated_at = NOW()
         WHERE owner_user_id = ? AND large_attachment = 1
           AND storage_status = 'finalized' AND published_at IS NOT NULL
           AND expires_at > NOW()`,
        [context.owner_user_id],
      );
    }

    await connection.commit();
    return {
      deletedCount: manualRows.length + largeRows.length,
      mailAttachmentCount: largeRows.length,
      manualFileCount: manualRows.length,
    };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

async function enqueueStaleUploadCleanup() {
  const connection = await getDbPool().getConnection();
  try {
    await connection.beginTransaction();
    const [rows] = await connection.query<ManualFileRow[]>(
      `
        SELECT id, owner_user_id, file_token, original_name, mime_type, size_bytes, storage_key
        FROM mailbox_file_assets
        WHERE storage_status = 'uploading'
          AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
        LIMIT 100
        FOR UPDATE
      `,
    );
    for (const row of rows) {
      await connection.query(
        `UPDATE mailbox_file_assets
         SET storage_status = 'deleted', share_enabled = 0, share_token = NULL,
             share_updated_at = NOW(), deleted_at = NOW(), updated_at = NOW()
         WHERE id = ?`,
        [row.id],
      );
      await enqueueStorageDelete(connection, {
        ownerUserId: row.owner_user_id,
        sourceKind: "manual_file",
        sourceRef: String(row.id),
        storageKey: row.storage_key,
        reason: "stale-upload",
      });
    }
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

async function enqueueUnlinkedStorageObjectCleanup(limit = 500) {
  const pool = getDbPool();
  const [rows] = await pool.query<UnlinkedStorageObjectRow[]>(
    `
      SELECT
        mso.owner_user_id_snapshot,
        mso.source_kind,
        mso.source_ref,
        mso.storage_key,
        mso.delete_after_at
      FROM mailbox_storage_objects mso
      LEFT JOIN mailbox_storage_delete_jobs msdj
        ON msdj.source_kind = mso.source_kind
       AND msdj.source_ref = mso.source_ref
       AND msdj.storage_key = mso.storage_key
      WHERE mso.lifecycle_status = 'unlinked'
        AND mso.delete_after_at IS NOT NULL
        AND (msdj.id IS NULL OR msdj.status = 'completed')
      ORDER BY mso.delete_after_at ASC, mso.id ASC
      LIMIT ?
    `,
    [Math.max(1, Math.min(2_000, Math.floor(limit)))],
  );

  for (const row of rows) {
    await pool.query(
      `
        INSERT INTO mailbox_storage_delete_jobs (
          owner_user_id,
          source_kind,
          source_ref,
          storage_key,
          status,
          attempt_count,
          process_after_at,
          processing_started_at,
          completed_at,
          last_error
        ) VALUES (?, ?, ?, ?, 'pending', 0, ?, NULL, NULL, NULL)
        ON DUPLICATE KEY UPDATE
          status = 'pending',
          attempt_count = 0,
          process_after_at = VALUES(process_after_at),
          processing_started_at = NULL,
          completed_at = NULL,
          last_error = NULL,
          updated_at = NOW()
      `,
      [row.owner_user_id_snapshot, row.source_kind, row.source_ref, row.storage_key, row.delete_after_at],
    );
  }

  return rows.length;
}

export async function processMailboxStorageDeletionJobs(limit = 50) {
  await ensureOfficialMailSchema();
  await enqueueStaleUploadCleanup();
  await enqueueUnlinkedStorageObjectCleanup();
  const pool = getDbPool();
  const normalizedLimit = Math.max(1, Math.min(200, Math.floor(limit)));
  await pool.query(
    `
      UPDATE mailbox_storage_objects
      SET lifecycle_status = 'unlinked', updated_at = NOW()
      WHERE lifecycle_status = 'deleting'
        AND updated_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
    `,
  );
  await pool.query(
    `
      UPDATE mailbox_storage_delete_jobs
      SET status = 'pending', processing_started_at = NULL, process_after_at = NOW(), updated_at = NOW()
      WHERE status = 'processing'
        AND processing_started_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
    `,
  );
  const [jobs] = await pool.query<StorageDeleteJobRow[]>(
    `
      SELECT
        msdj.id,
        msdj.source_kind,
        msdj.source_ref,
        mso.storage_key,
        msdj.attempt_count,
        mso.id AS storage_object_id,
        mso.lifecycle_status,
        mso.delete_after_at
      FROM mailbox_storage_delete_jobs msdj
      INNER JOIN mailbox_storage_objects mso
        ON mso.storage_key = msdj.storage_key
      WHERE msdj.status = 'pending'
        AND msdj.process_after_at <= NOW()
      ORDER BY msdj.id ASC
      LIMIT ?
    `,
    [normalizedLimit],
  );
  let completed = 0;
  let failed = 0;

  for (const job of jobs) {
    const [claim] = await pool.query<mysql.ResultSetHeader>(
      `
        UPDATE mailbox_storage_delete_jobs
        SET status = 'processing', attempt_count = attempt_count + 1, processing_started_at = NOW(), updated_at = NOW()
        WHERE id = ? AND status = 'pending'
      `,
      [job.id],
    );
    if (claim.affectedRows !== 1) continue;

    if (
      job.storage_object_id === null ||
      job.lifecycle_status === "reserved" ||
      job.lifecycle_status === "active" ||
      job.lifecycle_status === "deleted"
    ) {
      await pool.query(
        `
          UPDATE mailbox_storage_delete_jobs
          SET status = 'completed', completed_at = NOW(), processing_started_at = NULL,
              last_error = 'skipped-object-not-unlinked', updated_at = NOW()
          WHERE id = ?
        `,
        [job.id],
      );
      continue;
    }

    if (job.delete_after_at && job.delete_after_at.getTime() > Date.now()) {
      await pool.query(
        `
          UPDATE mailbox_storage_delete_jobs
          SET status = 'pending', process_after_at = ?, processing_started_at = NULL, updated_at = NOW()
          WHERE id = ?
        `,
        [job.delete_after_at, job.id],
      );
      continue;
    }

    const [objectClaim] = await pool.query<mysql.ResultSetHeader>(
      `
        UPDATE mailbox_storage_objects
        SET lifecycle_status = 'deleting', updated_at = NOW()
        WHERE id = ?
          AND lifecycle_status = 'unlinked'
          AND delete_after_at IS NOT NULL
          AND delete_after_at <= NOW()
      `,
      [job.storage_object_id],
    );
    if (objectClaim.affectedRows !== 1) {
      await pool.query(
        `
          UPDATE mailbox_storage_delete_jobs
          SET status = 'pending', process_after_at = DATE_ADD(NOW(), INTERVAL 1 MINUTE),
              processing_started_at = NULL, last_error = 'object-claim-conflict', updated_at = NOW()
          WHERE id = ?
        `,
        [job.id],
      );
      continue;
    }

    try {
      await deleteObjectFromObjectStorage({ key: job.storage_key });
      const connection = await pool.getConnection();
      try {
        await connection.beginTransaction();
        if (job.source_kind === "mail_attachment") {
          await connection.query(
            `
              UPDATE mailbox_message_attachments
              SET storage_status = 'metadata', storage_bucket = NULL, storage_key = NULL,
                  content_sha256 = NULL, cached_at = NULL, updated_at = NOW()
              WHERE id = ? AND storage_key = ?
            `,
            [Number(job.source_ref), job.storage_key],
          );
        }
        await connection.query(
          `
            UPDATE mailbox_storage_objects
            SET lifecycle_status = 'deleted', physical_deleted_at = NOW(), last_error = NULL, updated_at = NOW()
            WHERE id = ? AND lifecycle_status = 'deleting' AND storage_key = ?
          `,
          [job.storage_object_id, job.storage_key],
        );
        await connection.query(
          `
            UPDATE mailbox_storage_delete_jobs
            SET status = 'completed', completed_at = NOW(), processing_started_at = NULL,
                last_error = NULL, updated_at = NOW()
            WHERE id = ?
          `,
          [job.id],
        );
        await connection.commit();
      } catch (error) {
        await connection.rollback();
        throw error;
      } finally {
        connection.release();
      }
      completed += 1;
    } catch (error) {
      const attempt = Number(job.attempt_count) + 1;
      const retrySeconds = Math.min(3600, Math.max(5, 2 ** Math.min(10, attempt) * 5));
      const processAfter = new Date(Date.now() + retrySeconds * 1000);
      await pool.query(
        `
          UPDATE mailbox_storage_delete_jobs
          SET status = 'pending', process_after_at = ?, processing_started_at = NULL, last_error = ?, updated_at = NOW()
          WHERE id = ?
        `,
        [processAfter, (error instanceof Error ? error.message : "object-delete-failed").slice(0, 1000), job.id],
      );
      await pool.query(
        `
          UPDATE mailbox_storage_objects
          SET lifecycle_status = 'unlinked', last_error = ?, updated_at = NOW()
          WHERE id = ? AND lifecycle_status = 'deleting'
        `,
        [(error instanceof Error ? error.message : "object-delete-failed").slice(0, 1000), job.storage_object_id],
      );
      failed += 1;
    }
  }

  const [remainingRows] = await pool.query<(RowDataPacket & { item_count: number })[]>(
    `SELECT COUNT(*) AS item_count FROM mailbox_storage_delete_jobs WHERE status <> 'completed'`,
  );
  const [readyRows] = await pool.query<(RowDataPacket & { item_count: number })[]>(
    `
      SELECT COUNT(*) AS item_count
      FROM mailbox_storage_delete_jobs msdj
      INNER JOIN mailbox_storage_objects mso ON mso.storage_key = msdj.storage_key
      WHERE msdj.status = 'pending' AND msdj.process_after_at <= NOW()
    `,
  );
  return {
    attempted: jobs.length,
    completed,
    failed,
    ready: Number(readyRows[0]?.item_count ?? 0),
    remaining: Number(remainingRows[0]?.item_count ?? 0),
  };
}