import { cleanText, isTruthy } from "../lib/text";
import { slugify } from "../lib/slug";

type Db = Env["DB"];

export type PostRow = Record<string, unknown>;

export function blankPost(): PostRow {
  return {
    id: "",
    title: "",
    slug: "",
    excerpt: "",
    content: "",
    published: 1
  };
}

export async function getPostById(db: Db, id: number): Promise<PostRow | null> {
  return (await db
    .prepare("SELECT * FROM posts WHERE id = ? LIMIT 1")
    .bind(Number(id))
    .first()) as PostRow | null;
}

export async function readPost(db: Db, idOrSlug: string): Promise<PostRow | null> {
  if (!idOrSlug) return null;
  const trimmed = decodeURIComponent(String(idOrSlug)).trim();
  if (/^\d+$/.test(trimmed)) {
    return getPostById(db, Number(trimmed));
  }
  return (await db
    .prepare("SELECT * FROM posts WHERE slug = ? LIMIT 1")
    .bind(trimmed)
    .first()) as PostRow | null;
}

export async function listPosts(
  db: Db,
  { publishedOnly = true, q = "" } = {}
): Promise<PostRow[]> {
  const params: string[] = [];
  const where: string[] = [];
  if (publishedOnly) where.push("published = 1");
  if (q) {
    where.push("(title LIKE ? OR slug LIKE ? OR excerpt LIKE ? OR content LIKE ?)");
    const like = `%${q}%`;
    params.push(like, like, like, like);
  }
  const sql = `
    SELECT * FROM posts
    ${where.length ? `WHERE ${where.join(" AND ")}` : ""}
    ORDER BY published DESC, updated_at DESC, id DESC
  `;
  const statement = db.prepare(sql);
  const { results } = params.length
    ? await statement.bind(...params).all()
    : await statement.all();
  return (results || []) as PostRow[];
}

export async function uniqueSlug(
  db: Db,
  baseSlug: string,
  currentId: number | null
): Promise<string> {
  let slug = baseSlug || "post";
  let suffix = 2;

  while (true) {
    const existing = (await db
      .prepare("SELECT id FROM posts WHERE slug = ? LIMIT 1")
      .bind(slug)
      .first()) as { id: number } | null;
    if (!existing || Number(existing.id) === Number(currentId)) return slug;
    slug = `${baseSlug || "post"}-${suffix++}`;
  }
}

export async function createOrUpdatePost(
  db: Db,
  data: Record<string, unknown>,
  currentId: number | null
): Promise<PostRow | null> {
  const rawId = currentId != null ? Number(currentId) : NaN;
  const id = Number.isFinite(rawId) && rawId > 0 ? rawId : null;
  const title = cleanText(data.title);
  const content = cleanText(data.content);
  const excerpt = cleanText(data.excerpt);
  const slugBase = cleanText(data.slug) || title;
  const published = isTruthy(data.published) ? 1 : 0;

  if (!title || !content) {
    throw new Error("title 和 content 不能为空");
  }

  const slug = await uniqueSlug(db, slugify(slugBase), id);
  const now = new Date().toISOString();

  if (id) {
    await db
      .prepare(`
      UPDATE posts
      SET title = ?, slug = ?, excerpt = ?, content = ?, published = ?, updated_at = ?
      WHERE id = ?
    `)
      .bind(title, slug, excerpt, content, published, now, id)
      .run();
    return getPostById(db, id);
  }

  const result = await db
    .prepare(`
    INSERT INTO posts (title, slug, excerpt, content, published, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `)
    .bind(title, slug, excerpt, content, published, now, now)
    .run();
  const newId = result.meta?.last_row_id;
  if (newId == null) return null;
  return getPostById(db, Number(newId));
}

export async function bumpViewCount(db: Db, postId: number): Promise<void> {
  await db
    .prepare(
      "UPDATE posts SET view_count = COALESCE(view_count, 0) + 1 WHERE id = ?"
    )
    .bind(postId)
    .run();
}

export async function deletePost(db: Db, id: number): Promise<void> {
  await db.prepare("DELETE FROM posts WHERE id = ?").bind(id).run();
}
