-- Create database for DMARC data

-- Copyright 2012, 2016, 2024, Taughannock Networks. All rights reserved.

-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions
-- are met:

-- Redistributions of source code must retain the above copyright
-- notice, this list of conditions and the following disclaimer.

-- Redistributions in binary form must reproduce the above copyright
-- notice, this list of conditions and the following disclaimer in the
-- documentation and/or other materials provided with the distribution.

-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
-- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
-- HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
-- OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
-- AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
-- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY
-- WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
-- POSSIBILITY OF SUCH DAMAGE.

USE dmarc

--
-- Table structure for table failure
--

DROP TABLE IF EXISTS failure;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE failure (
  serial int unsigned NOT NULL AUTO_INCREMENT,
  org varchar(255) NOT NULL,
  bouncedomain varchar(255) DEFAULT NULL,
  bouncebox varchar(255) DEFAULT NULL,
  fromdomain varchar(255) DEFAULT NULL,
  frombox varchar(255) DEFAULT NULL,
  arrival timestamp NULL DEFAULT NULL,
  sourceip int unsigned DEFAULT NULL,
  sourceip6 binary(16) DEFAULT NULL,
  headers text,
  authres text,
  PRIMARY KEY (serial),
  KEY sourceip (sourceip),
  KEY fromdomain (fromdomain),
  KEY bouncedomain (bouncedomain)
) ENGINE=InnoDB AUTO_INCREMENT=1444 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table report
--

DROP TABLE IF EXISTS report;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE report (
  serial int unsigned NOT NULL AUTO_INCREMENT,
  mindate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  maxdate timestamp NOT NULL,
  domain varchar(255) NOT NULL,
  org varchar(255) NOT NULL,
  reportid varchar(255) NOT NULL,
  email varchar(255) DEFAULT NULL,
  extra_contact_info varchar(255) DEFAULT NULL,
  policy_adkim varchar(20) DEFAULT NULL,
  policy_aspf varchar(20) DEFAULT NULL,
  policy_p varchar(20) DEFAULT NULL,
  policy_sp varchar(20) DEFAULT NULL,
  policy_pct tinyint unsigned DEFAULT NULL,
  PRIMARY KEY (serial),
  UNIQUE KEY domain (org,domain,reportid),
  KEY dom (domain)
) ENGINE=InnoDB AUTO_INCREMENT=428086 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table rptrecord
--

DROP TABLE IF EXISTS rptrecord;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE rptrecord (
  serial int unsigned NOT NULL,
  recserial int unsigned NOT NULL AUTO_INCREMENT,
  ip int unsigned DEFAULT NULL,
  ip6 binary(16) DEFAULT NULL,
  rcount int unsigned NOT NULL,
  disposition enum('none','quarantine','reject') DEFAULT NULL,
  reason varchar(255) DEFAULT NULL,
  spf_align enum('fail','pass') DEFAULT NULL,
  dkim_align enum('fail','pass') DEFAULT NULL,
  identifier_hfrom varchar(255) DEFAULT NULL,
  PRIMARY KEY (recserial),
  KEY serial (serial,ip),
  KEY serial6 (serial,ip6),
  CONSTRAINT rptrecord_ibfk_1 FOREIGN KEY (serial) REFERENCES report (serial) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7011096 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table rptrecdkim
--

DROP TABLE IF EXISTS rptrecdkim;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE rptrecdkim (
  recserial int unsigned NOT NULL,
  dkimdomain varchar(255) DEFAULT NULL,
  dkimselector varchar(255) DEFAULT NULL,
  dkimresult enum('none','neutral','pass','fail','temperror','permerror') DEFAULT NULL,
  KEY recserial (recserial),
  CONSTRAINT rptrecdkim_ibfk_1 FOREIGN KEY (recserial) REFERENCES rptrecord (recserial) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table rptrecspf
--

DROP TABLE IF EXISTS rptrecspf;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE rptrecspf (
  recserial int unsigned NOT NULL,
  spfdomain varchar(255) DEFAULT NULL,
  spfresult enum('none','neutral','pass','fail','softfail','temperror','permerror') DEFAULT NULL,
  KEY recserial (recserial),
  CONSTRAINT rptrecspf_ibfk_1 FOREIGN KEY (recserial) REFERENCES rptrecord (recserial) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

