271 lines
10 KiB
SQL
271 lines
10 KiB
SQL
-- CreateTable
|
|
CREATE TABLE `users` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`openid` VARCHAR(64) NOT NULL,
|
|
`nickname` VARCHAR(32) NULL,
|
|
`headicon_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
|
|
`wx_session_key` VARCHAR(255) NULL,
|
|
`session_key_updated_at` DATETIME(3) NULL,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`registered_at` DATETIME(3) NOT NULL,
|
|
`last_login_at` DATETIME(3) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `users_openid_key`(`openid`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `courses` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`code` VARCHAR(64) NOT NULL,
|
|
`title` VARCHAR(128) NOT NULL,
|
|
`intro` VARCHAR(255) NULL,
|
|
`cover_image` VARCHAR(255) NULL,
|
|
`price_fen` INTEGER UNSIGNED NOT NULL DEFAULT 0,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`sort_order` INTEGER NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `courses_code_key`(`code`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `lessons` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`course_id` INTEGER UNSIGNED NOT NULL,
|
|
`play_id` VARCHAR(64) NOT NULL,
|
|
`chapter_id` VARCHAR(64) NOT NULL,
|
|
`chapter_title` VARCHAR(255) NULL,
|
|
`lesson_title` VARCHAR(255) NULL,
|
|
`duration` VARCHAR(16) NULL,
|
|
`published_at` DATETIME(3) NULL,
|
|
`cover_image` VARCHAR(255) NULL,
|
|
`sort_order` INTEGER NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
INDEX `lessons_course_id_sort_order_idx`(`course_id`, `sort_order`),
|
|
UNIQUE INDEX `lessons_course_id_chapter_id_play_id_key`(`course_id`, `chapter_id`, `play_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `free_lessons` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`series_no` INTEGER NOT NULL,
|
|
`sort_order` INTEGER NOT NULL DEFAULT 0,
|
|
`course_name` VARCHAR(255) NOT NULL,
|
|
`video_title` VARCHAR(255) NULL,
|
|
`intro` VARCHAR(255) NULL,
|
|
`publisher` VARCHAR(255) NULL,
|
|
`published_at` DATETIME(3) NULL,
|
|
`duration` VARCHAR(16) NULL,
|
|
`video_m3u8` VARCHAR(255) NULL,
|
|
`cover_image` VARCHAR(255) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
INDEX `free_lessons_series_no_sort_order_idx`(`series_no`, `sort_order`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `home_courses` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`course_name` VARCHAR(255) NOT NULL,
|
|
`intro` VARCHAR(255) NULL,
|
|
`published_at` DATETIME(3) NULL,
|
|
`cover_image` VARCHAR(255) NULL,
|
|
`content` LONGTEXT NULL,
|
|
`course_id` INTEGER UNSIGNED NULL,
|
|
`sort_order` INTEGER NOT NULL DEFAULT 0,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
INDEX `home_courses_status_sort_order_idx`(`status`, `sort_order`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `home_free_courses` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`series_no` INTEGER NOT NULL,
|
|
`course_name` VARCHAR(255) NOT NULL,
|
|
`intro` VARCHAR(255) NULL,
|
|
`published_at` DATETIME(3) NULL,
|
|
`cover_image` VARCHAR(255) NULL,
|
|
`sort_order` INTEGER NOT NULL DEFAULT 0,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
|
|
INDEX `home_free_courses_status_sort_order_idx`(`status`, `sort_order`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `banners` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`filename` VARCHAR(255) NOT NULL,
|
|
`sort_order` INTEGER NOT NULL DEFAULT 0,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
INDEX `banners_status_sort_order_idx`(`status`, `sort_order`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `articles` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`legacy_page_id` VARCHAR(32) NULL,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`intro` VARCHAR(255) NULL,
|
|
`publisher` VARCHAR(255) NULL,
|
|
`published_at` DATETIME(3) NULL,
|
|
`content` LONGTEXT NULL,
|
|
`sort_order` INTEGER NOT NULL DEFAULT 0,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
INDEX `articles_status_published_at_idx`(`status`, `published_at`),
|
|
INDEX `articles_legacy_page_id_idx`(`legacy_page_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `course_tips` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`tip_key` INTEGER NOT NULL,
|
|
`content` TEXT NULL,
|
|
`title` TINYTEXT NULL,
|
|
|
|
UNIQUE INDEX `course_tips_tip_key_key`(`tip_key`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `settings` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`key` VARCHAR(64) NOT NULL,
|
|
`value` VARCHAR(255) NULL,
|
|
`remark` VARCHAR(255) NULL,
|
|
|
|
UNIQUE INDEX `settings_key_key`(`key`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `entitlements` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`user_id` INTEGER UNSIGNED NOT NULL,
|
|
`course_id` INTEGER UNSIGNED NOT NULL,
|
|
`source` TINYINT NOT NULL,
|
|
`ref_id` INTEGER UNSIGNED NULL,
|
|
`granted_at` DATETIME(3) NOT NULL,
|
|
`expire_at` DATETIME(3) NULL,
|
|
`note` VARCHAR(255) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
INDEX `entitlements_course_id_idx`(`course_id`),
|
|
UNIQUE INDEX `entitlements_user_id_course_id_key`(`user_id`, `course_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `orders` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`out_trade_no` VARCHAR(32) NOT NULL,
|
|
`user_id` INTEGER UNSIGNED NOT NULL,
|
|
`course_id` INTEGER UNSIGNED NOT NULL,
|
|
`product_id` VARCHAR(64) NOT NULL,
|
|
`quantity` INTEGER UNSIGNED NOT NULL DEFAULT 1,
|
|
`amount_fen` INTEGER UNSIGNED NOT NULL,
|
|
`status` TINYINT NOT NULL,
|
|
`env` TINYINT NOT NULL DEFAULT 0,
|
|
`wx_order_id` VARCHAR(64) NULL,
|
|
`attach` VARCHAR(255) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`paid_at` DATETIME(3) NULL,
|
|
`delivered_at` DATETIME(3) NULL,
|
|
`refunded_at` DATETIME(3) NULL,
|
|
|
|
UNIQUE INDEX `orders_out_trade_no_key`(`out_trade_no`),
|
|
UNIQUE INDEX `orders_wx_order_id_key`(`wx_order_id`),
|
|
INDEX `orders_user_id_created_at_idx`(`user_id`, `created_at`),
|
|
INDEX `orders_status_created_at_idx`(`status`, `created_at`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `payment_notify_log` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`wx_order_id` VARCHAR(64) NULL,
|
|
`out_trade_no` VARCHAR(32) NULL,
|
|
`event` VARCHAR(64) NULL,
|
|
`raw_body` MEDIUMTEXT NULL,
|
|
`processed` TINYINT NOT NULL DEFAULT 0,
|
|
`err_msg` VARCHAR(255) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
INDEX `payment_notify_log_wx_order_id_idx`(`wx_order_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `products` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`course_id` INTEGER UNSIGNED NOT NULL,
|
|
`wx_product_id` VARCHAR(64) NOT NULL,
|
|
`name` VARCHAR(128) NOT NULL,
|
|
`price_fen` INTEGER UNSIGNED NOT NULL,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `products_wx_product_id_key`(`wx_product_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `admin_users` (
|
|
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`username` VARCHAR(64) NOT NULL,
|
|
`password_hash` VARCHAR(255) NOT NULL,
|
|
`display_name` VARCHAR(64) NULL,
|
|
`role` TINYINT NOT NULL DEFAULT 2,
|
|
`totp_secret` VARCHAR(64) NULL,
|
|
`status` TINYINT NOT NULL DEFAULT 1,
|
|
`last_login_at` DATETIME(3) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `admin_users_username_key`(`username`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `lessons` ADD CONSTRAINT `lessons_course_id_fkey` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `home_courses` ADD CONSTRAINT `home_courses_course_id_fkey` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `entitlements` ADD CONSTRAINT `entitlements_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `entitlements` ADD CONSTRAINT `entitlements_course_id_fkey` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `orders` ADD CONSTRAINT `orders_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `orders` ADD CONSTRAINT `orders_course_id_fkey` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE `products` ADD CONSTRAINT `products_course_id_fkey` FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|