import type { ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { blogPosts, blogPostToMarkdown, type BlogFaq } from "@/lib/blog-posts";
import { getDbPool } from "@/lib/db";

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

export type MarketingBlogStatus = "draft" | "published";

export type MarketingBlogPost = {
  authorName: string;
  contentMarkdown: string;
  createdAt: string;
  excerpt: string;
  faqs: BlogFaq[];
  id: number;
  keywords: string[];
  publishedAt: string | null;
  seoDescription: string;
  slug: string;
  status: MarketingBlogStatus;
  title: string;
  updatedAt: string;
};

export type MarketingBlogPostInput = {
  contentMarkdown?: unknown;
  excerpt?: unknown;
  keywords?: unknown;
  seoDescription?: unknown;
  slug?: unknown;
  status?: unknown;
  title?: unknown;
};

type MarketingBlogPostRow = RowDataPacket & {
  author_name: string;
  content_markdown: string;
  created_at: Date;
  excerpt: string;
  faq_json: string | null;
  id: number;
  keywords_json: string | null;
  published_at: Date | null;
  seo_description: string;
  slug: string;
  status: MarketingBlogStatus;
  title: string;
  updated_at: Date;
};

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

function parseStringList(value: string | null) {
  if (!value) {
    return [];
  }

  try {
    const parsed = JSON.parse(value);
    return Array.isArray(parsed)
      ? parsed.filter((item): item is string => typeof item === "string")
      : [];
  } catch {
    return [];
  }
}

function parseFaqs(value: string | null) {
  if (!value) {
    return [];
  }

  try {
    const parsed = JSON.parse(value);

    if (!Array.isArray(parsed)) {
      return [];
    }

    return parsed.flatMap((item) => {
      if (
        !item ||
        typeof item !== "object" ||
        typeof item.question !== "string" ||
        typeof item.answer !== "string"
      ) {
        return [];
      }

      return [
        {
          answer: item.answer,
          question: item.question,
        },
      ];
    });
  } catch {
    return [];
  }
}

function mapRow(row: MarketingBlogPostRow): MarketingBlogPost {
  return {
    authorName: row.author_name,
    contentMarkdown: row.content_markdown,
    createdAt: toIsoString(row.created_at) ?? new Date(0).toISOString(),
    excerpt: row.excerpt,
    faqs: parseFaqs(row.faq_json),
    id: Number(row.id),
    keywords: parseStringList(row.keywords_json),
    publishedAt: toIsoString(row.published_at),
    seoDescription: row.seo_description,
    slug: row.slug,
    status: row.status,
    title: row.title,
    updatedAt: toIsoString(row.updated_at) ?? new Date(0).toISOString(),
  };
}

function summarizeMarkdown(value: string) {
  const plain = value
    .replace(/```[\s\S]*?```/g, "")
    .replace(/^#{1,6}\s+/gm, "")
    .replace(/[>*_`|]/g, "")
    .replace(/\s+/g, " ")
    .trim();

  return plain.length > 180 ? `${plain.slice(0, 177).trim()}...` : plain;
}

function normalizeSlug(value: unknown) {
  const slug = typeof value === "string" ? value.trim().toLowerCase() : "";

  if (!/^[a-z0-9]+(?:-[a-z0-9]+)*$/.test(slug) || slug.length < 3 || slug.length > 160) {
    throw new Error("invalid-blog-slug");
  }

  return slug;
}

function normalizeText(value: unknown, field: "title" | "excerpt" | "seoDescription" | "contentMarkdown") {
  const normalized = typeof value === "string" ? value.trim() : "";
  const maxLength = {
    title: 255,
    excerpt: 500,
    seoDescription: 320,
    contentMarkdown: 100_000,
  }[field];

  if (!normalized || normalized.length > maxLength) {
    throw new Error(`invalid-blog-${field}`);
  }

  return normalized;
}

function normalizeKeywords(value: unknown) {
  const values = Array.isArray(value)
    ? value
    : typeof value === "string"
      ? value.split(",")
      : [];

  const keywords = Array.from(
    new Set(
      values
        .filter((item): item is string => typeof item === "string")
        .map((item) => item.trim())
        .filter(Boolean)
        .map((item) => item.slice(0, 80)),
    ),
  ).slice(0, 15);

  return keywords;
}

function normalizeInput(input: MarketingBlogPostInput) {
  const contentMarkdown = normalizeText(input.contentMarkdown, "contentMarkdown");
  const title = normalizeText(input.title, "title");
  const excerptInput = typeof input.excerpt === "string" ? input.excerpt.trim() : "";
  const descriptionInput = typeof input.seoDescription === "string" ? input.seoDescription.trim() : "";
  const excerpt = excerptInput ? normalizeText(excerptInput, "excerpt") : summarizeMarkdown(contentMarkdown);
  const seoDescription = descriptionInput
    ? normalizeText(descriptionInput, "seoDescription")
    : excerpt;

  return {
    contentMarkdown,
    excerpt,
    keywords: normalizeKeywords(input.keywords),
    seoDescription,
    slug: normalizeSlug(input.slug),
    status: input.status === "published" ? "published" : "draft" as MarketingBlogStatus,
    title,
  };
}

async function seedInitialPosts() {
  const pool = getDbPool();

  for (const post of blogPosts) {
    await pool.query<ResultSetHeader>(
      `
        INSERT IGNORE INTO marketing_blog_posts (
          slug,
          title,
          excerpt,
          seo_description,
          keywords_json,
          faq_json,
          content_markdown,
          status,
          author_name,
          created_by_email,
          published_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, 'published', '오피셜메일 콘텐츠팀', 'admin@officialsite.kr', ?)
      `,
      [
        post.slug,
        post.title,
        post.summary,
        post.description,
        JSON.stringify(post.keywords),
        JSON.stringify(post.faqs),
        blogPostToMarkdown(post),
        new Date(post.publishedAt),
      ],
    );
  }
}

export async function ensureMarketingBlogSchema() {
  if (!global.__marketingBlogSchemaPromise) {
    global.__marketingBlogSchemaPromise = (async () => {
      await getDbPool().query(`
        CREATE TABLE IF NOT EXISTS marketing_blog_posts (
          id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
          slug VARCHAR(160) NOT NULL,
          title VARCHAR(255) NOT NULL,
          excerpt VARCHAR(500) NOT NULL,
          seo_description VARCHAR(320) NOT NULL,
          keywords_json LONGTEXT NULL,
          faq_json LONGTEXT NULL,
          content_markdown LONGTEXT NOT NULL,
          status ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
          author_name VARCHAR(120) NOT NULL DEFAULT '오피셜메일 콘텐츠팀',
          created_by_email VARCHAR(191) NULL,
          published_at DATETIME NULL,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (id),
          UNIQUE KEY uq_marketing_blog_posts_slug (slug),
          KEY idx_marketing_blog_posts_status_published_at (status, published_at),
          KEY idx_marketing_blog_posts_updated_at (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      await seedInitialPosts();
    })().catch((error) => {
      global.__marketingBlogSchemaPromise = undefined;
      throw error;
    });
  }

  await global.__marketingBlogSchemaPromise;
}

export async function getPublishedMarketingBlogPosts() {
  await ensureMarketingBlogSchema();

  const [rows] = await getDbPool().query<MarketingBlogPostRow[]>(`
    SELECT
      id,
      slug,
      title,
      excerpt,
      seo_description,
      keywords_json,
      faq_json,
      content_markdown,
      status,
      author_name,
      published_at,
      created_at,
      updated_at
    FROM marketing_blog_posts
    WHERE status = 'published'
      AND published_at IS NOT NULL
      AND published_at <= NOW()
    ORDER BY published_at DESC, id DESC
  `);

  return rows.map(mapRow);
}

export async function getPublishedMarketingBlogPost(slug: string) {
  await ensureMarketingBlogSchema();

  const [rows] = await getDbPool().query<MarketingBlogPostRow[]>(
    `
      SELECT
        id,
        slug,
        title,
        excerpt,
        seo_description,
        keywords_json,
        faq_json,
        content_markdown,
        status,
        author_name,
        published_at,
        created_at,
        updated_at
      FROM marketing_blog_posts
      WHERE slug = ?
        AND status = 'published'
        AND published_at IS NOT NULL
        AND published_at <= NOW()
      LIMIT 1
    `,
    [slug],
  );

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

export async function getKavenixMarketingBlogPosts() {
  await ensureMarketingBlogSchema();

  const [rows] = await getDbPool().query<MarketingBlogPostRow[]>(`
    SELECT
      id,
      slug,
      title,
      excerpt,
      seo_description,
      keywords_json,
      faq_json,
      content_markdown,
      status,
      author_name,
      published_at,
      created_at,
      updated_at
    FROM marketing_blog_posts
    ORDER BY CASE WHEN status = 'draft' THEN 0 ELSE 1 END, updated_at DESC, id DESC
  `);

  return rows.map(mapRow);
}

export async function getKavenixMarketingBlogPostCount() {
  await ensureMarketingBlogSchema();

  const [rows] = await getDbPool().query<(RowDataPacket & { count: number })[]>(
    "SELECT COUNT(*) AS count FROM marketing_blog_posts",
  );

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

async function getMarketingBlogPostById(id: number) {
  const [rows] = await getDbPool().query<MarketingBlogPostRow[]>(
    `
      SELECT
        id,
        slug,
        title,
        excerpt,
        seo_description,
        keywords_json,
        faq_json,
        content_markdown,
        status,
        author_name,
        published_at,
        created_at,
        updated_at
      FROM marketing_blog_posts
      WHERE id = ?
      LIMIT 1
    `,
    [id],
  );

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

export async function createMarketingBlogPost(input: MarketingBlogPostInput, authorEmail: string) {
  await ensureMarketingBlogSchema();
  const post = normalizeInput(input);
  const [result] = await getDbPool().query<ResultSetHeader>(
    `
      INSERT INTO marketing_blog_posts (
        slug,
        title,
        excerpt,
        seo_description,
        keywords_json,
        content_markdown,
        status,
        author_name,
        created_by_email,
        published_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, '오피셜메일 콘텐츠팀', ?, CASE WHEN ? = 'published' THEN NOW() ELSE NULL END)
    `,
    [
      post.slug,
      post.title,
      post.excerpt,
      post.seoDescription,
      JSON.stringify(post.keywords),
      post.contentMarkdown,
      post.status,
      authorEmail,
      post.status,
    ],
  );

  return getMarketingBlogPostById(Number(result.insertId));
}

export async function updateMarketingBlogPost(
  id: number,
  input: MarketingBlogPostInput,
) {
  await ensureMarketingBlogSchema();
  const post = normalizeInput(input);
  const [result] = await getDbPool().query<ResultSetHeader>(
    `
      UPDATE marketing_blog_posts
      SET
        slug = ?,
        title = ?,
        excerpt = ?,
        seo_description = ?,
        keywords_json = ?,
        content_markdown = ?,
        status = ?,
        published_at = CASE
          WHEN ? = 'published' THEN COALESCE(published_at, NOW())
          ELSE NULL
        END
      WHERE id = ?
    `,
    [
      post.slug,
      post.title,
      post.excerpt,
      post.seoDescription,
      JSON.stringify(post.keywords),
      post.contentMarkdown,
      post.status,
      post.status,
      id,
    ],
  );

  if (result.affectedRows === 0) {
    throw new Error("blog-post-not-found");
  }

  return getMarketingBlogPostById(id);
}

export async function deleteMarketingBlogPost(id: number) {
  await ensureMarketingBlogSchema();
  const [result] = await getDbPool().query<ResultSetHeader>(
    "DELETE FROM marketing_blog_posts WHERE id = ?",
    [id],
  );

  if (result.affectedRows === 0) {
    throw new Error("blog-post-not-found");
  }
}