CREATE TABLE `achievements` (
	`id` char(36) NOT NULL,
	`type` enum('knowledge','behavior') NOT NULL,
	`name` varchar(100) NOT NULL,
	`description` varchar(300) NOT NULL,
	`icon_url` varchar(500),
	`condition` json NOT NULL,
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `achievements_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `admin_audit_log` (
	`id` int AUTO_INCREMENT NOT NULL,
	`admin_id` varchar(36) NOT NULL,
	`action` varchar(10) NOT NULL,
	`resource` varchar(500),
	`details` json,
	`ip_address` varchar(45),
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `admin_audit_log_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `admin_users` (
	`id` char(36) NOT NULL,
	`username` varchar(50) NOT NULL,
	`password_hash` varchar(255) NOT NULL,
	`role` enum('admin','super_admin') DEFAULT 'admin',
	`is_active` tinyint DEFAULT 1,
	`last_login_at` datetime,
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `admin_users_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_admin_username` UNIQUE(`username`)
);
--> statement-breakpoint
CREATE TABLE `categories` (
	`id` varchar(50) NOT NULL,
	`name` varchar(100) NOT NULL,
	`slug` varchar(100) NOT NULL,
	`parent_id` varchar(50),
	`sort_order` int DEFAULT 0,
	`question_count` int DEFAULT 0,
	`status` enum('active','inactive') DEFAULT 'active',
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `categories_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_slug` UNIQUE(`slug`)
);
--> statement-breakpoint
CREATE TABLE `knowledge_cards` (
	`id` char(36) NOT NULL,
	`question_id` char(36) NOT NULL,
	`summary` varchar(300) NOT NULL,
	`deep_dive` text,
	`source_ref` varchar(500),
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `knowledge_cards_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_question` UNIQUE(`question_id`)
);
--> statement-breakpoint
CREATE TABLE `leaderboard_snapshots` (
	`id` char(36) NOT NULL,
	`user_id` char(36) NOT NULL,
	`tier` enum('bronze','silver','gold','platinum','diamond','master','grandmaster','champion','legend','mythic') NOT NULL,
	`weekly_xp` int DEFAULT 0,
	`rank` int,
	`league` varchar(50),
	`week_start` date,
	`week_end` date,
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `leaderboard_snapshots_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `question_ratings` (
	`id` char(36) NOT NULL,
	`user_id` char(36) NOT NULL,
	`question_id` char(36) NOT NULL,
	`rating` enum('good','bad') NOT NULL,
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `question_ratings_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_user_question_rating` UNIQUE(`user_id`,`question_id`)
);
--> statement-breakpoint
CREATE TABLE `questions` (
	`id` char(36) NOT NULL,
	`stem` json NOT NULL,
	`content_type` enum('text','image','video','audio') NOT NULL,
	`correct_answer` varchar(500) NOT NULL,
	`distractors` json NOT NULL,
	`category_id` varchar(50) NOT NULL,
	`difficulty` tinyint,
	`dynamic_difficulty` decimal(3,1),
	`source` enum('system','ugc') DEFAULT 'system',
	`creator_id` char(36),
	`status` enum('draft','reviewing','published','archived') DEFAULT 'draft',
	`stats` json DEFAULT ('{"timesAnswered":0,"correctRate":0,"avgTimeMs":0}'),
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `questions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `skill_tree` (
	`id` char(36) NOT NULL,
	`category_id` varchar(50) NOT NULL,
	`title` varchar(100) NOT NULL,
	`parent_id` char(36),
	`sort_order` int DEFAULT 0,
	`questions_required` tinyint DEFAULT 4,
	`pass_threshold` tinyint DEFAULT 2,
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `skill_tree_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `subscriptions` (
	`id` char(36) NOT NULL,
	`user_id` char(36) NOT NULL,
	`tier` enum('free','pro','proplus') DEFAULT 'free',
	`platform` enum('huawei','apple','google'),
	`purchase_token` varchar(500),
	`expires_at` datetime,
	`auto_renew` tinyint DEFAULT 0,
	`status` enum('active','expired','cancelled') DEFAULT 'active',
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `subscriptions_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_subscription_user` UNIQUE(`user_id`)
);
--> statement-breakpoint
CREATE TABLE `user_achievements` (
	`id` char(36) NOT NULL,
	`user_id` char(36) NOT NULL,
	`achievement_id` char(36) NOT NULL,
	`unlocked_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `user_achievements_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_user_achievement` UNIQUE(`user_id`,`achievement_id`)
);
--> statement-breakpoint
CREATE TABLE `user_chapter_progress` (
	`id` char(36) NOT NULL,
	`user_id` char(36) NOT NULL,
	`chapter_id` char(36) NOT NULL,
	`status` enum('locked','unlocked','passed','perfect') DEFAULT 'locked',
	`best_correct_count` tinyint DEFAULT 0,
	`attempts` int DEFAULT 0,
	`completed_at` datetime,
	CONSTRAINT `user_chapter_progress_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_user_chapter` UNIQUE(`user_id`,`chapter_id`)
);
--> statement-breakpoint
CREATE TABLE `user_feedback` (
	`id` char(36) NOT NULL,
	`user_id` char(36) NOT NULL,
	`content` text NOT NULL,
	`contact` varchar(255),
	`page_context` varchar(200),
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `user_feedback_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `user_progress` (
	`id` char(36) NOT NULL,
	`user_id` char(36) NOT NULL,
	`question_id` char(36) NOT NULL,
	`correct` tinyint NOT NULL,
	`time_ms` int,
	`answered_at` datetime DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `user_progress_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `users` (
	`id` char(36) NOT NULL,
	`auth_type` enum('huawei','guest','phone','apple','google') NOT NULL,
	`auth_id` varchar(255) NOT NULL,
	`nickname` varchar(50),
	`avatar_url` varchar(500),
	`tier` enum('free','pro','proplus') DEFAULT 'free',
	`xp_total` int DEFAULT 0,
	`streak_days` int DEFAULT 0,
	`streak_last_date` date,
	`hearts_remaining` tinyint DEFAULT 5,
	`hearts_last_restore` datetime,
	`daily_xp_goal` smallint DEFAULT 50,
	`daily_xp_earned` smallint DEFAULT 0,
	`daily_xp_date` date,
	`current_theme` varchar(20) DEFAULT 'inkTeal',
	`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
	`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `uk_auth` UNIQUE(`auth_type`,`auth_id`)
);
--> statement-breakpoint
ALTER TABLE `knowledge_cards` ADD CONSTRAINT `knowledge_cards_question_id_questions_id_fk` FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `questions` ADD CONSTRAINT `questions_category_id_categories_id_fk` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `skill_tree` ADD CONSTRAINT `skill_tree_category_id_categories_id_fk` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `user_chapter_progress` ADD CONSTRAINT `user_chapter_progress_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `user_chapter_progress` ADD CONSTRAINT `user_chapter_progress_chapter_id_skill_tree_id_fk` FOREIGN KEY (`chapter_id`) REFERENCES `skill_tree`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `user_progress` ADD CONSTRAINT `user_progress_user_id_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `user_progress` ADD CONSTRAINT `user_progress_question_id_questions_id_fk` FOREIGN KEY (`question_id`) REFERENCES `questions`(`id`) ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `idx_user_week` ON `leaderboard_snapshots` (`user_id`,`week_start`);--> statement-breakpoint
CREATE INDEX `idx_user_answered` ON `user_progress` (`user_id`,`answered_at`);