#
# 데이터베이스 : nmail2
#

# --------------------------------------------------------

#
# 테이블 구조 nmail_addr
#

CREATE TABLE nmail_addr (
  ad_no bigint(20) unsigned NOT NULL auto_increment,
  adg_no bigint(20) unsigned NOT NULL default '0',
  ad_name varchar(50) default NULL,
  ad_email varchar(255) default NULL,
  ad_mobile varchar(20) default NULL,
  ad_office varchar(50) default NULL,
  ad_addr varchar(255) default NULL,
  ad_tel varchar(20) default NULL,
  ad_fax varchar(20) default NULL,
  ad_homepage varchar(255) default NULL,
  ad_memo text,
  PRIMARY KEY  (ad_no),
  KEY adg_no (adg_no)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_addr_group
#

CREATE TABLE nmail_addr_group (
  adg_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  adg_name varchar(50) default NULL,
  PRIMARY KEY  (adg_no),
  KEY adg_no (adg_no,m_no)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_board
#

CREATE TABLE nmail_board (
  b_no bigint(20) unsigned NOT NULL auto_increment,
  ba_no smallint(5) unsigned NOT NULL default '0',
  bca_no bigint(20) unsigned default NULL,
  m_no int(10) unsigned NOT NULL default '0',
  b_name varchar(20) NOT NULL default '',
  b_pwd varchar(32) NOT NULL default '',
  b_email varchar(255) default NULL,
  b_homepage varchar(255) default NULL,
  b_isuse_reply_mail char(1) NOT NULL default 'N',
  b_subject varchar(255) NOT NULL default '',
  b_html char(1) NOT NULL default 'N',
  b_memo text NOT NULL,
  b_link varchar(255) default NULL,
  b_file varchar(255) default NULL,
  b_refer bigint(20) unsigned NOT NULL default '0',
  b_down bigint(20) unsigned NOT NULL default '0',
  b_vote bigint(20) unsigned NOT NULL default '0',
  b_comment bigint(20) unsigned NOT NULL default '0',
  b_ref bigint(20) unsigned NOT NULL default '0',
  b_re_step smallint(5) unsigned NOT NULL default '0',
  b_re_level smallint(5) unsigned NOT NULL default '0',
  b_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  b_reghost varchar(15) NOT NULL default '',
  b_last_modifydate datetime default NULL,
  b_last_modifyhost varchar(15) default NULL,
  PRIMARY KEY  (b_no)
) COMMENT='게시판';

# --------------------------------------------------------

#
# 테이블 구조 nmail_board_admin
#

CREATE TABLE nmail_board_admin (
  ba_no smallint(5) unsigned NOT NULL auto_increment,
  ba_name varchar(255) NOT NULL default '',
  d_no int(10) unsigned NOT NULL default '0',
  ba_isuse_link char(1) NOT NULL default 'Y',
  ba_isuse_file char(1) NOT NULL default 'Y',
  ba_isuse_comment char(1) NOT NULL default 'Y',
  ba_isuse_reply char(1) NOT NULL default 'Y',
  ba_isuse_category char(1) NOT NULL default 'N',
  ba_page tinyint(3) unsigned NOT NULL default '10',
  ba_header_html char(1) NOT NULL default 'N',
  ba_header text,
  ba_footer_html char(1) NOT NULL default 'N',
  ba_footer text,
  ba_inc_header varchar(255) default NULL,
  ba_inc_footer varchar(255) default NULL,
  ba_perm_access tinyint(3) unsigned NOT NULL default '3',
  ba_perm_register tinyint(3) unsigned NOT NULL default '3',
  ba_perm_reply tinyint(3) unsigned NOT NULL default '3',
  ba_perm_read tinyint(3) unsigned NOT NULL default '3',
  ba_perm_down tinyint(3) unsigned NOT NULL default '3',
  ba_perm_modify tinyint(3) unsigned NOT NULL default '9',
  ba_perm_cmt_register tinyint(3) unsigned NOT NULL default '3',
  ba_perm_cmt_read tinyint(3) unsigned NOT NULL default '3',
  ba_perm_cmt_del tinyint(3) unsigned NOT NULL default '9',
  ba_mbronly_register text,
  ba_mbronly_reply text,
  ba_notice_no bigint(20) unsigned default NULL,
  ba_notice_subject varchar(255) default NULL,
  ba_notice_html char(1) default 'N',
  ba_notice_memo text,
  ba_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  ba_reghost varchar(15) NOT NULL default '',
  ba_last_modifydate datetime default NULL,
  ba_last_modifyhost varchar(15) default NULL,
  PRIMARY KEY  (ba_no)
) COMMENT='게시판관리';

# --------------------------------------------------------

#
# 테이블 구조 nmail_board_category
#

CREATE TABLE nmail_board_category (
  bca_no bigint(20) unsigned NOT NULL auto_increment,
  ba_no bigint(20) unsigned NOT NULL default '0',
  bca_name varchar(255) NOT NULL default '',
  bca_order tinyint(4) NOT NULL default '0',
  bca_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  bca_modifydate datetime default NULL,
  PRIMARY KEY  (bca_no),
  UNIQUE KEY ba_no (ba_no,bca_name)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_board_comment
#

CREATE TABLE nmail_board_comment (
  bc_no bigint(20) unsigned NOT NULL auto_increment,
  ba_no smallint(5) unsigned NOT NULL default '0',
  b_no bigint(20) unsigned NOT NULL default '0',
  m_no int(10) unsigned NOT NULL default '0',
  bc_name varchar(20) NOT NULL default '',
  bc_pwd varchar(32) NOT NULL default '',
  bc_memo text NOT NULL,
  bc_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  bc_reghost varchar(15) NOT NULL default '',
  PRIMARY KEY  (bc_no)
) COMMENT='게시판덧말';

# --------------------------------------------------------

#
# 테이블 구조 nmail_diary
#

CREATE TABLE nmail_diary (
  di_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  di_kind tinyint(3) unsigned NOT NULL default '0',
  di_subject varchar(255) NOT NULL default '',
  di_startdate date NOT NULL default '0000-00-00',
  di_starttime time default NULL,
  di_enddate date NOT NULL default '0000-00-00',
  di_endtime time default NULL,
  di_isallday enum('Y','N') NOT NULL default 'N',
  di_repeat enum('N','M','Y','L') NOT NULL default 'N',
  di_content text,
  di_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  di_modifydate datetime default NULL,
  PRIMARY KEY  (di_no),
  KEY m_no (m_no,di_kind,di_startdate,di_enddate)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_diary_lunar
#

CREATE TABLE nmail_diary_lunar (
  lunar_date date NOT NULL default '0000-00-00',
  solar_date date NOT NULL default '0000-00-00',
  yun tinyint(1) NOT NULL default '0',
  ganji char(4) binary NOT NULL default '',
  PRIMARY KEY  (solar_date),
  KEY lunar_date (lunar_date)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_domain
#

CREATE TABLE nmail_domain (
  d_no int(10) unsigned NOT NULL auto_increment,
  d_name varchar(255) NOT NULL default '',
  d_is_use enum('Y','N') NOT NULL default 'Y',
  d_quota int(10) unsigned NOT NULL default '0',
  d_quota_mbr_use int(10) unsigned NOT NULL default '0',
  d_quota_mbr_default int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (d_no),
  UNIQUE KEY d_name (d_name)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_mail_box
#

CREATE TABLE nmail_mail_box (
  m_no int(10) unsigned NOT NULL default '0',
  mb_id varchar(5) NOT NULL default '',
  mb_name varchar(50) NOT NULL default '',
  mb_is_system enum('Y','N') NOT NULL default 'Y',
  mb_sort tinyint(3) unsigned NOT NULL default '0',
  mb_size bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (m_no,mb_id),
  KEY mb_sort (mb_sort)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_mail_receive_check
#

CREATE TABLE nmail_mail_receive_check (
  mrc_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  mf_no bigint(20) unsigned default NULL,
  mrc_to varchar(255) default NULL,
  mrc_subject varchar(255) default NULL,
  mrc_senddate datetime default NULL,
  mrc_readdate datetime default NULL,
  PRIMARY KEY  (mrc_no),
  KEY mrc_no (mrc_no,m_no),
  KEY mrc_no_2 (mrc_no,mrc_senddate)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_member
#

CREATE TABLE nmail_member (
  m_no int(10) unsigned NOT NULL auto_increment,
  d_no int(10) unsigned NOT NULL default '0',
  m_id varchar(50) NOT NULL default '',
  m_pwd varchar(32) NOT NULL default '',
  m_level tinyint(3) unsigned NOT NULL default '3',
  m_name varchar(50) NOT NULL default '',
  m_email varchar(255) default NULL,
  m_is_use enum('Y','N') NOT NULL default 'Y',
  m_mail_file_group smallint(4) unsigned zerofill NOT NULL default '0000',
  m_quota int(10) unsigned NOT NULL default '0',
  m_sign_default bigint(20) unsigned default '0',
  m_forwarding text,
  m_forwarding_type enum('N','Y','C') NOT NULL default 'N',
  m_lostpwd_q varchar(255) default NULL,
  m_lostpwd_a varchar(255) default NULL,
  m_last_name varchar(50) default NULL,
  m_homepage varchar(255) default NULL,
  m_jumin varchar(14) default NULL,
  m_sex enum('M','F') default NULL,
  m_birthday date default NULL,
  m_birthday_east enum('S','L') default NULL,
  m_mobile varchar(20) default NULL,
  m_home_tel varchar(20) default NULL,
  m_home_zipcode varchar(10) default NULL,
  m_home_addr1 varchar(255) default NULL,
  m_home_addr2 varchar(255) default NULL,
  m_job varchar(50) default NULL,
  m_office varchar(50) default NULL,
  m_office_subject varchar(50) default NULL,
  m_office_tel varchar(20) default NULL,
  m_office_fax varchar(20) default NULL,
  m_office_zipcode varchar(10) default NULL,
  m_office_addr1 varchar(255) default NULL,
  m_office_addr2 varchar(255) default NULL,
  m_ismarry enum('Y','N') default NULL,
  m_marrydate date default NULL,
  m_isremail enum('Y','N') default 'Y',
  m_memo text,
  m_etc1 varchar(255) default NULL,
  m_date2 date default NULL,
  m_date3 date default NULL,
  m_date4 date default NULL,
  m_date5 date default NULL,
  m_etc2 varchar(255) default NULL,
  m_etc3 varchar(255) default NULL,
  m_etc4 varchar(255) default NULL,
  m_etc5 varchar(255) default NULL,
  m_date1 date default NULL,
  m_file1 varchar(255) default NULL,
  m_file2 varchar(255) default NULL,
  m_file3 varchar(255) default NULL,
  m_file4 varchar(255) default NULL,
  m_file5 varchar(255) default NULL,
  m_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  m_reghost varchar(15) NOT NULL default '',
  m_login_cnt bigint(20) unsigned NOT NULL default '0',
  m_last_logindate datetime default NULL,
  m_last_loginhost varchar(15) default NULL,
  m_last_modifydate datetime default NULL,
  m_last_modifyhost varchar(15) default NULL,
  PRIMARY KEY  (m_no),
  UNIQUE KEY d_no (d_no,m_id)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_member_connect
#

CREATE TABLE nmail_member_connect (
  m_no bigint(20) unsigned NOT NULL default '0',
  mc_date int(10) unsigned NOT NULL default '0',
  mc_host varchar(15) NOT NULL default ''
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_member_field
#

CREATE TABLE nmail_member_field (
  mbf_no tinyint(3) unsigned NOT NULL auto_increment,
  d_no int(10) unsigned NOT NULL default '0',
  mbf_step tinyint(3) unsigned NOT NULL default '1',
  mbf_name varchar(255) NOT NULL default '',
  mbf_text varchar(255) NOT NULL default '',
  mbf_length smallint(5) unsigned NOT NULL default '0',
  mbf_maxlength smallint(5) unsigned NOT NULL default '0',
  mbf_isnotnull enum('Y','N') NOT NULL default 'N',
  mbf_isuse enum('Y','N') NOT NULL default 'Y',
  mbf_islist enum('Y','N') NOT NULL default 'Y',
  mbf_issystem enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (mbf_no),
  UNIQUE KEY mbf_name (d_no,mbf_name)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_member_login_log
#

CREATE TABLE nmail_member_login_log (
  mll_no bigint(20) unsigned NOT NULL auto_increment,
  m_no bigint(20) unsigned NOT NULL default '0',
  mll_date datetime NOT NULL default '0000-00-00 00:00:00',
  mll_host varchar(15) NOT NULL default '',
  PRIMARY KEY  (mll_no)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_notice
#

CREATE TABLE nmail_notice (
  nt_no bigint(20) unsigned NOT NULL auto_increment,
  d_no int(10) unsigned NOT NULL default '0',
  nt_subject varchar(255) default NULL,
  nt_content text,
  nt_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (nt_no),
  KEY nt_no (nt_no,d_no)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_pop3
#

CREATE TABLE nmail_pop3 (
  p3_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  p3_server varchar(255) default NULL,
  p3_id varchar(255) default NULL,
  p3_pwd varchar(255) default NULL,
  p3_isdel enum('Y','N') default 'Y',
  PRIMARY KEY  (p3_no),
  KEY p3_no (p3_no,m_no)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_sign
#

CREATE TABLE nmail_sign (
  sg_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  sg_name varchar(50) default NULL,
  sg_content text,
  PRIMARY KEY  (sg_no),
  KEY sg_no (sg_no,m_no)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_spam_reject
#

CREATE TABLE nmail_spam_reject (
  m_no int(10) unsigned NOT NULL default '0',
  sr_email varchar(255) NOT NULL default '',
  sr_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (m_no,sr_email),
  KEY sr_regdate (sr_regdate)
);

# --------------------------------------------------------

#
# 테이블 구조 nmail_zipcode
#

CREATE TABLE nmail_zipcode (
  zip1 char(3) NOT NULL default '',
  zip2 char(3) NOT NULL default '',
  sido varchar(4) default NULL,
  gugun varchar(15) default NULL,
  dong varchar(43) default NULL,
  bunji varchar(17) default NULL
);


# --------------------------------------------------------

# 데이타

INSERT INTO nmail_diary VALUES (1, 0, 0, '신정', '1900-01-01', NULL, '1900-01-01', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (2, 0, 0, '삼일절', '1900-03-01', NULL, '1900-03-01', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (3, 0, 0, '식목일', '1900-04-05', NULL, '1900-04-05', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (4, 0, 0, '근로자의 날', '1900-05-01', NULL, '1900-05-01', NULL, 'Y', 'Y', '기념일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (5, 0, 0, '어린이 날', '1900-05-05', NULL, '1900-05-05', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (6, 0, 0, '어버이 날', '1900-05-08', NULL, '1900-05-08', NULL, 'Y', 'Y', '기념일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (7, 0, 0, '스승의 날', '1900-05-08', NULL, '1900-05-08', NULL, 'Y', 'Y', '기념일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (8, 0, 0, '현충일', '1900-06-06', NULL, '1900-06-06', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (9, 0, 0, '제헌절', '1900-07-17', NULL, '1900-07-17', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (10, 0, 0, '광복절', '1900-08-15', NULL, '1900-08-15', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (11, 0, 0, '국군의 날', '1900-10-01', NULL, '1900-10-01', NULL, 'Y', 'Y', '기념일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (12, 0, 0, '개천절', '1900-10-03', NULL, '1900-10-03', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (13, 0, 0, '한글날', '1900-10-09', NULL, '1900-10-09', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (14, 0, 0, '성탄절', '1900-12-25', NULL, '1900-12-25', NULL, 'Y', 'Y', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (15, 0, 0, '', '1899-12-30', NULL, '1899-12-30', NULL, 'Y', 'L', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (16, 0, 0, '설날', '1900-01-01', NULL, '1900-01-01', NULL, 'Y', 'L', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (17, 0, 0, '', '1900-01-02', NULL, '1900-01-02', NULL, 'Y', 'L', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (18, 0, 0, '석가탄신일', '1900-04-08', NULL, '1900-04-08', NULL, 'Y', 'L', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (19, 0, 0, '', '1900-08-14', NULL, '1900-08-14', NULL, 'Y', 'L', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (20, 0, 0, '추석', '1900-08-15', NULL, '1900-08-15', NULL, 'Y', 'L', '공휴일', '1900-01-01 00:00:00', NULL);
INSERT INTO nmail_diary VALUES (21, 0, 0, '', '1900-08-16', NULL, '1900-08-16', NULL, 'Y', 'L', '공휴일', '1900-01-01 00:00:00', NULL);


#[2005-12-01]
#ALTER TABLE nmail_mail_file_1_0000 ADD mf_spam_point FLOAT( 2 ) DEFAULT '0' NOT NULL ;


#[2005-12-13]
ALTER TABLE nmail_member ADD INDEX m_quota ( d_no , m_quota );
ALTER TABLE nmail_domain DROP d_quota_mbr_use ;
ALTER TABLE nmail_domain ADD d_quota_mbr_count INT UNSIGNED DEFAULT '100' NOT NULL AFTER d_quota_mbr_default ;
ALTER TABLE nmail_domain ADD d_memo TEXT;
ALTER TABLE nmail_domain ADD d_regdate DATETIME NOT NULL ;
ALTER TABLE nmail_domain CHANGE d_quota d_quota INT( 10 ) UNSIGNED DEFAULT '2097152' NOT NULL;
ALTER TABLE nmail_domain CHANGE d_quota_mbr_default d_quota_mbr_default INT( 10 ) UNSIGNED DEFAULT '20480' NOT NULL;


#[2005-12-14]
ALTER TABLE nmail_member_field CHANGE mbf_no mbf_no INT UNSIGNED NOT NULL AUTO_INCREMENT;


#[2005-12-15]
#ALTER TABLE nmail_mail_file_1_0000 DROP INDEX mf_arrival_date , ADD INDEX mf_arrival_date ( m_no , mb_id , mf_arrival_date ) ;


#[2006-01-06]
ALTER TABLE nmail_mail_receive_check
ADD mrcext_m_id VARCHAR( 50 ) default NULL,
ADD mrcext_d_name VARCHAR( 255 ) default NULL,
ADD mrcext_key VARCHAR( 10 ) default NULL,
ADD mrcext_ip VARCHAR( 15 ) default NULL;
#ALTER TABLE nmail_mail_file_1_0000 ADD mf_is_userauth enum('Y', 'N') ;
#ALTER TABLE nmail_mail_file_1_0000 ADD mfext1 CHAR(1) ;
#ALTER TABLE nmail_mail_file_1_0000 ADD mfext2 VARCHAR( 20 ) ;
#ALTER TABLE nmail_mail_file_1_0000 ADD mfext3 VARCHAR( 255 ) ;


#[2006-01-18]
#UPDATE nmail_member_field SET mbf_islist='N' WHERE mbf_name='m_no';
#UPDATE nmail_member_field SET mbf_islist='Y' WHERE mbf_name='m_last_logindate';


#[2006-01-25]
#ALTER TABLE nmail_mail_file_1_0000 DROP PRIMARY KEY, ADD PRIMARY KEY ( mf_no );
#ALTER TABLE nmail_mail_file_1_0000 ADD INDEX mf_no ( m_no , mb_id , mf_no ) ;
#ALTER TABLE nmail_mail_file_1_0000 ADD INDEX mf_filename ( mf_filename )

#[2006-02-02]
CREATE TABLE nmail_auto_filter
(
  as_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  as_name varchar(255),
  as_condition varchar(255),
  as_condition_text text,
  as_exec varchar(255),
  as_exec_text text,
  as_econdition varchar(255),
  as_econdition_text text,
  as_is_use enum('Y','N') default 'Y',
  as_sort tinyint unsigned NOT NULL default '0',
  PRIMARY KEY  (as_no),
  KEY as_m_no (m_no)
);

CREATE TABLE nmail_spam_allow (
  m_no int(10) unsigned NOT NULL default '0',
  sa_email varchar(255) NOT NULL default '',
  sa_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (m_no,sa_email),
  KEY sa_regdate (sa_regdate)
) ;

#[2006-02-07]
CREATE TABLE nmail_spam_config (
  m_no int(10) unsigned NOT NULL default '0',
  sc_addr_allow enum('Y','N') NOT NULL default 'Y',
  sc_fake_del enum('Y','N') NOT NULL default 'Y',
  sc_foreign_spam enum('Y','N') NOT NULL default 'N',
  sc_write_allow_add enum('Y','N') NOT NULL default 'Y',
  sc_from_subj_null_dell enum('Y','N') NOT NULL default 'N',
  sc_moddate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (m_no)
) ;
ALTER TABLE nmail_addr ADD INDEX ad_email ( adg_no , ad_email ) ;

#[2006-02-22]
CREATE TABLE nmail_member_group (
  mg_no int(10) unsigned NOT NULL auto_increment,
  d_no int(10) unsigned NOT NULL default '0',
  mg_name varchar(50) default NULL,
  mg_quota_default int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (mg_no),
  KEY mg_d_no (d_no)
);
ALTER TABLE nmail_member ADD mg_no INT UNSIGNED AFTER d_no ;
ALTER TABLE nmail_member ADD INDEX ( mg_no ) ;
ALTER TABLE nmail_member ADD m_autoreply_isuse ENUM( 'Y', 'N' ) DEFAULT 'N' NOT NULL;
ALTER TABLE nmail_member ADD m_autoreply_subject VARCHAR( 255 );
ALTER TABLE nmail_member ADD m_autoreply_body TEXT;
CREATE TABLE nmail_auto_reply_loop_check (
  m_no int(10) unsigned NOT NULL default '0',
  ar_lc_email varchar(255) NOT NULL default '',
  ar_lc_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (m_no,ar_lc_email),
  KEY ar_lc_regdate (ar_lc_regdate)
) ;
ALTER TABLE nmail_domain ADD d_saveday TINYINT UNSIGNED DEFAULT '0'  AFTER d_quota_mbr_count ;
ALTER TABLE nmail_mail_box ADD mb_saveday TINYINT UNSIGNED DEFAULT '0' ;

#[2006-03-06]
ALTER TABLE nmail_member ADD m_suspend_start DATE;
ALTER TABLE nmail_member ADD m_suspend_end DATE;
ALTER TABLE nmail_member ADD m_use_start DATE;
ALTER TABLE nmail_member ADD m_use_end DATE;

#[2006-06-02]
ALTER TABLE nmail_domain ADD d_skin_main VARCHAR( 20 ) NOT NULL DEFAULT 'basic' AFTER d_saveday;
ALTER TABLE nmail_domain ADD d_skin_webmail VARCHAR( 20 ) NOT NULL DEFAULT 'basic' AFTER d_skin_main;
ALTER TABLE nmail_domain ADD d_skin_member VARCHAR( 20 ) NOT NULL DEFAULT 'basic' AFTER d_skin_webmail;
ALTER TABLE nmail_domain ADD d_skin_board VARCHAR( 20 ) NOT NULL DEFAULT 'basic' AFTER d_skin_member;

#[2006-06-08]
CREATE TABLE nmail_white_ip (
  ip_ipaddr varchar(15) NOT NULL default '',
  ip_netmask varchar(15) NOT NULL default '',
  ip_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (ip_ipaddr,ip_netmask),
  KEY ip_regdate (ip_regdate)
) ;
CREATE TABLE nmail_black_ip (
  ip_ipaddr varchar(15) NOT NULL default '',
  ip_netmask varchar(15) NOT NULL default '',
  ip_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (ip_ipaddr,ip_netmask),
  KEY ip_regdate (ip_regdate)
) ;
CREATE TABLE nmail_relay_ip (
  ip_ipaddr varchar(15) NOT NULL default '',
  ip_netmask varchar(15) NOT NULL default '',
  ip_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (ip_ipaddr,ip_netmask),
  KEY ip_regdate (ip_regdate)
) ;

#[2006-06-22]
ALTER TABLE nmail_mail_receive_check DROP INDEX mrc_no ;
ALTER TABLE nmail_mail_receive_check ADD INDEX m_no ( m_no ) ;
ALTER TABLE nmail_addr_group DROP INDEX adg_no ;
ALTER TABLE nmail_addr_group ADD INDEX m_no ( m_no ) ;

#[2006-07-14]
ALTER TABLE nmail_domain ADD d_shareaddr_isuse ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'Y' AFTER d_skin_board ;
ALTER TABLE nmail_domain ADD d_shareaddr_reg ENUM( 'U', 'A' ) NOT NULL DEFAULT 'U' AFTER d_shareaddr_isuse ;
ALTER TABLE nmail_domain ADD d_sharediary_isuse ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'Y' AFTER d_shareaddr_isuse ;
ALTER TABLE nmail_domain ADD d_sharediary_reg ENUM( 'U', 'A' ) NOT NULL DEFAULT 'U' AFTER d_sharediary_isuse ;
ALTER TABLE nmail_addr_group ADD d_no INT UNSIGNED NULL ;
ALTER TABLE nmail_addr_group ADD INDEX ( d_no ) ;
ALTER TABLE nmail_addr ADD ad_no_share BIGINT unsigned;

#[2006-07-19]
ALTER TABLE nmail_diary ADD d_no INT UNSIGNED NULL ;
ALTER TABLE nmail_diary ADD di_no_share BIGINT UNSIGNED NULL ;
ALTER TABLE nmail_diary ADD INDEX ( d_no ) ;

#[2006-07-20]
ALTER TABLE nmail_domain ADD d_quota_board INT UNSIGNED NOT NULL DEFAULT '2097152' AFTER d_quota_mbr_count ;
ALTER TABLE nmail_board ADD b_file_size INT UNSIGNED NOT NULL DEFAULT '0' AFTER b_file ;
ALTER TABLE nmail_board ADD INDEX ( ba_no ) ;
ALTER TABLE nmail_board_comment ADD INDEX ( b_no ) ;
ALTER TABLE nmail_board_admin ADD INDEX ( d_no ) ;

#[2006-07-25]
ALTER TABLE nmail_member ADD m_cfg_new_mail_box VARCHAR( 255 ) NULL DEFAULT 'inbox|spam';
ALTER TABLE nmail_member ADD m_cfg_new_cnt TINYINT UNSIGNED NULL DEFAULT '7';
ALTER TABLE nmail_member ADD m_cfg_week_diray_cnt TINYINT UNSIGNED NULL DEFAULT '5';
ALTER TABLE nmail_member ADD m_cfg_from_name VARCHAR( 255 ) NULL ;
ALTER TABLE nmail_member ADD m_cfg_mail_list_cnt TINYINT UNSIGNED NULL DEFAULT '20';

#[2006-08-04]
ALTER TABLE nmail_domain ADD d_site_name VARCHAR( 255 ) NULL AFTER d_shareaddr_reg ;
ALTER TABLE nmail_domain ADD d_site_owner VARCHAR( 255 ) NULL AFTER d_site_name ;
ALTER TABLE nmail_domain ADD d_site_title VARCHAR( 255 )  NULL AFTER d_site_owner ;
ALTER TABLE nmail_domain ADD d_site_license TEXT NULL AFTER d_site_title ;


#[2007-03-07]
ALTER TABLE nmail_domain ADD d_svc_smtp enum('Y', 'N') DEFAULT 'Y' NOT NULL AFTER d_saveday ;
ALTER TABLE nmail_domain ADD d_svc_pop3 enum('Y', 'N') DEFAULT 'Y' NOT NULL AFTER d_svc_smtp ;
ALTER TABLE nmail_domain ADD d_svc_forward enum('Y', 'N') DEFAULT 'Y' NOT NULL AFTER d_svc_pop3 ;
ALTER TABLE nmail_member ADD m_svc_smtp enum('Y', 'N') DEFAULT 'Y' NOT NULL;
ALTER TABLE nmail_member ADD m_svc_pop3 enum('Y', 'N') DEFAULT 'Y' NOT NULL;


#[2007-04-04]
UPDATE nmail_diary SET di_content='기념일' WHERE m_no='0' AND di_subject='식목일' AND di_startdate='1900-04-05' AND di_content='공휴일';
UPDATE nmail_diary SET di_startdate='1900-05-15', di_enddate='1900-05-15' WHERE m_no='0' AND di_subject='스승의 날' AND di_startdate='1900-05-08' AND di_content='기념일';


#[2007-05-29]
ALTER TABLE nmail_mail_receive_check ADD mrc_status CHAR( 1 ) NULL AFTER mrc_readdate;
#UPDATE nmail_mail_receive_check SET mrc_status='N' WHERE mrc_readdate IS NULL AND mrc_status IS NULL;
#UPDATE nmail_mail_receive_check SET mrc_status='R' WHERE mrc_readdate IS NOT NULL AND mrc_status IS NULL;


#[2007-07-21]
ALTER TABLE nmail_member ADD m_usertype CHAR( 1 ) NULL ;
CREATE TABLE nmail_mailing_list (
  m_no int(10) unsigned NOT NULL default '0',
  ml_email varchar(255) NOT NULL default '',
  ml_perms char(2) NOT NULL default 'RW',
  ml_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (m_no,ml_email),
  KEY ml_regdate (ml_regdate)
) ;


#[2008-02-22]
CREATE TABLE nmail_member_size (
m_no int(10) unsigned NOT NULL default '0',
ms_mail BIGINT UNSIGNED NOT NULL DEFAULT '0',
ms_bigfile BIGINT UNSIGNED NOT NULL DEFAULT '0',
ms_webhard BIGINT UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY  (m_no)
) ;

CREATE TABLE nmail_webhard_folder (
wfd_key INT UNSIGNED NOT NULL AUTO_INCREMENT ,
m_no INT UNSIGNED,
ws_key INT UNSIGNED,
wfd_parent INT UNSIGNED DEFAULT '0' NOT NULL ,
wfd_depth TINYINT UNSIGNED NOT NULL ,
wfd_name VARCHAR( 255 ) NOT NULL ,
wfd_issystem ENUM( 'Y', 'N' ) DEFAULT 'N' NOT NULL ,
wfd_memo TEXT,
wfd_regdate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY ( wfd_key ) ,
KEY wdf_m_no (m_no, wfd_parent),
KEY wdf_ws_key (ws_key)
);

CREATE TABLE nmail_webhard_file (
wf_key BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
m_no INT UNSIGNED DEFAULT '0' NOT NULL ,
wfd_key INT UNSIGNED NOT NULL,
wf_name VARCHAR( 255 ) NOT NULL ,
wf_size INT UNSIGNED DEFAULT '0' NOT NULL ,
wf_type VARCHAR( 10 ) ,
wf_memo TEXT,
wf_regdate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY ( wf_key ) ,
KEY wf_wfd_key (wfd_key),
KEY wf_m_no (m_no)
);

CREATE TABLE nmail_webhard_share (
ws_key BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
d_no INT UNSIGNED DEFAULT '0' NOT NULL ,
ws_name VARCHAR( 255 ) NOT NULL ,
ws_quota INT UNSIGNED DEFAULT '0' NOT NULL ,
ws_quota_use BIGINT UNSIGNED DEFAULT '0' NOT NULL ,
ws_mg_no TEXT,
ws_admin_id TEXT,
ws_perm VARCHAR( 7 ),
ws_memo TEXT,
ws_isuse ENUM( 'Y', 'N' ) DEFAULT 'Y' NOT NULL ,
ws_regdate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY ( ws_key ) ,
KEY ws_d_no (d_no)
);

CREATE TABLE nmail_bigfile (
bf_key BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
m_no INT UNSIGNED NOT NULL,
wf_key BIGINT UNSIGNED,
wfd_key INT UNSIGNED,
mf_no bigint(20) unsigned,
bf_to TEXT,
bf_subject VARCHAR( 255 ),
bf_type ENUM( 'B', 'W' ) DEFAULT 'B' NOT NULL ,
bf_name VARCHAR( 255 ) NOT NULL ,
bf_size INT UNSIGNED DEFAULT '0' NOT NULL ,
bf_down INT UNSIGNED DEFAULT '0' ,
bf_expire ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N' ,
bf_regdate DATETIME NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY ( bf_key ) ,
KEY bf_m_no (m_no)
);

ALTER TABLE nmail_domain
ADD  d_bigfile_isuse enum('Y','N') NOT NULL default 'Y' AFTER d_svc_forward ,
ADD  d_bigfile_quota int(10) unsigned NOT NULL default '10485760' AFTER d_bigfile_isuse ,
ADD  d_bigfile_quota_default int(10) unsigned NOT NULL default '102400' AFTER d_bigfile_quota ,
ADD  d_webhard_isuse enum('Y','N') NOT NULL default 'Y' AFTER d_bigfile_quota_default ,
ADD  d_webhard_quota int(10) unsigned NOT NULL default '10485760' AFTER d_webhard_isuse ,
ADD  d_webhard_quota_default int(10) unsigned NOT NULL default '102400' AFTER d_webhard_quota ;

ALTER TABLE nmail_member ADD m_bigfile_quota INT UNSIGNED NOT NULL DEFAULT '102400' AFTER m_quota ,
ADD m_webhard_quota INT UNSIGNED NOT NULL DEFAULT '102400' AFTER m_bigfile_quota ;

ALTER TABLE nmail_domain ADD d_skin_webhard VARCHAR( 20 ) NOT NULL DEFAULT 'basic' AFTER d_skin_board ;


#[2008-03-19]
ALTER TABLE nmail_board_admin ADD ba_mg_no TEXT NULL AFTER ba_mbronly_reply ,
ADD ba_use_id TEXT NULL AFTER ba_mg_no ;


#[2008-03-21]
ALTER TABLE nmail_board_category DROP INDEX ba_no ;

#[2008-03-27]
ALTER TABLE nmail_domain ADD d_site_logo1 VARCHAR( 255 ) NULL AFTER d_site_license ,
ADD d_site_logo2 VARCHAR( 255 ) NULL AFTER d_site_logo1 ;
ALTER TABLE nmail_domain ADD d_site_logo1_w SMALLINT UNSIGNED NULL AFTER d_site_logo1 ,
ADD d_site_logo1_h SMALLINT UNSIGNED NULL AFTER d_site_logo1_w ;
ALTER TABLE nmail_domain ADD d_site_logo2_w SMALLINT UNSIGNED NULL AFTER d_site_logo2 ,
ADD d_site_logo2_h SMALLINT UNSIGNED NULL AFTER d_site_logo2_w ;

#2.4.0

#[2008-05-27]
CREATE TABLE nmail_log_smtp (
s_date date default NULL,
s_time time default NULL,
s_ip varchar(255) default NULL,
s_sender_user varchar(255) default NULL,
s_sender_dom varchar(255) default NULL,
s_rcpt_user varchar(255) default NULL,
s_rcpt_dom varchar(255) default NULL,
s_msgid varchar(255) default NULL,
s_status varchar(100) default NULL,
s_authuser varchar(255) default NULL,
s_msgsize bigint(20) unsigned default '0',
s_smail_type varchar(20) default NULL,
s_smail_rcpt_user varchar(255) default NULL,
s_smail_rcpt_dom varchar(255) default NULL,
s_smail_mx varchar(255) default NULL,
s_smail_date date default NULL,
s_smail_time time default NULL,
KEY ls_s_date (s_date,s_msgid(4))
);

CREATE TABLE nmail_log_pop3 (
p_date date default NULL,
p_time time default NULL,
p_ip varchar(255) default NULL,
p_user varchar(255) default NULL,
p_dom varchar(255) default NULL,
KEY lp_p_date (p_date)
);


#[2008-06-12]
ALTER TABLE nmail_board ADD b_files TEXT NULL AFTER b_file ;


#[2008-07-29]
ALTER TABLE nmail_member_group ADD mg_parent INT UNSIGNED NOT NULL DEFAULT '0' AFTER d_no ,
ADD mg_depth TINYINT UNSIGNED NOT NULL DEFAULT '1' AFTER mg_parent ,
ADD mg_sort TINYINT UNSIGNED NOT NULL DEFAULT '1' AFTER mg_depth ;

CREATE TABLE nmail_member_group_rel (
mg_no int(10) unsigned NOT NULL default '0',
m_no int(10) unsigned NOT NULL default '0',
PRIMARY KEY  (mg_no, m_no)
);
CREATE TABLE nmail_member_class (
mc_no INT UNSIGNED NOT NULL AUTO_INCREMENT ,
d_no int(10) unsigned NOT NULL default '0',
mc_name VARCHAR(50) NOT NULL,
mc_sort TINYINT UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY  (mc_no),
KEY mc_d_no (d_no)
);

ALTER TABLE nmail_member ADD mc_no INT UNSIGNED NULL AFTER mg_no ;


#[2008-08-12]
ALTER TABLE nmail_domain ADD d_mbrgrp_isuse ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'Y' AFTER d_skin_webhard ;


#[2008-09-12]
ALTER TABLE nmail_webhard_share ADD ws_guest_id TEXT NULL AFTER ws_mg_no ;


#[2008-09-24]
ALTER TABLE nmail_diary_lunar CHANGE lunar_date lunar_date VARCHAR( 10 ) NOT NULL DEFAULT '0000-00-00' ;


#[2009-02-09]
CREATE TABLE nmail_letter_group (
lg_no INT UNSIGNED NOT NULL AUTO_INCREMENT ,
lg_name VARCHAR(50) NOT NULL,
lg_sort TINYINT UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY  (lg_no)
);

CREATE TABLE nmail_letter (
l_no BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
lg_no INT UNSIGNED NOT NULL,
l_name VARCHAR( 255 ) NOT NULL,
l_preview VARCHAR( 255 ),
l_top VARCHAR( 255 ),
l_top_w SMALLINT UNSIGNED NULL,
l_top_h SMALLINT UNSIGNED NULL,
l_middle VARCHAR( 255 ),
l_bottom VARCHAR( 255 ),
l_bottom_w SMALLINT UNSIGNED NULL,
l_bottom_h SMALLINT UNSIGNED NULL,
l_bgcolor VARCHAR( 20 ),
l_color VARCHAR( 20 ),
l_sort TINYINT UNSIGNED NOT NULL DEFAULT '1',
l_isuse ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'Y' ,
l_memo TEXT,
l_regdate DATETIME NOT NULL default '0000-00-00 00:00:00',
l_send_cnt INT UNSIGNED DEFAULT '0' ,
l_lastsenddate DATETIME default '0000-00-00 00:00:00',
PRIMARY KEY ( l_no ) ,
KEY l_lg_no (lg_no)
);

#[2009-04-02]
CREATE TABLE nmail_log_return (
mr_no bigint(20) unsigned NOT NULL auto_increment,
mr_mail_from varchar(255) default NULL,
mr_rcpt_to varchar(255) default NULL,
mr_smtp_error_type tinyint(4) unsigned default NULL,
mr_smtp_error varchar(255) default NULL,
mr_smtp_error_str varchar(255) default NULL,
mr_smtp_server varchar(255) default NULL,
mr_msgid varchar(255) default NULL,
mr_hdr_subject varchar(255) default NULL,
mr_hdr_date datetime NOT NULL default '0000-00-00 00:00:00',
mr_date datetime NOT NULL default '0000-00-00 00:00:00',
mr_body text,
PRIMARY KEY  (mr_no),
KEY mr_hdr_date (mr_hdr_date),
KEY mr_msgid (mr_msgid)
);

CREATE TABLE nmail_log_filter (
f_no bigint(20) unsigned NOT NULL auto_increment,
m_no bigint(20) unsigned NOT NULL,
f_code varchar(10) NOT NULL,
f_action char(1) NOT NULL,
f_msgid varchar(255) default NULL,
f_memo varchar(255) default NULL,
f_mail_from varchar(255) default NULL,
f_rcpt_to varchar(255) default NULL,
f_hdr_from varchar(255) default NULL,
f_hdr_subject varchar(255) default NULL,
f_date datetime NOT NULL default '0000-00-00 00:00:00',
f_ip varchar(15) default NULL,
PRIMARY KEY  (f_no),
KEY lf_date (f_date),
KEY lf_msgid (f_msgid)
);

#2.5.0

#[2009-06-25]
ALTER TABLE nmail_log_smtp DROP INDEX ls_s_date , ADD INDEX ls_s_date ( s_date , s_msgid ( 7 ) );
ALTER TABLE nmail_log_smtp CHANGE s_msgid s_msgid VARCHAR( 20 ) NULL DEFAULT NULL ;

#[2009-07-14]
UPDATE nmail_diary SET di_content='기념일' WHERE m_no='0' AND di_subject='제헌절' AND di_startdate='1900-07-17' AND di_content='공휴일';

#[2010-10-06]
ALTER TABLE nmail_member ADD m_cfg_from_addr VARCHAR( 255 ) NULL AFTER m_cfg_from_name ;

#[2010-12-24]
ALTER TABLE nmail_board_admin ADD ba_isuse_rewrite CHAR( 1 ) NOT NULL DEFAULT 'N' AFTER ba_isuse_reply ;
ALTER TABLE nmail_board_admin ADD ba_isuse_read_log CHAR( 1 ) NOT NULL DEFAULT 'N' AFTER ba_isuse_rewrite ;

ALTER TABLE nmail_board_admin ADD ba_perm_rewrite tinyint(3) unsigned NOT NULL default '3' AFTER ba_perm_reply;
ALTER TABLE nmail_board_admin ADD ba_perm_read_log tinyint(3) unsigned NOT NULL default '3' AFTER ba_perm_rewrite;

CREATE TABLE nmail_board_read_log (
  br_no bigint(20) unsigned NOT NULL auto_increment,
  ba_no smallint(5) unsigned NOT NULL default '0',
  b_no bigint(20) unsigned NOT NULL default '0',
  m_no int(10) unsigned NOT NULL default '0',
  br_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  br_reghost varchar(15) NOT NULL default '',
  PRIMARY KEY  (br_no),
  KEY br_b_no (b_no)
);

CREATE TABLE nmail_board_draft (
  bd_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  b_subject varchar(255) NOT NULL default '',
  b_memo mediumtext NOT NULL,
  bd_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  bd_reghost varchar(15) NOT NULL default '',
  PRIMARY KEY  (bd_no),
  KEY bd_m_no (m_no)
);

# MySQL Only
ALTER TABLE nmail_board CHANGE b_memo b_memo MEDIUMTEXT NOT NULL;


# [2011-01-11]
CREATE TABLE nmail_board_menu (
  bm_no smallint(5) unsigned NOT NULL auto_increment,
  d_no int(10) unsigned NOT NULL default '0',
  bm_name varchar(255) NOT NULL,
  bm_sort tinyint(3) unsigned NOT NULL default '1',
  PRIMARY KEY  (bm_no),
  KEY bm_d_no (d_no)
);
ALTER TABLE nmail_board_admin ADD bm_no SMALLINT UNSIGNED AFTER d_no ;
ALTER TABLE nmail_board_admin ADD ba_sort tinyint(3) unsigned NOT NULL default '1' AFTER bm_no ;

ALTER TABLE nmail_domain ADD d_mbrgrp_list TEXT AFTER d_mbrgrp_isuse;
ALTER TABLE nmail_domain ADD d_mbrgrp_view TEXT AFTER d_mbrgrp_list;
UPDATE nmail_domain SET d_mbrgrp_list='m_mobile|m_office_tel', d_mbrgrp_view='m_mobile|m_office_tel' WHERE d_mbrgrp_list IS NULL;


# [2012-09-27]
CREATE TABLE nmail_quota_alert (
  qa_no bigint(20) unsigned NOT NULL auto_increment,
  m_no int(10) unsigned NOT NULL default '0',
  qa_cursize bigint(20) unsigned NOT NULL default '0',
  qa_maxsize bigint(20) unsigned NOT NULL default '0',
  qa_date datetime NOT NULL default '0000-00-00 00:00:00',
  qa_type char(1) NOT NULL default 'L',
  PRIMARY KEY  (qa_no),
  KEY qa_m_no_date (m_no, qa_date)
);

ALTER TABLE nmail_mail_receive_check ADD mrc_reserve_status char(1);
ALTER TABLE nmail_mail_receive_check ADD INDEX mrc_reserve_status ( mrc_reserve_status );
ALTER TABLE nmail_mail_receive_check ADD INDEX mrc_m_no_senddate ( m_no, mrc_senddate );


# [2012-10-15]
ALTER TABLE nmail_addr_group ADD adg_parent BIGINT UNSIGNED NOT NULL DEFAULT '0' AFTER adg_name;
ALTER TABLE nmail_addr_group ADD adg_depth TINYINT UNSIGNED NOT NULL DEFAULT '1' AFTER adg_parent;
ALTER TABLE nmail_addr_group ADD adg_sort TINYINT UNSIGNED NOT NULL DEFAULT '1' AFTER adg_depth;

# [2012-10-19]
CREATE TABLE nmail_domain_alias (
  da_no int(10) unsigned NOT NULL auto_increment,
  d_no int(10) unsigned NOT NULL default '0',
  da_name varchar(255) NOT NULL default '',
  da_regdate DATETIME NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (da_no),
  UNIQUE KEY da_name (d_no,da_name)
);

# [2013-01-28]
ALTER TABLE nmail_domain_alias ADD da_mail_from_select ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N' AFTER da_name;

# [2013-07-01]
CREATE TABLE nmail_log_login (
d_no int(10) unsigned NOT NULL,
m_no int(10) unsigned NOT NULL,
m_id VARCHAR( 50 ) NOT NULL,
l_date date NOT NULL,
l_time time NOT NULL,
l_ip varchar(15) NOT NULL,
l_status enum('OK','FI','FP','LID','LIP') NOT NULL,
KEY l_l_date_time (l_date, l_time),
KEY l_l_date_ip (l_date, l_ip),
KEY l_l_date_m_no (l_date, m_no),
KEY l_l_date_d_no (l_date, d_no)
);

# [2013-07-05]
ALTER TABLE nmail_log_pop3 ADD INDEX lp_date_time ( p_date , p_time );
ALTER TABLE nmail_log_pop3 DROP INDEX lp_p_date;

CREATE TABLE nmail_log_member (
d_no int(10) unsigned NOT NULL,
m_no int(10) unsigned NOT NULL,
m_id VARCHAR( 50 ) NOT NULL,
l_date date NOT NULL,
l_time time NOT NULL,
l_ip varchar(15) NOT NULL,
l_admin_id VARCHAR( 255 ) NOT NULL,
l_status enum('ADD','DEL','MODP') NOT NULL,
KEY l_m_date_time (l_date, l_time),
KEY l_m_date_ip (l_date, l_ip),
KEY l_m_date_m_no (l_date, m_no),
KEY l_m_date_d_no (l_date, d_no)
);

# [2013-07-08]
ALTER TABLE nmail_domain_alias ADD da_memo TEXT NULL AFTER da_mail_from_select ;

# [2013-07-30]
ALTER TABLE nmail_mail_receive_check DROP INDEX mrc_no_2;
ALTER TABLE nmail_board ADD INDEX b_ref_list ( ba_no , b_ref , b_re_step );
INSERT INTO nmail_white_ip (ip_ipaddr, ip_netmask, ip_regdate) VALUES ('127.0.0.1', '255.255.255.255', SYSDATE());

# [2013-10-07]
CREATE TABLE nmail_web_allow_ip (
  d_no int(10) unsigned NOT NULL,
  ip_ipaddr varchar(15) NOT NULL default '',
  ip_netmask varchar(15) NOT NULL default '',
  ip_memo varchar(255),
  ip_regdate datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (d_no, ip_ipaddr,ip_netmask),
  KEY wa_ip_regdate (ip_regdate)
);
ALTER TABLE nmail_log_login CHANGE l_status l_status ENUM( 'OK', 'FI', 'FP', 'LID', 'LIP', 'DIP' ) NOT NULL DEFAULT 'OK';

#3.0.0

# [2015-11-05]
CREATE TABLE nmail_sadmin_spam_addr (
  ssa_email varchar(255) NOT NULL DEFAULT '',
  ssa_regdate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (ssa_email),
  KEY ssa_regdate (ssa_regdate)
);

CREATE TABLE nmail_sadmin_spam_word (
  ssw_word varchar(255) NOT NULL DEFAULT '',
  ssw_regdate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (ssw_word),
  KEY ssw_regdate (ssw_regdate)
);

CREATE TABLE nmail_smtp_send (
s_date date default NULL,
s_time time default NULL,
s_ip varchar(15) default NULL,
m_no int(10) unsigned NOT NULL default '0',
s_rcpt_user varchar(255) default NULL,
s_rcpt_dom varchar(255) default NULL,
s_subject varchar(255) default NULL,
s_msgsize bigint(20) unsigned default '0',
KEY smtp_s_date_ip (s_date,s_ip),
KEY smtp_s_date_mno (s_date,m_no)
);

CREATE TABLE nmail_smtp_send_deny (
s_date date default NULL,
s_time time default NULL,
s_ip varchar(15) default NULL,
m_no int(10) unsigned NOT NULL default '0',
s_limit_cnt int(10) unsigned NOT NULL default '0',
s_limit_msg char(3) default NULL,
KEY smtp_sd_date (s_date)
);

ALTER TABLE nmail_smtp_send ADD s_rcpt_count SMALLINT UNSIGNED NOT NULL DEFAULT '1';

ALTER TABLE nmail_smtp_send ADD s_msgid varchar(255) default NULL;

ALTER TABLE nmail_log_pop3 ADD p_login varchar(16) default NULL;

ALTER TABLE nmail_pop3 ADD p3_authtype varchar(10) default 'CLR';

ALTER TABLE nmail_pop3 ADD p3_leave varchar(10) default NULL;

ALTER TABLE nmail_spam_config CHANGE sc_fake_del sc_fake_del enum('N') NOT NULL default 'N';

ALTER TABLE nmail_domain CHANGE d_skin_main d_skin_main VARCHAR( 20 ) NOT NULL DEFAULT 'bootstrap' AFTER d_saveday;

ALTER TABLE nmail_domain CHANGE d_skin_webmail d_skin_webmail VARCHAR( 20 ) NOT NULL DEFAULT 'bootstrap' AFTER d_skin_main;

ALTER TABLE nmail_domain CHANGE d_skin_member d_skin_member VARCHAR( 20 ) NOT NULL DEFAULT 'bootstrap' AFTER d_skin_webmail;

ALTER TABLE nmail_domain CHANGE d_skin_board d_skin_board VARCHAR( 20 ) NOT NULL DEFAULT 'bootstrap' AFTER d_skin_member;

ALTER TABLE nmail_domain CHANGE d_skin_webhard d_skin_webhard VARCHAR( 20 ) NOT NULL DEFAULT 'bootstrap' AFTER d_skin_board ;

# 2020-12
ALTER TABLE nmail_domain MODIFY d_quota BIGINT (10) unsigned NOT NULL default '2097152';

ALTER TABLE nmail_member ADD m_last_pwdchgdate DATETIME NULL;


### v3.3
# 2022-12-23
CREATE TABLE nmail_team_mail (
 tm_no int(10) unsigned NOT NULL auto_increment,
 d_no int(10) unsigned NOT NULL default '0',
 m_id varchar(50) NOT NULL default '',
 team_m_id varchar(50) NOT NULL default '',
 tm_is_use enum('Y','N') default 'Y',
 tm_memo text,
 tm_regdate DATETIME NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY  (tm_no),
 UNIQUE KEY da_name (d_no, m_id, team_m_id)
);

ALTER TABLE nmail_member ADD m_team_mail_is_use ENUM( 'Y', 'N' ) NOT NULL DEFAULT 'N';

# 2024-02-27
ALTER TABLE nmail_mail_receive_check ADD INDEX mrc_m_no_readdate ( m_no, mrc_readdate );

# 2024-03-18
ALTER TABLE nmail_domain MODIFY d_quota BIGINT unsigned NOT NULL default '2097152';
ALTER TABLE nmail_domain MODIFY d_quota_board BIGINT unsigned NOT NULL default '2097152';
ALTER TABLE nmail_domain MODIFY d_bigfile_quota BIGINT unsigned NOT NULL default '10485760';
ALTER TABLE nmail_domain MODIFY d_webhard_quota BIGINT unsigned NOT NULL default '10485760';

# 2024-09-02
ALTER TABLE nmail_webhard_file MODIFY wf_size BIGINT unsigned NOT NULL default 0;
ALTER TABLE nmail_mail_box MODIFY mb_name varchar(255) NOT NULL DEFAULT '';

# 2025-05-20 2단계 인증 추가
ALTER TABLE nmail_member ADD m_2fa_isuse enum('Y', 'N') NOT NULL DEFAULT 'N';
ALTER TABLE nmail_member ADD m_2fa_code varchar(255) NULL;
ALTER TABLE nmail_member ADD m_2fa_ip varchar(15) NULL;
ALTER TABLE nmail_member ADD m_2fa_date DATETIME NULL;

# 2025-05-29 2단계 인증 서비스 사용 여부 추가
ALTER TABLE nmail_domain ADD d_svc_2fa enum('Y', 'N') DEFAULT 'N' NOT NULL AFTER d_svc_forward;

# 2025-05-30 2단계 인증 필수여부 추가
ALTER TABLE nmail_member ADD m_2fa_required enum('Y', 'N') NOT NULL DEFAULT 'N' AFTER m_2fa_isuse;

# 2025-06-11 신뢰하는 기기
CREATE TABLE nmail_member_device (
                                 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                                 m_no INT UNSIGNED NOT NULL,
                                 os VARCHAR(255) NOT NULL,
                                 user_agent VARCHAR(255) NOT NULL,
                                 device_token VARCHAR(128) NOT NULL,
                                 is_trusted enum('Y','N') default 'N',
                                 ip VARCHAR(15),
                                 created_at DATETIME DEFAULT '0000-00-00 00:00:00',
                                 last_logindate datetime default '0000-00-00 00:00:00',
                                 last_loginhost VARCHAR(15),
                                 PRIMARY KEY (id),
                                 UNIQUE KEY token (m_no, device_token)
);

# 2025-06-19 세션 ID 저장
ALTER TABLE nmail_member_device ADD session_id VARCHAR(255) NOT NULL;

# 2025-07-15 기존 웹메일 접근 IP 관리에 아이디 추가
ALTER TABLE nmail_web_allow_ip ADD m_id VARCHAR(255) NOT NULL DEFAULT '' AFTER d_no;
ALTER TABLE nmail_web_allow_ip DROP PRIMARY KEY, ADD PRIMARY KEY (d_no, ip_ipaddr, ip_netmask, m_id);

# 2025-07-17 웹메일 로그인 로그에 아이디/도메인 구분
ALTER TABLE nmail_log_login CHANGE l_status l_status ENUM( 'OK', 'FI', 'FP', 'LID', 'LIP', 'DID', 'DIP') NOT NULL DEFAULT 'OK';

# 2025-07-21 메모 추가
ALTER TABLE nmail_white_ip ADD ip_memo varchar(255) AFTER ip_netmask;
ALTER TABLE nmail_relay_ip ADD ip_memo varchar(255) AFTER ip_netmask;
ALTER TABLE nmail_black_ip ADD ip_memo varchar(255) AFTER ip_netmask;

ALTER TABLE nmail_sadmin_spam_word ADD ssw_memo varchar(255) AFTER ssw_word;
ALTER TABLE nmail_sadmin_spam_addr ADD ssa_memo varchar(255) AFTER ssa_email;

# 2025-07-24 스팸 단어 관리
ALTER TABLE nmail_sadmin_spam_word DROP PRIMARY KEY, ADD ssw_no bigint(20) unsigned NOT NULL auto_increment FIRST, ADD PRIMARY KEY (ssw_no);
ALTER TABLE nmail_sadmin_spam_word ADD UNIQUE KEY (ssw_word);

# 2025-07-29 메일계정 관리 로그 개선
ALTER TABLE nmail_log_member CHANGE l_status l_status enum('ADD', 'DEL', 'MODP', 'SMTPS', 'SMTPUS', 'POP3S', 'POP3US', 'FORWARDS', 'FORWARDUS', '2FAS', '2FAUS', '2FARS', '2FARUS', 'MODMA', 'MODMU') NOT NULL;

# 2025-08-21 메일발송 상태 개선
ALTER TABLE nmail_smtp_send ADD s_send_user VARCHAR(255) NOT NULL DEFAULT '' AFTER m_no;
ALTER TABLE nmail_smtp_send ADD s_send_dom VARCHAR(255) NOT NULL DEFAULT '' AFTER s_send_user;

# 2025-08-25 도메인 관리
ALTER TABLE nmail_domain ADD d_webmail_url VARCHAR(255) NULL AFTER d_name;
ALTER TABLE nmail_domain ADD d_svc_external_pop3 enum('Y','N') default 'Y' AFTER d_svc_forward;
ALTER TABLE nmail_domain ADD d_svc_mailing_list enum('Y','N') default 'Y' AFTER d_svc_external_pop3;
ALTER TABLE nmail_domain ADD d_site_top_notice TEXT NULL AFTER d_site_title;
ALTER TABLE nmail_domain ADD d_site_bottom_notice TEXT NULL AFTER d_site_top_notice;
ALTER TABLE nmail_domain ADD d_site_favicon VARCHAR(255) NULL AFTER d_site_logo2_h;
ALTER TABLE nmail_domain ADD d_skin_limit_level enum('7','9') default '7' AFTER d_saveday;
ALTER TABLE nmail_domain ADD d_skin_birthday enum('Y','N') default 'Y' AFTER d_skin_webhard;
ALTER TABLE nmail_domain ADD d_sharediary_target enum('0', '1') default '1' AFTER d_sharediary_reg;
ALTER TABLE nmail_domain ADD d_login_signup enum('Y','N') default 'Y' AFTER d_site_favicon;
ALTER TABLE nmail_domain ADD d_login_account_recovery enum('Y','N') default 'Y' AFTER d_login_signup;
ALTER TABLE nmail_domain ADD d_login_profile_edit enum('Y','N') default 'Y' AFTER d_login_account_recovery;
ALTER TABLE nmail_domain ADD d_login_account_delete enum('Y','N') default 'Y' AFTER d_login_account_recovery;
ALTER TABLE nmail_domain ADD d_modifydate datetime NOT NULL default '0000-00-00 00:00:00';

# 2025-09-01 메일계정 관리 로그 개선
ALTER TABLE nmail_log_member ADD d_name varchar(255) NOT NULL AFTER d_no;

# 2025-09-01 웹메일 로그인 로그 개선
ALTER TABLE nmail_log_login ADD d_name varchar(255) NOT NULL AFTER d_no;

# 2025-09-08 사번 항목 추가
ALTER TABLE nmail_member ADD m_emp_no varchar(255) default NULL;

# 2025-09-10 서명관리
ALTER TABLE nmail_sign ADD sg_files TEXT NULL;
ALTER TABLE nmail_sign ADD sg_file_size INT UNSIGNED NOT NULL DEFAULT '0';

# 2025-09-12 게시판 이미지 업로드
ALTER TABLE nmail_board ADD b_image_files TEXT NULL after b_file_size;
ALTER TABLE nmail_board ADD b_image_size INT UNSIGNED NOT NULL DEFAULT '0' after b_image_files;

# 2025-09-17 중요 메일 체크
CREATE TABLE nmail_mail_star (
id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
ms_m_no INT UNSIGNED NOT NULL,
ms_mf_no bigint(20) unsigned,
created_at DATETIME DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id),
UNIQUE KEY token (ms_m_no, ms_mf_no)
);

# 2025-11-04 회원 아이디 50자까지 늘리기
UPDATE nmail_member_field SET mbf_maxlength=50 WHERE mbf_name='m_id' and mbf_maxlength=20;

# 2025-11-06 발송 전 미리보기 추가
ALTER TABLE nmail_member ADD m_send_preview enum('Y','N') DEFAULT 'N';

# 2025-11-12 편지함 백업시, 한글 파일명 지원 옵션 추가
ALTER TABLE nmail_member ADD m_mail_box_backup_type enum('filename','subject') DEFAULT 'filename';

# 2025-11-15 아이디, 비밀번호 저장 길이 늘리기
ALTER TABLE nmail_member MODIFY m_id VARCHAR(255)  NOT NULL DEFAULT '';
ALTER TABLE nmail_member MODIFY m_pwd VARCHAR(255)  NOT NULL DEFAULT '';
ALTER TABLE nmail_board MODIFY b_pwd VARCHAR(255)  NOT NULL DEFAULT '';
ALTER TABLE nmail_board_comment MODIFY bc_pwd VARCHAR(255)  NOT NULL DEFAULT '';

# 2025-11-19 기기 브라우저
ALTER TABLE nmail_member_device ADD browser VARCHAR(255) NOT NULL;

# 2025-11-19 최근 SMTP/POP3 인증일, 최근 메일 수신일
ALTER TABLE nmail_member ADD m_smtp_auth_date datetime default NULL;
ALTER TABLE nmail_member ADD m_pop3_auth_date datetime default NULL;
ALTER TABLE nmail_member ADD m_receive_date datetime default NULL;

# 2025-11-19 POP3 로그 메일 개수, 용량 항목 추가
ALTER  TABLE nmail_log_pop3 ADD p_count bigint(20) unsigned NOT NULL;
ALTER  TABLE nmail_log_pop3 ADD p_size bigint(20) unsigned NOT NULL;

# 2025-11-20 메일계정 관리 로그 - 회원탈퇴 추가
ALTER TABLE nmail_log_member CHANGE l_status l_status enum('ADD', 'DEL', 'WITHDRAWAL', 'MODP', 'LOSTOK', 'SMTPS', 'SMTPUS', 'POP3S', 'POP3US', 'FORWARDS', 'FORWARDUS', '2FAS', '2FAUS', '2FARS', '2FARUS', 'MODMA', 'MODMU') NOT NULL;

# 2025-11-21 편지함 트리 펼침여부 추가
ALTER TABLE nmail_mail_box ADD mb_open enum('Y','N') DEFAULT 'Y';

# 2025-11-25 편지함 HTML 제한 설정 추가
ALTER TABLE nmail_mail_box ADD html_limit enum('Y', 'N') DEFAULT 'N';

# 2025-12-01 대표 계정 사용 여부, 보낸주소를 대표 계정 주소로 사용할 수 있도록 기능 추가
ALTER TABLE nmail_team_mail ADD tm_is_from_use enum('Y','N') default 'N' after tm_is_use;

# 2025-12-03 정기점검 기록
CREATE TABLE nmail_monitor (
     id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
     cmd TEXT NOT NULL,
     result MEDIUMTEXT NOT NULL,
     df MEDIUMTEXT NOT NULL,
     created_at DATETIME DEFAULT '0000-00-00 00:00:00',
     PRIMARY KEY (id)
);