-- 用户信息表
CREATE TABLE `users` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`email` text NOT NULL,
	`password_hash` text NOT NULL,
	`nickname` text,
	`language` text DEFAULT 'zh',
	`role` text DEFAULT 'visitor',
	`created_at` text DEFAULT CURRENT_TIMESTAMP,
	`last_login_at` text
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_users_email` ON `users` (`email`);
--> statement-breakpoint

-- 博客内容信息表
CREATE TABLE `blog_posts` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`slug` text NOT NULL,
	`title` text NOT NULL,
	`title_en` text,
	`content_md` text NOT NULL,
	`content_md_en` text,
	`cover_image` text NOT NULL,
	`cover_width` integer DEFAULT 800,
	`cover_height` integer DEFAULT 600,
	`tags` text,
	`has_breakpoint` integer DEFAULT 0,
	`status` text DEFAULT 'draft',
	`like_count` integer DEFAULT 0,
	`dislike_count` integer DEFAULT 0,
	`publish_date` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP
);
--> statement-breakpoint
CREATE UNIQUE INDEX `blog_posts_slug_unique` ON `blog_posts` (`slug`);
--> statement-breakpoint
CREATE INDEX `idx_posts_status_date` ON `blog_posts` (`status`,`publish_date`);
--> statement-breakpoint
CREATE INDEX `idx_posts_tags` ON `blog_posts` (`tags`);
--> statement-breakpoint

-- 用户埋点信息表
CREATE TABLE `user_events` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`user_id` integer,
	`blog_id` integer,
	`event_type` text NOT NULL,
	`action_meta` text,
	`visitor_hash` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`blog_id`) REFERENCES `blog_posts`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE INDEX `idx_events_type_date` ON `user_events` (`event_type`,`created_at`);
--> statement-breakpoint
CREATE INDEX `idx_events_blog` ON `user_events` (`blog_id`);
--> statement-breakpoint

-- 网页访问量信息表
CREATE TABLE `page_views` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`view_date` text NOT NULL,
	`page_type` text NOT NULL,
	`blog_id` integer,
	`visitor_hash` text NOT NULL,
	`pv_count` integer DEFAULT 1,
	`created_at` text DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (`blog_id`) REFERENCES `blog_posts`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_views_unique` ON `page_views` (`view_date`,`page_type`,`blog_id`,`visitor_hash`);