import "server-only";

import type { Pool, PoolConnection, ResultSetHeader } from "mysql2/promise";

export type MailboxStorageSourceKind = "mail_attachment" | "manual_file";

type QueryExecutor = Pool | PoolConnection;

const DEFAULT_DELETE_GRACE_HOURS = 24;
const MAX_DELETE_GRACE_HOURS = 24 * 30;

function readDeleteGraceHours() {
  const value = Number(
    process.env.MAILBOX_STORAGE_DELETE_GRACE_HOURS ?? DEFAULT_DELETE_GRACE_HOURS,
  );
  if (!Number.isFinite(value) || value < 0) return DEFAULT_DELETE_GRACE_HOURS;
  return Math.min(MAX_DELETE_GRACE_HOURS, value);
}

export function getMailboxStorageDeleteAfter() {
  return new Date(Date.now() + readDeleteGraceHours() * 60 * 60 * 1000);
}

export async function reserveMailboxStorageObject(
  executor: QueryExecutor,
  input: {
    mailboxEmail: string | null;
    mailboxId: number | null;
    mimeType: string;
    originalName: string;
    ownerEmail: string;
    ownerUserId: number;
    sizeBytes: number;
    sourceKind: MailboxStorageSourceKind;
    sourceRef: string;
    storageKey: string;
  },
) {
  await executor.query(
    `
      INSERT INTO mailbox_storage_objects (
        owner_user_id_snapshot,
        owner_email_snapshot,
        mailbox_id_snapshot,
        mailbox_email_snapshot,
        source_kind,
        source_ref,
        original_name,
        mime_type,
        size_bytes,
        storage_key,
        lifecycle_status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'reserved')
      ON DUPLICATE KEY UPDATE
        owner_user_id_snapshot = VALUES(owner_user_id_snapshot),
        owner_email_snapshot = VALUES(owner_email_snapshot),
        mailbox_id_snapshot = VALUES(mailbox_id_snapshot),
        mailbox_email_snapshot = VALUES(mailbox_email_snapshot),
        source_kind = VALUES(source_kind),
        source_ref = VALUES(source_ref),
        original_name = VALUES(original_name),
        mime_type = VALUES(mime_type),
        size_bytes = VALUES(size_bytes),
        lifecycle_status = IF(lifecycle_status = 'deleting', lifecycle_status, 'reserved'),
        unlinked_reason = NULL,
        unlinked_at = NULL,
        delete_after_at = NULL,
        physical_deleted_at = NULL,
        last_error = NULL,
        updated_at = NOW()
    `,
    [
      input.ownerUserId,
      input.ownerEmail.trim().toLowerCase(),
      input.mailboxId,
      input.mailboxEmail?.trim().toLowerCase() || null,
      input.sourceKind,
      input.sourceRef,
      input.originalName,
      input.mimeType,
      input.sizeBytes,
      input.storageKey,
    ],
  );
}

export async function activateMailboxStorageObject(
  executor: QueryExecutor,
  input: { storageBucket: string; storageKey: string },
) {
  const [result] = await executor.query<ResultSetHeader>(
    `
      UPDATE mailbox_storage_objects
      SET lifecycle_status = 'active',
          storage_bucket = ?,
          activated_at = COALESCE(activated_at, NOW()),
          unlinked_reason = NULL,
          unlinked_at = NULL,
          delete_after_at = NULL,
          physical_deleted_at = NULL,
          last_error = NULL,
          updated_at = NOW()
      WHERE storage_key = ?
        AND lifecycle_status = 'reserved'
    `,
    [input.storageBucket, input.storageKey],
  );
  if (result.affectedRows !== 1) throw new Error("mailbox-storage-object-activation-conflict");
}

export async function scheduleMailboxStorageObjectDeletion(
  executor: QueryExecutor,
  input: {
    ownerUserId: number;
    reason: string;
    sourceKind: MailboxStorageSourceKind;
    sourceRef: string;
    storageKey: string;
  },
) {
  const deleteAfter = getMailboxStorageDeleteAfter();

  await executor.query(
    `
      INSERT INTO mailbox_storage_objects (
        owner_user_id_snapshot,
        owner_email_snapshot,
        source_kind,
        source_ref,
        storage_key,
        lifecycle_status,
        unlinked_reason,
        unlinked_at,
        delete_after_at
      ) VALUES (
        ?,
        COALESCE((SELECT LOWER(email) FROM users WHERE id = ? LIMIT 1), CONCAT('deleted-user-', ?)),
        ?,
        ?,
        ?,
        'unlinked',
        ?,
        NOW(),
        ?
      )
      ON DUPLICATE KEY UPDATE
        lifecycle_status = IF(lifecycle_status = 'deleted', lifecycle_status, 'unlinked'),
        unlinked_reason = IF(lifecycle_status = 'deleted', unlinked_reason, VALUES(unlinked_reason)),
        unlinked_at = IF(lifecycle_status = 'deleted', unlinked_at, COALESCE(unlinked_at, NOW())),
        delete_after_at = IF(lifecycle_status = 'deleted', delete_after_at, COALESCE(delete_after_at, VALUES(delete_after_at))),
        updated_at = NOW()
    `,
    [
      input.ownerUserId,
      input.ownerUserId,
      input.ownerUserId,
      input.sourceKind,
      input.sourceRef,
      input.storageKey,
      input.reason.slice(0, 64),
      deleteAfter,
    ],
  );

  await executor.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()
    `,
    [input.ownerUserId, input.sourceKind, input.sourceRef, input.storageKey, deleteAfter],
  );
}