import "server-only";

import { spawn } from "node:child_process";
import { promises as fs } from "node:fs";
import path from "node:path";

import type { BlogPost, BlogPostMeta } from "@/lib/blog";
import { getAllBlogSlugs, getBlogPost } from "@/lib/blog";
import { deleteBlogPost as deleteMarkdownPost, upsertBlogPost as upsertMarkdownPost } from "@/lib/admin/blogAdmin";

export type AdminBlogPostUpsert = {
  slug: string;
  title: string;
  date: string; // YYYY-MM-DD preferred
  excerpt: string;
  coverImage?: string;
  tags?: string; // comma separated
  content: string; // markdown body
};

export type AdminImageMeta = {
  fileName: string;
  size: number;
  updatedAt: string;
  mimeType?: string;
};

export type AdminImageObject = {
  fileName: string;
  mimeType: string;
  bytes: Uint8Array;
};

type DbPostRow = {
  slug: string;
  title: string;
  date: string;
  excerpt: string;
  coverImage: string | null;
  tags: string | null;
  content: string;
  createdAt: string;
  updatedAt: string;
};

function todayLocalYYYYMMDD(): string {
  const d = new Date();
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, "0");
  const day = String(d.getDate()).padStart(2, "0");
  return `${y}-${m}-${day}`;
}

function normalizeSlug(input: string): string {
  const s = input
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "");
  if (!s) throw new Error("Invalid slug.");
  if (s.includes("..")) throw new Error("Invalid slug.");
  return s;
}

function normalizeDate(input: string): string {
  const raw = (input || "").trim();
  if (!raw) return todayLocalYYYYMMDD();
  if (/^\d{4}-\d{2}-\d{2}$/.test(raw)) return raw;
  const d = new Date(raw);
  if (Number.isNaN(d.getTime())) throw new Error("Invalid date.");
  return d.toISOString().slice(0, 10);
}

function sqlString(value: string): string {
  return `'${value.replace(/'/g, "''")}'`;
}

function validateImageFileName(fileName: string): string {
  const name = fileName.trim();
  if (!name) throw new Error("Missing filename.");
  if (name.includes("..")) throw new Error("Invalid filename.");
  if (name.includes("/") || name.includes("\\")) throw new Error("Invalid filename.");
  if (path.basename(name) !== name) throw new Error("Invalid filename.");

  const lower = name.toLowerCase();
  const ok =
    lower.endsWith(".png") ||
    lower.endsWith(".jpg") ||
    lower.endsWith(".jpeg") ||
    lower.endsWith(".webp") ||
    lower.endsWith(".gif") ||
    lower.endsWith(".svg");
  if (!ok) throw new Error("Invalid image type.");

  return name;
}

function normalizeImageFileNameForUpload(input: string): string {
  const raw = input.trim();
  if (!raw) throw new Error("Invalid filename.");
  const base = path.basename(raw);
  const cleaned = base
    .replace(/[/\\]+/g, "-")
    .replace(/\s+/g, "-")
    .replace(/[^a-zA-Z0-9._-]/g, "");
  if (!cleaned) throw new Error("Invalid filename.");
  // Reuse strict validation (extension etc).
  return validateImageFileName(cleaned);
}

function guessMimeType(fileName: string, fallback?: string): string {
  const lower = fileName.toLowerCase();
  if (lower.endsWith(".png")) return "image/png";
  if (lower.endsWith(".jpg") || lower.endsWith(".jpeg")) return "image/jpeg";
  if (lower.endsWith(".webp")) return "image/webp";
  if (lower.endsWith(".gif")) return "image/gif";
  if (lower.endsWith(".svg")) return "image/svg+xml";
  return fallback || "application/octet-stream";
}

function getDbPath(): string {
  const fromEnv = (process.env.CMS_DB_PATH || "").trim();
  if (fromEnv) return fromEnv;
  return path.join(process.cwd(), "content", "cms.sqlite");
}

function shouldWritePostsToDisk(): boolean {
  const raw = String(process.env.CMS_WRITE_POSTS_TO_DISK || "").trim().toLowerCase();
  if (!raw) return true;
  return !(raw === "0" || raw === "false" || raw === "no" || raw === "off");
}

let schemaPromise: Promise<string> | null = null;
let seedPostsPromise: Promise<void> | null = null;
let seedImagesPromise: Promise<void> | null = null;
let postsSeeded = false;
let imagesSeeded = false;

async function runSql(dbPath: string, sql: string, opts?: { json?: boolean }): Promise<string> {
  await fs.mkdir(path.dirname(dbPath), { recursive: true });

  const BUSY_TIMEOUT_MS = 15000;
  const MAX_RETRIES = 8;

  const normalizeJsonOutput = (out: string): string => {
    const s = String(out || "").trim();
    if (!s) return "[]";
    try {
      JSON.parse(s);
      return s;
    } catch {
      // sqlite3 can sometimes emit multiple JSON values when multiple statements produce output.
      // Keep the last JSON block to make callers resilient.
      const idxArr = s.lastIndexOf("\n[");
      const idxObj = s.lastIndexOf("\n{");
      const idx = Math.max(idxArr, idxObj);
      if (idx !== -1) {
        const candidate = s.slice(idx + 1).trim();
        try {
          JSON.parse(candidate);
          return candidate;
        } catch {
          // fall through
        }
      }
      return s;
    }
  };

  for (let attempt = 0; attempt <= MAX_RETRIES; attempt++) {
    try {
      // eslint-disable-next-line no-await-in-loop
      const out = await new Promise<string>((resolve, reject) => {
        const args: string[] = [];
        if (opts?.json) args.push("-json");
        args.push("-cmd", `.timeout ${BUSY_TIMEOUT_MS}`, dbPath);
        const child = spawn("sqlite3", args, { stdio: ["pipe", "pipe", "pipe"] });

        let out = "";
        let err = "";
        child.stdout.on("data", (d) => (out += String(d)));
        child.stderr.on("data", (d) => (err += String(d)));
        child.on("error", reject);
        child.on("close", (code) => {
          if (code === 0) return resolve(out);
          reject(new Error(err || `sqlite3 exited with code ${code}`));
        });

        child.stdin.write(sql);
        child.stdin.end();
      });
      return opts?.json ? normalizeJsonOutput(out) : out;
    } catch (err: any) {
      const msg = String(err?.message || err || "");
      const isBusy =
        /database is locked/i.test(msg) ||
        /\bSQLITE_BUSY\b/i.test(msg) ||
        /\bbusy\b/i.test(msg);
      if (!isBusy || attempt === MAX_RETRIES) throw err;

      const delayMs = Math.min(1500, 75 * 2 ** attempt);
      // eslint-disable-next-line no-await-in-loop
      await new Promise((r) => setTimeout(r, delayMs));
    }
  }

  // Unreachable, but TypeScript wants a return.
  return "";
}

async function ensureSchema(): Promise<string> {
  if (schemaPromise) return schemaPromise;

  schemaPromise = (async () => {
    const dbPath = getDbPath();
    const schema = `
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS blog_posts (
  slug TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  date TEXT NOT NULL,
  excerpt TEXT NOT NULL DEFAULT '',
  cover_image TEXT,
  tags TEXT,
  content_markdown TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS blog_posts_date_idx ON blog_posts(date);

CREATE TABLE IF NOT EXISTS blog_images (
  file_name TEXT PRIMARY KEY,
  mime_type TEXT NOT NULL,
  size_bytes INTEGER NOT NULL,
  data_base64 TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS blog_images_updated_idx ON blog_images(updated_at);
`;
    await runSql(dbPath, schema);
    return dbPath;
  })();

  try {
    return await schemaPromise;
  } catch (e) {
    schemaPromise = null;
    throw e;
  }
}

async function countPosts(dbPath: string): Promise<number> {
  const out = await runSql(dbPath, "SELECT COUNT(*) AS c FROM blog_posts;\n", { json: true });
  try {
    const parsed = JSON.parse(out || "[]");
    const c = Number(parsed?.[0]?.c ?? 0);
    return Number.isFinite(c) ? c : 0;
  } catch {
    return 0;
  }
}

async function countImages(dbPath: string): Promise<number> {
  const out = await runSql(dbPath, "SELECT COUNT(*) AS c FROM blog_images;\n", { json: true });
  try {
    const parsed = JSON.parse(out || "[]");
    const c = Number(parsed?.[0]?.c ?? 0);
    return Number.isFinite(c) ? c : 0;
  } catch {
    return 0;
  }
}

async function tryInsertImageObject(dbPath: string, input: AdminImageObject): Promise<boolean> {
  const fileName = validateImageFileName(input.fileName);
  const mimeType = guessMimeType(fileName, input.mimeType);
  const bytes = input.bytes || new Uint8Array();
  const base64 = Buffer.from(bytes).toString("base64");
  const size = bytes.byteLength;
  const now = new Date().toISOString();

  const sql = `
INSERT INTO blog_images (file_name, mime_type, size_bytes, data_base64, created_at, updated_at)
VALUES (
  ${sqlString(fileName)},
  ${sqlString(mimeType)},
  ${String(size)},
  ${sqlString(base64)},
  ${sqlString(now)},
  ${sqlString(now)}
);
`;
  try {
    await runSql(dbPath, sql);
    return true;
  } catch (err: any) {
    const msg = String(err?.message || err || "");
    const isUnique = /UNIQUE constraint failed:\s*blog_images\.file_name/i.test(msg);
    if (isUnique) return false;
    throw err;
  }
}

async function seedFromMarkdownIfEmpty(dbPath: string): Promise<void> {
  if (postsSeeded) return;
  if (seedPostsPromise) return seedPostsPromise;

  seedPostsPromise = (async () => {
    const n = await countPosts(dbPath);
    if (n > 0) {
      postsSeeded = true;
      return;
    }

    const slugs = await getAllBlogSlugs();
    for (const slug of slugs) {
      const post = await getBlogPost(slug);
      if (!post) continue;
      const tags = post.tags?.join(", ") || "";
      await upsertPost(
        {
          slug: post.slug,
          title: post.title,
          date: post.date,
          excerpt: post.excerpt,
          coverImage: post.coverImage,
          tags,
          content: post.content,
        },
        { skipMarkdownWrite: true }
      );
    }
    postsSeeded = true;
  })();

  try {
    await seedPostsPromise;
  } finally {
    seedPostsPromise = null;
  }
}

async function seedImagesFromDiskIfEmpty(dbPath: string): Promise<void> {
  if (imagesSeeded) return;
  if (seedImagesPromise) return seedImagesPromise;

  seedImagesPromise = (async () => {
    const n = await countImages(dbPath);
    if (n > 0) {
      imagesSeeded = true;
      return;
    }

    const dir = path.join(process.cwd(), "content", "blog", "images");
    let entries: string[] = [];
    try {
      entries = await fs.readdir(dir);
    } catch {
      entries = [];
    }

    for (const fileName of entries) {
      const safe = (() => {
        try {
          return validateImageFileName(fileName);
        } catch {
          return null;
        }
      })();
      if (!safe) continue;
      const full = path.join(dir, safe);
      try {
        const bytes = await fs.readFile(full);
        await upsertImage({ fileName: safe, mimeType: guessMimeType(safe), bytes: new Uint8Array(bytes) });
      } catch {
        // ignore
      }
    }
    imagesSeeded = true;
  })();

  try {
    await seedImagesPromise;
  } finally {
    seedImagesPromise = null;
  }
}

function rowToMeta(r: any): BlogPostMeta {
  return {
    slug: String(r.slug),
    title: String(r.title),
    date: String(r.date),
    excerpt: String(r.excerpt || ""),
    coverImage: r.coverImage ? String(r.coverImage) : undefined,
    tags: r.tags ? String(r.tags).split(",").map((t: string) => t.trim()).filter(Boolean) : undefined,
  };
}

function rowToPost(r: any): BlogPost {
  const meta = rowToMeta(r);
  return {
    ...meta,
    readingTimeMinutes: undefined,
    content: String(r.content || ""),
  };
}

export async function listPosts(opts?: { limit?: number }): Promise<BlogPostMeta[]> {
  const dbPath = await ensureSchema();
  await seedFromMarkdownIfEmpty(dbPath);

  const limit = Number(opts?.limit || 0);
  const limitSql = limit > 0 ? `\nLIMIT ${Math.floor(limit)}` : "";

  const sql = `
SELECT
  slug,
  title,
  date,
  excerpt,
  cover_image AS coverImage,
  tags
FROM blog_posts
ORDER BY date DESC, updated_at DESC${limitSql};
`;
  const out = await runSql(dbPath, sql, { json: true });
  const rows = JSON.parse(out || "[]") as any[];
  return rows.map(rowToMeta);
}

export async function getPost(slug: string): Promise<BlogPost | null> {
  const dbPath = await ensureSchema();
  await seedFromMarkdownIfEmpty(dbPath);

  const s = normalizeSlug(slug);
  const sql = `
SELECT
  slug,
  title,
  date,
  excerpt,
  cover_image AS coverImage,
  tags,
  content_markdown AS content,
  created_at AS createdAt,
  updated_at AS updatedAt
FROM blog_posts
WHERE slug = ${sqlString(s)}
LIMIT 1;
`;
  const out = await runSql(dbPath, sql, { json: true });
  const rows = JSON.parse(out || "[]") as any[];
  if (!rows.length) return null;
  return rowToPost(rows[0]);
}

export async function upsertPost(input: AdminBlogPostUpsert, opts?: { skipMarkdownWrite?: boolean }): Promise<{ slug: string }> {
  const dbPath = await ensureSchema();

  const slug = normalizeSlug(input.slug);
  const title = (input.title || "").trim() || slug;
  const date = normalizeDate(input.date);
  const excerpt = (input.excerpt || "").trim();
  const coverImage = (input.coverImage || "").trim();
  const tags = (input.tags || "").trim();
  const content = (input.content || "").replace(/\r\n/g, "\n").trim();

  const now = new Date().toISOString();
  const sql = `
INSERT INTO blog_posts (slug, title, date, excerpt, cover_image, tags, content_markdown, created_at, updated_at)
VALUES (
  ${sqlString(slug)},
  ${sqlString(title)},
  ${sqlString(date)},
  ${sqlString(excerpt)},
  ${coverImage ? sqlString(coverImage) : "NULL"},
  ${tags ? sqlString(tags) : "NULL"},
  ${sqlString(content)},
  ${sqlString(now)},
  ${sqlString(now)}
)
ON CONFLICT(slug) DO UPDATE SET
  title=excluded.title,
  date=excluded.date,
  excerpt=excluded.excerpt,
  cover_image=excluded.cover_image,
  tags=excluded.tags,
  content_markdown=excluded.content_markdown,
  updated_at=excluded.updated_at;
`;
  await runSql(dbPath, sql);

  if (!opts?.skipMarkdownWrite && shouldWritePostsToDisk()) {
    try {
      await upsertMarkdownPost({
        slug,
        title,
        date,
        excerpt,
        coverImage: coverImage || undefined,
        tags: tags || undefined,
        content,
      });
    } catch {
      // ignore (hosted environments may have read-only filesystems)
    }
  }

  return { slug };
}

export async function deletePost(slug: string): Promise<void> {
  const dbPath = await ensureSchema();
  const s = normalizeSlug(slug);
  await runSql(dbPath, `DELETE FROM blog_posts WHERE slug=${sqlString(s)};\n`);
  if (shouldWritePostsToDisk()) {
    try {
      await deleteMarkdownPost(s);
    } catch {
      // ignore
    }
  }
}

export async function listImages(): Promise<AdminImageMeta[]> {
  const dbPath = await ensureSchema();
  await seedImagesFromDiskIfEmpty(dbPath);

  const sql = `
SELECT
  file_name AS fileName,
  size_bytes AS size,
  mime_type AS mimeType,
  updated_at AS updatedAt
FROM blog_images
ORDER BY updated_at DESC;
`;
  const out = await runSql(dbPath, sql, { json: true });
  const rows = JSON.parse(out || "[]") as any[];
  return rows.map((r) => ({
    fileName: String(r.fileName),
    size: Number(r.size || 0),
    updatedAt: String(r.updatedAt || new Date(0).toISOString()),
    mimeType: r.mimeType ? String(r.mimeType) : undefined,
  }));
}

export async function getImage(fileName: string): Promise<AdminImageObject | null> {
  const dbPath = await ensureSchema();
  await seedImagesFromDiskIfEmpty(dbPath);

  const safe = validateImageFileName(fileName);
  const sql = `
SELECT
  file_name AS fileName,
  mime_type AS mimeType,
  data_base64 AS dataBase64
FROM blog_images
WHERE file_name = ${sqlString(safe)}
LIMIT 1;
`;
  const out = await runSql(dbPath, sql, { json: true });
  const rows = JSON.parse(out || "[]") as any[];
  if (!rows.length) return null;

  const mimeType = String(rows[0].mimeType || guessMimeType(safe));
  const dataBase64 = String(rows[0].dataBase64 || "");
  if (!dataBase64) return null;
  const buf = Buffer.from(dataBase64, "base64");
  return { fileName: safe, mimeType, bytes: new Uint8Array(buf) };
}

export async function upsertImage(input: AdminImageObject): Promise<{ fileName: string }> {
  const dbPath = await ensureSchema();
  const fileName = validateImageFileName(input.fileName);
  const mimeType = guessMimeType(fileName, input.mimeType);
  const bytes = input.bytes || new Uint8Array();
  const base64 = Buffer.from(bytes).toString("base64");
  const size = bytes.byteLength;
  const now = new Date().toISOString();

  const sql = `
INSERT INTO blog_images (file_name, mime_type, size_bytes, data_base64, created_at, updated_at)
VALUES (
  ${sqlString(fileName)},
  ${sqlString(mimeType)},
  ${String(size)},
  ${sqlString(base64)},
  ${sqlString(now)},
  ${sqlString(now)}
)
ON CONFLICT(file_name) DO UPDATE SET
  mime_type=excluded.mime_type,
  size_bytes=excluded.size_bytes,
  data_base64=excluded.data_base64,
  updated_at=excluded.updated_at;
`;
  await runSql(dbPath, sql);
  return { fileName };
}

export async function saveImageObject(input: { desiredFileName: string; mimeType?: string; bytes: Uint8Array }): Promise<{ fileName: string }> {
  const dbPath = await ensureSchema();
  await seedImagesFromDiskIfEmpty(dbPath);

  const normalized = normalizeImageFileNameForUpload(input.desiredFileName);
  const ext = path.extname(normalized);
  const base = path.basename(normalized, ext);

  for (let i = 0; i < 1000; i++) {
    const fileName = i === 0 ? normalized : `${base}-${i}${ext}`;
    const inserted = await tryInsertImageObject(dbPath, {
      fileName,
      mimeType: input.mimeType || guessMimeType(fileName),
      bytes: input.bytes,
    });
    if (inserted) return { fileName };
  }

  throw new Error("Unable to allocate a unique image filename.");
}

export async function deleteImage(fileName: string): Promise<void> {
  const dbPath = await ensureSchema();
  const safe = validateImageFileName(fileName);
  await runSql(dbPath, `DELETE FROM blog_images WHERE file_name=${sqlString(safe)};\n`);
}