import type { D1DatabaseLike, D1StatementLike } from "../d1.ts";

export interface ChapterSegmentInput {
  id: string;
  position: number;
  text: string;
  language: "zh-CN" | "en-US";
  contentHash: string;
  included: boolean;
  sourceKind: "epub" | "pdf_text" | "ocr" | "manual";
  pageNumber: number | null;
  ocrConfidence: number | null;
  warning: string | null;
}

export interface ChapterInput {
  id: string;
  title: string;
  position: number;
  pageStart: number | null;
  pageEnd: number | null;
  segments: ChapterSegmentInput[];
}

interface ChapterRow {
  id: string;
  book_id: string;
  title: string;
  position: number;
  page_start: number | null;
  page_end: number | null;
  text_version: number;
  created_at: string;
  updated_at: string;
}

interface SegmentRow {
  id: string;
  chapter_id: string;
  position: number;
  text: string;
  language: "zh-CN" | "en-US";
  content_hash: string;
  text_version: number;
  included: number;
  source_kind: ChapterSegmentInput["sourceKind"];
  page_number: number | null;
  ocr_confidence: number | null;
  warning: string | null;
  created_at: string;
  updated_at: string;
}

interface AudioKeyRow {
  object_key: string | null;
}

interface OwnedIdRow {
  id: string;
  book_id: string;
}

const MAX_CHAPTERS_PER_BATCH = 10;
const MAX_SEGMENTS_PER_BATCH = 280;
// D1 accepts at most 100 bound values; each segment uses 13.
const SEGMENTS_PER_STATEMENT = 7;

function ensureInteger(value: number, label: string, minimum = 0): void {
  if (!Number.isInteger(value) || value < minimum) {
    throw new Error(`${label}必须是不小于 ${minimum} 的整数。`);
  }
}

function validateSegment(segment: ChapterSegmentInput): void {
  if (!segment.id.trim()) throw new Error("片段 ID 不能为空。");
  ensureInteger(segment.position, "片段顺序");
  if (!segment.text.trim()) throw new Error("片段正文不能为空。");
  if (segment.language !== "zh-CN" && segment.language !== "en-US") {
    throw new Error("片段语言不受支持。");
  }
  if (!/^[a-f0-9]{8,}$/i.test(segment.contentHash)) {
    throw new Error("片段内容哈希格式不正确。");
  }
  if (segment.pageNumber !== null) ensureInteger(segment.pageNumber, "页码", 1);
  if (
    segment.ocrConfidence !== null &&
    (!Number.isFinite(segment.ocrConfidence) ||
      segment.ocrConfidence < 0 ||
      segment.ocrConfidence > 100)
  ) {
    throw new Error("OCR 置信度必须在 0 到 100 之间。");
  }
}

function validateChapter(chapter: ChapterInput): void {
  if (!chapter.id.trim()) throw new Error("章节 ID 不能为空。");
  if (!chapter.title.trim()) throw new Error("章节标题不能为空。");
  ensureInteger(chapter.position, "章节顺序");
  if (chapter.pageStart !== null) ensureInteger(chapter.pageStart, "起始页", 1);
  if (chapter.pageEnd !== null) ensureInteger(chapter.pageEnd, "结束页", 1);
  if (
    chapter.pageStart !== null &&
    chapter.pageEnd !== null &&
    chapter.pageEnd < chapter.pageStart
  ) {
    throw new Error("章节结束页不能早于起始页。");
  }
  const positions = new Set<number>();
  const ids = new Set<string>();
  for (const segment of chapter.segments) {
    validateSegment(segment);
    if (positions.has(segment.position)) throw new Error("片段顺序不能重复。");
    if (ids.has(segment.id)) throw new Error("片段 ID 不能重复。");
    positions.add(segment.position);
    ids.add(segment.id);
  }
}

function chunked<T>(values: readonly T[], size: number): T[][] {
  const chunks: T[][] = [];
  for (let index = 0; index < values.length; index += size) {
    chunks.push(values.slice(index, index + size));
  }
  return chunks;
}

function placeholders(rows: number, columns: number): string {
  const row = `(${Array.from({ length: columns }, () => "?").join(", ")})`;
  return Array.from({ length: rows }, () => row).join(", ");
}

function asChapter(row: ChapterRow, segments: SegmentRow[]) {
  return {
    id: row.id,
    bookId: row.book_id,
    title: row.title,
    position: row.position,
    pageStart: row.page_start,
    pageEnd: row.page_end,
    textVersion: row.text_version,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    segments: segments.map((segment) => ({
      id: segment.id,
      chapterId: segment.chapter_id,
      position: segment.position,
      text: segment.text,
      language: segment.language,
      contentHash: segment.content_hash,
      textVersion: segment.text_version,
      included: Boolean(segment.included),
      sourceKind: segment.source_kind,
      pageNumber: segment.page_number,
      ocrConfidence: segment.ocr_confidence,
      warning: segment.warning,
      createdAt: segment.created_at,
      updatedAt: segment.updated_at,
    })),
  };
}

export class ChapterRepository {
  private readonly database: D1DatabaseLike;

  constructor(database: D1DatabaseLike) {
    this.database = database;
  }

  private async assertBook(bookId: string): Promise<void> {
    const book = await this.database
      .prepare("SELECT id FROM books WHERE id = ? LIMIT 1")
      .bind(bookId)
      .first<{ id: string }>();
    if (!book) throw new Error("找不到这本书。");
  }

  private async audioKeys(bookId: string): Promise<string[]> {
    const result = await this.database
      .prepare(
        "SELECT object_key FROM audio_segments WHERE book_id = ? AND object_key IS NOT NULL",
      )
      .bind(bookId)
      .all<AudioKeyRow>();
    return (result.results ?? [])
      .map((row) => row.object_key)
      .filter((key): key is string => Boolean(key));
  }

  async saveBatch(bookId: string, chapters: readonly ChapterInput[]): Promise<{
    invalidatedAudioKeys: string[];
  }> {
    await this.assertBook(bookId);
    if (!chapters.length) throw new Error("一次保存至少包含一个章节。");
    if (chapters.length > MAX_CHAPTERS_PER_BATCH) {
      throw new Error(`一次最多保存 ${MAX_CHAPTERS_PER_BATCH} 个章节。`);
    }
    const segmentCount = chapters.reduce(
      (total, chapter) => total + chapter.segments.length,
      0,
    );
    if (segmentCount > MAX_SEGMENTS_PER_BATCH) {
      throw new Error(
        `一次最多保存 ${MAX_SEGMENTS_PER_BATCH} 个片段，请分批提交。`,
      );
    }
    const chapterPositions = new Set<number>();
    const chapterIds = new Set<string>();
    const segmentIds = new Set<string>();
    for (const chapter of chapters) {
      validateChapter(chapter);
      if (chapterPositions.has(chapter.position)) throw new Error("章节顺序不能重复。");
      if (chapterIds.has(chapter.id)) throw new Error("章节 ID 不能重复。");
      chapterPositions.add(chapter.position);
      chapterIds.add(chapter.id);
      for (const segment of chapter.segments) {
        if (segmentIds.has(segment.id)) throw new Error("片段 ID 不能跨章节重复。");
        segmentIds.add(segment.id);
      }
    }

    const existingChapters = await this.database
      .prepare(`SELECT id, book_id FROM chapters WHERE id IN (${chapters.map(() => "?").join(", ")})`)
      .bind(...chapters.map((chapter) => chapter.id))
      .all<OwnedIdRow>();
    if ((existingChapters.results ?? []).some((row) => row.book_id !== bookId)) {
      throw new Error("章节不属于这本书。");
    }
    for (const ids of chunked([...segmentIds], 90)) {
      if (!ids.length) continue;
      const existingSegments = await this.database
        .prepare(`
          SELECT segment.id, chapter.book_id
          FROM segments segment
          JOIN chapters chapter ON chapter.id = segment.chapter_id
          WHERE segment.id IN (${ids.map(() => "?").join(", ")})
        `)
        .bind(...ids)
        .all<OwnedIdRow>();
      if ((existingSegments.results ?? []).some((row) => row.book_id !== bookId)) {
        throw new Error("片段不属于这本书。");
      }
    }

    const beforeAudioKeys = await this.audioKeys(bookId);
    const now = new Date().toISOString();
    const statements: D1StatementLike[] = [];
    const pruneStatements: D1StatementLike[] = [];

    statements.push(
      this.database
        .prepare(`
          WITH conflicting AS (
            SELECT id, ROW_NUMBER() OVER (ORDER BY position, id) AS ordinal
            FROM chapters
            WHERE book_id = ?
              AND position IN (${chapters.map(() => "?").join(", ")})
          ), ceiling AS (
            SELECT COALESCE(MAX(position), -1) AS max_position
            FROM chapters
            WHERE book_id = ?
          )
          UPDATE chapters
          SET position = (
            SELECT ceiling.max_position + conflicting.ordinal
            FROM conflicting, ceiling
            WHERE conflicting.id = chapters.id
          )
          WHERE id IN (SELECT id FROM conflicting)
        `)
        .bind(bookId, ...chapters.map((chapter) => chapter.position), bookId),
      this.database
        .prepare(`
          WITH ranked AS (
            SELECT
              id,
              chapter_id,
              ROW_NUMBER() OVER (PARTITION BY chapter_id ORDER BY position, id) AS ordinal,
              MAX(position) OVER (PARTITION BY chapter_id) AS max_position
            FROM segments
            WHERE chapter_id IN (${chapters.map(() => "?").join(", ")})
          )
          UPDATE segments
          SET position = (
            SELECT ranked.max_position + ranked.ordinal
            FROM ranked
            WHERE ranked.id = segments.id
          )
          WHERE id IN (SELECT id FROM ranked)
        `)
        .bind(...chapters.map((chapter) => chapter.id)),
    );

    for (const chapter of chapters) {
      statements.push(
        this.database
          .prepare(`
            INSERT INTO chapters (
              id, book_id, title, position, page_start, page_end, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(id) DO UPDATE SET
              title = excluded.title,
              position = excluded.position,
              page_start = excluded.page_start,
              page_end = excluded.page_end,
              text_version = CASE
                WHEN chapters.title <> excluded.title
                  OR chapters.page_start IS NOT excluded.page_start
                  OR chapters.page_end IS NOT excluded.page_end
                THEN chapters.text_version + 1
                ELSE chapters.text_version
              END,
              updated_at = excluded.updated_at
            WHERE chapters.book_id = excluded.book_id
          `)
          .bind(
            chapter.id,
            bookId,
            chapter.title.trim(),
            chapter.position,
            chapter.pageStart,
            chapter.pageEnd,
            now,
            now,
          ),
      );

      for (const segmentChunk of chunked(
        chapter.segments,
        SEGMENTS_PER_STATEMENT,
      )) {
        const values = segmentChunk.flatMap((segment) => [
          segment.id,
          chapter.id,
          segment.position,
          segment.text.trim(),
          segment.language,
          segment.contentHash,
          segment.included ? 1 : 0,
          segment.sourceKind,
          segment.pageNumber,
          segment.ocrConfidence,
          segment.warning,
          now,
          now,
        ]);
        statements.push(
          this.database
            .prepare(`
              INSERT INTO segments (
                id, chapter_id, position, text, language, content_hash,
                included, source_kind, page_number, ocr_confidence, warning,
                created_at, updated_at
              ) VALUES ${placeholders(segmentChunk.length, 13)}
              ON CONFLICT(id) DO UPDATE SET
                chapter_id = excluded.chapter_id,
                position = excluded.position,
                text = excluded.text,
                language = excluded.language,
                content_hash = excluded.content_hash,
                text_version = CASE
                  WHEN segments.content_hash <> excluded.content_hash
                    OR segments.included <> excluded.included
                  THEN segments.text_version + 1
                  ELSE segments.text_version
                END,
                included = excluded.included,
                source_kind = excluded.source_kind,
                page_number = excluded.page_number,
                ocr_confidence = excluded.ocr_confidence,
                warning = excluded.warning,
                updated_at = excluded.updated_at
            `)
            .bind(...values),
        );
      }

      if (chapter.segments.length) {
        pruneStatements.push(
          this.database
            .prepare(`
              DELETE FROM segments
              WHERE chapter_id = ?
                AND id NOT IN (${chapter.segments.map(() => "?").join(", ")})
            `)
            .bind(chapter.id, ...chapter.segments.map((segment) => segment.id)),
        );
      } else {
        pruneStatements.push(
          this.database.prepare("DELETE FROM segments WHERE chapter_id = ?").bind(chapter.id),
        );
      }
    }

    statements.push(...pruneStatements);

    statements.push(
      this.database
        .prepare(`
          DELETE FROM storage_ledger
          WHERE kind = 'audio' AND book_id = ?
            AND object_key IN (
              SELECT audio.object_key
              FROM audio_segments audio
              JOIN segments segment ON segment.id = audio.segment_id
              JOIN chapters chapter ON chapter.id = segment.chapter_id
              WHERE chapter.book_id = ?
                AND audio.text_version <> segment.text_version
                AND audio.object_key IS NOT NULL
            )
        `)
        .bind(bookId, bookId),
      this.database
        .prepare(`
          DELETE FROM audio_segments
          WHERE book_id = ?
            AND EXISTS (
              SELECT 1 FROM segments
              WHERE segments.id = audio_segments.segment_id
                AND segments.text_version <> audio_segments.text_version
            )
        `)
        .bind(bookId),
      this.database
        .prepare("UPDATE books SET updated_at = ?, status = 'partial' WHERE id = ?")
        .bind(now, bookId),
    );

    if (statements.length > 50) {
      throw new Error("本次保存内容过多，请缩小批次后重试。");
    }
    await this.database.batch(statements);
    const afterAudioKeys = new Set(await this.audioKeys(bookId));
    const invalidatedAudioKeys = beforeAudioKeys.filter((key) => !afterAudioKeys.has(key));
    for (const keys of chunked(invalidatedAudioKeys, 90)) {
      await this.database.batch([
        this.database
          .prepare(`DELETE FROM storage_ledger WHERE kind = 'audio' AND object_key IN (${keys.map(() => "?").join(", ")})`)
          .bind(...keys),
      ]);
    }
    return {
      invalidatedAudioKeys,
    };
  }

  async list(bookId: string) {
    await this.assertBook(bookId);
    const [chapterResult, segmentResult] = await Promise.all([
      this.database
        .prepare("SELECT * FROM chapters WHERE book_id = ? ORDER BY position")
        .bind(bookId)
        .all<ChapterRow>(),
      this.database
        .prepare(`
          SELECT segments.*
          FROM segments
          JOIN chapters ON chapters.id = segments.chapter_id
          WHERE chapters.book_id = ?
          ORDER BY chapters.position, segments.position
        `)
        .bind(bookId)
        .all<SegmentRow>(),
    ]);
    const allSegments = segmentResult.results ?? [];
    return (chapterResult.results ?? []).map((chapter) =>
      asChapter(
        chapter,
        allSegments.filter((segment) => segment.chapter_id === chapter.id),
      ),
    );
  }

  async finalize(bookId: string, keptChapterIds: readonly string[]): Promise<{
    invalidatedAudioKeys: string[];
  }> {
    await this.assertBook(bookId);
    if (!keptChapterIds.length) throw new Error("至少保留一个章节。");
    if (new Set(keptChapterIds).size !== keptChapterIds.length) {
      throw new Error("保留的章节 ID 不能重复。");
    }
    const beforeAudioKeys = await this.audioKeys(bookId);
    const now = new Date().toISOString();
    await this.database.batch([
      this.database
        .prepare(`
          DELETE FROM storage_ledger
          WHERE kind = 'audio' AND book_id = ?
            AND object_key IN (
              SELECT audio.object_key
              FROM audio_segments audio
              JOIN segments ON segments.id = audio.segment_id
              JOIN chapters ON chapters.id = segments.chapter_id
              WHERE chapters.book_id = ?
                AND chapters.id NOT IN (${keptChapterIds.map(() => "?").join(", ")})
            )
        `)
        .bind(bookId, bookId, ...keptChapterIds),
      this.database
        .prepare(`
          DELETE FROM chapters
          WHERE book_id = ? AND id NOT IN (${keptChapterIds.map(() => "?").join(", ")})
        `)
        .bind(bookId, ...keptChapterIds),
      this.database
        .prepare("UPDATE books SET status = 'ready', status_detail = NULL, updated_at = ? WHERE id = ?")
        .bind(now, bookId),
    ]);
    const afterAudioKeys = new Set(await this.audioKeys(bookId));
    return {
      invalidatedAudioKeys: beforeAudioKeys.filter((key) => !afterAudioKeys.has(key)),
    };
  }
}
