DROP TABLE IF EXISTS domain;
CREATE TABLE domain (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  domain VARCHAR(63) NOT NULL,
  dmarc_rua VARCHAR(64) NOT NULL DEFAULT '',
  dmarc_rec VARCHAR(63) NOT NULL DEFAULT '',
  recv INT UNSIGNED NOT NULL DEFAULT 0,
  sent INT UNSIGNED NOT NULL DEFAULT 0,
  spamtrap INT UNSIGNED NOT NULL DEFAULT 0,
  dmarc_ri MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  original_ri MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  whitelisted TINYINT NOT NULL DEFAULT 0,
  add_dmarc TINYINT NOT NULL DEFAULT 0,
  add_adsp TINYINT NOT NULL DEFAULT 0,
  prefix_len TINYINT NOT NULL DEFAULT 0,
  since TIMESTAMP NOT NULL DEFAULT NOW(),
  last_report INT UNSIGNED NOT NULL DEFAULT 0,
  last_recv INT UNSIGNED NOT NULL DEFAULT 0,
  last_sent INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE INDEX by_dom(domain)
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

/*
* many-to-many link between domains and received messages
*/
DROP TABLE IF EXISTS msg_ref;
CREATE TABLE msg_ref (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message_in INT UNSIGNED NOT NULL COMMENT 'Foreign key to message_in',
  domain INT UNSIGNED NOT NULL COMMENT 'Foreign key to domain',
  auth SET ('author', 'spf_helo', 'spf', 'dkim', 'org', 'dmarc', 'aligned', 'dnswl', 'nx') NOT NULL,
  spf ENUM ('none', 'neutral', 'pass', 'fail', 'softfail', 'temperror', 'permerror') NOT NULL,
  dkim ENUM ('none', 'pass', 'fail', 'policy', 'neutral', 'temperror', 'permerror') NOT NULL,
  dkim_trans TINYINT UNSIGNED NOT NULL DEFAULT 0,
  dkim_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
  dkim_selector VARCHAR(63) NOT NULL DEFAULT '',
  INDEX by_dom_msg(domain, message_in),
  INDEX by_msg_auth(message_in, auth)
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

/*
* received messages
*/
DROP TABLE IF EXISTS message_in;
CREATE TABLE message_in (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ino INT UNSIGNED NOT NULL,
  mtime INT UNSIGNED NOT NULL,
  pid  INT UNSIGNED NOT NULL,
  ip INET6 NOT NULL,
  date VARCHAR(63),
  message_id VARCHAR(63),
  dmarc_dkim ENUM ('none', 'fail', 'pass') DEFAULT 'none',
  dmarc_spf ENUM ('none', 'fail', 'pass') DEFAULT 'none',
  dmarc_reason ENUM ('none', 'forwarded', 'sampled_out',
    'trusted_forwarder', 'mailing_list', 'local_policy', 'other') NOT NULL DEFAULT 'none',
  dmarc_dispo ENUM ('none', 'quarantine', 'reject') NOT NULL DEFAULT 'none',
  envelope_sender VARCHAR(63) NOT NULL DEFAULT '',
  content_type VARCHAR(63) NOT NULL DEFAULT 'text/plain',
  content_encoding VARCHAR(63) NOT NULL DEFAULT '7bit',
  received_count SMALLINT UNSIGNED NOT NULL,
  signatures_count SMALLINT UNSIGNED NOT NULL,
  mailing_list TINYINT NOT NULL DEFAULT 0,
  spamtrap TINYINT NOT NULL DEFAULT 0,
  score SMALLINT DEFAULT NULL COMMENT 'NULL if not tested',
  UNIQUE KEY (mtime, pid, ino)
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

/*
* user table
*/
DROP TABLE IF EXISTS user;
CREATE TABLE user (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  addr VARCHAR(63) NOT NULL,
  rcpt_max_perday MEDIUMINT UNSIGNED NOT NULL DEFAULT 10000,
  INDEX by_addr(addr(16))
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

/*
* sent messages
*/
DROP TABLE IF EXISTS message_out;
CREATE TABLE message_out (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ino INT UNSIGNED NOT NULL,
  mtime INT UNSIGNED NOT NULL,
  pid  INT UNSIGNED NOT NULL,
  user INT UNSIGNED NOT NULL COMMENT 'Foreign key to user',
  ip INET6 NOT NULL,
  rcpt_count INT UNSIGNED NOT NULL DEFAULT 1,
  date VARCHAR(63),
  message_id VARCHAR(63),
  envelope_sender VARCHAR(63) NOT NULL DEFAULT '',
  content_type VARCHAR(63) NOT NULL DEFAULT 'text/plain',
  content_encoding VARCHAR(63) NOT NULL DEFAULT '7bit',
  UNIQUE KEY (mtime, pid, ino)
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

/*
* many-to-many link between domains and sent messages
*/
DROP TABLE IF EXISTS msg_out_ref;
CREATE TABLE msg_out_ref (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  message_out INT UNSIGNED NOT NULL COMMENT 'Foreign key to message_out',
  domain INT UNSIGNED NOT NULL COMMENT 'Foreign key to domain',
  INDEX by_dom_msg_out(domain, message_out)
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


/*
* bounces after sending aggregate reports
*/
DROP TABLE IF EXISTS dmarc_bounce;
CREATE TABLE dmarc_bounce (
  addr VARBINARY(320) NOT NULL PRIMARY KEY,
  since TIMESTAMP NOT NULL DEFAULT NOW() COMMENT 'bounce date',
  days INT UNSIGNED NOT NULL DEFAULT 40 COMMENT 'quarantine period'
)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- varchar(320)+CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci:
-- ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes


delimiter //

/*
* Called by db_sql_select_domain:
* Insert/update domain, return domain_ref
*/
DROP PROCEDURE IF EXISTS recv_from_domain//

CREATE PROCEDURE recv_from_domain (
  IN m_domain VARCHAR(63),
  IN m_dkim TINYINT,
  IN m_prefix_len TINYINT,
  IN m_dmarc_ri MEDIUMINT UNSIGNED,
  IN m_original_ri MEDIUMINT UNSIGNED,
  IN m_dmarc_rec VARCHAR(63),
  IN m_dmarc_rua VARCHAR(64))
  MODIFIES SQL DATA
BEGIN
  DECLARE d_id INT UNSIGNED;
  DECLARE d_white TINYINT;
  BEGIN
    DECLARE Empty_set CONDITION FOR 1329;
    DECLARE CONTINUE HANDLER FOR Empty_set
      BEGIN
        # meanwhile, domain might have been inserted by another child
        DECLARE Duplicate_entry CONDITION FOR 1062;
        DECLARE CONTINUE HANDLER FOR Duplicate_entry
          SELECT id, whitelisted INTO d_id, d_white
            FROM domain WHERE domain = m_domain;
        SET d_white = 0;
        SET d_id = 0;
        INSERT INTO domain SET domain = m_domain;
        IF d_id = 0 THEN
          SELECT LAST_INSERT_ID() INTO d_id;
        END IF;
      END;
      SELECT id, whitelisted INTO d_id, d_white
        FROM domain WHERE domain = m_domain;
  END;
  IF d_white >= 0 AND d_white < 1 AND m_dkim = 1 THEN
    # whitelisted=1 just affects the order of signature validation attempts
    UPDATE domain SET whitelisted = GREATEST(1, whitelisted),
      prefix_len = IFNULL(m_prefix_len, prefix_len),
      recv = recv + 1, last_recv = UNIX_TIMESTAMP()+0 WHERE id = d_id;
  ELSE
    UPDATE domain SET recv = recv + 1,
      prefix_len = IFNULL(m_prefix_len, prefix_len),
      last_recv = UNIX_TIMESTAMP()+0 WHERE id = d_id;
  END IF;
  IF m_dmarc_ri > 0 THEN
    UPDATE domain SET dmarc_ri = m_dmarc_ri, original_ri = m_original_ri,
      dmarc_rec = m_dmarc_rec, dmarc_rua = m_dmarc_rua  WHERE id = d_id;
  END IF;
  SELECT d_id AS domain_ref;
END //


/*
* Called by db_sql_select_user:
* Insert/update user, insert message_out
*/
DROP PROCEDURE IF EXISTS sent_message //
CREATE PROCEDURE sent_message (
  IN m_addr VARCHAR(63),
  IN m_ino INT UNSIGNED,
  IN m_mtime INT UNSIGNED,
  IN m_pid INT UNSIGNED,
  IN m_ip INET6,
  IN m_date VARCHAR(63),
  IN m_id VARCHAR(63),
  IN m_es VARCHAR(63),
  IN m_ct VARCHAR(63),
  IN m_ce VARCHAR(63),
  IN m_rcpt INT UNSIGNED)
  MODIFIES SQL DATA
BEGIN
  DECLARE user_ref INT UNSIGNED;
  DECLARE Empty_set CONDITION FOR 1329;
  DECLARE CONTINUE HANDLER FOR Empty_set
    BEGIN
      INSERT INTO user SET addr = m_addr;
      SELECT LAST_INSERT_ID() INTO user_ref;
    END;
  SELECT id INTO user_ref FROM user WHERE addr = m_addr LIMIT 1;
  INSERT INTO message_out SET ino = m_ino,
    mtime = m_mtime,
    pid = m_pid,
    ip = m_ip,
    user = user_ref,
    date = m_date,
    message_id = m_id,
    envelope_sender = m_es,
    content_type = m_ct,
    content_encoding = m_ce,
    rcpt_count = m_rcpt;
  SELECT user_ref, LAST_INSERT_ID() AS message_ref;
END //

/*
* Called by db_sql_insert_target_ref:
*  Insert/update domain, insert msg_out_ref
*/
DROP PROCEDURE IF EXISTS sent_to_domain //
CREATE PROCEDURE sent_to_domain (
  IN message_ref INT UNSIGNED,
  IN c_flag TINYINT UNSIGNED,
  IN m_domain VARCHAR(63))
  MODIFIES SQL DATA
BEGIN
  DECLARE d_id INT UNSIGNED;
  DECLARE d_white TINYINT;
  DECLARE Empty_set CONDITION FOR 1329;
  DECLARE CONTINUE HANDLER FOR Empty_set
    BEGIN
      INSERT INTO domain SET domain = m_domain;
      SELECT LAST_INSERT_ID() INTO d_id;
    END;
  SELECT id INTO d_id FROM domain WHERE domain = m_domain;
  IF c_flag = 0 THEN
    SET d_white = 2;
  ELSE
    SET d_white = 0;
  END IF;
  UPDATE domain SET whitelisted = IF(whitelisted<0, whitelisted, GREATEST(whitelisted, d_white)),
    sent = sent + 1,
    last_sent = UNIX_TIMESTAMP()+0 WHERE id = d_id;
  INSERT INTO msg_out_ref SET message_out = message_ref,
    domain = d_id;
END //

delimiter ;
