CREATE DATABASE IF NOT EXISTS cashout DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE cashout;

CREATE TABLE IF NOT EXISTS tb_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cf_key VARCHAR(100) NOT NULL DEFAULT '',
  cf_value TEXT,
  UNIQUE KEY uk_cf_key (cf_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_account (
  ac_no INT AUTO_INCREMENT PRIMARY KEY,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  ac_id VARCHAR(100) NOT NULL DEFAULT '',
  ac_passwd VARCHAR(255) NOT NULL DEFAULT '',
  ac_nick VARCHAR(100) NOT NULL DEFAULT '',
  ac_profile TEXT,
  ac_phone VARCHAR(20) NOT NULL DEFAULT '',
  ac_status VARCHAR(10) NOT NULL DEFAULT 'on',
  ac_level INT NOT NULL DEFAULT 1,
  ac_point INT NOT NULL DEFAULT 0,
  ac_money INT NOT NULL DEFAULT 0,
  ac_icon VARCHAR(255) NOT NULL DEFAULT '',
  is_admin VARCHAR(1) NOT NULL DEFAULT 'N',
  visit_cnt INT NOT NULL DEFAULT 0,
  write_cnt INT NOT NULL DEFAULT 0,
  comment_cnt INT NOT NULL DEFAULT 0,
  login_ip VARCHAR(50) NOT NULL DEFAULT '',
  last_login DATETIME DEFAULT NULL,
  last_lottery_date DATETIME DEFAULT NULL,
  lottery_alarm_yn VARCHAR(1) NOT NULL DEFAULT 'N',
  lottery_alarm_send VARCHAR(1) NOT NULL DEFAULT 'N',
  q_alarm_yn VARCHAR(1) NOT NULL DEFAULT 'N',
  q_alarm_send VARCHAR(1) NOT NULL DEFAULT 'N',
  q_alarm_set VARCHAR(1) NOT NULL DEFAULT 'N',
  nick_date DATETIME DEFAULT NULL,
  follow INT NOT NULL DEFAULT 0,
  follower INT NOT NULL DEFAULT 0,
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_ac_code (ac_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_account_cache (
  ac_no INT AUTO_INCREMENT PRIMARY KEY,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  ac_id VARCHAR(100) NOT NULL DEFAULT '',
  ac_nick VARCHAR(100) NOT NULL DEFAULT '',
  ac_profile TEXT,
  ac_phone VARCHAR(20) NOT NULL DEFAULT '',
  ac_status VARCHAR(10) NOT NULL DEFAULT 'on',
  ac_level INT NOT NULL DEFAULT 1,
  ac_point INT NOT NULL DEFAULT 0,
  ac_money INT NOT NULL DEFAULT 0,
  ac_icon VARCHAR(255) NOT NULL DEFAULT '',
  is_admin VARCHAR(1) NOT NULL DEFAULT 'N',
  visit_cnt INT NOT NULL DEFAULT 0,
  write_cnt INT NOT NULL DEFAULT 0,
  comment_cnt INT NOT NULL DEFAULT 0,
  login_ip VARCHAR(50) NOT NULL DEFAULT '',
  last_login DATETIME DEFAULT NULL,
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_ac_code (ac_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_board (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bd_id VARCHAR(50) NOT NULL DEFAULT '',
  bd_name VARCHAR(100) NOT NULL DEFAULT '',
  bd_group VARCHAR(50) NOT NULL DEFAULT '',
  ord_no INT NOT NULL DEFAULT 0,
  bd_use_category VARCHAR(1) NOT NULL DEFAULT 'N',
  bd_category_info TEXT,
  UNIQUE KEY uk_bd_id (bd_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_alarm (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  t_code VARCHAR(50) NOT NULL DEFAULT '',
  al_type VARCHAR(20) NOT NULL DEFAULT '',
  al_read VARCHAR(1) NOT NULL DEFAULT 'N',
  al_flag VARCHAR(50) NOT NULL DEFAULT '',
  al_msg TEXT,
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_follow (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  fw_ac_code VARCHAR(50) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_board_content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bd_id VARCHAR(50) NOT NULL DEFAULT '',
  bd_name VARCHAR(100) NOT NULL DEFAULT '',
  ca_name VARCHAR(100) NOT NULL DEFAULT '',
  bo_subject VARCHAR(255) NOT NULL DEFAULT '',
  bo_content LONGTEXT,
  bo_thumbnail VARCHAR(255) NOT NULL DEFAULT '',
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  hit_cnt INT NOT NULL DEFAULT 0,
  like_cnt INT NOT NULL DEFAULT 0,
  bad_cnt INT NOT NULL DEFAULT 0,
  comment_cnt INT NOT NULL DEFAULT 0,
  point_sum INT NOT NULL DEFAULT 0,
  point_cnt INT NOT NULL DEFAULT 0,
  is_notice VARCHAR(1) NOT NULL DEFAULT 'N',
  is_secret VARCHAR(1) NOT NULL DEFAULT 'N',
  status VARCHAR(10) NOT NULL DEFAULT 'on',
  bo_pnum INT NOT NULL DEFAULT 0,
  bo_reply VARCHAR(10) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_board_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cache_type VARCHAR(50) NOT NULL DEFAULT '',
  bd_id VARCHAR(50) NOT NULL DEFAULT '',
  bd_name VARCHAR(100) NOT NULL DEFAULT '',
  bo_subject VARCHAR(255) NOT NULL DEFAULT '',
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  comment_cnt INT NOT NULL DEFAULT 0,
  like_cnt INT NOT NULL DEFAULT 0,
  bo_thumbnail VARCHAR(255) NOT NULL DEFAULT '',
  period VARCHAR(20) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_favorite_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cache_type VARCHAR(50) NOT NULL DEFAULT '',
  period VARCHAR(20) NOT NULL DEFAULT '',
  bd_id VARCHAR(50) NOT NULL DEFAULT '',
  bd_name VARCHAR(100) NOT NULL DEFAULT '',
  bo_subject VARCHAR(255) NOT NULL DEFAULT '',
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  comment_cnt INT NOT NULL DEFAULT 0,
  like_cnt INT NOT NULL DEFAULT 0,
  bo_thumbnail VARCHAR(255) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_popup (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pp_code VARCHAR(50) NOT NULL DEFAULT '',
  pp_status VARCHAR(10) NOT NULL DEFAULT 'off',
  pp_device VARCHAR(20) NOT NULL DEFAULT 'both',
  pp_no INT NOT NULL DEFAULT 0,
  start_date DATETIME DEFAULT NULL,
  end_date DATETIME DEFAULT NULL,
  pp_content TEXT,
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_event (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ev_title VARCHAR(255) NOT NULL DEFAULT '',
  ev_content TEXT,
  ev_status VARCHAR(10) NOT NULL DEFAULT 'on',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_event_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ev_title VARCHAR(255) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_seo (
  id INT AUTO_INCREMENT PRIMARY KEY,
  page_url VARCHAR(255) NOT NULL DEFAULT '',
  page_title VARCHAR(255) NOT NULL DEFAULT '',
  page_keyword VARCHAR(255) NOT NULL DEFAULT '',
  page_desc TEXT,
  page_h1 VARCHAR(255) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_visit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_uniq VARCHAR(100) NOT NULL DEFAULT '',
  ac_id VARCHAR(100) NOT NULL DEFAULT '',
  log_date DATE DEFAULT NULL,
  log_time TIME DEFAULT NULL,
  vi_os VARCHAR(100) NOT NULL DEFAULT '',
  vi_browser VARCHAR(100) NOT NULL DEFAULT '',
  vi_type VARCHAR(10) NOT NULL DEFAULT '',
  vi_ip VARCHAR(50) NOT NULL DEFAULT '',
  vi_device VARCHAR(100) NOT NULL DEFAULT '',
  vi_refer TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_account_level_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ac_level INT NOT NULL DEFAULT 0,
  next_exp_acc INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS log_service (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  ac_id VARCHAR(100) NOT NULL DEFAULT '',
  log_type VARCHAR(50) NOT NULL DEFAULT '',
  msg TEXT,
  log_ip VARCHAR(50) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_point (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  po_type INT NOT NULL DEFAULT 0,
  po_content VARCHAR(255) NOT NULL DEFAULT '',
  po_point INT NOT NULL DEFAULT 0,
  po_rel VARCHAR(100) NOT NULL DEFAULT '',
  po_uniq VARCHAR(100) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_po_uniq (po_uniq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_money (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  mo_type INT NOT NULL DEFAULT 0,
  mo_content VARCHAR(255) NOT NULL DEFAULT '',
  mo_money INT NOT NULL DEFAULT 0,
  mo_rel VARCHAR(100) NOT NULL DEFAULT '',
  mo_uniq VARCHAR(100) NOT NULL DEFAULT '',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_mo_uniq (mo_uniq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tb_photo_confirm (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bd_id VARCHAR(50) NOT NULL DEFAULT '',
  bo_id INT NOT NULL DEFAULT 0,
  ac_code VARCHAR(50) NOT NULL DEFAULT '',
  status VARCHAR(10) NOT NULL DEFAULT 'pending',
  regist_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert minimum required config data
INSERT IGNORE INTO tb_config (cf_key, cf_value) VALUES
('cf_title', 'CASHOUT'),
('cf_keyword', 'cashout'),
('cf_description', 'CASHOUT Community'),
('cf_login_point', '100'),
('cf_login_money', '0'),
('cf_max_money', '100000'),
('cf_use_tvlogin', 'N'),
('cf_use_recaptcha', 'N');
