-- MySQL 5.7.44 建表脚本（由 SQLite 转换）
-- 统一字符集与引擎
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 分组表
DROP TABLE IF EXISTS `grouping`;
CREATE TABLE `grouping` (
                            `id`       bigint(20)     NOT NULL AUTO_INCREMENT,
                            `grouping` VARCHAR(255) NOT NULL,
                            `parentId` BIGINT(20)      DEFAULT NULL,
                            `is_deleted` TINYINT(1) DEFAULT 0,
                            `deleted_at` DATETIME   DEFAULT NULL,
                            `deleted_by` BIGINT(20)    DEFAULT NULL,
                            PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 图片表
DROP TABLE IF EXISTS `image`;
CREATE TABLE `image` (
                         `id`           BIGINT(20)      NOT NULL AUTO_INCREMENT,
                         `markdown_id`  BIGINT(20)      DEFAULT NULL,
                         `original_name` VARCHAR(255) NOT NULL,
                         `stored_name`  VARCHAR(255) NOT NULL,
                         `url`          VARCHAR(500) NOT NULL,
                         `size`         BIGINT       NOT NULL,
                         `content_type` VARCHAR(100) NOT NULL,
                         `created_at`   DATETIME     DEFAULT CURRENT_TIMESTAMP,
                         PRIMARY KEY (`id`),
                         UNIQUE KEY `uk_stored_name` (`stored_name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4;

-- 3. 图片名称映射表
DROP TABLE IF EXISTS `image_name`;
CREATE TABLE `image_name` (
                              `id`          BIGINT(20)      NOT NULL AUTO_INCREMENT,
                              `markdown_id` BIGINT(20)      NOT NULL,
                              `file_name`   TEXT         NOT NULL,
                              PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Markdown 文件表
DROP TABLE IF EXISTS `markdown_file`;
CREATE TABLE `markdown_file` (
                                 `id`          BIGINT(20)      NOT NULL AUTO_INCREMENT,
                                 `grouping_id` BIGINT(20)      NOT NULL DEFAULT 1,
                                 `title`       VARCHAR(255) NOT NULL,
                                 `file_name`   VARCHAR(255) NOT NULL,
                                 `content`     LONGTEXT     NOT NULL,
                                 `created_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP,
                                 `updated_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                                 `is_deleted`  TINYINT(1)   DEFAULT 0,
                                 `deleted_at`  DATETIME     DEFAULT NULL,
                                 `deleted_by`  BIGINT(20)      DEFAULT NULL,
                                 `is_private`  TINYINT(1)   DEFAULT 0,
                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. 注册邀请码表
DROP TABLE IF EXISTS `registration_codes`;
CREATE TABLE `registration_codes` (
                                      `id`          BIGINT(20)      NOT NULL AUTO_INCREMENT,
                                      `code`        VARCHAR(255) NOT NULL,
                                      `expiry_time` DATETIME     NOT NULL,
                                      `created_by`  VARCHAR(100) DEFAULT NULL,
                                      `created_at`  DATETIME     DEFAULT CURRENT_TIMESTAMP,
                                      PRIMARY KEY (`id`),
                                      UNIQUE KEY `uk_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- 6. 系统设置表
DROP TABLE IF EXISTS `system_settings`;
CREATE TABLE `system_settings` (
                                   `setting_key` VARCHAR(100) NOT NULL,
                                   `setting_value` TEXT,
                                   `description`   TEXT,
                                   PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. 用户表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
                        `id`            BIGINT(20)      NOT NULL AUTO_INCREMENT,
                        `username`      VARCHAR(50)  NOT NULL,
                        `password`      VARCHAR(255) NOT NULL,
                        `email`         VARCHAR(100) DEFAULT NULL,
                        `role`          VARCHAR(50)  DEFAULT 'USER' COMMENT '用户角色：ADMIN-管理员，USER-普通用户',
                        `created_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP,
                        `updated_at`    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        `token`         VARCHAR(255) DEFAULT NULL,
                        `token_enddata` DATETIME     DEFAULT NULL,
                        PRIMARY KEY (`id`),
                        UNIQUE KEY `uk_username` (`username`),
                        KEY `idx_user_role` (`role`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;