CREATE TABLE `categories` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`name` text NOT NULL,
	`icon` text DEFAULT '📁' NOT NULL,
	`color` text DEFAULT '#6B7280',
	`parent_id` integer,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
	`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
	FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE TABLE `item_tags` (
	`item_id` integer NOT NULL,
	`tag_id` integer NOT NULL,
	FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `items` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`user_id` integer NOT NULL,
	`type` text NOT NULL,
	`title` text NOT NULL,
	`description` text,
	`content` text,
	`url` text,
	`cover_url` text,
	`favicon_url` text,
	`source_host` text,
	`file_path` text,
	`file_size` integer,
	`file_mime` text,
	`category_id` integer,
	`note` text,
	`rating` integer,
	`starred` integer DEFAULT false NOT NULL,
	`is_archived` integer DEFAULT false NOT NULL,
	`ai_summary` text,
	`published_at` text,
	`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
	`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL,
	FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE TABLE `settings` (
	`key` text PRIMARY KEY NOT NULL,
	`value` text,
	`updated_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL
);
--> statement-breakpoint
CREATE TABLE `tags` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`name` text NOT NULL,
	`color` text DEFAULT '#6B7280',
	`created_at` integer DEFAULT (cast((julianday('now') - 2440587.5)*86400000 as integer)) NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `tags_name_unique` ON `tags` (`name`);--> statement-breakpoint

-- FTS5 全文搜索虚拟表
CREATE VIRTUAL TABLE items_fts USING fts5(
  id UNINDEXED,
  title,
  description,
  content,
  note,
  content=items,
  content_rowid=id,
  tokenize='unicode61 remove_diacritics 1'
);--> statement-breakpoint

-- FTS5 同步触发器：INSERT
CREATE TRIGGER items_fts_ai AFTER INSERT ON items BEGIN
  INSERT INTO items_fts(rowid, title, description, content, note)
  VALUES (new.id, new.title, new.description, new.content, new.note);
END;--> statement-breakpoint

-- FTS5 同步触发器：DELETE
CREATE TRIGGER items_fts_ad AFTER DELETE ON items BEGIN
  INSERT INTO items_fts(items_fts, rowid, title, description, content, note)
  VALUES ('delete', old.id, old.title, old.description, old.content, old.note);
END;--> statement-breakpoint

-- FTS5 同步触发器：UPDATE
CREATE TRIGGER items_fts_au AFTER UPDATE ON items BEGIN
  INSERT INTO items_fts(items_fts, rowid, title, description, content, note)
  VALUES ('delete', old.id, old.title, old.description, old.content, old.note);
  INSERT INTO items_fts(rowid, title, description, content, note)
  VALUES (new.id, new.title, new.description, new.content, new.note);
END;--> statement-breakpoint

-- 常用查询索引
CREATE INDEX idx_items_user ON items(user_id);
CREATE INDEX idx_items_type ON items(type);
CREATE INDEX idx_items_category ON items(category_id);
CREATE INDEX idx_items_starred ON items(starred);
CREATE INDEX idx_items_created ON items(created_at DESC);
CREATE INDEX idx_item_tags_tag ON item_tags(tag_id);
CREATE UNIQUE INDEX idx_item_tags_unique ON item_tags(item_id, tag_id);