-- ================================================
-- 다중게시판 테이블 생성 SQL (DB: board)
-- ================================================

CREATE TABLE IF NOT EXISTS `ai_board_config` (
  `brd_id`      varchar(30)  NOT NULL COMMENT '게시판ID',
  `brd_name`    varchar(100) NOT NULL COMMENT '게시판명',
  `brd_type`    tinyint(1)   NOT NULL DEFAULT '1' COMMENT '1=일반 2=갤러리',
  `use_comment` tinyint(1)   NOT NULL DEFAULT '1' COMMENT '댓글사용 1=사용',
  `use_write`   tinyint(1)   NOT NULL DEFAULT '1' COMMENT '작성권한 1=로그인 2=누구나',
  `file_cnt`    tinyint(2)   NOT NULL DEFAULT '3' COMMENT '첨부파일최대개수',
  `file_size`   int(11)      NOT NULL DEFAULT '5120' COMMENT '파일최대크기(KB)',
  `list_cnt`    tinyint(3)   NOT NULL DEFAULT '15' COMMENT '목록당게시글수',
  `sort_order`  tinyint(3)   NOT NULL DEFAULT '1',
  `use_yn`      tinyint(1)   NOT NULL DEFAULT '1',
  PRIMARY KEY (`brd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ai_board` (
  `idx`          int(11)      NOT NULL AUTO_INCREMENT,
  `brd_id`       varchar(30)  NOT NULL,
  `is_notice`    tinyint(1)   NOT NULL DEFAULT '0',
  `subject`      varchar(300) NOT NULL,
  `content`      longtext,
  `writer`       varchar(100) NOT NULL,
  `writer_email` varchar(200) DEFAULT '',
  `hit`          int(11)      NOT NULL DEFAULT '0',
  `reg_dt`       datetime     NOT NULL,
  `mod_dt`       datetime     DEFAULT NULL,
  `del_yn`       tinyint(1)   NOT NULL DEFAULT '0',
  PRIMARY KEY (`idx`),
  KEY `idx_brd_id` (`brd_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ai_board_file` (
  `idx`        int(11)      NOT NULL AUTO_INCREMENT,
  `board_idx`  int(11)      NOT NULL,
  `ori_name`   varchar(300) NOT NULL,
  `save_name`  varchar(300) NOT NULL,
  `file_size`  int(11)      NOT NULL DEFAULT '0',
  `file_ext`   varchar(20)  DEFAULT '',
  `img_yn`     tinyint(1)   NOT NULL DEFAULT '0',
  `reg_dt`     datetime     NOT NULL,
  PRIMARY KEY (`idx`),
  KEY `idx_board_idx` (`board_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ai_board_comment` (
  `idx`          int(11)  NOT NULL AUTO_INCREMENT,
  `board_idx`    int(11)  NOT NULL,
  `content`      text     NOT NULL,
  `writer`       varchar(100) NOT NULL,
  `writer_email` varchar(200) DEFAULT '',
  `reg_dt`       datetime NOT NULL,
  `del_yn`       tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idx`),
  KEY `idx_board_idx` (`board_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 기본 샘플 게시판 3개
INSERT IGNORE INTO `ai_board_config` VALUES ('notice',  '공지사항',   1, 0, 1, 2, 5120,  15, 1, 1);
INSERT IGNORE INTO `ai_board_config` VALUES ('free',    '자유게시판', 1, 1, 1, 3, 5120,  15, 2, 1);
INSERT IGNORE INTO `ai_board_config` VALUES ('gallery', '갤러리',     2, 1, 1, 5, 10240, 12, 3, 1);
