
CREATE TABLE `wbnewslettersubscribersent` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlsid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlisid` int(11) unsigned NOT NULL DEFAULT 0,
  `created` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `status` enum('unknown','sent','failed','returned') NOT NULL DEFAULT 'unknown',
  PRIMARY KEY (`nlsid`,`nlisid`),
  KEY `mandatorid` (`mandatorid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='newsletter subscriber sendout';

CREATE TABLE `wbnewslettersubscriber` (
  `nlsid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL DEFAULT 0,
  `obscure` char(4) NOT NULL DEFAULT '',
  `emaillocal` varchar(200) NOT NULL,
  `emaildomain` varchar(125) NOT NULL,
  `approved` tinyint(4) NOT NULL DEFAULT 0,
  `approvedtimestamp` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `subscribed` tinyint(4) NOT NULL DEFAULT 0,
  `subscribedtimestamp` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `unsubscribedtimestamp` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `enabled` tinyint(4) NOT NULL DEFAULT 0,
  `enabledtimestamp` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `blacklisted` tinyint(4) NOT NULL DEFAULT 0,
  `blacklistedtimestamp` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `deleted` tinyint(4) NOT NULL DEFAULT 0,
  `checked` tinyint(1) NOT NULL DEFAULT 0,
  `checkedtimestamp` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `new` tinyint(1) NOT NULL DEFAULT 1,
  `bounced` int(11) unsigned NOT NULL DEFAULT 0,
  `forename` varchar(255) NOT NULL DEFAULT '',
  `surname` varchar(255) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `persongenderid` int(11) unsigned NOT NULL DEFAULT 0,
  `company` varchar(255) NOT NULL DEFAULT '',
  `phone` varchar(200) NOT NULL DEFAULT '',
  `street` varchar(200) NOT NULL DEFAULT '',
  `city` varchar(200) NOT NULL DEFAULT '',
  `zip` varchar(50) NOT NULL DEFAULT '',
  `position` varchar(255) NOT NULL DEFAULT '',
  `note` text NOT NULL DEFAULT '',
  `lang` char(5) NOT NULL DEFAULT 'C',
  `country` char(2) NOT NULL DEFAULT 'C',
  `created` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (`nlsid`),
  KEY `email` (`emaillocal`,`emaildomain`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='newsletter subscriber';

CREATE TABLE `wbnewslettersubscribermandator` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT '0',
  `nlsid` int(11) unsigned NOT NULL DEFAULT '0',
  KEY (`mandatorid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='newsletter subscriber available for mandator';

CREATE TABLE `wbnewslettergenericsource` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlgsid` int(11) unsigned NOT NULL,
  `title` varchar(64) NOT NULL DEFAULT "",
  `brief` text NOT NULL DEFAULT "",
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY `nlgsid` (`mandatorid`,`nlgsid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='newsletter generic subscriber sources';

CREATE TABLE `wbnewslettersubscribersource` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlsid` int(11) unsigned NOT NULL,
  `namespace` varchar(32) NOT NULL,
  `xid` int(11) unsigned NOT NULL,
  KEY `nlsid` (`nlsid`),
  KEY `mandatorid` (`mandatorid`),
  KEY `namespace` (`namespace`),
  KEY `namespacexid` (`namespace`,`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='newsletter subscriber sources' ;

CREATE TABLE `wbnewsletterissue` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlisid` int(11) unsigned NOT NULL DEFAULT 0,
  `linkid` int(11) unsigned NOT NULL DEFAULT 0,
  `vfsfid` int(11) unsigned NOT NULL DEFAULT 0,
  `template` varchar(255) NOT NULL DEFAULT '2_column',
  `urlid` int(11) unsigned NOT NULL DEFAULT 0,
  `urltext` varchar(255) NOT NULL DEFAULT '',
  `lang` char(5) NOT NULL DEFAULT 'C',
  `title` varchar(255) NOT NULL DEFAULT '',
  `brief` text NOT NULL DEFAULT '',
  `blurb` text NOT NULL DEFAULT '',
  `enabled` tinyint(4) NOT NULL DEFAULT 0,
  `deleted` tinyint(4) NOT NULL DEFAULT 0,
  `created` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `sent` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (`mandatorid`,`nlisid`),
  KEY `created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='newsletter issue';

CREATE TABLE `wbnewsletterissuetracklink` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlisid` int(11) unsigned NOT NULL,
  `linkid` int(11) unsigned NOT NULL,
  KEY `mandatorid` (`mandatorid`),
  KEY (`nlisid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='newsletter issue tracklink ids';

CREATE TABLE `wbnewsletterarticle` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlarticleid` int(11) unsigned NOT NULL DEFAULT 0,
  `vfsfid` int(11) unsigned NOT NULL DEFAULT 0,
  `urlid` int(11) unsigned NOT NULL DEFAULT 0,
  `urltext` varchar(255) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `brief` text NOT NULL DEFAULT '',
  `blurb` text NOT NULL DEFAULT '',
  `enabled` tinyint(4) NOT NULL DEFAULT 0,
  `deleted` tinyint(4) NOT NULL DEFAULT 0,
  `created` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (`mandatorid`,`nlarticleid`),
  KEY `created` (`created`),
  KEY `mandatorid` (`mandatorid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='newsletter articles';

CREATE TABLE `wbnewsletterissuearticle` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlisid` int(11) unsigned NOT NULL,
  `nlarticleid` int(11) unsigned NOT NULL,
  KEY `nlisid` (`nlisid`),
  KEY `mandatorid` (`mandatorid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='newsletter issue article ids';

CREATE TABLE `wbnewslettertopic` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nltid` int(11) unsigned NOT NULL DEFAULT 0,
  `title` varchar(64) NOT NULL DEFAULT "",
  `brief` text NOT NULL DEFAULT "",
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `changed` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (`mandatorid`, `nltid`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='newsletter topics';

INSERT INTO wbnewslettertopic (mandatorid, nltid, title, brief)
  VALUES (0, 1, 'Allgemeines', 'Allgemeneine Themen');
INSERT INTO wbnewslettertopic (mandatorid, nltid, title, brief)
  SELECT mandatorid, 1, 'Allgemeines', 'Allgemeneine Themen' FROM wbmandator;

CREATE TABLE `wbnewslettersubscribertopic` (
  `mandatorid` int(11) unsigned NOT NULL,
  `nltid` int(11) unsigned NOT NULL,
  `nlsid` int(11) unsigned NOT NULL,
  KEY (`mandatorid`),
  KEY `nltid` (`mandatorid`, `nltid`),
  KEY `nlsid` (`nlsid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='newsletter subscriber interested in topic';

CREATE TABLE `wbnewsletterissuetopic` (
  `mandatorid` int(11) unsigned NOT NULL DEFAULT 0,
  `nlisid` int(11) unsigned NOT NULL,
  `nltid` int(11) unsigned NOT NULL,
  KEY `nlisid` (`nlisid`),
  KEY `nltid` (`nltid`),
  KEY `mandatorid` (`mandatorid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="newsletter issue's topics";
