import { planAudioRetention, planStorageAdmission } from "../../domain/storage.ts";
import type { D1DatabaseLike } from "../d1.ts";

interface SegmentAudioRow {
  id: string;
  book_id: string;
  chapter_id: string;
  text: string;
  language: "zh-CN" | "en-US";
  text_version: number;
}

interface AudioRow {
  id: string;
  segment_id: string;
  text_version: number;
  voice: string;
  object_key: string;
  duration_ms: number | null;
  bytes: number;
  status: "ready";
  last_used_at: string;
}

interface UsageRow {
  book_id: string;
  bytes: number;
  last_used_at: string;
}

interface ObjectKeyRow { object_key: string }

function audioId(segmentId: string, textVersion: number, voice: string): string {
  return `${segmentId}:v${textVersion}:${voice}`;
}

function publicAudio(row: AudioRow) {
  return {
    id: row.id,
    segmentId: row.segment_id,
    textVersion: row.text_version,
    voice: row.voice,
    durationMs: row.duration_ms,
    bytes: row.bytes,
    status: row.status,
    lastUsedAt: row.last_used_at,
    url: `/api/segments/${encodeURIComponent(row.segment_id)}/audio?voice=${encodeURIComponent(row.voice)}`,
  };
}

export class AudioRepository {
  private readonly database: D1DatabaseLike;

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

  async segmentForAudio(segmentId: string) {
    const row = await this.database.prepare(`
      SELECT segment.id, chapter.book_id, segment.chapter_id, segment.text,
             segment.language, segment.text_version
      FROM segments segment
      JOIN chapters chapter ON chapter.id = segment.chapter_id
      WHERE segment.id = ? AND segment.included = 1
      LIMIT 1
    `).bind(segmentId).first<SegmentAudioRow>();
    return row ? {
      id: row.id,
      bookId: row.book_id,
      chapterId: row.chapter_id,
      text: row.text,
      language: row.language,
      textVersion: row.text_version,
    } : null;
  }

  async find(segmentId: string, voice: string): Promise<ReturnType<typeof publicAudio> | null> {
    const now = new Date().toISOString();
    const row = await this.database.prepare(`
      SELECT audio.id, audio.segment_id, audio.text_version, audio.voice,
             audio.object_key, audio.duration_ms, audio.bytes, audio.status, audio.last_used_at
      FROM audio_segments audio
      JOIN segments segment ON segment.id = audio.segment_id
      WHERE audio.segment_id = ? AND audio.voice = ? AND audio.status = 'ready'
        AND audio.object_key IS NOT NULL AND audio.text_version = segment.text_version
        AND segment.included = 1
      LIMIT 1
    `).bind(segmentId, voice).first<AudioRow>();
    if (!row) return null;
    await this.database.batch([
      this.database.prepare("UPDATE audio_segments SET last_used_at = ? WHERE id = ?").bind(now, row.id),
      this.database.prepare("UPDATE storage_ledger SET last_used_at = ? WHERE object_key = ?").bind(now, row.object_key),
    ]);
    return publicAudio({ ...row, last_used_at: now });
  }

  async objectForAudio(segmentId: string, voice: string): Promise<{ objectKey: string; audio: ReturnType<typeof publicAudio> } | null> {
    const audio = await this.find(segmentId, voice);
    if (!audio) return null;
    const row = await this.database.prepare("SELECT object_key FROM audio_segments WHERE id = ? LIMIT 1")
      .bind(audio.id).first<ObjectKeyRow>();
    return row ? { objectKey: row.object_key, audio } : null;
  }

  async registerReady(input: {
    segmentId: string;
    textVersion: number;
    voice: string;
    objectKey: string;
    bytes: number;
    durationMs: number | null;
    now?: string;
  }) {
    const segment = await this.segmentForAudio(input.segmentId);
    if (!segment || segment.textVersion !== input.textVersion) {
      throw new Error("正文已经变化，请重新生成这一句。");
    }
    if (!input.voice.trim()) throw new Error("声音不能为空。");
    if (!Number.isInteger(input.bytes) || input.bytes < 1) throw new Error("音频不能为空。");
    const now = input.now ?? new Date().toISOString();
    const id = audioId(input.segmentId, input.textVersion, input.voice);
    await this.database.batch([
      this.database.prepare(`
        INSERT INTO audio_segments (
          id, book_id, segment_id, text_version, voice, object_key,
          duration_ms, bytes, status, last_used_at, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'ready', ?, ?, ?)
        ON CONFLICT(segment_id, text_version, voice) DO UPDATE SET
          object_key = excluded.object_key, duration_ms = excluded.duration_ms,
          bytes = excluded.bytes, status = 'ready', error_summary = NULL,
          last_used_at = excluded.last_used_at, updated_at = excluded.updated_at
      `).bind(id, segment.bookId, input.segmentId, input.textVersion, input.voice,
        input.objectKey, input.durationMs, input.bytes, now, now, now),
      this.database.prepare(`
        INSERT INTO storage_ledger (object_key, kind, book_id, bytes, last_used_at, created_at)
        VALUES (?, 'audio', ?, ?, ?, ?)
        ON CONFLICT(object_key) DO UPDATE SET
          book_id = excluded.book_id, bytes = excluded.bytes, last_used_at = excluded.last_used_at
      `).bind(input.objectKey, segment.bookId, input.bytes, now, now),
      this.database.prepare("UPDATE books SET last_listened_at = ?, updated_at = ? WHERE id = ?")
        .bind(now, now, segment.bookId),
    ]);
    const stored = await this.find(input.segmentId, input.voice);
    if (!stored) throw new Error("音频登记失败。");
    return stored;
  }

  async listChapter(bookId: string, chapterId: string) {
    const result = await this.database.prepare(`
      SELECT audio.id, audio.segment_id, audio.text_version, audio.voice,
             audio.object_key, audio.duration_ms, audio.bytes, audio.status, audio.last_used_at
      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 chapter.id = ? AND segment.included = 1
        AND audio.status = 'ready' AND audio.object_key IS NOT NULL
        AND audio.text_version = segment.text_version
      ORDER BY segment.position
    `).bind(bookId, chapterId).all<AudioRow>();
    return (result.results ?? []).map(publicAudio);
  }

  private async audioUsage() {
    const result = await this.database.prepare(`
      SELECT book_id, SUM(bytes) AS bytes, MAX(last_used_at) AS last_used_at
      FROM storage_ledger WHERE kind = 'audio' AND book_id IS NOT NULL
      GROUP BY book_id
    `).all<UsageRow>();
    return (result.results ?? []).map((row) => ({
      bookId: row.book_id,
      bytes: Number(row.bytes),
      lastUsedAt: row.last_used_at,
    }));
  }

  async cleanupForAdmission(requestedBookId: string, pendingBytes: number) {
    const usage = await this.audioUsage();
    const used = await this.database.prepare("SELECT COALESCE(SUM(bytes), 0) AS bytes FROM storage_ledger")
      .first<{ bytes: number }>();
    const retentionIds = planAudioRetention(usage, requestedBookId);
    const afterRetention = usage.filter((entry) => !retentionIds.includes(entry.bookId));
    const retentionBytes = usage.filter((entry) => retentionIds.includes(entry.bookId))
      .reduce((total, entry) => total + entry.bytes, 0);
    const admission = planStorageAdmission({
      usedBytes: Number(used?.bytes ?? 0) - retentionBytes,
      pendingBytes,
      audioBooks: afterRetention.filter((entry) => entry.bookId !== requestedBookId),
    });
    const evictedBookIds = [...new Set([...retentionIds, ...admission.evictBookIds])];
    let objectKeys: string[] = [];
    if (evictedBookIds.length) {
      const result = await this.database.prepare(`
        SELECT object_key FROM storage_ledger
        WHERE kind = 'audio' AND book_id IN (${evictedBookIds.map(() => "?").join(", ")})
        ORDER BY last_used_at, object_key
      `).bind(...evictedBookIds).all<ObjectKeyRow>();
      objectKeys = (result.results ?? []).map((row) => row.object_key);
      await this.database.batch([
        this.database.prepare(`DELETE FROM storage_ledger WHERE kind = 'audio' AND book_id IN (${evictedBookIds.map(() => "?").join(", ")})`).bind(...evictedBookIds),
        this.database.prepare(`DELETE FROM audio_segments WHERE book_id IN (${evictedBookIds.map(() => "?").join(", ")})`).bind(...evictedBookIds),
      ]);
    }
    return { allowed: admission.allowed, objectKeys, evictedBookIds };
  }
}
