import "server-only";

import { randomUUID } from "crypto";
import type { PoolConnection, RowDataPacket } from "mysql2/promise";
import { getDbPool } from "@/lib/db";
import {
  buildObjectStoragePublicUrl,
  deleteObjectFromObjectStorage,
  uploadBufferToObjectStorage,
} from "@/lib/object-storage";

declare global {
  var __kavenixBlogImageSchemaPromise: Promise<void> | undefined;
}

type KavenixBlogImageRow = RowDataPacket & {
  draft_token: string;
  post_id: number | null;
  storage_key: string;
  storage_status: "active" | "delete_pending";
};

type KavenixBlogImageCleanupRow = RowDataPacket & {
  storage_key: string;
};

const DEFAULT_MAX_IMAGE_BYTES = 10 * 1024 * 1024;
const DEFAULT_MAX_AUDIO_BYTES = 50 * 1024 * 1024;
const DEFAULT_MAX_FILE_BYTES = 50 * 1024 * 1024;
const DEFAULT_MAX_VIDEO_BYTES = 200 * 1024 * 1024;
const DEFAULT_STORAGE_PREFIX = "official-mail/kavenix/blog-images";
const MARKDOWN_IMAGE_PATTERN = /!\[[^\]]*\]\((https?:\/\/[^\s)]+)(?:\s+["'][^"']*["'])?\)/g;
const RICH_BLOCK_URL_PATTERN = /^:::official-block\{type=[a-z0-9-]+;title=[^;]*;url=([^}]*)\}$/gm;

const IMAGE_TYPES = {
  "image/avif": "avif",
  "image/gif": "gif",
  "image/jpeg": "jpg",
  "image/png": "png",
  "image/webp": "webp",
} as const;

const AUDIO_TYPES = {
  "audio/aac": "aac",
  "audio/flac": "flac",
  "audio/mp4": "m4a",
  "audio/mpeg": "mp3",
  "audio/ogg": "ogg",
  "audio/wav": "wav",
  "audio/x-m4a": "m4a",
  "audio/x-wav": "wav",
} as const;

const VIDEO_TYPES = {
  "video/mp4": "mp4",
  "video/quicktime": "mov",
  "video/webm": "webm",
} as const;

const FILE_TYPES = {
  "application/msword": "doc",
  "application/pdf": "pdf",
  "application/vnd.ms-excel": "xls",
  "application/vnd.ms-powerpoint": "ppt",
  "application/vnd.openxmlformats-officedocument.presentationml.presentation": "pptx",
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "xlsx",
  "application/vnd.openxmlformats-officedocument.wordprocessingml.document": "docx",
  "application/zip": "zip",
  "text/plain": "txt",
} as const;

type KavenixBlogAssetKind = "audio" | "file" | "image" | "video";

function getStoragePrefix() {
  const configured = process.env.KAVENIX_BLOG_STORAGE_PREFIX?.trim().replace(/^\/+|\/+$/g, "");
  return configured || DEFAULT_STORAGE_PREFIX;
}

function readMaxImageBytes() {
  const configured = Number(process.env.KAVENIX_BLOG_IMAGE_MAX_BYTES ?? DEFAULT_MAX_IMAGE_BYTES);

  if (!Number.isFinite(configured) || configured < 1) {
    return DEFAULT_MAX_IMAGE_BYTES;
  }

  return Math.floor(configured);
}

function readMaxAudioBytes() {
  const configured = Number(process.env.KAVENIX_BLOG_AUDIO_MAX_BYTES ?? DEFAULT_MAX_AUDIO_BYTES);

  if (!Number.isFinite(configured) || configured < 1) {
    return DEFAULT_MAX_AUDIO_BYTES;
  }

  return Math.floor(configured);
}

function readMaxFileBytes() {
  const configured = Number(process.env.KAVENIX_BLOG_FILE_MAX_BYTES ?? DEFAULT_MAX_FILE_BYTES);
  return Number.isFinite(configured) && configured >= 1 ? Math.floor(configured) : DEFAULT_MAX_FILE_BYTES;
}

function readMaxVideoBytes() {
  const configured = Number(process.env.KAVENIX_BLOG_VIDEO_MAX_BYTES ?? DEFAULT_MAX_VIDEO_BYTES);
  return Number.isFinite(configured) && configured >= 1 ? Math.floor(configured) : DEFAULT_MAX_VIDEO_BYTES;
}

function imageSignatureMatches(contentType: keyof typeof IMAGE_TYPES, buffer: Buffer) {
  if (contentType === "image/jpeg") {
    return buffer.length >= 3 && buffer[0] === 0xff && buffer[1] === 0xd8 && buffer[2] === 0xff;
  }

  if (contentType === "image/png") {
    return buffer.length >= 8 && buffer.subarray(0, 8).equals(Buffer.from([0x89, 0x50, 0x4e, 0x47, 0x0d, 0x0a, 0x1a, 0x0a]));
  }

  if (contentType === "image/gif") {
    return buffer.length >= 6 && ["GIF87a", "GIF89a"].includes(buffer.subarray(0, 6).toString("ascii"));
  }

  if (contentType === "image/webp") {
    return buffer.length >= 12 && buffer.subarray(0, 4).toString("ascii") === "RIFF" && buffer.subarray(8, 12).toString("ascii") === "WEBP";
  }

  return buffer.length >= 12 && buffer.subarray(4, 8).toString("ascii") === "ftyp" && /avif|avis/.test(buffer.subarray(8, 16).toString("ascii"));
}

function ensureImageFile(input: { body: Buffer; contentType: string; sizeBytes: number }) {
  const contentType = input.contentType.trim().toLowerCase() as keyof typeof IMAGE_TYPES;

  if (!(contentType in IMAGE_TYPES)) {
    throw new Error("blog-image-type-invalid");
  }

  if (input.sizeBytes < 1 || input.body.length < 1) {
    throw new Error("blog-image-empty");
  }

  if (input.sizeBytes > readMaxImageBytes() || input.body.length > readMaxImageBytes()) {
    throw new Error("blog-image-too-large");
  }

  if (!imageSignatureMatches(contentType, input.body)) {
    throw new Error("blog-image-content-invalid");
  }

  return {
    contentType,
    extension: IMAGE_TYPES[contentType],
  };
}

function audioSignatureMatches(contentType: keyof typeof AUDIO_TYPES, buffer: Buffer) {
  if (contentType === "audio/mpeg") {
    return (
      buffer.subarray(0, 3).toString("ascii") === "ID3" ||
      (buffer.length >= 2 && buffer[0] === 0xff && (buffer[1] & 0xe0) === 0xe0)
    );
  }

  if (contentType === "audio/wav" || contentType === "audio/x-wav") {
    return (
      buffer.length >= 12 &&
      buffer.subarray(0, 4).toString("ascii") === "RIFF" &&
      buffer.subarray(8, 12).toString("ascii") === "WAVE"
    );
  }

  if (contentType === "audio/ogg") {
    return buffer.subarray(0, 4).toString("ascii") === "OggS";
  }

  if (contentType === "audio/mp4" || contentType === "audio/x-m4a") {
    return buffer.length >= 12 && buffer.subarray(4, 8).toString("ascii") === "ftyp";
  }

  if (contentType === "audio/aac") {
    return buffer.length >= 2 && buffer[0] === 0xff && (buffer[1] & 0xf0) === 0xf0;
  }

  return buffer.subarray(0, 4).toString("ascii") === "fLaC";
}

function ensureAudioFile(input: { body: Buffer; contentType: string; sizeBytes: number }) {
  const contentType = input.contentType.trim().toLowerCase() as keyof typeof AUDIO_TYPES;

  if (!(contentType in AUDIO_TYPES)) {
    throw new Error("blog-audio-type-invalid");
  }

  if (input.sizeBytes < 1 || input.body.length < 1) {
    throw new Error("blog-audio-empty");
  }

  if (input.sizeBytes > readMaxAudioBytes() || input.body.length > readMaxAudioBytes()) {
    throw new Error("blog-audio-too-large");
  }

  if (!audioSignatureMatches(contentType, input.body)) {
    throw new Error("blog-audio-content-invalid");
  }

  return {
    contentType,
    extension: AUDIO_TYPES[contentType],
  };
}

function ensureVideoFile(input: { body: Buffer; contentType: string; sizeBytes: number }) {
  const contentType = input.contentType.trim().toLowerCase() as keyof typeof VIDEO_TYPES;

  if (!(contentType in VIDEO_TYPES)) throw new Error("blog-video-type-invalid");
  if (input.sizeBytes < 1 || input.body.length < 1) throw new Error("blog-video-empty");
  if (input.sizeBytes > readMaxVideoBytes() || input.body.length > readMaxVideoBytes()) {
    throw new Error("blog-video-too-large");
  }

  const valid = contentType === "video/webm"
    ? input.body.length >= 4 && input.body.subarray(0, 4).equals(Buffer.from([0x1a, 0x45, 0xdf, 0xa3]))
    : input.body.length >= 12 && input.body.subarray(4, 8).toString("ascii") === "ftyp";

  if (!valid) throw new Error("blog-video-content-invalid");

  return { contentType, extension: VIDEO_TYPES[contentType] };
}

function ensureDocumentFile(input: { body: Buffer; contentType: string; sizeBytes: number }) {
  const contentType = input.contentType.trim().toLowerCase() as keyof typeof FILE_TYPES;

  if (!(contentType in FILE_TYPES)) throw new Error("blog-file-type-invalid");
  if (input.sizeBytes < 1 || input.body.length < 1) throw new Error("blog-file-empty");
  if (input.sizeBytes > readMaxFileBytes() || input.body.length > readMaxFileBytes()) {
    throw new Error("blog-file-too-large");
  }

  const extension = FILE_TYPES[contentType];
  const zipBased = ["docx", "xlsx", "pptx", "zip"].includes(extension);
  const legacyOffice = ["doc", "xls", "ppt"].includes(extension);
  const valid =
    contentType === "text/plain" ||
    (extension === "pdf" && input.body.subarray(0, 4).toString("ascii") === "%PDF") ||
    (zipBased && input.body.subarray(0, 2).toString("ascii") === "PK") ||
    (legacyOffice && input.body.subarray(0, 8).equals(Buffer.from([0xd0, 0xcf, 0x11, 0xe0, 0xa1, 0xb1, 0x1a, 0xe1])));

  if (!valid) throw new Error("blog-file-content-invalid");

  return { contentType, extension };
}

function enqueueImageCleanup(queryable: PoolConnection, storageKeys: string[]) {
  if (storageKeys.length === 0) {
    return Promise.resolve();
  }

  const values = storageKeys.map(() => "(?)").join(", ");
  return queryable.query(
    `
      INSERT INTO marketing_blog_image_cleanup_queue (storage_key)
      VALUES ${values}
      ON DUPLICATE KEY UPDATE updated_at = NOW()
    `,
    storageKeys,
  );
}

export function normalizeKavenixBlogImageDraftToken(value: unknown) {
  const token = typeof value === "string" ? value.trim().toLowerCase() : "";
  return /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/.test(token) ? token : null;
}

export async function ensureKavenixBlogImageSchema() {
  if (!global.__kavenixBlogImageSchemaPromise) {
    global.__kavenixBlogImageSchemaPromise = (async () => {
      const pool = getDbPool();

      await pool.query(`
        CREATE TABLE IF NOT EXISTS marketing_blog_images (
          storage_key VARCHAR(512) NOT NULL,
          public_url VARCHAR(2048) NOT NULL,
          draft_token CHAR(36) NOT NULL,
          post_id BIGINT UNSIGNED NULL,
          created_by_email VARCHAR(320) NOT NULL,
          storage_status ENUM('active', 'delete_pending') NOT NULL DEFAULT 'active',
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (storage_key),
          KEY idx_marketing_blog_images_post_status (post_id, storage_status),
          KEY idx_marketing_blog_images_draft_status (draft_token, storage_status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      await pool.query(`
        CREATE TABLE IF NOT EXISTS marketing_blog_image_cleanup_queue (
          storage_key VARCHAR(512) NOT NULL,
          attempts INT UNSIGNED NOT NULL DEFAULT 0,
          last_error VARCHAR(500) NULL,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (storage_key),
          KEY idx_marketing_blog_image_cleanup_updated_at (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
    })().catch((error) => {
      global.__kavenixBlogImageSchemaPromise = undefined;
      throw error;
    });
  }

  await global.__kavenixBlogImageSchemaPromise;
}

export async function uploadKavenixBlogAsset(input: {
  body: Buffer;
  contentType: string;
  draftToken: string;
  email: string;
  kind: KavenixBlogAssetKind;
  sizeBytes: number;
}) {
  const draftToken = normalizeKavenixBlogImageDraftToken(input.draftToken);

  if (!draftToken) {
    throw new Error("blog-image-draft-token-invalid");
  }

  const asset = input.kind === "audio"
    ? ensureAudioFile(input)
    : input.kind === "video"
      ? ensureVideoFile(input)
      : input.kind === "file"
        ? ensureDocumentFile(input)
        : ensureImageFile(input);
  await ensureKavenixBlogImageSchema();

  const now = new Date();
  const key = [
    getStoragePrefix(),
    `${now.getUTCFullYear()}`,
    `${now.getUTCMonth() + 1}`.padStart(2, "0"),
    `${now.getUTCDate()}`.padStart(2, "0"),
    ...(input.kind === "image" ? [] : [input.kind]),
    `${randomUUID()}.${asset.extension}`,
  ].join("/");
  const uploaded = await uploadBufferToObjectStorage({
    body: input.body,
    cacheControl: "public, max-age=31536000, immutable",
    contentType: asset.contentType,
    key,
  });

  try {
    await getDbPool().query(
      `
        INSERT INTO marketing_blog_images (
          storage_key,
          public_url,
          draft_token,
          created_by_email
        ) VALUES (?, ?, ?, ?)
      `,
      [uploaded.key, uploaded.publicUrl, draftToken, input.email.trim().toLowerCase()],
    );
  } catch (error) {
    await deleteObjectFromObjectStorage({ key: uploaded.key }).catch(() => undefined);
    throw error;
  }

  return {
    publicUrl: uploaded.publicUrl,
  };
}

function storageKeyFromPublicUrl(value: string) {
  try {
    const imageUrl = new URL(value);
    const storagePrefix = getStoragePrefix();
    const publicPrefixUrl = new URL(buildObjectStoragePublicUrl(`${storagePrefix}/`));

    if (imageUrl.origin !== publicPrefixUrl.origin || !imageUrl.pathname.startsWith(publicPrefixUrl.pathname)) {
      return null;
    }

    const suffix = imageUrl.pathname.slice(publicPrefixUrl.pathname.length);
    const decodedSegments = suffix.split("/").map((segment) => decodeURIComponent(segment));

    if (
      decodedSegments.length === 0 ||
      decodedSegments.some((segment) => !segment || segment === "." || segment === ".." || segment.includes("/"))
    ) {
      return null;
    }

    return `${storagePrefix}/${decodedSegments.join("/")}`;
  } catch {
    return null;
  }
}

export function getKavenixBlogImageStorageKeys(contentMarkdown: string) {
  const keys = new Set<string>();

  for (const match of contentMarkdown.matchAll(MARKDOWN_IMAGE_PATTERN)) {
    const storageKey = storageKeyFromPublicUrl(match[1]);
    if (storageKey) keys.add(storageKey);
  }

  for (const match of contentMarkdown.matchAll(RICH_BLOCK_URL_PATTERN)) {
    try {
      const storageKey = storageKeyFromPublicUrl(decodeURIComponent(match[1]));
      if (storageKey) keys.add(storageKey);
    } catch {
      continue;
    }
  }

  return [...keys];
}

export async function reconcileKavenixBlogImages(
  queryable: PoolConnection,
  input: {
    contentMarkdown: string;
    draftToken: string | null;
    postId: number;
  },
) {
  const storageKeys = getKavenixBlogImageStorageKeys(input.contentMarkdown);
  const storedByKey = new Map<string, KavenixBlogImageRow>();
  const [postRows] = await queryable.query<KavenixBlogImageRow[]>(
    `
      SELECT storage_key, draft_token, post_id, storage_status
      FROM marketing_blog_images
      WHERE post_id = ?
        AND storage_status = 'active'
      FOR UPDATE
    `,
    [input.postId],
  );

  for (const row of postRows) {
    storedByKey.set(row.storage_key, row);
  }

  let draftRows: KavenixBlogImageRow[] = [];

  if (input.draftToken) {
    const [rows] = await queryable.query<KavenixBlogImageRow[]>(
      `
        SELECT storage_key, draft_token, post_id, storage_status
        FROM marketing_blog_images
        WHERE post_id IS NULL
          AND draft_token = ?
          AND storage_status = 'active'
        FOR UPDATE
      `,
      [input.draftToken],
    );
    draftRows = rows;

    for (const row of draftRows) {
      storedByKey.set(row.storage_key, row);
    }
  }

  for (const storageKey of storageKeys) {
    if (!storedByKey.has(storageKey)) {
      throw new Error("blog-image-not-available");
    }
  }

  const newlyAttachedKeys = draftRows
    .map((row) => row.storage_key)
    .filter((storageKey) => storageKeys.includes(storageKey));

  if (newlyAttachedKeys.length > 0) {
    await queryable.query(
      `
        UPDATE marketing_blog_images
        SET post_id = ?, updated_at = NOW()
        WHERE storage_key IN (${newlyAttachedKeys.map(() => "?").join(", ")})
      `,
      [input.postId, ...newlyAttachedKeys],
    );
  }

  const removedKeys = [...postRows, ...draftRows]
    .map((row) => row.storage_key)
    .filter((storageKey) => !storageKeys.includes(storageKey));

  if (removedKeys.length > 0) {
    await queryable.query(
      `
        UPDATE marketing_blog_images
        SET storage_status = 'delete_pending', post_id = NULL, updated_at = NOW()
        WHERE storage_key IN (${removedKeys.map(() => "?").join(", ")})
      `,
      removedKeys,
    );
    await enqueueImageCleanup(queryable, removedKeys);
  }
}

export async function queueKavenixBlogPostImageDeletion(queryable: PoolConnection, postId: number) {
  const [rows] = await queryable.query<KavenixBlogImageRow[]>(
    `
      SELECT storage_key, draft_token, post_id, storage_status
      FROM marketing_blog_images
      WHERE post_id = ?
        AND storage_status = 'active'
      FOR UPDATE
    `,
    [postId],
  );
  const storageKeys = rows.map((row) => row.storage_key);

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

  await queryable.query(
    `
      UPDATE marketing_blog_images
      SET storage_status = 'delete_pending', post_id = NULL, updated_at = NOW()
      WHERE storage_key IN (${storageKeys.map(() => "?").join(", ")})
    `,
    storageKeys,
  );
  await enqueueImageCleanup(queryable, storageKeys);
}

export async function processKavenixBlogImageCleanup() {
  await ensureKavenixBlogImageSchema();
  const pool = getDbPool();
  const [rows] = await pool.query<KavenixBlogImageCleanupRow[]>(`
    SELECT storage_key
    FROM marketing_blog_image_cleanup_queue
    ORDER BY created_at ASC
    LIMIT 64
  `);

  for (const row of rows) {
    try {
      await deleteObjectFromObjectStorage({ key: row.storage_key });
      const connection = await pool.getConnection();

      try {
        await connection.beginTransaction();
        await connection.query(
          "DELETE FROM marketing_blog_images WHERE storage_key = ? AND storage_status = 'delete_pending'",
          [row.storage_key],
        );
        await connection.query("DELETE FROM marketing_blog_image_cleanup_queue WHERE storage_key = ?", [row.storage_key]);
        await connection.commit();
      } catch (error) {
        await connection.rollback();
        throw error;
      } finally {
        connection.release();
      }
    } catch (error) {
      const message = error instanceof Error ? error.message.slice(0, 500) : "object-storage-delete-failed";
      await pool.query(
        `
          UPDATE marketing_blog_image_cleanup_queue
          SET attempts = attempts + 1, last_error = ?, updated_at = NOW()
          WHERE storage_key = ?
        `,
        [message, row.storage_key],
      );
    }
  }
}