create database if not exists 4dageOffcial default character set utf8mb4 collate utf8mb4_unicode_ci;

-- 切换到4维官网数据库
use 4dageOffcial;

/**
* 清除所有表
*/
drop table if exists `case`;
drop table if exists `news`;
drop table if exists `user`;


/**
 * 用户表
 */
create table if not exists `user`(
    `id`          integer         not null    auto_increment,
    `name`        char(36)        not null,
    `psw`         char(33),
    `describe`    varchar(100),      
    `time`        datetime        not null    default CURRENT_TIMESTAMP,
    `hide`        integer         not null    default 0,
    primary key(`id`)
) engine=InnoDB charset=utf8mb4;


/**
 * 如果没有初始化管理员账号则初始化一个
 */
INSERT INTO `user`(`name`, `psw`, `describe`) 
SELECT 'admin', 'c3284d0f94606de1fd2af172aba15bf3', '管理员' FROM DUAL 
WHERE NOT EXISTS(SELECT `describe` FROM `user` WHERE `describe` = '管理员');

-- 案例
create table if not exists `case`(
    `id`      integer         not null    auto_increment,
    `title`   varchar(255)    not null,
    `content` longtext        not null,
    `cover`   varchar(500),
    `logo`    varchar(500),
    `label`   varchar(500),
    `date`    varchar(33)        not null,
    `sort`    integer,
    `link`    varchar(255),
    `type`    integer         not null    default 1,
    `address` varchar(255),
    `hide`    integer         not null    default 0,
    `user_id` integer         not null,

    primary key(`id`),
    fulltext (`title`) WITH PARSER ngram,
    constraint FK_case_user foreign key(`user_id`) references user(`id`)
) engine=InnoDB charset=utf8mb4;


-- 案例
create table if not exists `news`(
    `id`      integer         not null    auto_increment,
    `title`   varchar(255)    not null,
    `content` longtext        not null,
    `cover`   varchar(500),
    `label`   varchar(500),
    `date`    varchar(33)     not null ,
    `sort`    integer,
    `address` varchar(255),
    `origin`  varchar(255),
    `hide`    integer         not null    default 0,
    `user_id` integer         not null,

    primary key(`id`),
    fulltext (`title`) WITH PARSER ngram,
    constraint FK_news_user foreign key(`user_id`) references user(`id`)
) engine=InnoDB charset=utf8mb4;