
CREATE TABLE IF NOT EXISTS necosuke_lpo_mast_account (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  status    ENUM('active', 'inactive') NOT NULL,
  hash      CHAR(40)     NOT NULL,
  name      VARCHAR(255) NOT NULL,
  email     VARCHAR(255) NOT NULL,
  password  VARCHAR(75)  NOT NULL,
  created   DATETIME     NOT NULL,
  updated   DATETIME     NOT NULL,
  logined   DATETIME     NULL,

  UNIQUE (hash),
  UNIQUE (email),
  INDEX  (status, email, password)
) TYPE=InnoDB;

CREATE TABLE IF NOT EXISTS necosuke_lpo_mast_lpo
(
  id             INT(10) UNSIGNED NOT NULL auto_increment,
  status         ENUM('active', 'inactive', 'deleted') NOT NULL,
  account_id     INT(10) UNSIGNED NOT NULL,
  name           VARCHAR(255)     NOT NULL,
  selector       VARCHAR(50)               DEFAULT '',
  landing_url    VARCHAR(255)              DEFAULT '',
  conversion_url VARCHAR(255)              DEFAULT '',
  created        DATETIME         NOT NULL,
  updated        DATETIME         NOT NULL,

  PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE IF NOT EXISTS necosuke_lpo_mast_condition
(
  id          INT(10) UNSIGNED NOT NULL auto_increment,
  status      ENUM('active', 'inactive', 'deleted') NOT NULL,
  lpo_id      INT(10) UNSIGNED NOT NULL,
  priority    INT(10) UNSIGNED NOT NULL,
  `condition` ENUM('all', 'keyword', 'referrer', 'query') NOT NULL,
  value       VARCHAR(255)              DEFAULT '',
  created     DATETIME         NOT NULL,
  updated     DATETIME         NOT NULL,

  PRIMARY KEY (id),
  INDEX (lpo_id),
  INDEX (lpo_id, status, priority)
) TYPE=InnoDB;

CREATE TABLE IF NOT EXISTS necosuke_lpo_mast_content
(
  id           INT(10) UNSIGNED NOT NULL auto_increment,
  status       ENUM('active', 'inactive', 'deleted') NOT NULL,
  condition_id INT(10) UNSIGNED NOT NULL,
  name         VARCHAR(255)     NOT NULL,
  rate         INT(10) UNSIGNED     NULL,
  content      TEXT                      DEFAULT '',
  created      DATETIME         NOT NULL,
  updated      DATETIME         NOT NULL,

  PRIMARY KEY (id),
  INDEX (condition_id),
  INDEX (condition_id, status)
) TYPE=InnoDB;

CREATE TABLE IF NOT EXISTS necosuke_lpo_tran_registration (
  id       INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  date     DATETIME     NOT NULL,
  hash     CHAR(40)     NOT NULL,
  name     VARCHAR(255) NOT NULL,
  email    VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,

  INDEX  (date),
  UNIQUE (hash),
  INDEX  (email)
) TYPE=InnoDB;

CREATE TABLE IF NOT EXISTS necosuke_lpo_tran_reminder (
  id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  date  DATETIME     NOT NULL,
  hash  CHAR(40)     NOT NULL,
  email VARCHAR(255) NOT NULL,

  INDEX  (date),
  UNIQUE (hash),
  INDEX  (email)
) TYPE=InnoDB;

/*
CREATE TABLE IF NOT EXISTS necosuke_lpo_user_1_log
(
  id           INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date`       DATETIME         NOT NULL,
  log          ENUM('LANDING', 'CONVERSION') NOT NULL,
  lpo_id       INT(10) UNSIGNED NOT NULL,
  condition_id INT(10) UNSIGNED NOT NULL,
  content_id   INT(10) UNSIGNED NOT NULL,

  PRIMARY KEY (id),
  INDEX (log, lpo_id),
  INDEX (log, condition_id),
  INDEX (log, content_id)
) TYPE=InnoDB;
*/
