CREATE TABLE `audio_segments` (
	`id` text PRIMARY KEY NOT NULL,
	`book_id` text NOT NULL,
	`segment_id` text NOT NULL,
	`text_version` integer NOT NULL,
	`voice` text NOT NULL,
	`object_key` text,
	`duration_ms` integer,
	`bytes` integer DEFAULT 0 NOT NULL,
	`status` text DEFAULT 'queued' NOT NULL,
	`error_summary` text,
	`last_used_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`segment_id`) REFERENCES `segments`(`id`) ON UPDATE no action ON DELETE cascade,
	CONSTRAINT "audio_segments_version_positive" CHECK("audio_segments"."text_version" > 0),
	CONSTRAINT "audio_segments_bytes_nonnegative" CHECK("audio_segments"."bytes" >= 0)
);
--> statement-breakpoint
CREATE UNIQUE INDEX `audio_segments_object_key_unique` ON `audio_segments` (`object_key`);--> statement-breakpoint
CREATE UNIQUE INDEX `audio_segments_lookup_idx` ON `audio_segments` (`segment_id`,`text_version`,`voice`);--> statement-breakpoint
CREATE INDEX `audio_segments_book_used_idx` ON `audio_segments` (`book_id`,`last_used_at`);--> statement-breakpoint
CREATE TABLE `books` (
	`id` text PRIMARY KEY NOT NULL,
	`title` text NOT NULL,
	`author` text DEFAULT '未知作者' NOT NULL,
	`format` text NOT NULL,
	`source_object_key` text NOT NULL,
	`source_bytes` integer NOT NULL,
	`cover_object_key` text,
	`status` text DEFAULT 'processing' NOT NULL,
	`status_detail` text,
	`preferred_zh_voice` text,
	`preferred_en_voice` text,
	`preferred_rate` real,
	`last_listened_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CONSTRAINT "books_source_bytes_positive" CHECK("books"."source_bytes" > 0),
	CONSTRAINT "books_rate_range" CHECK("books"."preferred_rate" IS NULL OR ("books"."preferred_rate" >= 0.5 AND "books"."preferred_rate" <= 3))
);
--> statement-breakpoint
CREATE UNIQUE INDEX `books_source_object_key_unique` ON `books` (`source_object_key`);--> statement-breakpoint
CREATE INDEX `books_recent_idx` ON `books` (`last_listened_at`,`created_at`);--> statement-breakpoint
CREATE INDEX `books_title_idx` ON `books` (`title`);--> statement-breakpoint
CREATE INDEX `books_author_idx` ON `books` (`author`);--> statement-breakpoint
CREATE TABLE `chapters` (
	`id` text PRIMARY KEY NOT NULL,
	`book_id` text NOT NULL,
	`title` text NOT NULL,
	`position` integer NOT NULL,
	`page_start` integer,
	`page_end` integer,
	`text_version` integer DEFAULT 1 NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON UPDATE no action ON DELETE cascade,
	CONSTRAINT "chapters_position_nonnegative" CHECK("chapters"."position" >= 0),
	CONSTRAINT "chapters_text_version_positive" CHECK("chapters"."text_version" > 0)
);
--> statement-breakpoint
CREATE UNIQUE INDEX `chapters_book_position_idx` ON `chapters` (`book_id`,`position`);--> statement-breakpoint
CREATE TABLE `playback_progress` (
	`book_id` text PRIMARY KEY NOT NULL,
	`chapter_id` text NOT NULL,
	`segment_id` text NOT NULL,
	`offset_ms` integer DEFAULT 0 NOT NULL,
	`playback_rate` real DEFAULT 1 NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`chapter_id`) REFERENCES `chapters`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`segment_id`) REFERENCES `segments`(`id`) ON UPDATE no action ON DELETE cascade,
	CONSTRAINT "progress_offset_nonnegative" CHECK("playback_progress"."offset_ms" >= 0),
	CONSTRAINT "progress_rate_range" CHECK("playback_progress"."playback_rate" >= 0.5 AND "playback_progress"."playback_rate" <= 3)
);
--> statement-breakpoint
CREATE TABLE `segments` (
	`id` text PRIMARY KEY NOT NULL,
	`chapter_id` text NOT NULL,
	`position` integer NOT NULL,
	`text` text NOT NULL,
	`language` text NOT NULL,
	`content_hash` text NOT NULL,
	`text_version` integer DEFAULT 1 NOT NULL,
	`included` integer DEFAULT true NOT NULL,
	`source_kind` text DEFAULT 'manual' NOT NULL,
	`page_number` integer,
	`ocr_confidence` real,
	`warning` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`chapter_id`) REFERENCES `chapters`(`id`) ON UPDATE no action ON DELETE cascade,
	CONSTRAINT "segments_position_nonnegative" CHECK("segments"."position" >= 0),
	CONSTRAINT "segments_text_version_positive" CHECK("segments"."text_version" > 0),
	CONSTRAINT "segments_ocr_confidence_range" CHECK("segments"."ocr_confidence" IS NULL OR ("segments"."ocr_confidence" >= 0 AND "segments"."ocr_confidence" <= 100))
);
--> statement-breakpoint
CREATE UNIQUE INDEX `segments_chapter_position_idx` ON `segments` (`chapter_id`,`position`);--> statement-breakpoint
CREATE INDEX `segments_content_hash_idx` ON `segments` (`content_hash`);--> statement-breakpoint
CREATE TABLE `storage_ledger` (
	`object_key` text PRIMARY KEY NOT NULL,
	`kind` text NOT NULL,
	`book_id` text,
	`bytes` integer NOT NULL,
	`last_used_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`book_id`) REFERENCES `books`(`id`) ON UPDATE no action ON DELETE cascade,
	CONSTRAINT "storage_ledger_bytes_nonnegative" CHECK("storage_ledger"."bytes" >= 0)
);
--> statement-breakpoint
CREATE INDEX `storage_ledger_book_kind_idx` ON `storage_ledger` (`book_id`,`kind`);--> statement-breakpoint
CREATE INDEX `storage_ledger_used_idx` ON `storage_ledger` (`last_used_at`);--> statement-breakpoint
CREATE TABLE `upload_sessions` (
	`id` text PRIMARY KEY NOT NULL,
	`r2_upload_id` text NOT NULL,
	`object_key` text NOT NULL,
	`file_name` text NOT NULL,
	`mime_type` text NOT NULL,
	`format` text NOT NULL,
	`total_bytes` integer NOT NULL,
	`part_size` integer NOT NULL,
	`part_count` integer NOT NULL,
	`uploaded_parts_json` text DEFAULT '[]' NOT NULL,
	`status` text DEFAULT 'created' NOT NULL,
	`expires_at` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	CONSTRAINT "upload_sessions_total_positive" CHECK("upload_sessions"."total_bytes" > 0),
	CONSTRAINT "upload_sessions_part_size_positive" CHECK("upload_sessions"."part_size" > 0),
	CONSTRAINT "upload_sessions_part_count_positive" CHECK("upload_sessions"."part_count" > 0)
);
--> statement-breakpoint
CREATE UNIQUE INDEX `upload_sessions_object_key_unique` ON `upload_sessions` (`object_key`);--> statement-breakpoint
CREATE INDEX `upload_sessions_expiry_idx` ON `upload_sessions` (`expires_at`);