import "server-only";

import { and, asc, eq, inArray, sql } from "drizzle-orm";

import { getDb } from "@/db";
import {
  artifactGroups,
  artifactConditions,
  artifactProfiles,
  artifactRecommendations,
  catalogArtifacts,
  catalogCharacters,
  catalogWeapons,
  constellationConditions,
  constellationRecommendations,
  extraSectionRows,
  extraSections,
  guides,
  guidePosterBleeding,
  guideSections,
  media,
  outboxEvents,
  teamCompositions,
  teamConditions,
  teamMembers,
  weaponRecommendations,
  weaponConditions,
} from "@/db/schema";
import {
  artifactSectionSchema,
  constellationSectionSchema,
  createExtraSectionSchema,
  createGuideSchema,
  reorderExtraSectionsSchema,
  saveExtraSectionSchema,
  slugFromCharacterName,
  teamSectionSchema,
  updateGuideSchema,
  weaponSectionSchema,
} from "@/lib/guides/schemas";
import { normalizeExtraRichText } from "@/lib/rich-text";
import type { AdminEditorScope } from "@/lib/events/invalidation";
import {
  writeAuditLog,
  type AuditAction,
  type AuditActor,
  type AuditMetadata,
} from "@/lib/audit-log";

const DEFAULT_SECTIONS = [
  { kind: "weapon", slug: "weapon", title: "Weapons", sortOrder: 0 },
  { kind: "artifact", slug: "artifact", title: "Artifacts & Stats", sortOrder: 1 },
  { kind: "constellations", slug: "constellations", title: "Constellations", sortOrder: 2 },
  { kind: "team", slug: "team", title: "Team Calc", sortOrder: 3 },
] as const;

export interface GuideEventDetails {
  operation: "overwrite";
  actorName: string;
}

export interface GuideMutationContext {
  actor: AuditActor;
  overwrite?: boolean;
}

function invalidationDetails(
  context: GuideMutationContext,
): GuideEventDetails | undefined {
  return context.overwrite
    ? { operation: "overwrite", actorName: context.actor.label.slice(0, 100) }
    : undefined;
}

async function auditGuide(
  tx: Parameters<Parameters<ReturnType<typeof getDb>["transaction"]>[0]>[0],
  context: GuideMutationContext,
  action: AuditAction,
  guide: { id: string; name: string; characterKey: string; version: number },
  scope?: AdminEditorScope,
  metadata: AuditMetadata = {},
) {
  await writeAuditLog(tx, context.actor, {
    action,
    targetType: "guide",
    targetId: guide.id,
    scope,
    resultingVersion: guide.version,
    metadata: {
      guideName: guide.name,
      characterKey: guide.characterKey,
      ...(context.overwrite ? { overwrite: true } : {}),
      ...metadata,
    },
  });
}

export class GuideVersionConflictError extends Error {
  constructor(readonly currentVersion: number) {
    super("ข้อมูลถูกแก้ไขจากแท็บอื่น กด Save now เพื่อบันทึก Local draft ทับเวอร์ชันล่าสุด");
    this.name = "GuideVersionConflictError";
  }
}

async function emitGuideEvents(tx: Parameters<Parameters<ReturnType<typeof getDb>["transaction"]>[0]>[0], guideId: string, version: number, directoryChanged = false, scope?: AdminEditorScope, details?: GuideEventDetails) {
  await tx.insert(outboxEvents).values([
    ...(directoryChanged
      ? [{ topic: "directory", aggregateId: guideId, eventType: "directory.updated", payload: { id: guideId, version } }]
      : []),
    { topic: `page:${guideId}`, aggregateId: guideId, eventType: "page.updated", payload: { id: guideId, version } },
    { topic: "admin", aggregateId: guideId, eventType: "admin.updated", payload: { id: guideId, version, ...(scope ? { scope } : {}), ...details } },
  ]);
}

export async function createGuide(input: unknown, context: GuideMutationContext) {
  const parsed = createGuideSchema.parse(input);
  const db = getDb();
  const [character] = await db.select().from(catalogCharacters).where(eq(catalogCharacters.key, parsed.characterKey)).limit(1);
  if (!character) throw new Error("ไม่พบตัวละครใน Catalog");
  const [cover] = await db.select().from(media).where(and(eq(media.id, parsed.coverMediaId), eq(media.status, "ready"))).limit(1);
  if (!cover) throw new Error("กรุณาอัปโหลดภาพ Guide ให้สำเร็จ");
  const baseSlug = slugFromCharacterName(character.name, character.key);
  const [slugCollision] = await db.select({ id: guides.id }).from(guides).where(eq(guides.slug, baseSlug)).limit(1);
  const slug = slugCollision ? `${baseSlug}-${character.key.toLowerCase()}` : baseSlug;

  return db.transaction(async (tx) => {
    await tx.execute(sql`select pg_advisory_xact_lock(hashtext('guides-sort-order'))`);
    const [order] = await tx
      .select({ maximum: sql<number>`coalesce(max(${guides.sortOrder}), 0)` })
      .from(guides);
    const [guide] = await tx.insert(guides).values({
      characterKey: character.key,
      slug,
      name: character.name,
      overview: parsed.overview,
      coverMediaId: parsed.coverMediaId,
      sortOrder: order.maximum + 10,
    }).returning();
    await tx.insert(guideSections).values(DEFAULT_SECTIONS.map((section) => ({ ...section, guideId: guide.id })));
    const [condition] = await tx.insert(artifactConditions).values({ guideId: guide.id, sortOrder: 0 }).returning();
    await tx.insert(artifactGroups).values([
      { conditionId: condition.id, name: "Late Game", sortOrder: 0 },
      { conditionId: condition.id, name: "Early Game", sortOrder: 1 },
    ]);
    await tx.insert(artifactProfiles).values({ guideId: guide.id });
    await tx.update(media).set({ currentReferenceCount: sql`${media.currentReferenceCount} + 1` }).where(eq(media.id, parsed.coverMediaId));
    await emitGuideEvents(tx, guide.id, guide.version, true);
    await auditGuide(tx, context, "guide.created", guide);
    return guide;
  });
}

async function advanceGuideVersion(tx: Parameters<Parameters<ReturnType<typeof getDb>["transaction"]>[0]>[0], guideId: string, expectedVersion: number, directoryChanged = false, scope?: AdminEditorScope, details?: GuideEventDetails) {
  const [guide] = await tx.update(guides).set({ version: expectedVersion + 1 }).where(and(eq(guides.id, guideId), eq(guides.version, expectedVersion), sql`${guides.trashedAt} is null`)).returning();
  if (!guide) {
    const [current] = await tx.select({ version: guides.version }).from(guides).where(eq(guides.id, guideId)).limit(1);
    if (current) throw new GuideVersionConflictError(current.version);
    throw new Error("ไม่พบ Guide");
  }
  await emitGuideEvents(tx, guide.id, guide.version, directoryChanged, scope, details);
  return guide;
}

async function syncConditionIconReferences(
  tx: Parameters<Parameters<ReturnType<typeof getDb>["transaction"]>[0]>[0],
  previous: Array<string | null | undefined>,
  next: Array<string | null | undefined>,
) {
  const nextIds = [...new Set(next.filter((id): id is string => Boolean(id)))];
  if (nextIds.length) {
    const ready = await tx.select({ id: media.id }).from(media).where(and(inArray(media.id, nextIds), eq(media.status, "ready")));
    if (ready.length !== nextIds.length) throw new Error("ไม่พบภาพ Condition ที่อัปโหลดแล้ว");
  }
  const changes = new Map<string, number>();
  for (const id of previous) if (id) changes.set(id, (changes.get(id) ?? 0) - 1);
  for (const id of next) if (id) changes.set(id, (changes.get(id) ?? 0) + 1);
  for (const [id, change] of changes) {
    if (change) await tx.update(media).set({ currentReferenceCount: sql`greatest(${media.currentReferenceCount} + ${change}, 0)` }).where(eq(media.id, id));
  }
}

export async function saveWeapons(guideId: string, input: unknown, context: GuideMutationContext) {
  const data = weaponSectionSchema.parse(input);
  const [guide] = await getDb().select().from(guides).where(eq(guides.id, guideId)).limit(1);
  if (!guide) throw new Error("ไม่พบ Guide");
  return getDb().transaction(async (tx) => {
    const updated = await advanceGuideVersion(tx, guideId, data.expectedVersion, false, "weapon", invalidationDetails(context));
    const previous = await tx.select({ iconMediaId: weaponConditions.iconMediaId }).from(weaponConditions).where(eq(weaponConditions.guideId, guideId));
    await syncConditionIconReferences(tx, previous.map((group) => group.iconMediaId), data.groups.map((group) => group.iconMediaId));
    await tx.update(guideSections).set({ note: data.note }).where(and(eq(guideSections.guideId, guideId), eq(guideSections.slug, "weapon")));
    await tx.delete(weaponConditions).where(eq(weaponConditions.guideId, guideId));
    for (const [sortOrder, group] of data.groups.entries()) {
      const [created] = await tx.insert(weaponConditions).values({ guideId, name: group.name, note: group.note, iconMediaId: group.iconMediaId, sortOrder }).returning();
      if (group.rows.length) await tx.insert(weaponRecommendations).values(group.rows.map((row, rowOrder) => ({ ...row, refinement: row.refinement, overallPercent: row.overallPercent?.toString() ?? null, personalPercent: row.personalPercent?.toString() ?? null, conditionId: created.id, sortOrder: rowOrder })));
    }
    await auditGuide(tx, context, "guide.weapon.saved", updated, "weapon");
    return updated;
  });
}

export async function updateGuideBasics(guideId: string, input: unknown, context: GuideMutationContext) {
  const data = updateGuideSchema.parse(input);
  const [current] = await getDb().select().from(guides).where(eq(guides.id, guideId)).limit(1);
  if (!current) throw new Error("ไม่พบ Guide");
  const [cover] = await getDb().select().from(media).where(and(eq(media.id, data.coverMediaId), eq(media.status, "ready"))).limit(1);
  if (!cover) throw new Error("ไม่พบภาพ Guide ที่อัปโหลดแล้ว");
  return getDb().transaction(async (tx) => {
    const posterArtMediaId = data.posterArtMediaId === undefined
      ? current.posterArtMediaId
      : data.posterArtMediaId;
    if (posterArtMediaId) {
      const [poster] = await tx
        .select({ id: media.id })
        .from(media)
        .where(and(eq(media.id, posterArtMediaId), eq(media.status, "ready")))
        .limit(1);
      if (!poster) throw new Error("ไม่พบภาพตัวละครที่อัปโหลดแล้ว");
    }
    if (data.posterBleeding !== undefined) {
      const bleedingMediaIds = [...new Set(data.posterBleeding.map((layer) => layer.mediaId))];
      const bleedingMedia = bleedingMediaIds.length
        ? await tx.select({ id: media.id }).from(media).where(and(inArray(media.id, bleedingMediaIds), eq(media.status, "ready")))
        : [];
      if (bleedingMedia.length !== bleedingMediaIds.length) throw new Error("ไม่พบภาพ Bleeding ที่อัปโหลดแล้ว");
    }
    const updated = await advanceGuideVersion(tx, guideId, data.expectedVersion, true, "overview", invalidationDetails(context));
    await tx.update(guides).set({
      overview: data.overview,
      coverMediaId: data.coverMediaId,
      youtubeUrls: data.youtubeUrls,
      ...(data.posterArtMediaId !== undefined ? { posterArtMediaId } : {}),
      ...(data.posterArtOffsetX !== undefined ? { posterArtOffsetX: Math.round(data.posterArtOffsetX) } : {}),
      ...(data.posterArtOffsetY !== undefined ? { posterArtOffsetY: Math.round(data.posterArtOffsetY) } : {}),
      ...(data.posterArtScale !== undefined ? { posterArtScale: Math.round(data.posterArtScale) } : {}),
      ...(data.posterArtRotation !== undefined ? { posterArtRotation: Math.round(data.posterArtRotation) } : {}),
      ...(data.posterTopColors !== undefined ? { posterTopColors: data.posterTopColors } : {}),
      ...(data.posterBottomColors !== undefined ? { posterBottomColors: data.posterBottomColors } : {}),
    }).where(eq(guides.id, guideId));
    if (data.posterBleeding !== undefined) {
      const previous = await tx.select({ mediaId: guidePosterBleeding.mediaId }).from(guidePosterBleeding).where(eq(guidePosterBleeding.guideId, guideId));
      for (const layer of previous) {
        await tx.update(media).set({ currentReferenceCount: sql`greatest(${media.currentReferenceCount} - 1, 0)` }).where(eq(media.id, layer.mediaId));
      }
      await tx.delete(guidePosterBleeding).where(eq(guidePosterBleeding.guideId, guideId));
      if (data.posterBleeding.length) {
        await tx.insert(guidePosterBleeding).values(data.posterBleeding.map((layer, sortOrder) => ({
          guideId,
          mediaId: layer.mediaId,
          amount: Math.round(layer.amount),
          scale: Math.round(layer.scale),
          blendMode: layer.blendMode,
          sortOrder,
        })));
        for (const layer of data.posterBleeding) {
          await tx.update(media).set({ currentReferenceCount: sql`${media.currentReferenceCount} + 1` }).where(eq(media.id, layer.mediaId));
        }
      }
    }
    if (current.coverMediaId !== data.coverMediaId) {
      await tx.update(media).set({ currentReferenceCount: sql`greatest(${media.currentReferenceCount} - 1, 0)` }).where(eq(media.id, current.coverMediaId));
      await tx.update(media).set({ currentReferenceCount: sql`${media.currentReferenceCount} + 1` }).where(eq(media.id, data.coverMediaId));
    }
    if (current.posterArtMediaId !== posterArtMediaId) {
      if (current.posterArtMediaId) {
        await tx.update(media).set({ currentReferenceCount: sql`greatest(${media.currentReferenceCount} - 1, 0)` }).where(eq(media.id, current.posterArtMediaId));
      }
      if (posterArtMediaId) {
        await tx.update(media).set({ currentReferenceCount: sql`${media.currentReferenceCount} + 1` }).where(eq(media.id, posterArtMediaId));
      }
    }
    await auditGuide(tx, context, "guide.overview.saved", updated, "overview");
    return updated;
  });
}

export async function saveArtifacts(guideId: string, input: unknown, context: GuideMutationContext) {
  const data = artifactSectionSchema.parse(input);
  const [guide] = await getDb().select().from(guides).where(eq(guides.id, guideId)).limit(1);
  if (!guide) throw new Error("ไม่พบ Guide");
  return getDb().transaction(async (tx) => {
    const updated = await advanceGuideVersion(tx, guideId, data.expectedVersion, false, "artifact", invalidationDetails(context));
    const previousConditions = await tx.select({ iconMediaId: artifactConditions.iconMediaId }).from(artifactConditions).where(eq(artifactConditions.guideId, guideId));
    const [previousProfile] = await tx.select({ recommendedConditions: artifactProfiles.recommendedConditions, targetConditions: artifactProfiles.targetConditions }).from(artifactProfiles).where(eq(artifactProfiles.guideId, guideId)).limit(1);
    await syncConditionIconReferences(tx, [
      ...previousConditions.map((group) => group.iconMediaId),
      ...(previousProfile?.recommendedConditions ?? []).map((group) => group.iconMediaId),
      ...(previousProfile?.targetConditions ?? []).map((group) => group.iconMediaId),
    ], [
      ...data.groups.map((group) => group.iconMediaId),
      ...(data.recommendedGroups ?? []).map((group) => group.iconMediaId),
      ...data.targetGroups.map((group) => group.iconMediaId),
    ]);
    await tx.delete(artifactConditions).where(eq(artifactConditions.guideId, guideId));
    for (const [conditionOrder, condition] of data.groups.entries()) {
      const [createdCondition] = await tx.insert(artifactConditions).values({ guideId, name: condition.name, note: condition.note, iconMediaId: condition.iconMediaId, sortOrder: conditionOrder }).returning();
      for (const [sortOrder, group] of condition.tiers.entries()) {
        const [created] = await tx.insert(artifactGroups).values({ conditionId: createdCondition.id, name: group.name, sortOrder }).returning();
        if (group.rows.length) await tx.insert(artifactRecommendations).values(group.rows.map((row, rowOrder) => ({ ...row, percent: row.percent.toString(), groupId: created.id, sortOrder: rowOrder })));
      }
    }
    await tx.insert(artifactProfiles).values({ guideId, sands: data.sands, goblet: data.goblet, circlet: data.circlet, substats: data.substats, recommendedConditions: data.recommendedGroups ?? [{ name: null, note: null, sands: data.sands, goblet: data.goblet, circlet: data.circlet, substats: data.substats }], targets: data.targetGroups[0]?.rows ?? [], targetConditions: data.targetGroups, talents: data.talents, note: data.note }).onConflictDoUpdate({ target: artifactProfiles.guideId, set: { sands: data.sands, goblet: data.goblet, circlet: data.circlet, substats: data.substats, recommendedConditions: data.recommendedGroups ?? [{ name: null, note: null, sands: data.sands, goblet: data.goblet, circlet: data.circlet, substats: data.substats }], targets: data.targetGroups[0]?.rows ?? [], targetConditions: data.targetGroups, talents: data.talents, note: data.note } });
    await auditGuide(tx, context, "guide.artifact.saved", updated, "artifact");
    return updated;
  });
}

export async function saveConstellations(guideId: string, input: unknown, context: GuideMutationContext) {
  const data = constellationSectionSchema.parse(input);
  return getDb().transaction(async (tx) => {
    const updated = await advanceGuideVersion(tx, guideId, data.expectedVersion, false, "constellations", invalidationDetails(context));
    const previous = await tx.select({ iconMediaId: constellationConditions.iconMediaId }).from(constellationConditions).where(eq(constellationConditions.guideId, guideId));
    await syncConditionIconReferences(tx, previous.map((group) => group.iconMediaId), data.groups.map((group) => group.iconMediaId));
    await tx.update(guideSections).set({ note: data.note }).where(and(eq(guideSections.guideId, guideId), eq(guideSections.slug, "constellations")));
    await tx.delete(constellationConditions).where(eq(constellationConditions.guideId, guideId));
    for (const [sortOrder, group] of data.groups.entries()) {
      const [created] = await tx.insert(constellationConditions).values({ guideId, name: group.name, note: group.note, iconMediaId: group.iconMediaId, sortOrder }).returning();
      if (group.rows.length) await tx.insert(constellationRecommendations).values(group.rows.map((row) => ({ ...row, cumulativePercent: row.cumulativePercent.toString(), conditionId: created.id })));
    }
    await auditGuide(tx, context, "guide.constellations.saved", updated, "constellations");
    return updated;
  });
}

export async function saveTeams(guideId: string, input: unknown, context: GuideMutationContext) {
  const data = teamSectionSchema.parse(input);
  const [guide] = await getDb().select().from(guides).where(eq(guides.id, guideId)).limit(1);
  if (!guide) throw new Error("ไม่พบ Guide");
  const members = data.groups.flatMap((group) => group.teams.flatMap((team) => team.members));
  const characterKeys = [...new Set(members.map((member) => member.characterKey))];
  const weaponKeys = [...new Set(members.map((member) => member.weaponKey).filter((key): key is string => Boolean(key)))];
  const artifactKeys = [...new Set(members.flatMap((member) => [member.artifactKey, member.secondArtifactKey]).filter((key): key is string => Boolean(key)))];
  const [characters, weapons, artifacts] = await Promise.all([
    characterKeys.length ? getDb().select().from(catalogCharacters).where(inArray(catalogCharacters.key, characterKeys)) : [],
    weaponKeys.length ? getDb().select().from(catalogWeapons).where(inArray(catalogWeapons.key, weaponKeys)) : [],
    artifactKeys.length ? getDb().select().from(catalogArtifacts).where(inArray(catalogArtifacts.key, artifactKeys)) : [],
  ]);
  if (characters.length !== characterKeys.length) throw new Error("ไม่พบตัวละครบางรายการใน Catalog");
  if (weapons.length !== weaponKeys.length) throw new Error("ไม่พบอาวุธบางรายการใน Catalog");
  if (artifacts.length !== artifactKeys.length) throw new Error("ไม่พบ Artifact บางรายการใน Catalog");
  for (const member of members) {
    const character = characters.find((item) => item.key === member.characterKey);
    const weapon = weapons.find((item) => item.key === member.weaponKey);
    if (weapon && weapon.weaponType !== character?.weaponType) {
      throw new Error(`${weapon.name} ไม่รองรับ ${character?.name ?? "ตัวละครนี้"}`);
    }
  }
  return getDb().transaction(async (tx) => {
    const updated = await advanceGuideVersion(tx, guideId, data.expectedVersion, false, "team", invalidationDetails(context));
    const previous = await tx.select({ iconMediaId: teamConditions.iconMediaId }).from(teamConditions).where(eq(teamConditions.guideId, guideId));
    await syncConditionIconReferences(tx, previous.map((group) => group.iconMediaId), data.groups.map((group) => group.iconMediaId));
    await tx.delete(teamConditions).where(eq(teamConditions.guideId, guideId));
    for (const [groupOrder, group] of data.groups.entries()) {
      const [condition] = await tx.insert(teamConditions).values({ guideId, name: group.name, note: group.note, iconMediaId: group.iconMediaId, sortOrder: groupOrder }).returning();
      for (let start = 0; start < group.teams.length; start += 500) {
        const batch = group.teams.slice(start, start + 500);
        const created = await tx.insert(teamCompositions).values(
          batch.map((team, index) => ({
            guideId,
            conditionId: condition.id,
            title: team.title,
            rotationSeconds: team.rotationSeconds.toString(),
            combo: team.combo,
            sortOrder: start + index,
          })),
        ).returning({ id: teamCompositions.id, sortOrder: teamCompositions.sortOrder });
        const teamIdByOrder = new Map<number, string>();
        for (const team of created) teamIdByOrder.set(team.sortOrder, team.id);
        const memberRows = batch.flatMap((team, index) => {
          const teamId = teamIdByOrder.get(start + index);
          if (!teamId) throw new Error("บันทึก Team ไม่สำเร็จ");
          return team.members.map((member, position) => ({
            ...member,
            teamId,
            position,
            damage: member.damage.toString(),
          }));
        });
        for (let memberStart = 0; memberStart < memberRows.length; memberStart += 500) {
          await tx.insert(teamMembers).values(memberRows.slice(memberStart, memberStart + 500));
        }
      }
    }
    await auditGuide(tx, context, "guide.team.saved", updated, "team");
    return updated;
  });
}

export async function createExtraSection(
  guideId: string,
  input: unknown,
  context: GuideMutationContext,
) {
  const data = createExtraSectionSchema.parse(input);
  return getDb().transaction(async (tx) => {
    const existing = await tx
      .select({ slug: guideSections.slug, sortOrder: guideSections.sortOrder })
      .from(guideSections)
      .where(and(eq(guideSections.guideId, guideId), eq(guideSections.kind, "extra")))
      .orderBy(asc(guideSections.sortOrder));
    if (existing.length >= 20) throw new Error("Guide มี Extra sections ได้สูงสุด 20 ส่วน");

    const [updated] = await tx
      .update(guides)
      .set({ version: sql`${guides.version} + 1` })
      .where(and(eq(guides.id, guideId), sql`${guides.trashedAt} is null`))
      .returning();
    if (!updated) throw new Error("ไม่พบ Guide");

    const suffix = existing.reduce((highest, section) => {
      const match = /^extra-(\d+)$/u.exec(section.slug);
      return match ? Math.max(highest, Number(match[1])) : highest;
    }, 0) + 1;
    const sortOrder = Math.max(3, ...existing.map((section) => section.sortOrder)) + 1;
    const [section] = await tx
      .insert(guideSections)
      .values({
        guideId,
        kind: "extra",
        slug: `extra-${suffix}`,
        title: data.title,
        enabled: true,
        sortOrder,
      })
      .returning();
    await tx.insert(extraSections).values({ sectionId: section.id });
    await emitGuideEvents(tx, guideId, updated.version, false, "extras:list");
    await writeAuditLog(tx, context.actor, {
      action: "guide.extra.created",
      targetType: "extra_section",
      targetId: section.id,
      scope: "extras:list",
      resultingVersion: updated.version,
      metadata: {
        guideId,
        guideName: updated.name,
        characterKey: updated.characterKey,
        sectionTitle: section.title,
      },
    });
    return { ...section, version: updated.version };
  });
}

export async function saveExtraSection(
  guideId: string,
  sectionId: string,
  input: unknown,
  context: GuideMutationContext,
) {
  const data = saveExtraSectionSchema.parse(input);
  return getDb().transaction(async (tx) => {
    const [target] = await tx
      .select({ id: guideSections.id })
      .from(guideSections)
      .where(and(
        eq(guideSections.id, sectionId),
        eq(guideSections.guideId, guideId),
        eq(guideSections.kind, "extra"),
      ))
      .limit(1);
    if (!target) throw new Error("ไม่พบ Extra section");

    const updated = await advanceGuideVersion(tx, guideId, data.expectedVersion, false, `extra:${sectionId}`, invalidationDetails(context));
    await tx
      .update(guideSections)
      .set({ title: data.section.title, enabled: true })
      .where(eq(guideSections.id, sectionId));
    await tx.delete(extraSectionRows).where(eq(extraSectionRows.sectionId, sectionId));
    if (data.section.rows.length) {
      await tx.insert(extraSectionRows).values(data.section.rows.map((row, sortOrder) => ({
        ...row,
        body: normalizeExtraRichText(row.body, row.highlights),
        highlights: [],
        percent: row.percent?.toString() ?? null,
        sectionId,
        sortOrder,
      })));
    }
    await writeAuditLog(tx, context.actor, {
      action: "guide.extra.saved",
      targetType: "extra_section",
      targetId: sectionId,
      scope: `extra:${sectionId}`,
      resultingVersion: updated.version,
      metadata: {
        guideId,
        guideName: updated.name,
        characterKey: updated.characterKey,
        sectionTitle: data.section.title,
        ...(context.overwrite ? { overwrite: true } : {}),
      },
    });
    return updated;
  });
}

export async function deleteExtraSection(
  guideId: string,
  sectionId: string,
  context: GuideMutationContext,
) {
  return getDb().transaction(async (tx) => {
    const [target] = await tx
      .select({ id: guideSections.id, title: guideSections.title })
      .from(guideSections)
      .where(and(
        eq(guideSections.id, sectionId),
        eq(guideSections.guideId, guideId),
        eq(guideSections.kind, "extra"),
      ))
      .limit(1);
    if (!target) throw new Error("ไม่พบ Extra section");

    const [updated] = await tx
      .update(guides)
      .set({ version: sql`${guides.version} + 1` })
      .where(and(eq(guides.id, guideId), sql`${guides.trashedAt} is null`))
      .returning();
    if (!updated) throw new Error("ไม่พบ Guide");
    await tx.delete(guideSections).where(eq(guideSections.id, sectionId));
    await emitGuideEvents(tx, guideId, updated.version);
    await writeAuditLog(tx, context.actor, {
      action: "guide.extra.deleted",
      targetType: "extra_section",
      targetId: sectionId,
      scope: "extras:list",
      resultingVersion: updated.version,
      metadata: {
        guideId,
        guideName: updated.name,
        characterKey: updated.characterKey,
        sectionTitle: target.title,
      },
    });
    return updated;
  });
}

export async function reorderExtraSections(
  guideId: string,
  input: unknown,
  context: GuideMutationContext,
) {
  const data = reorderExtraSectionsSchema.parse(input);
  return getDb().transaction(async (tx) => {
    const existing = await tx
      .select({ id: guideSections.id })
      .from(guideSections)
      .where(and(eq(guideSections.guideId, guideId), eq(guideSections.kind, "extra")))
      .orderBy(asc(guideSections.sortOrder));
    const existingIds = new Set(existing.map((section) => section.id));
    if (
      existingIds.size !== data.sectionIds.length ||
      data.sectionIds.some((sectionId) => !existingIds.has(sectionId))
    ) {
      throw new Error("รายการ Extra sections ไม่ตรงกับข้อมูลล่าสุด");
    }

    const updated = await advanceGuideVersion(tx, guideId, data.expectedVersion, false, "extras:list");
    for (const [index, sectionId] of data.sectionIds.entries()) {
      await tx
        .update(guideSections)
        .set({ sortOrder: 4 + index })
        .where(eq(guideSections.id, sectionId));
    }
    await auditGuide(tx, context, "guide.extra.reordered", updated, "extras:list", {
      sectionIds: data.sectionIds,
    });
    return updated;
  });
}

export async function trashGuide(guideId: string, context: GuideMutationContext) {
  return getDb().transaction(async (tx) => {
    const [guide] = await tx.update(guides).set({ trashedAt: new Date(), version: sql`${guides.version} + 1` }).where(eq(guides.id, guideId)).returning();
    if (!guide) throw new Error("ไม่พบ Guide");
    await emitGuideEvents(tx, guide.id, guide.version, true);
    await auditGuide(tx, context, "guide.trashed", guide);
    return guide;
  });
}

export async function restoreGuide(guideId: string, context: GuideMutationContext) {
  return getDb().transaction(async (tx) => {
    const [guide] = await tx.update(guides).set({ trashedAt: null, version: sql`${guides.version} + 1` }).where(eq(guides.id, guideId)).returning();
    if (!guide) throw new Error("ไม่พบ Guide");
    await emitGuideEvents(tx, guide.id, guide.version, true);
    await auditGuide(tx, context, "guide.restored", guide);
    return guide;
  });
}

export async function setGuideVisibility(
  guideId: string,
  input: {
    isPublic: boolean;
    expectedVersion: number;
  },
  context: GuideMutationContext,
) {
  return getDb().transaction(async (tx) => {
    const [previous] = await tx
      .select({ isPublic: guides.isPublic, publishedAt: guides.publishedAt })
      .from(guides)
      .where(eq(guides.id, guideId))
      .for("update");
    const [guide] = await tx
      .update(guides)
      .set({
        isPublic: input.isPublic,
        version: sql`${guides.version} + 1`,
        ...(input.isPublic ? {
          publishedAt: new Date(),
        } : {}),
      })
      .where(and(
        eq(guides.id, guideId),
        eq(guides.version, input.expectedVersion),
        sql`${guides.trashedAt} is null`,
      ))
      .returning();
    if (!guide) {
      const [current] = await tx
        .select({ version: guides.version })
        .from(guides)
        .where(and(eq(guides.id, guideId), sql`${guides.trashedAt} is null`))
        .limit(1);
      if (!current) throw new Error("ไม่พบ Guide");
      throw new GuideVersionConflictError(current.version);
    }
    await emitGuideEvents(tx, guide.id, guide.version, true);
    if (input.isPublic && previous && !previous.isPublic && !previous.publishedAt) {
      await tx.insert(outboxEvents).values({
        topic: "notifications:guide",
        aggregateId: guide.id,
        eventType: "guide.published",
        payload: { publishedAt: guide.publishedAt!.toISOString() },
      });
    }
    await auditGuide(
      tx,
      context,
      input.isPublic ? "guide.published" : "guide.unpublished",
      guide,
    );
    return guide;
  });
}

export async function permanentlyDeleteGuide(
  guideId: string,
  context: GuideMutationContext,
) {
  return getDb().transaction(async (tx) => {
    const [guide] = await tx.select().from(guides).where(eq(guides.id, guideId)).limit(1);
    if (!guide?.trashedAt) throw new Error("Guide ต้องอยู่ในถังขยะก่อนลบถาวร");
    const bleeding = await tx.select({ mediaId: guidePosterBleeding.mediaId }).from(guidePosterBleeding).where(eq(guidePosterBleeding.guideId, guideId));
    const weapons = await tx.select({ iconMediaId: weaponConditions.iconMediaId }).from(weaponConditions).where(eq(weaponConditions.guideId, guideId));
    const artifacts = await tx.select({ iconMediaId: artifactConditions.iconMediaId }).from(artifactConditions).where(eq(artifactConditions.guideId, guideId));
    const constellations = await tx.select({ iconMediaId: constellationConditions.iconMediaId }).from(constellationConditions).where(eq(constellationConditions.guideId, guideId));
    const teams = await tx.select({ iconMediaId: teamConditions.iconMediaId }).from(teamConditions).where(eq(teamConditions.guideId, guideId));
    const profiles = await tx.select({ recommendedConditions: artifactProfiles.recommendedConditions, targetConditions: artifactProfiles.targetConditions }).from(artifactProfiles).where(eq(artifactProfiles.guideId, guideId));
    const conditionIconIds = [
      ...weapons, ...artifacts, ...constellations, ...teams,
      ...(profiles[0]?.recommendedConditions ?? []),
      ...(profiles[0]?.targetConditions ?? []),
    ].map((condition) => condition.iconMediaId);
    await tx.delete(guides).where(eq(guides.id, guideId));
    await syncConditionIconReferences(tx, conditionIconIds, []);
    await tx.update(media).set({ currentReferenceCount: sql`greatest(${media.currentReferenceCount} - 1, 0)` }).where(eq(media.id, guide.coverMediaId));
    if (guide.posterArtMediaId) {
      await tx.update(media).set({ currentReferenceCount: sql`greatest(${media.currentReferenceCount} - 1, 0)` }).where(eq(media.id, guide.posterArtMediaId));
    }
    for (const layer of bleeding) {
      await tx.update(media).set({ currentReferenceCount: sql`greatest(${media.currentReferenceCount} - 1, 0)` }).where(eq(media.id, layer.mediaId));
    }
    await emitGuideEvents(tx, guide.id, guide.version + 1, true);
    await writeAuditLog(tx, context.actor, {
      action: "guide.permanently_deleted",
      targetType: "guide",
      targetId: guide.id,
      resultingVersion: guide.version + 1,
      metadata: {
        guideName: guide.name,
        characterKey: guide.characterKey,
      },
    });
    return { version: guide.version + 1 };
  });
}