CREATE TABLE wbnewslettersubscribersent (
  `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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 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 '',
  `gender` enum('unknown','male','female') NOT NULL DEFAULT 'unknown',
  `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=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='newsletter subscriber';

CREATE TABLE `wbnewslettergenericsource` (
  `nlgsid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `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` (`nlgsid`)
) ENGINE=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='newsletter generic subscriber sources';

CREATE TABLE `wbnewslettersubscribersource` (
  `nlsid` int(11) unsigned NOT NULL,
  `namespace` varchar(32) NOT NULL,
  `xid` int(11) unsigned NOT NULL,
  KEY `nlsid` (`nlsid`),
  KEY `namespace` (`namespace`),
  KEY `namespacexid` (`namespace`,`xid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='newsletter subscriber sources';

CREATE TABLE `wbnewsletterissue` (
  `nlisid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `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 (`nlisid`),
  KEY (`created`)
)  ENGINE=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='newsletter issue';

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

CREATE TABLE `wbnewsletterarticle` (
  `nlarticleid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `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 (`nlarticleid`),
  KEY (`created`)
)  ENGINE=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='newsletter articles';

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

CREATE TABLE `wbnewslettertopic` (
  `nltid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `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 `nltid` (`nltid`)
) ENGINE=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COMMENT='newsletter topics';

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

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