-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Erstellungszeit: 16. Mrz 2026 um 12:01
-- Server-Version: 10.4.32-MariaDB
-- PHP-Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Datenbank: `mr`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `admin_users`
--

CREATE TABLE `admin_users` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `role` enum('admin','list_manager') NOT NULL DEFAULT 'list_manager',
  `list_id` int(11) DEFAULT NULL,
  `failed_logins` int(11) DEFAULT 0,
  `last_login_attempt` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `app_schema_migrations`
--

CREATE TABLE `app_schema_migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `migration_key` varchar(190) NOT NULL,
  `applied_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `bounce_patterns`
--

CREATE TABLE `bounce_patterns` (
  `id` int(11) NOT NULL,
  `pattern` varchar(255) NOT NULL,
  `bounce_type` enum('hard','soft','spam','other') NOT NULL DEFAULT 'other',
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle `bounce_patterns`
--

INSERT INTO `bounce_patterns` (`id`, `pattern`, `bounce_type`, `description`) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `campaigns`
--

CREATE TABLE `campaigns` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `subject_override` varchar(255) DEFAULT NULL,
  `template_id` int(11) NOT NULL,
  `layout_id` int(11) DEFAULT NULL,
  `status` enum('draft','scheduled','processing','sent','cancelled') NOT NULL DEFAULT 'draft',
  `type` enum('date','followup','transaction') NOT NULL DEFAULT 'date',
  `schedule_at` datetime DEFAULT NULL,
  `followup_days` int(11) DEFAULT NULL,
  `filter_type` enum('list','tag','all') NOT NULL DEFAULT 'all',
  `filter_value` varchar(255) DEFAULT NULL,
  `list_id` int(11) DEFAULT NULL,
  `tag_id` int(11) DEFAULT NULL,
  `tag_mode` enum('include','exclude') DEFAULT NULL,
  `use_doi_only` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `priority` tinyint(1) NOT NULL DEFAULT 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `custom_field_definitions`
--

CREATE TABLE `custom_field_definitions` (
  `id` int(11) NOT NULL,
  `field_key` varchar(100) NOT NULL,
  `label` varchar(255) NOT NULL,
  `field_type` enum('text','date','number','checkbox','select') DEFAULT 'text',
  `is_required` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_bounces`
--

CREATE TABLE `email_bounces` (
  `id` int(11) NOT NULL,
  `queue_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `bounce_type` enum('hard','soft','spam','other') DEFAULT 'other',
  `reason` text DEFAULT NULL,
  `raw_body` text DEFAULT NULL,
  `logged_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_layouts`
--

CREATE TABLE `email_layouts` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `header_html` longtext NOT NULL,
  `footer_html` longtext NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_link_tracking`
--

CREATE TABLE `email_link_tracking` (
  `id` int(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `original_url` text NOT NULL,
  `queue_id` int(11) NOT NULL,
  `clicked_at` datetime DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `device_type` enum('desktop','mobile','unknown') DEFAULT 'unknown'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_opens`
--

CREATE TABLE `email_opens` (
  `id` int(11) NOT NULL,
  `queue_id` int(11) NOT NULL,
  `opened_at` datetime NOT NULL DEFAULT current_timestamp(),
  `user_agent` text DEFAULT NULL,
  `device_type` enum('desktop','mobile','unknown') DEFAULT 'unknown',
  `ip_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_templates`
--

CREATE TABLE `email_templates` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `subject` varchar(191) NOT NULL,
  `editor_mode` varchar(32) NOT NULL DEFAULT 'code',
  `source_format` varchar(32) NOT NULL DEFAULT 'html',
  `builder_schema_version` smallint(5) unsigned NOT NULL DEFAULT 1,
  `body_html` longtext NOT NULL,
  `source_content` longtext DEFAULT NULL,
  `layout_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `forms`
--

CREATE TABLE `forms` (
  `id` int(11) NOT NULL,
  `list_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `success_message` text DEFAULT NULL,
  `erfolgstext` text DEFAULT NULL,
  `redirect_url` varchar(500) DEFAULT NULL,
  `redirect_url_confirm` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `show_anrede` tinyint(1) DEFAULT 0,
  `show_vorname` tinyint(1) DEFAULT 0,
  `show_nachname` tinyint(1) DEFAULT 0,
  `require_vorname` tinyint(1) NOT NULL DEFAULT 0,
  `require_nachname` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `formular_logs`
--

CREATE TABLE `formular_logs` (
  `id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `action` varchar(50) NOT NULL,
  `message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `is_admin_preview` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `form_fields`
--

CREATE TABLE `form_fields` (
  `id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `required` tinyint(1) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `form_list`
--

CREATE TABLE `form_list` (
  `id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `list_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `form_tags`
--

CREATE TABLE `form_tags` (
  `id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `import_log`
--

CREATE TABLE `import_log` (
  `id` int(11) NOT NULL,
  `admin_user` varchar(255) DEFAULT NULL,
  `list_id` int(11) DEFAULT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `total` int(11) DEFAULT NULL,
  `imported` int(11) DEFAULT NULL,
  `duplicates` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `lists`
--

CREATE TABLE `lists` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `requires_doi` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1 = Double-Opt-In erforderlich, 0 = nicht erforderlich',
  `from_identity_id` int(11) DEFAULT NULL,
  `notify_on_signup` tinyint(1) NOT NULL DEFAULT 0,
  `doi_template_id` int(11) DEFAULT NULL,
  `welcome_template_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `password_resets`
--

CREATE TABLE `password_resets` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `reset_token` varchar(100) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `pdf_downloads`
--

CREATE TABLE `pdf_downloads` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `pdf_download_stats`
--

CREATE TABLE `pdf_download_stats` (
  `id` int(11) NOT NULL,
  `pdf_id` int(11) NOT NULL,
  `recipient_id` int(11) DEFAULT 0,
  `ip_address` varchar(45) DEFAULT NULL,
  `downloaded_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `pdf_tokens`
--

CREATE TABLE `pdf_tokens` (
  `id` int(11) NOT NULL,
  `pdf_id` int(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `valid_until` datetime NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipients`
--

CREATE TABLE `recipients` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `anrede` varchar(255) DEFAULT NULL,
  `vorname` varchar(255) DEFAULT NULL,
  `nachname` varchar(255) DEFAULT NULL,
  `last_opened_at` datetime DEFAULT NULL,
  `last_clicked_at` datetime DEFAULT NULL,
  `notizen` text DEFAULT NULL,
  `status` enum('active','inactive','unsubscribed','bounced','pause') NOT NULL DEFAULT 'active',
  `password_hash` varchar(255) DEFAULT NULL,
  `login_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Zeitpunkt, an dem der EmpfÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¤nger angelegt wurde',
  `unsubscribed_at` datetime DEFAULT NULL COMMENT 'Zeitpunkt, an dem sich der EmpfÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¤nger abgemeldet hat'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipients_pause`
--

CREATE TABLE `recipients_pause` (
  `id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `pause_start` datetime NOT NULL,
  `pause_days` int(11) NOT NULL,
  `pause_end` datetime GENERATED ALWAYS AS (`pause_start` + interval `pause_days` day) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_custom_fields`
--

CREATE TABLE `recipient_custom_fields` (
  `id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `field_key` varchar(100) NOT NULL,
  `field_value` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_fields`
--

CREATE TABLE `recipient_fields` (
  `id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `field_key` varchar(100) NOT NULL,
  `field_value` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_lists`
--

CREATE TABLE `recipient_lists` (
  `recipient_id` int(11) NOT NULL,
  `list_id` int(11) NOT NULL,
  `subscribed_at` datetime DEFAULT current_timestamp(),
  `pause_token` varchar(64) DEFAULT NULL,
  `last_follow_up_at` datetime DEFAULT NULL COMMENT 'Zeitpunkt des letzten versendeten Follow-Ups fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r diesen EmpfÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¤nger in dieser Liste'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_list_optin`
--

CREATE TABLE `recipient_list_optin` (
  `recipient_id` int(11) NOT NULL,
  `list_id` int(11) NOT NULL,
  `doi_token` varchar(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `doi_confirmed` tinyint(1) DEFAULT 0,
  `doi_requested_at` datetime DEFAULT NULL,
  `doi_confirmed_at` datetime DEFAULT NULL,
  `formular_id` int(11) DEFAULT NULL,
  `doi_ip_signup_enc` varbinary(255) DEFAULT NULL COMMENT 'AES/ChaCha20-verschlÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼sselte IP bei Anmeldung',
  `doi_ip_signup_hash` char(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `doi_ip_confirm_enc` varbinary(255) DEFAULT NULL COMMENT 'VerschlÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼sselte IP beim Klick auf BestÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¤tigungslink',
  `doi_ip_confirm_hash` char(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `doi_user_agent` varchar(255) DEFAULT NULL COMMENT 'User-Agent bei Anmeldung',
  `doi_referrer` varchar(255) DEFAULT NULL COMMENT 'Referrer-URL des Formulars',
  `doi_mail_sent_at` datetime DEFAULT NULL COMMENT 'Versandzeitpunkt der DOI-Mail',
  `doi_mail_hash` char(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `doi_policy_hash` char(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `doi_version` varchar(20) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `doi_created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Erstellt',
  `doi_updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'Letzte ÃƒÆ’Ã†â€™ÃƒÂ¢Ã¢â€šÂ¬Ã…Â¾nderung',
  `doi_mail_body_b64` mediumblob DEFAULT NULL COMMENT 'Base64-kodierter HTML-Body der versendeten DOI-Mail'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci KEY_BLOCK_SIZE=8 ROW_FORMAT=COMPRESSED;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_tags`
--

CREATE TABLE `recipient_tags` (
  `id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  `assigned_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Zeitpunkt der Tag-Zuweisung'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `sender_identities`
--

CREATE TABLE `sender_identities` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `dkim_selector` varchar(64) DEFAULT NULL,
  `dkim_private_key` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `send_queue`
--

CREATE TABLE `send_queue` (
  `id` int(11) NOT NULL,
  `view_token` varchar(64) DEFAULT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `recipient_id` int(11) NOT NULL,
  `list_id` int(11) DEFAULT NULL,
  `scheduled_at` datetime NOT NULL,
  `status` enum('pending','sending','sent','error','processing') DEFAULT 'pending',
  `error_message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `sent_at` datetime DEFAULT NULL,
  `processing_time_ms` int(11) DEFAULT NULL,
  `priority` int(11) NOT NULL DEFAULT 5
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `settings`
--

CREATE TABLE `settings` (
  `id` int(10) UNSIGNED NOT NULL,
  `setting_key` varchar(100) NOT NULL COMMENT 'Eindeutiger Name der Einstellung (z. B. worker_max_emails)',
  `setting_value` text NOT NULL COMMENT 'Wert als String (Zahl, Text oder JSON)',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Erstellungszeitpunkt',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Letzte ÃƒÆ’Ã†â€™ÃƒÂ¢Ã¢â€šÂ¬Ã…Â¾nderung'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Globale Konfigurations- und Worker-Settings';

--
-- Daten fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `settings`
--

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `created_at`, `updated_at`) VALUES
(1, 'worker_max_emails', '100', '2025-08-02 17:40:29', '2026-03-16 10:53:04'),
(2, 'worker_max_runtime', '240', '2025-08-02 17:40:29', '2026-03-16 10:53:04'),
(3, 'worker_sleep_us', '5000000', '2025-08-02 17:40:29', '2026-03-16 10:53:04'),
(4, 'bounce_guard_total_threshold', '20', '2026-03-17 00:00:00', '2026-03-17 00:00:00'),
(5, 'bounce_guard_hard_threshold', '10', '2026-03-17 00:00:00', '2026-03-17 00:00:00'),
(6, 'bounce_guard_soft_threshold', '15', '2026-03-17 00:00:00', '2026-03-17 00:00:00'),
(7, 'bounce_guard_spam_threshold', '1', '2026-03-17 00:00:00', '2026-03-17 00:00:00'),
(8, 'default_editor_mode', 'email_builder', '2026-03-20 00:00:00', '2026-03-20 00:00:00'),
(9, 'allowed_editor_modes', 'code,ckeditor,email_builder', '2026-03-20 00:00:00', '2026-03-20 00:00:00');

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `settings_log`
--

CREATE TABLE `settings_log` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `setting_key` varchar(255) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `smtp_accounts`
--

CREATE TABLE `smtp_accounts` (
  `id` int(11) NOT NULL,
  `smtp_host` varchar(255) NOT NULL,
  `smtp_user` varchar(255) NOT NULL,
  `smtp_email` varchar(255) NOT NULL,
  `smtp_pass` varchar(255) NOT NULL,
  `smtp_port` int(11) NOT NULL DEFAULT 587,
  `smtp_secure` enum('tls','ssl') DEFAULT 'tls',
  `is_active` tinyint(1) DEFAULT 1,
  `last_used_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `max_global_20min` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `smtp_domain_rules`
--

CREATE TABLE `smtp_domain_rules` (
  `id` int(11) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `smtp_account_id` int(11) NOT NULL,
  `max_per_20min` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `smtp_domain_sendlog`
--

CREATE TABLE `smtp_domain_sendlog` (
  `id` int(11) NOT NULL,
  `smtp_id` int(11) NOT NULL,
  `domain` varchar(255) NOT NULL,
  `sent_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `suspicious_ips`
--

CREATE TABLE `suspicious_ips` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `window_start` datetime NOT NULL,
  `window_end` datetime NOT NULL,
  `clicks_total` int(11) NOT NULL DEFAULT 0,
  `uniq_queue_click` int(11) NOT NULL DEFAULT 0,
  `max_clicks_per_minute` int(11) NOT NULL DEFAULT 0,
  `opens_total` int(11) NOT NULL DEFAULT 0,
  `uniq_queue_open` int(11) NOT NULL DEFAULT 0,
  `suspicion_score` int(11) NOT NULL DEFAULT 0,
  `reasons` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `system_logs`
--

CREATE TABLE `system_logs` (
  `id` int(11) NOT NULL,
  `log_level` varchar(10) NOT NULL,
  `message` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `ip_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `tags`
--

CREATE TABLE `tags` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `expiration_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Trigger `tags`
--
DELIMITER $$
CREATE TRIGGER `tr_tags_prevent_delete_system` BEFORE DELETE ON `tags` FOR EACH ROW BEGIN
  IF OLD.is_system = 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'System-Tag kann nicht geloescht werden';
  END IF;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `tr_tags_prevent_update_system` BEFORE UPDATE ON `tags` FOR EACH ROW BEGIN
  
  IF OLD.is_system = 1 AND NEW.name <> OLD.name THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'System-Tag kann nicht umbenannt werden';
  END IF;

  
  IF OLD.is_system = 1 AND NEW.is_system <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'System-Tag Kennzeichnung kann nicht entfernt werden';
  END IF;
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `email_link_actions`
--

CREATE TABLE `email_link_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_link_tracking_id` int(11) NOT NULL,
  `action_type` enum('assign_tag','remove_tag') NOT NULL,
  `tag_id` int(11) NOT NULL,
  `only_once` tinyint(1) NOT NULL DEFAULT 1,
  `executed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_email_link_actions_tracking` (`email_link_tracking_id`),
  KEY `idx_email_link_actions_tag` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `automation_rules`
--

CREATE TABLE `automation_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `event_type` enum('tag_assigned','tag_removed') NOT NULL,
  `event_tag_id` int(11) NOT NULL,
  `action_type` enum('assign_tag','remove_tag','subscribe_list','unsubscribe_list') NOT NULL,
  `action_target_tag_id` int(11) DEFAULT NULL,
  `action_target_list_id` int(11) DEFAULT NULL,
  `only_once_per_recipient` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_automation_rules_event` (`is_active`,`event_type`,`event_tag_id`),
  KEY `idx_automation_rules_target_tag` (`action_target_tag_id`),
  KEY `idx_automation_rules_target_list` (`action_target_list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `automation_rule_logs`
--

CREATE TABLE `automation_rule_logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rule_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `event_type` varchar(50) NOT NULL,
  `event_reference` varchar(190) DEFAULT NULL,
  `status` enum('executed','skipped','failed') NOT NULL DEFAULT 'executed',
  `details` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_automation_rule_logs_rule_recipient` (`rule_id`,`recipient_id`),
  KEY `idx_automation_rule_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `transaction_log`
--

CREATE TABLE `transaction_log` (
  `id` int(11) NOT NULL,
  `campaign_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `status` enum('triggered','skipped','error') NOT NULL,
  `message` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `transaction_rules`
--

CREATE TABLE `transaction_rules` (
  `id` int(11) NOT NULL,
  `campaign_id` int(11) NOT NULL,
  `field_key` varchar(64) DEFAULT NULL,
  `field_value` text DEFAULT NULL,
  `date_offset_days` int(11) DEFAULT 0,
  `required_tag_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `date_compare_mode` enum('equal','before','after') DEFAULT 'equal'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `user_log`
--

CREATE TABLE `user_log` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten fuer Tabelle ounce_patterns
--

INSERT INTO ounce_patterns (id, pattern, ounce_type, description) VALUES
(1, '%user unknown%', 'hard', 'Empfaenger unbekannt'),
(2, '%mailbox full%', 'soft', 'Postfach voll'),
(3, '%blocked%', 'spam', 'Blockiert durch Spamfilter'),
(4, '%temporarily deferred%', 'soft', 'Voruebergehende Ablehnung'),
(5, '%mailbox not found%', 'hard', 'Postfach nicht gefunden'),
(6, '%mailbox unavailable%', 'hard', 'Postfach nicht verfuegbar'),
(7, '%host or domain name not found%', 'hard', 'Postfach oder Domain nicht gefunden'),
(8, '%user is unknown%', 'hard', 'Empfaenger unbekannt'),
(9, '%not accepted%', 'hard', 'E-Mail abgelehnt'),
(10, '%unrouteable address%', 'hard', 'Empfaenger nicht gefunden'),
(13, '%no mailbox%', 'hard', NULL),
(14, '%invalid address%', 'hard', NULL),
(15, '%recipient address rejected%', 'hard', NULL),
(16, '%does not exist%', 'hard', NULL),
(17, '%doesnt have a%', 'hard', NULL),
(18, '%mailbox not available%', 'hard', NULL),
(19, '%no such user%', 'hard', NULL),
(20, '%delivery not allowed%', 'hard', NULL),
(21, '%mailbox inactive%', 'hard', NULL),
(22, '%the email account that you tried to reach is disabled%', 'hard', NULL),
(23, '%account has been disabled%', 'hard', NULL),
(24, '%550 address rejected%', 'hard', NULL),
(25, '%account that you tried to reach is disabled%', 'hard', NULL),
(26, '%550 no such local user%', 'hard', NULL),
(27, '%550 account suspended%', 'hard', NULL),
(28, '%550 #5.1.0 address rejected%', 'hard', NULL),
(29, '%this account is disabled%', 'hard', NULL),
(30, 'benutzer hat zuviele mails auf dem server', 'soft', 'Postfach voll'),
(33, '%speichervolumen ueberschritten%', 'soft', NULL),
(34, '%mailbox is full%', 'soft', NULL),
(35, '%onnection refused%', 'soft', NULL),
(36, '%refused%', 'soft', NULL),
(37, '%permanent error%', 'soft', NULL),
(38, '%mailbox over quota%', 'soft', NULL),
(39, '%mailbox deaktiviert%', 'hard', NULL),
(40, '%malformed or unexpected name server reply%', 'soft', NULL),
(41, '%mailbox disk quota exceeded%', 'soft', NULL),
(42, '%account is temporarily over quota%', 'soft', NULL),
(43, '%account is full%', 'soft', NULL),
(44, '%maildir over quota%', 'soft', NULL),
(45, '%quota exceeded%', 'soft', NULL),
(46, '%552-quota exceeded%', 'soft', NULL),
(47, '%account temporary disabled%', 'soft', NULL),
(48, '%unable to accept message%', 'soft', NULL),
(49, '%554 rejected by surbl%', 'soft', NULL),
(50, '%cannot be delivered%', 'soft', NULL),
(51, '%spam or virus classification%', 'spam', NULL),
(52, '%message detected as spam%', 'spam', NULL),
(53, '%as spamming%', 'spam', NULL),
(54, '%als spam%', 'spam', NULL),
(55, '%virus eingestuft%', 'spam', NULL),
(56, '%as spam%', 'spam', NULL),
(57, '%550 spam message%', 'spam', NULL),
(58, '%as spam by content filtering%', 'spam', NULL),
(59, '%recipients have complained about included content%', 'spam', NULL),
(60, '%is listed on sbl-multi.rbl.spamrl.com%', 'spam', NULL),
(61, '%blocked by spamassassin%', 'spam', NULL),
(62, '%the internet community may consider spam%', 'spam', NULL),
(63, '%message because it is spam%', 'spam', NULL),
(64, '%message cannot be accepted, spam rejection%', 'spam', NULL),
(65, '%your message was detected as spam%', 'spam', NULL),
(66, '%blacklisted url%', 'spam', NULL);

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `admin_users`
--
ALTER TABLE `admin_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `app_schema_migrations`
--
ALTER TABLE `app_schema_migrations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_migration_key` (`migration_key`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `bounce_patterns`
--
ALTER TABLE `bounce_patterns`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `pattern` (`pattern`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `campaigns`
--
ALTER TABLE `campaigns`
  ADD PRIMARY KEY (`id`),
  ADD KEY `template_id` (`template_id`),
  ADD KEY `layout_id` (`layout_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `custom_field_definitions`
--
ALTER TABLE `custom_field_definitions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `field_key` (`field_key`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `email_bounces`
--
ALTER TABLE `email_bounces`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_email_bounces_logged_at` (`logged_at`),
  ADD KEY `idx_email_bounces_queue_logged_at` (`queue_id`,`logged_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `email_layouts`
--
ALTER TABLE `email_layouts`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `email_link_tracking`
--
ALTER TABLE `email_link_tracking`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `token` (`token`),
  ADD KEY `queue_id` (`queue_id`),
  ADD KEY `idx_email_link_tracking_clicked_at_queue` (`clicked_at`,`queue_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `email_opens`
--
ALTER TABLE `email_opens`
  ADD PRIMARY KEY (`id`),
  ADD KEY `queue_id` (`queue_id`),
  ADD KEY `idx_email_opens_opened_at_queue` (`opened_at`,`queue_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `email_templates`
--
ALTER TABLE `email_templates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `layout_id` (`layout_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `forms`
--
ALTER TABLE `forms`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `formular_logs`
--
ALTER TABLE `formular_logs`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `form_fields`
--
ALTER TABLE `form_fields`
  ADD PRIMARY KEY (`id`),
  ADD KEY `form_id` (`form_id`),
  ADD KEY `fk_form_fields_field_id` (`field_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `form_list`
--
ALTER TABLE `form_list`
  ADD PRIMARY KEY (`id`),
  ADD KEY `form_id` (`form_id`),
  ADD KEY `list_id` (`list_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `form_tags`
--
ALTER TABLE `form_tags`
  ADD PRIMARY KEY (`id`),
  ADD KEY `form_id` (`form_id`),
  ADD KEY `tag_id` (`tag_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `import_log`
--
ALTER TABLE `import_log`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `lists`
--
ALTER TABLE `lists`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_lists_sender` (`from_identity_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `password_resets`
--
ALTER TABLE `password_resets`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `pdf_downloads`
--
ALTER TABLE `pdf_downloads`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `pdf_download_stats`
--
ALTER TABLE `pdf_download_stats`
  ADD PRIMARY KEY (`id`),
  ADD KEY `pdf_id` (`pdf_id`),
  ADD KEY `idx_pdf_download_stats_downloaded_at` (`downloaded_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `pdf_tokens`
--
ALTER TABLE `pdf_tokens`
  ADD PRIMARY KEY (`id`),
  ADD KEY `pdf_id` (`pdf_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `recipients`
--
ALTER TABLE `recipients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_recipients_unsubscribed_at` (`unsubscribed_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `recipients_pause`
--
ALTER TABLE `recipients_pause`
  ADD PRIMARY KEY (`id`),
  ADD KEY `recipient_id` (`recipient_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `recipient_custom_fields`
--
ALTER TABLE `recipient_custom_fields`
  ADD PRIMARY KEY (`id`),
  ADD KEY `recipient_id` (`recipient_id`),
  ADD KEY `field_key` (`field_key`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `recipient_fields`
--
ALTER TABLE `recipient_fields`
  ADD PRIMARY KEY (`id`),
  ADD KEY `recipient_id` (`recipient_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `recipient_lists`
--
ALTER TABLE `recipient_lists`
  ADD PRIMARY KEY (`recipient_id`,`list_id`),
  ADD UNIQUE KEY `pause_token` (`pause_token`),
  ADD KEY `list_id` (`list_id`),
  ADD KEY `idx_last_follow_up_at` (`last_follow_up_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `recipient_list_optin`
--
ALTER TABLE `recipient_list_optin`
  ADD PRIMARY KEY (`recipient_id`,`list_id`),
  ADD UNIQUE KEY `uniq_doi_token` (`doi_token`),
  ADD KEY `idx_doi_confirmed_at` (`doi_confirmed_at`),
  ADD KEY `idx_doi_mail_sent_at` (`doi_mail_sent_at`),
  ADD KEY `idx_open_doi` (`doi_confirmed`,`doi_requested_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `recipient_tags`
--
ALTER TABLE `recipient_tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_recipient_tag` (`recipient_id`,`tag_id`),
  ADD KEY `idx_assigned_at` (`assigned_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `sender_identities`
--
ALTER TABLE `sender_identities`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `send_queue`
--
ALTER TABLE `send_queue`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_campaign_recipient_template` (`campaign_id`,`recipient_id`,`template_id`),
  ADD KEY `idx_template_id` (`template_id`),
  ADD KEY `fk_sendqueue_recipient` (`recipient_id`),
  ADD KEY `idx_send_queue_list` (`list_id`),
  ADD KEY `idx_sq_status_sent_at` (`status`,`sent_at`),
  ADD KEY `idx_sq_status_created_at` (`status`,`created_at`),
  ADD KEY `idx_sq_campaign_status_sent_at` (`campaign_id`,`status`,`sent_at`),
  ADD KEY `idx_sq_list_status_sent_at` (`list_id`,`status`,`sent_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_setting_key` (`setting_key`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `settings_log`
--
ALTER TABLE `settings_log`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `smtp_accounts`
--
ALTER TABLE `smtp_accounts`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `smtp_domain_rules`
--
ALTER TABLE `smtp_domain_rules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_smtp_account` (`smtp_account_id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `smtp_domain_sendlog`
--
ALTER TABLE `smtp_domain_sendlog`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_quick_lookup` (`smtp_id`,`domain`,`sent_at`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `suspicious_ips`
--
ALTER TABLE `suspicious_ips`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ip` (`ip_address`),
  ADD KEY `idx_time` (`window_start`,`window_end`),
  ADD KEY `idx_score` (`suspicion_score`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `system_logs`
--
ALTER TABLE `system_logs`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `tags`
--
ALTER TABLE `tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_tag_name` (`name`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `transaction_log`
--
ALTER TABLE `transaction_log`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `transaction_rules`
--
ALTER TABLE `transaction_rules`
  ADD PRIMARY KEY (`id`);

--
-- Indizes fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r die Tabelle `user_log`
--
ALTER TABLE `user_log`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r exportierte Tabellen
--

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `admin_users`
--
ALTER TABLE `admin_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `app_schema_migrations`
--
ALTER TABLE `app_schema_migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `bounce_patterns`
--
ALTER TABLE `bounce_patterns`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `campaigns`
--
ALTER TABLE `campaigns`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `custom_field_definitions`
--
ALTER TABLE `custom_field_definitions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_bounces`
--
ALTER TABLE `email_bounces`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_layouts`
--
ALTER TABLE `email_layouts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_link_tracking`
--
ALTER TABLE `email_link_tracking`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_opens`
--
ALTER TABLE `email_opens`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `email_templates`
--
ALTER TABLE `email_templates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `forms`
--
ALTER TABLE `forms`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `formular_logs`
--
ALTER TABLE `formular_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `form_fields`
--
ALTER TABLE `form_fields`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `form_list`
--
ALTER TABLE `form_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `form_tags`
--
ALTER TABLE `form_tags`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `import_log`
--
ALTER TABLE `import_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `lists`
--
ALTER TABLE `lists`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `password_resets`
--
ALTER TABLE `password_resets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `pdf_downloads`
--
ALTER TABLE `pdf_downloads`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `pdf_download_stats`
--
ALTER TABLE `pdf_download_stats`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `pdf_tokens`
--
ALTER TABLE `pdf_tokens`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipients`
--
ALTER TABLE `recipients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipients_pause`
--
ALTER TABLE `recipients_pause`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_custom_fields`
--
ALTER TABLE `recipient_custom_fields`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_fields`
--
ALTER TABLE `recipient_fields`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `recipient_tags`
--
ALTER TABLE `recipient_tags`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `sender_identities`
--
ALTER TABLE `sender_identities`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `send_queue`
--
ALTER TABLE `send_queue`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `settings_log`
--
ALTER TABLE `settings_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `smtp_accounts`
--
ALTER TABLE `smtp_accounts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `smtp_domain_rules`
--
ALTER TABLE `smtp_domain_rules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `smtp_domain_sendlog`
--
ALTER TABLE `smtp_domain_sendlog`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `suspicious_ips`
--
ALTER TABLE `suspicious_ips`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `system_logs`
--
ALTER TABLE `system_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `tags`
--
ALTER TABLE `tags`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `transaction_log`
--
ALTER TABLE `transaction_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `transaction_rules`
--
ALTER TABLE `transaction_rules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT fÃƒÆ’Ã†â€™Ãƒâ€šÃ‚Â¼r Tabelle `user_log`
--
ALTER TABLE `user_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `campaigns`
--
ALTER TABLE `campaigns`
  ADD CONSTRAINT `campaigns_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `email_templates` (`id`),
  ADD CONSTRAINT `campaigns_ibfk_2` FOREIGN KEY (`layout_id`) REFERENCES `email_layouts` (`id`);

--
-- Constraints der Tabelle `email_link_tracking`
--
ALTER TABLE `email_link_tracking`
  ADD CONSTRAINT `email_link_tracking_ibfk_1` FOREIGN KEY (`queue_id`) REFERENCES `send_queue` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `email_opens`
--
ALTER TABLE `email_opens`
  ADD CONSTRAINT `email_opens_ibfk_1` FOREIGN KEY (`queue_id`) REFERENCES `send_queue` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `email_templates`
--
ALTER TABLE `email_templates`
  ADD CONSTRAINT `email_templates_ibfk_1` FOREIGN KEY (`layout_id`) REFERENCES `email_layouts` (`id`) ON DELETE SET NULL;

--
-- Constraints der Tabelle `form_fields`
--
ALTER TABLE `form_fields`
  ADD CONSTRAINT `fk_form_fields_field_id` FOREIGN KEY (`field_id`) REFERENCES `custom_field_definitions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `form_fields_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `form_list`
--
ALTER TABLE `form_list`
  ADD CONSTRAINT `form_list_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `form_list_ibfk_2` FOREIGN KEY (`list_id`) REFERENCES `lists` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `form_tags`
--
ALTER TABLE `form_tags`
  ADD CONSTRAINT `form_tags_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `form_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `lists`
--
ALTER TABLE `lists`
  ADD CONSTRAINT `fk_lists_sender` FOREIGN KEY (`from_identity_id`) REFERENCES `sender_identities` (`id`) ON DELETE SET NULL;

--
-- Constraints der Tabelle `pdf_download_stats`
--
ALTER TABLE `pdf_download_stats`
  ADD CONSTRAINT `pdf_download_stats_ibfk_1` FOREIGN KEY (`pdf_id`) REFERENCES `pdf_downloads` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `pdf_tokens`
--
ALTER TABLE `pdf_tokens`
  ADD CONSTRAINT `pdf_tokens_ibfk_1` FOREIGN KEY (`pdf_id`) REFERENCES `pdf_downloads` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `recipients_pause`
--
ALTER TABLE `recipients_pause`
  ADD CONSTRAINT `recipients_pause_ibfk_1` FOREIGN KEY (`recipient_id`) REFERENCES `recipients` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `recipient_custom_fields`
--
ALTER TABLE `recipient_custom_fields`
  ADD CONSTRAINT `recipient_custom_fields_ibfk_1` FOREIGN KEY (`recipient_id`) REFERENCES `recipients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `recipient_custom_fields_ibfk_2` FOREIGN KEY (`field_key`) REFERENCES `custom_field_definitions` (`field_key`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `recipient_fields`
--
ALTER TABLE `recipient_fields`
  ADD CONSTRAINT `recipient_fields_ibfk_1` FOREIGN KEY (`recipient_id`) REFERENCES `recipients` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `recipient_lists`
--
ALTER TABLE `recipient_lists`
  ADD CONSTRAINT `recipient_lists_ibfk_1` FOREIGN KEY (`recipient_id`) REFERENCES `recipients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `recipient_lists_ibfk_2` FOREIGN KEY (`list_id`) REFERENCES `lists` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `recipient_tags`
--
ALTER TABLE `recipient_tags`
  ADD CONSTRAINT `recipient_tags_ibfk_1` FOREIGN KEY (`recipient_id`) REFERENCES `recipients` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `send_queue`
--
ALTER TABLE `send_queue`
  ADD CONSTRAINT `fk_sendqueue_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sendqueue_recipient` FOREIGN KEY (`recipient_id`) REFERENCES `recipients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sendqueue_template` FOREIGN KEY (`template_id`) REFERENCES `email_templates` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `send_queue_ibfk_1` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `send_queue_ibfk_2` FOREIGN KEY (`recipient_id`) REFERENCES `recipients` (`id`) ON DELETE CASCADE;

--
-- Constraints der Tabelle `smtp_domain_rules`
--
ALTER TABLE `smtp_domain_rules`
  ADD CONSTRAINT `fk_smtp_account` FOREIGN KEY (`smtp_account_id`) REFERENCES `smtp_accounts` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;





-- --------------------------------------------------------

--
-- Tabellenstruktur fuer Tabelle `member_password_resets`
--

CREATE TABLE `member_password_resets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `recipient_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `reset_token` varchar(100) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) NOT NULL DEFAULT 0,
  `used_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_member_password_resets_token` (`reset_token`),
  KEY `idx_member_password_resets_email` (`email`),
  KEY `idx_member_password_resets_recipient` (`recipient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;