43 lines
1.9 KiB
SQL
43 lines
1.9 KiB
SQL
-- Forum Phase 5: Achievement system + topic subscriptions
|
|
|
|
CREATE TABLE `achievements` (
|
|
`key` VARCHAR(50) NOT NULL,
|
|
`name` VARCHAR(50) NOT NULL,
|
|
`description` VARCHAR(200) NOT NULL,
|
|
`icon` VARCHAR(20) NOT NULL,
|
|
`tier` VARCHAR(20) NOT NULL DEFAULT 'bronze',
|
|
`points` INT NOT NULL DEFAULT 0,
|
|
`category` VARCHAR(30) NOT NULL DEFAULT 'general',
|
|
`sort_order` INT NOT NULL DEFAULT 0,
|
|
`is_active` BOOLEAN NOT NULL DEFAULT TRUE,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (`key`),
|
|
INDEX `achievements_category_is_active_sort_order_idx`(`category`, `is_active`, `sort_order`)
|
|
);
|
|
|
|
CREATE TABLE `user_achievements` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`user_id` INT NOT NULL,
|
|
`achievement_key` VARCHAR(50) NOT NULL,
|
|
`progress` INT NOT NULL DEFAULT 0,
|
|
`unlocked_at` DATETIME(3) NULL,
|
|
`notified_at` DATETIME(3) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `user_achievements_user_id_achievement_key_key`(`user_id`, `achievement_key`),
|
|
INDEX `user_achievements_user_id_unlocked_at_idx`(`user_id`, `unlocked_at`),
|
|
INDEX `user_achievements_achievement_key_idx`(`achievement_key`)
|
|
);
|
|
|
|
CREATE TABLE `forum_topic_subscriptions` (
|
|
`id` INT NOT NULL AUTO_INCREMENT,
|
|
`topic_id` INT NOT NULL,
|
|
`user_id` INT NOT NULL,
|
|
`notify_reply` BOOLEAN NOT NULL DEFAULT TRUE,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE INDEX `forum_topic_subscriptions_topic_id_user_id_key`(`topic_id`, `user_id`),
|
|
INDEX `forum_topic_subscriptions_user_id_created_at_idx`(`user_id`, `created_at`)
|
|
);
|