DROP TABLE IF EXISTS servermgr.tw_user;

CREATE TABLE servermgr.tw_user (
    uid varchar(36) NOT NULL COMMENT "사용자 UUID",
    id varchar(30) NOT NULL,
    passwd varchar(100) NOT NULL,
    name varchar(20) NOT NULL COMMENT "사용자명",
    email varchar(50) NOT NULL,
    phone varchar(20) NULL COMMENT '연락처',
    mobile varchar(20) NULL COMMENT '핸드폰',
    role varchar(255) NOT NULL DEFAULT 'user' COMMENT '사용자등급',
    status varchar(10) NOT NULL DEFAULT 'use' COMMENT 'use: 사용,unuse: 사용않함',
    updated_at timestamp NULL DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    deleted_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (uid),
    UNIQUE KEY (id),
    UNIQUE KEY (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT ='사용자 정보';
-- insert into tw_user (uid,id,passwd,name,email,role,status) select uuid(),id,passwd,name,email,role,status from cfmgr.user;
DROP TABLE IF EXISTS servermgr.tw_user_profile;

DROP TABLE IF EXISTS servermgr.tw_user_sns;

CREATE TABLE servermgr.tw_user_sns (
    uid int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    user_uid varchar(36) NULL COMMENT '사용자 정보',
    site varchar(20) NOT NULL COMMENT 'Site: GOOGLE,FACEBOOK 등등',
    id varchar(255) NOT NULL COMMENT 'sns 로그인 인중후 Return ID값',
    name varchar(50) NOT NULL,
    email varchar(50) NOT NULL,
    detail text NOT NULL COMMENT 'JSON형식 원본값',
    status varchar(10) NOT NULL DEFAULT 'use' COMMENT 'use: 사용,unuse: 사용않함',
    updated_at timestamp NULL DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (uid),
    UNIQUE KEY (site,id),
    CONSTRAINT FOREIGN KEY (user_uid) REFERENCES tw_user (uid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT ='SNS 로그인 후 정보';


DROP TABLE IF EXISTS servermgr.tw_category;
-- 1. 게시물 추가전 grpno에 해당하는 max(grporder)+1씩증가 작업
-- update tw_category set grporder=grporder+1 where grpno=그룹번호 and grporder > 선택한 grpno
-- 2. 게시물 추가시 작업
-- insert tw_category grpno=그룹번호,grporder=grporder+1,grpdepth=grpdepth+1
-- 3. 게시물 조회시 작업
-- select * from tw_category order by grpno desc,grporder asc
CREATE TABLE servermgr.tw_category (
    uid int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    grpno int(10) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Group번호: 상위가없을시 기본 uid와 같음,항상 숫자여야함',
    grporder int(5) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Group순서: 상위가없을시 1부터시작',
    grpdepth int(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Group깊이: 상위가없을시 1부터시작 , 상위 grpdepth+1씩 추가필요',
    parent_uid int(10) UNSIGNED NULL COMMENT '부모UID',
    name varchar(255) NOT NULL COMMENT '분류명',
    linkurl varchar(100) NOT NULL DEFAULT '/front/board' COMMENT 'Front Link URL',
    isaccess varchar(30) NOT NULL DEFAULT 'guest' COMMENT '접근권한',
    isread varchar(30) NOT NULL DEFAULT 'guest' COMMENT '읽기권한',
    iswrite varchar(30) NOT NULL DEFAULT 'guest' COMMENT '쓰기권한',
    isreply varchar(30) NOT NULL DEFAULT 'guest' COMMENT '답글권한',
    isupload varchar(30) NOT NULL DEFAULT 'guest' COMMENT 'Upload권한',
    isdownload varchar(30) NOT NULL DEFAULT 'guest' COMMENT 'Download권한',
    head text NULL COMMENT '위 내용',
    tail text NULL COMMENT '아래 내용',
    status varchar(10) NOT NULL DEFAULT 'use' COMMENT 'use: 표시,unuse: 표시않함',
    updated_at timestamp NULL DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    deleted_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT ='분류';

DROP TABLE IF EXISTS servermgr.tw_board;
-- 1. 게시물 추가전 grpno에 해당하는 max(grporder)+1씩증가 작업
-- update tw_board set grporder=grporder+1 where grpno=그룹번호 and grporder > 선택한 grpno
-- 2. 게시물 추가시 작업
-- insert tw_board grpno=그룹번호,grporder=grporder+1,grpdepth=grpdepth+1
-- 3. 게시물 조회시 작업
-- select * from tw_board order by grpno desc,grporder asc
CREATE TABLE servermgr.tw_board (
    uid int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    grpno int(10) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Group번호: 상위가없을시 기본 uid와 같음,항상 숫자여야함',
    grporder int(5) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Group순서: 상위가없을시 1부터시작',
    grpdepth int(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Group깊이: 상위가없을시 1부터시작 , 상위 grpdepth+1씩 추가필요',
    parent_uid int(10) UNSIGNED NULL COMMENT '부모UID',
	category_uid int(10) UNSIGNED NOT NULL COMMENT '분류 UID',
    user_uid varchar(36) NULL COMMENT '작성자 정보',
    title varchar(255) NOT NULL COMMENT '제목',
    content text NOT NULL COMMENT '내용',
	passwd varchar(20) NULL COMMENT '작성자 암호',
    board_file varchar(255) NULL COMMENT '파일명',
	view_cnt int(5) NOT NULL DEFAULT 0 COMMENT '조회수',
    status varchar(10) NOT NULL DEFAULT 'use' COMMENT 'use: 사용, unuse: 사용않함 등등',
    updated_at timestamp NULL DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    deleted_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (uid),
    CONSTRAINT FOREIGN KEY (category_uid) REFERENCES tw_category (uid),
    CONSTRAINT FOREIGN KEY (user_uid) REFERENCES tw_user (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT ='게시물 정보';

DROP TABLE IF EXISTS servermgr.tw_sitepage;
CREATE TABLE servermgr.tw_sitepage (
    uid int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	category_uid int(10) UNSIGNED NOT NULL COMMENT '분류 UID',
    user_uid varchar(36) NULL COMMENT '작성자 정보',
    title varchar(255) NOT NULL COMMENT '제목',
    content text NOT NULL COMMENT '내용',
    status varchar(10) NOT NULL DEFAULT 'use' COMMENT 'use: 사용, unuse: 사용않함 등등',
    updated_at timestamp NULL DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    deleted_at timestamp NULL DEFAULT NULL,
    PRIMARY KEY (uid),
    CONSTRAINT FOREIGN KEY (category_uid) REFERENCES tw_category (uid),
    CONSTRAINT FOREIGN KEY (user_uid) REFERENCES tw_user (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT ='사이트페이지 정보';

DROP TABLE IF EXISTS servermgr.tw_hpilo;

CREATE TABLE
    servermgr.tw_hpilo (
        uid int(10) unsigned NOT NULL AUTO_INCREMENT,
        customer varchar(100) NOT NULL COMMENT '고객정보',
        id varchar(20) NOT NULL DEFAULT 'Administrator' COMMENT 'API IP Address',
        passwd varchar(20) NOT NULL COMMENT 'Password',
        ip varchar(50) NOT NULL COMMENT 'API IP Address',
        port int(5) unsigned NOT NULL COMMENT 'API Port',
        model varchar(255) NOT NULL DEFAULT 'model' COMMENT 'model',
        processor varchar(255) NOT NULL DEFAULT 'none' COMMENT 'processor',
        memory int(4) unsigned NOT NULL DEFAULT '0' COMMENT 'memory',
        health varchar(10) NOT NULL DEFAULT 'OK' COMMENT 'All Device Health',
        power varchar(10) NOT NULL DEFAULT 'Off' COMMENT 'Power status',
        detail text NOT NULL DEFAULT '' COMMENT '상세내용',
        status varchar(10) NOT NULL DEFAULT 'use',
        updated_at timestamp NULL DEFAULT NULL,
        created_at timestamp NOT NULL DEFAULT current_timestamp(),
        PRIMARY KEY (uid),
        UNIQUE KEY (ip,port)
    ) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci COMMENT = 'hpilo 정보';