import { relations, sql } from "drizzle-orm";
import {
  check,
  index,
  integer,
  real,
  sqliteTable,
  text,
  uniqueIndex,
} from "drizzle-orm/sqlite-core";

const timestamps = {
  createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`),
};

export const books = sqliteTable(
  "books",
  {
    id: text("id").primaryKey(),
    title: text("title").notNull(),
    author: text("author").notNull().default("未知作者"),
    format: text("format", { enum: ["epub", "pdf"] }).notNull(),
    sourceObjectKey: text("source_object_key").notNull().unique(),
    sourceBytes: integer("source_bytes").notNull(),
    coverObjectKey: text("cover_object_key"),
    status: text("status", {
      enum: ["uploading", "processing", "ready", "partial", "failed"],
    })
      .notNull()
      .default("processing"),
    statusDetail: text("status_detail"),
    preferredZhVoice: text("preferred_zh_voice"),
    preferredEnVoice: text("preferred_en_voice"),
    preferredRate: real("preferred_rate"),
    lastListenedAt: text("last_listened_at"),
    ...timestamps,
  },
  (table) => [
    index("books_recent_idx").on(table.lastListenedAt, table.createdAt),
    index("books_title_idx").on(table.title),
    index("books_author_idx").on(table.author),
    check("books_source_bytes_positive", sql`${table.sourceBytes} > 0`),
    check(
      "books_rate_range",
      sql`${table.preferredRate} IS NULL OR (${table.preferredRate} >= 0.5 AND ${table.preferredRate} <= 3)`,
    ),
  ],
);

export const chapters = sqliteTable(
  "chapters",
  {
    id: text("id").primaryKey(),
    bookId: text("book_id")
      .notNull()
      .references(() => books.id, { onDelete: "cascade" }),
    title: text("title").notNull(),
    position: integer("position").notNull(),
    pageStart: integer("page_start"),
    pageEnd: integer("page_end"),
    textVersion: integer("text_version").notNull().default(1),
    ...timestamps,
  },
  (table) => [
    uniqueIndex("chapters_book_position_idx").on(table.bookId, table.position),
    check("chapters_position_nonnegative", sql`${table.position} >= 0`),
    check("chapters_text_version_positive", sql`${table.textVersion} > 0`),
  ],
);

export const segments = sqliteTable(
  "segments",
  {
    id: text("id").primaryKey(),
    chapterId: text("chapter_id")
      .notNull()
      .references(() => chapters.id, { onDelete: "cascade" }),
    position: integer("position").notNull(),
    text: text("text").notNull(),
    language: text("language", { enum: ["zh-CN", "en-US"] }).notNull(),
    contentHash: text("content_hash").notNull(),
    textVersion: integer("text_version").notNull().default(1),
    included: integer("included", { mode: "boolean" }).notNull().default(true),
    sourceKind: text("source_kind", {
      enum: ["epub", "pdf_text", "ocr", "manual"],
    })
      .notNull()
      .default("manual"),
    pageNumber: integer("page_number"),
    ocrConfidence: real("ocr_confidence"),
    warning: text("warning"),
    ...timestamps,
  },
  (table) => [
    uniqueIndex("segments_chapter_position_idx").on(
      table.chapterId,
      table.position,
    ),
    index("segments_content_hash_idx").on(table.contentHash),
    check("segments_position_nonnegative", sql`${table.position} >= 0`),
    check("segments_text_version_positive", sql`${table.textVersion} > 0`),
    check(
      "segments_ocr_confidence_range",
      sql`${table.ocrConfidence} IS NULL OR (${table.ocrConfidence} >= 0 AND ${table.ocrConfidence} <= 100)`,
    ),
  ],
);

export const audioSegments = sqliteTable(
  "audio_segments",
  {
    id: text("id").primaryKey(),
    bookId: text("book_id")
      .notNull()
      .references(() => books.id, { onDelete: "cascade" }),
    segmentId: text("segment_id")
      .notNull()
      .references(() => segments.id, { onDelete: "cascade" }),
    textVersion: integer("text_version").notNull(),
    voice: text("voice").notNull(),
    objectKey: text("object_key").unique(),
    durationMs: integer("duration_ms"),
    bytes: integer("bytes").notNull().default(0),
    status: text("status", {
      enum: ["queued", "generating", "ready", "failed"],
    })
      .notNull()
      .default("queued"),
    errorSummary: text("error_summary"),
    lastUsedAt: text("last_used_at").notNull().default(sql`CURRENT_TIMESTAMP`),
    ...timestamps,
  },
  (table) => [
    uniqueIndex("audio_segments_lookup_idx").on(
      table.segmentId,
      table.textVersion,
      table.voice,
    ),
    index("audio_segments_book_used_idx").on(table.bookId, table.lastUsedAt),
    check("audio_segments_version_positive", sql`${table.textVersion} > 0`),
    check("audio_segments_bytes_nonnegative", sql`${table.bytes} >= 0`),
  ],
);

export const playbackProgress = sqliteTable(
  "playback_progress",
  {
    bookId: text("book_id")
      .primaryKey()
      .references(() => books.id, { onDelete: "cascade" }),
    chapterId: text("chapter_id")
      .notNull()
      .references(() => chapters.id, { onDelete: "cascade" }),
    segmentId: text("segment_id")
      .notNull()
      .references(() => segments.id, { onDelete: "cascade" }),
    offsetMs: integer("offset_ms").notNull().default(0),
    playbackRate: real("playback_rate").notNull().default(1),
    updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    check("progress_offset_nonnegative", sql`${table.offsetMs} >= 0`),
    check(
      "progress_rate_range",
      sql`${table.playbackRate} >= 0.5 AND ${table.playbackRate} <= 3`,
    ),
  ],
);

export const uploadSessions = sqliteTable(
  "upload_sessions",
  {
    id: text("id").primaryKey(),
    r2UploadId: text("r2_upload_id").notNull(),
    objectKey: text("object_key").notNull().unique(),
    fileName: text("file_name").notNull(),
    mimeType: text("mime_type").notNull(),
    format: text("format", { enum: ["epub", "pdf"] }).notNull(),
    totalBytes: integer("total_bytes").notNull(),
    partSize: integer("part_size").notNull(),
    partCount: integer("part_count").notNull(),
    uploadedPartsJson: text("uploaded_parts_json").notNull().default("[]"),
    status: text("status", {
      enum: ["created", "uploading", "completing", "complete", "aborted"],
    })
      .notNull()
      .default("created"),
    expiresAt: text("expires_at").notNull(),
    createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
    updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    index("upload_sessions_expiry_idx").on(table.expiresAt),
    check("upload_sessions_total_positive", sql`${table.totalBytes} > 0`),
    check("upload_sessions_part_size_positive", sql`${table.partSize} > 0`),
    check("upload_sessions_part_count_positive", sql`${table.partCount} > 0`),
  ],
);

export const storageLedger = sqliteTable(
  "storage_ledger",
  {
    objectKey: text("object_key").primaryKey(),
    kind: text("kind", {
      enum: ["source", "cover", "audio", "upload_part"],
    }).notNull(),
    bookId: text("book_id").references(() => books.id, {
      onDelete: "cascade",
    }),
    bytes: integer("bytes").notNull(),
    lastUsedAt: text("last_used_at").notNull().default(sql`CURRENT_TIMESTAMP`),
    createdAt: text("created_at").notNull().default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    index("storage_ledger_book_kind_idx").on(table.bookId, table.kind),
    index("storage_ledger_used_idx").on(table.lastUsedAt),
    check("storage_ledger_bytes_nonnegative", sql`${table.bytes} >= 0`),
  ],
);

export const rateLimits = sqliteTable(
  "rate_limits",
  {
    action: text("action").notNull(),
    clientKey: text("client_key").notNull(),
    windowStart: integer("window_start").notNull(),
    count: integer("count").notNull().default(0),
    updatedAt: text("updated_at").notNull().default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    uniqueIndex("rate_limits_window_idx").on(table.action, table.clientKey, table.windowStart),
    index("rate_limits_updated_idx").on(table.updatedAt),
    check("rate_limits_count_positive", sql`${table.count} > 0`),
  ],
);

export const booksRelations = relations(books, ({ many, one }) => ({
  chapters: many(chapters),
  audioSegments: many(audioSegments),
  progress: one(playbackProgress),
  storageObjects: many(storageLedger),
}));

export const chaptersRelations = relations(chapters, ({ one, many }) => ({
  book: one(books, { fields: [chapters.bookId], references: [books.id] }),
  segments: many(segments),
}));

export const segmentsRelations = relations(segments, ({ one, many }) => ({
  chapter: one(chapters, {
    fields: [segments.chapterId],
    references: [chapters.id],
  }),
  audio: many(audioSegments),
}));

export const audioSegmentsRelations = relations(audioSegments, ({ one }) => ({
  book: one(books, {
    fields: [audioSegments.bookId],
    references: [books.id],
  }),
  segment: one(segments, {
    fields: [audioSegments.segmentId],
    references: [segments.id],
  }),
}));
