import type { D1DatabaseLike } from "../d1.ts";
import type { UploadSession } from "../uploads.ts";

interface BookRow {
  id: string;
  title: string;
  author: string;
  format: "epub" | "pdf";
  source_bytes: number;
  cover_object_key: string | null;
  status: "uploading" | "processing" | "ready" | "partial" | "failed";
  status_detail: string | null;
  preferred_zh_voice: string | null;
  preferred_en_voice: string | null;
  preferred_rate: number | null;
  last_listened_at: string | null;
  created_at: string;
  updated_at: string;
  progress_chapter_title?: string | null;
  progress_chapter_position?: number | null;
  progress_segment_position?: number | null;
  progress_updated_at?: string | null;
  progress_index?: number | null;
  progress_total?: number | null;
}

interface SourceRow {
  source_object_key: string;
  title: string;
  format: "epub" | "pdf";
}

interface CoverRow {
  cover_object_key: string | null;
  bytes: number | null;
}

const BOOK_SELECT = `
  SELECT
    b.*,
    progress_chapter.title AS progress_chapter_title,
    progress_chapter.position AS progress_chapter_position,
    progress_segment.position AS progress_segment_position,
    playback_progress.updated_at AS progress_updated_at,
    CASE WHEN playback_progress.book_id IS NULL THEN NULL ELSE (
      SELECT COUNT(*)
      FROM segments earlier_segment
      JOIN chapters earlier_chapter ON earlier_chapter.id = earlier_segment.chapter_id
      WHERE earlier_chapter.book_id = b.id
        AND earlier_segment.included = 1
        AND (
          earlier_chapter.position < progress_chapter.position
          OR (
            earlier_chapter.position = progress_chapter.position
            AND earlier_segment.position < progress_segment.position
          )
        )
    ) END AS progress_index,
    CASE WHEN playback_progress.book_id IS NULL THEN NULL ELSE (
      SELECT COUNT(*)
      FROM segments total_segment
      JOIN chapters total_chapter ON total_chapter.id = total_segment.chapter_id
      WHERE total_chapter.book_id = b.id AND total_segment.included = 1
    ) END AS progress_total
  FROM books b
  LEFT JOIN playback_progress ON playback_progress.book_id = b.id
  LEFT JOIN chapters progress_chapter ON progress_chapter.id = playback_progress.chapter_id
  LEFT JOIN segments progress_segment ON progress_segment.id = playback_progress.segment_id
`;

function toBook(row: BookRow) {
  const progress = row.progress_updated_at && row.progress_chapter_title !== null
    ? {
        chapterTitle: row.progress_chapter_title ?? "",
        chapterPosition: Number(row.progress_chapter_position ?? 0),
        segmentPosition: Number(row.progress_segment_position ?? 0),
        percent: Math.min(100, Math.max(0, Math.round(
          (Number(row.progress_index ?? 0) / Math.max(1, Number(row.progress_total ?? 0))) * 100,
        ))),
        updatedAt: row.progress_updated_at,
      }
    : null;
  return {
    id: row.id,
    title: row.title,
    author: row.author,
    format: row.format,
    sourceBytes: row.source_bytes,
    coverUrl: row.cover_object_key
      ? `/api/books/${encodeURIComponent(row.id)}/cover?v=${encodeURIComponent(row.updated_at)}`
      : null,
    progress,
    status: row.status,
    statusDetail: row.status_detail,
    preferredZhVoice: row.preferred_zh_voice,
    preferredEnVoice: row.preferred_en_voice,
    preferredRate: row.preferred_rate,
    lastListenedAt: row.last_listened_at,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

function titleFromFile(fileName: string): string {
  return fileName.replace(/\.(epub|pdf)$/i, "").trim() || "未命名书籍";
}

function escapeLike(value: string): string {
  return value.replace(/[\\%_]/g, "\\$&");
}

export class BookRepository {
  private readonly database: D1DatabaseLike;

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

  async registerCompletedUpload(session: UploadSession) {
    if (session.status !== "complete") {
      throw new Error("只有完成上传的文件才能登记为书籍。");
    }
    await this.database.batch([
      this.database
        .prepare(`
          INSERT INTO books (
            id, title, author, format, source_object_key, source_bytes,
            status, created_at, updated_at
          ) VALUES (?, ?, '未知作者', ?, ?, ?, 'processing', ?, ?)
          ON CONFLICT(id) DO UPDATE SET
            source_object_key = excluded.source_object_key,
            source_bytes = excluded.source_bytes,
            updated_at = excluded.updated_at
        `)
        .bind(
          session.id,
          titleFromFile(session.fileName),
          session.format,
          session.objectKey,
          session.totalBytes,
          session.createdAt,
          session.updatedAt,
        ),
      this.database
        .prepare(`
          INSERT INTO storage_ledger (
            object_key, kind, book_id, bytes, last_used_at, created_at
          ) VALUES (?, 'source', ?, ?, ?, ?)
          ON CONFLICT(object_key) DO UPDATE SET
            book_id = excluded.book_id,
            bytes = excluded.bytes,
            last_used_at = excluded.last_used_at
        `)
        .bind(
          session.objectKey,
          session.id,
          session.totalBytes,
          session.updatedAt,
          session.createdAt,
        ),
    ]);
    const book = await this.get(session.id);
    if (!book) throw new Error("书籍登记失败。");
    return book;
  }

  async get(id: string) {
    const row = await this.database
      .prepare(`${BOOK_SELECT} WHERE b.id = ? LIMIT 1`)
      .bind(id)
      .first<BookRow>();
    return row ? toBook(row) : null;
  }

  async source(id: string) {
    const row = await this.database
      .prepare(
        "SELECT source_object_key, title, format FROM books WHERE id = ? LIMIT 1",
      )
      .bind(id)
      .first<SourceRow>();
    if (!row) return null;
    const extension = row.format === "epub" ? ".epub" : ".pdf";
    return {
      objectKey: row.source_object_key,
      fileName: `${row.title.replace(/[\\/:*?"<>|]+/g, "-")}${extension}`,
      mimeType:
        row.format === "epub" ? "application/epub+zip" : "application/pdf",
    };
  }

  async list(query = "") {
    const trimmed = query.trim();
    const statement = trimmed
      ? this.database
          .prepare(`${BOOK_SELECT}
            WHERE b.title LIKE ? ESCAPE '\\' COLLATE NOCASE
               OR b.author LIKE ? ESCAPE '\\' COLLATE NOCASE
            ORDER BY COALESCE(b.last_listened_at, b.created_at) DESC, b.created_at DESC
            LIMIT 100
          `)
          .bind(`%${escapeLike(trimmed)}%`, `%${escapeLike(trimmed)}%`)
      : this.database.prepare(`${BOOK_SELECT}
          ORDER BY COALESCE(b.last_listened_at, b.created_at) DESC, b.created_at DESC
          LIMIT 100
        `);
    const result = await statement.all<BookRow>();
    return (result.results ?? []).map(toBook);
  }

  async updateMetadata(
    id: string,
    patch: {
      title?: string;
      author?: string;
      preferredZhVoice?: string | null;
      preferredEnVoice?: string | null;
      preferredRate?: number | null;
    },
  ) {
    const current = await this.get(id);
    if (!current) throw new Error("找不到这本书。");
    const title = patch.title === undefined ? current.title : patch.title.trim();
    const author = patch.author === undefined ? current.author : patch.author.trim();
    if (!title) throw new Error("书名不能为空。");
    if (!author) throw new Error("作者不能为空。");
    const rate = patch.preferredRate === undefined
      ? current.preferredRate
      : patch.preferredRate;
    if (rate !== null && (!Number.isFinite(rate) || rate < 0.5 || rate > 3)) {
      throw new Error("默认语速必须在 0.5 到 3 倍之间。");
    }
    const updatedAt = new Date().toISOString();
    await this.database
      .prepare(`
        UPDATE books SET
          title = ?, author = ?, preferred_zh_voice = ?, preferred_en_voice = ?,
          preferred_rate = ?, updated_at = ?
        WHERE id = ?
      `)
      .bind(
        title,
        author,
        patch.preferredZhVoice === undefined
          ? current.preferredZhVoice
          : patch.preferredZhVoice,
        patch.preferredEnVoice === undefined
          ? current.preferredEnVoice
          : patch.preferredEnVoice,
        rate,
        updatedAt,
        id,
      )
      .run();
    return this.get(id);
  }

  async cover(id: string): Promise<{ objectKey: string | null; bytes: number } | null> {
    const row = await this.database.prepare(`
      SELECT b.cover_object_key, ledger.bytes
      FROM books b
      LEFT JOIN storage_ledger ledger
        ON ledger.object_key = b.cover_object_key AND ledger.kind = 'cover'
      WHERE b.id = ? LIMIT 1
    `).bind(id).first<CoverRow>();
    return row ? { objectKey: row.cover_object_key, bytes: Number(row.bytes ?? 0) } : null;
  }

  async setCover(id: string, input: { objectKey: string; bytes: number; now: string }) {
    const current = await this.cover(id);
    if (!current) throw new Error("找不到这本书。");
    if (!Number.isInteger(input.bytes) || input.bytes < 1) throw new Error("封面大小无效。");
    await this.database.batch([
      this.database.prepare("DELETE FROM storage_ledger WHERE book_id = ? AND kind = 'cover'").bind(id),
      this.database.prepare(`
        INSERT INTO storage_ledger (object_key, kind, book_id, bytes, last_used_at, created_at)
        VALUES (?, 'cover', ?, ?, ?, ?)
      `).bind(input.objectKey, id, input.bytes, input.now, input.now),
      this.database.prepare("UPDATE books SET cover_object_key = ?, updated_at = ? WHERE id = ?")
        .bind(input.objectKey, input.now, id),
    ]);
    const book = await this.get(id);
    if (!book) throw new Error("封面保存失败。");
    return { book, previousObjectKey: current.objectKey };
  }

  async removeCover(id: string, now: string) {
    const current = await this.cover(id);
    if (!current) return null;
    await this.database.batch([
      this.database.prepare("UPDATE books SET cover_object_key = NULL, updated_at = ? WHERE id = ?")
        .bind(now, id),
      this.database.prepare("DELETE FROM storage_ledger WHERE book_id = ? AND kind = 'cover'").bind(id),
    ]);
    const book = await this.get(id);
    if (!book) throw new Error("封面移除失败。");
    return { book, previousObjectKey: current.objectKey };
  }

  async delete(id: string): Promise<string[]> {
    const book = await this.get(id);
    if (!book) throw new Error("找不到这本书。");
    const result = await this.database
      .prepare("SELECT object_key FROM storage_ledger WHERE book_id = ? ORDER BY object_key")
      .bind(id)
      .all<{ object_key: string }>();
    const objectKeys = (result.results ?? []).map((row) => row.object_key);
    await this.database.prepare("DELETE FROM books WHERE id = ?").bind(id).run();
    return objectKeys;
  }
}
