DROP TABLE IF EXISTS `fk_disposition`;
CREATE TABLE `fk_disposition` (
  `disposition` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`disposition`)
);
/*!40000 ALTER TABLE `fk_disposition` DISABLE KEYS */;
INSERT INTO `fk_disposition` (`disposition`)
VALUES
        ('none'),
        ('quarantine'),
        ('reject');
/*!40000 ALTER TABLE `fk_disposition` ENABLE KEYS */;
DROP TABLE IF EXISTS `fk_disposition_reason`;
CREATE TABLE `fk_disposition_reason` (
  `type` varchar(24) NOT NULL DEFAULT '',
  PRIMARY KEY (`type`)
);
/*!40000 ALTER TABLE `fk_disposition_reason` DISABLE KEYS */;
INSERT INTO `fk_disposition_reason` (`type`)
VALUES
        ('forwarded'),
        ('local_policy'),
        ('mailing_list'),
        ('other'),
        ('sampled_out'),
        ('trusted_forwarder');
/*!40000 ALTER TABLE `fk_disposition_reason` ENABLE KEYS */;
DROP TABLE IF EXISTS `fk_dkim_result`;
CREATE TABLE `fk_dkim_result` (
  `result` varchar(9) NOT NULL DEFAULT '',
  PRIMARY KEY (`result`)
);
/*!40000 ALTER TABLE `fk_dkim_result` DISABLE KEYS */;
INSERT INTO `fk_dkim_result` (`result`)
VALUES
        ('fail'),
        ('neutral'),
        ('none'),
        ('pass'),
        ('permerror'),
        ('policy'),
        ('temperror');
/*!40000 ALTER TABLE `fk_dkim_result` ENABLE KEYS */;
DROP TABLE IF EXISTS `fk_spf_result`;
CREATE TABLE `fk_spf_result` (
  `result` varchar(9) NOT NULL DEFAULT '',
  PRIMARY KEY (`result`)
);
/*!40000 ALTER TABLE `fk_spf_result` DISABLE KEYS */;
INSERT INTO `fk_spf_result` (`result`)
VALUES
        ('fail'),
        ('neutral'),
        ('none'),
        ('pass'),
        ('permerror'),
        ('softfail'),
        ('temperror');
/*!40000 ALTER TABLE `fk_spf_result` ENABLE KEYS */;
DROP TABLE IF EXISTS `fk_spf_scope`;
CREATE TABLE `fk_spf_scope` (
  `scope` varchar(5) NOT NULL DEFAULT '',
  PRIMARY KEY (`scope`)
);
/*!40000 ALTER TABLE `fk_spf_scope` DISABLE KEYS */;
INSERT INTO `fk_spf_scope` (`scope`)
VALUES
        ('helo'),
        ('mfrom');
/*!40000 ALTER TABLE `fk_spf_scope` ENABLE KEYS */;
DROP TABLE IF EXISTS `report`;
CREATE TABLE `report` (
  `id` integer PRIMARY KEY AUTOINCREMENT,
  `domain` varchar(253) NOT NULL DEFAULT '',
  `begin` datetime NOT NULL,
  `end` datetime NOT NULL,
  `error` varchar(255) DEFAULT NULL
);
DROP TABLE IF EXISTS `report_policy_published`;
CREATE TABLE `report_policy_published` (
  `report_id` int(11) NOT NULL,
  `adkim` char(1) DEFAULT NULL,
  `aspf` char(1) DEFAULT NULL,
  `p` varchar(10) DEFAULT NULL,
  `sp` varchar(10) DEFAULT NULL,
  `pct` tinyint(1) DEFAULT NULL,
  CONSTRAINT `report_policy_published_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS `report_record`;
CREATE TABLE `report_record` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `report_id` int(11) NOT NULL,
  `source_ip` varbinary(16) NOT NULL DEFAULT '',
  `disposition` varchar(10) NOT NULL DEFAULT '',
  `dkim` char(4) NOT NULL DEFAULT '',
  `spf` char(4) NOT NULL DEFAULT '',
  `envelope_to` varchar(253) NOT NULL DEFAULT '',
  `envelope_from` varchar(253) NOT NULL DEFAULT '',
  `header_from` varchar(253) NOT NULL DEFAULT '',
  CONSTRAINT `report_record_ibfk_2` FOREIGN KEY (`disposition`) REFERENCES `fk_disposition` (`disposition`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `report_record_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS `report_record_disp_reason`;
CREATE TABLE `report_record_disp_reason` (
  `report_record_id` int(11) NOT NULL,
  `type` varchar(24) NOT NULL DEFAULT '',
  `comment` varchar(255) DEFAULT NULL,
  CONSTRAINT `report_record_disp_reason_ibfk_4` FOREIGN KEY (`type`) REFERENCES `fk_disposition_reason` (`type`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `report_record_disp_reason_ibfk_3` FOREIGN KEY (`report_record_id`) REFERENCES `report_record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS `report_record_dkim`;
CREATE TABLE `report_record_dkim` (
  `report_record_id` integer int(11) NOT NULL,
  `domain` varchar(253) NOT NULL DEFAULT '',
  `selector` varchar(253) NOT NULL DEFAULT '',
  `result` varchar(9) NOT NULL DEFAULT '',
  `human_result` varchar(64) DEFAULT NULL,
  CONSTRAINT `report_record_dkim_ibfk_2` FOREIGN KEY (`result`) REFERENCES `fk_dkim_result` (`result`),
  CONSTRAINT `report_record_dkim_ibfk_1` FOREIGN KEY (`report_record_id`) REFERENCES `report_record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);
DROP TABLE IF EXISTS `report_record_spf`;
CREATE TABLE `report_record_spf` (
  `report_record_id` int(11) NOT NULL,
  `domain` varchar(253) NOT NULL DEFAULT '',
  `scope` varchar(5) NOT NULL DEFAULT '',
  `result` varchar(9) NOT NULL DEFAULT '',
  CONSTRAINT `report_record_spf_ibfk_1` FOREIGN KEY (`report_record_id`) REFERENCES `report_record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `report_record_spf_ibfk_2` FOREIGN KEY (`scope`) REFERENCES `fk_spf_scope` (`scope`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `report_record_spf_ibfk_3` FOREIGN KEY (`result`) REFERENCES `fk_spf_result` (`result`) ON DELETE CASCADE ON UPDATE CASCADE
);