SET character_set_connection=utf8;
SET character_set_database=utf8;
SET character_set_results=utf8;
SET character_set_server=utf8;

CREATE TABLE IF NOT EXISTS `sys_asset` (
	`uid`		int unsigned not null auto_increment,
	`original_asset` varchar(255) not null,
	`filemtime`	int unsigned not null,
	`fileext`	varchar(4),
	`copied_asset`	varchar(255),
	`asset_url`	varchar(255),
	primary key(`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_doc` (
	`uid`		int unsigned not null auto_increment,
	`menu_uid`	int unsigned not null comment 'sys_menu.uid',
	`page`		tinyint unsigned not null default '1',
	`tag`		varchar(255),
	`title`		varchar(255),
	`content`	text,
	`file`		varchar(255) comment 'doc file name for include',
	`read_count`	int unsigned default '0',
	`last_accessed`	datetime,
	`created_at`	datetime DEFAULT CURRENT_TIMESTAMP,
	`updated_at`	datetime DEFAULT CURRENT_TIMESTAMP,
	primary key (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_file` (
    `uid`       int unsigned auto_increment,
    `type`      varchar(4) comment 'File Category: HD|INLN, etc',
    `sort`      tinyint unsigned not null default '0',
    `name`      varchar(255),
    `path`      varchar(255) comment 'relative path to file root (files)',
    `realname`  varchar(50),
    `download`  int unsigned not null default '0',
    `ext`       char(4),
    `size`      int unsigned not null default '0',
    `tbl`       varchar(100),
    `idx`       int unsigned not null default '0',
    `description`   varchar(255),
    `show`      tinyint unsigned not null default '1',
    primary key(`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_group` (
	`uid`		int unsigned auto_increment,
	`id`		varchar(100) not null unique,
	`sort`		int unsigned default '0',
	`category`	varchar(50),
	`subcategory`	varchar(50),
	`name`			varchar(255),
	`level_policy`	json comment 'Define group level policy (JSON)',
	key (`category`),
	primary key(`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_group_ext` (
	`group_uid`		int unsigned not null,
	`member_uid`	int unsigned not null,
	`level`		tinyint unsigned not null default '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_language` (
    `uid`       int unsigned auto_increment,
    `str`      	varchar(50) not null unique,
    `kr`		varchar(50),
    `tw`		varchar(50),
    primary key(`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_menu` (
	`uid`		int unsigned auto_increment,
	`family`	int unsigned not null default '0',
	`parent`	int unsigned not null default '0',
	`left`		int unsigned not null default '0',
	`right`		int unsigned not null default '0',
	`depth`		tinyint unsigned not null default '0',
	`namespace`	varchar(50),
	`id`		varchar(100) not null,
	`title`		varchar(255) not null,
	`type`		varchar(50) not null default 'dummy',
	`class`		varchar(100),
	`permission`	tinyint unsigned not null default '3',
	`hidden`	tinyint unsigned not null default '0',
	`options`	json,
	`description`	varchar(500),
	`image`	varchar(100),
	key (`namespace`),
	key (`id`),
	primary key (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `sys_menu` (`family`, `left`, `right`, `id`, `class`, `title`, `type`) VALUES (1, 1, 2, 'main', 'Main', 'Main', 'Main');

CREATE TABLE IF NOT EXISTS `sys_menu_ext` (
	`menu_uid`		int unsigned not null,
	`level_or_uid`		int unsigned not null comment 'level or group_uid',
	`permission`	tinyint unsigned not null default '0',
	`type`	varchar(10) comment 'admin/level/group',
	key (`type`),
	key (`menu_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_main` (
	`uid`		int unsigned not null auto_increment,
	`db`		varchar(100),
	`id`		varchar(100) not null,
	`type`		char(10),
	`sort`		tinyint unsigned not null default '0',
	`menu_uid`	int unsigned not null default '0' comment 'sys_menu.uid',
	`link`		varchar(255),
	`title`		varchar(255) not null,
	`description`	varchar(500),
	`content`	json,
	`image`		varchar(100) comment 'image url',
	`category`	varchar(50),
	`tag`		varchar(255),
	`options`	json,
	`display`	enum('Y', 'N') not null default 'Y',
	`expired_at` date,
	key (`id`),
	primary key (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sys_template` (
	`uid`		int unsigned not null auto_increment,
	`db`		varchar(100),
	`id`		varchar(100) not null,
	`contents`	json comment 'HTML/TEXT/External content',
	primary key (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
