
    --
    -- Table structure for table `[db_prefix]handlers`
    --

    CREATE TABLE IF NOT EXISTS `[db_prefix]handlers` (
      `id` int(11) NOT NULL auto_increment,
      `alert_unit` varchar(64) NOT NULL default '',
      `alert_action` varchar(64) NOT NULL default '',
      `module_uri` varchar(64) NOT NULL default '',
      `module_class` varchar(64) NOT NULL default '',
      `module_method` varchar(64) NOT NULL default '',
      PRIMARY KEY  (`id`),
      UNIQUE `handler` (`alert_unit`, `alert_action`, `module_uri`, `module_class`, `module_method`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


    --
    -- Dumping data for tables `sys_alerts_handlers` and `sys_alerts`
    --
    INSERT INTO `sys_alerts_handlers` (`name`, `class`, `file`, `eval`) VALUES
	('bx_pommo_profiles_activity', '', '', 'BxDolService::call(\'pommo\', \'response_profiles\', array($this));');

    SET @iLastHandler = (SELECT `id` FROM `sys_alerts_handlers` WHERE `name`='bx_pommo_profiles_activity' LIMIT 1);
	INSERT INTO `sys_alerts` (`unit`, `action`, `handler_id`) VALUES
    ('profile', 'join', @iLastHandler),
    ('profile', 'edit', @iLastHandler),
    ('profile', 'delete', @iLastHandler);



    --
    -- Admin menu ;
    --
    SET @iOrder = (SELECT MAX(`order`) FROM `sys_menu_admin` WHERE `parent_id`='2');
    INSERT INTO 
        `sys_menu_admin` 
    SET
        `name`          = 'Pommo',
        `title`         = 'Pommo Mass Mailer', 
        `url`           = '{siteUrl}modules/mscott/pommo/mailer/',
        `description`   = 'Pommo settings',
        `icon`          = 'envelope',
        `parent_id`     = 2,
        `order`         = @iOrder+1;



-- POMMO STUFF!!!
SELECT @memail := `VALUE` FROM `sys_options` WHERE `Name` = 'site_email';
SELECT @msitename := `VALUE` FROM `sys_options` WHERE `Name` = 'site_title';
SELECT @adminuser := `NickName` FROM `Profiles` WHERE `Role` = '3' LIMIT 1;
SELECT @adminpass := `Password` FROM `Profiles` WHERE `Role` = '3' LIMIT 1;

CREATE TABLE IF NOT EXISTS `pommo_config` (
  `config_name` varchar(64) NOT NULL default '',
  `config_value` text NOT NULL,
  `config_description` tinytext NOT NULL,
  `autoload` enum('on','off') NOT NULL default 'on',
  `user_change` enum('on','off') NOT NULL default 'on',
  PRIMARY KEY  (`config_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `pommo_config` VALUES('admin_username', @adminuser, 'Username', 'off', 'off');
INSERT INTO `pommo_config` VALUES('admin_password', @adminpass , 'Password', 'off', 'off');
INSERT INTO `pommo_config` VALUES('admin_email', @memail , 'Administrator Email', 'on', 'on');
INSERT INTO `pommo_config` VALUES('site_name', @msitename , 'Website Name', 'on', 'on');
INSERT INTO `pommo_config` VALUES('site_url', '' , 'Website URL', 'on', 'on');
INSERT INTO `pommo_config` VALUES('site_success', '', 'Signup Success URL', 'off', 'off');
INSERT INTO `pommo_config` VALUES('site_confirm', '', '', 'off', 'off');
INSERT INTO `pommo_config` VALUES('list_name', @msitename, 'List Name', 'on', 'on');
INSERT INTO `pommo_config` VALUES('list_fromname', 'The Webmaster', 'From Name', 'on', 'on');
INSERT INTO `pommo_config` VALUES('list_fromemail', @memail , 'From Email', 'on', 'on');
INSERT INTO `pommo_config` VALUES('list_frombounce', @memail , 'Bounces', 'off', 'on');
INSERT INTO `pommo_config` VALUES('list_exchanger', 'sendmail', 'List Exchanger', 'off', 'on');
INSERT INTO `pommo_config` VALUES('list_confirm', 'off', 'Confirmation Messages', 'off', 'off');
INSERT INTO `pommo_config` VALUES('list_charset', 'UTF-8', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('list_wysiwyg', 'on', '', 'on', 'on');
INSERT INTO `pommo_config` VALUES('maxRuntime', '80', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('messages', '', '','off', 'off');
INSERT INTO `pommo_config` VALUES('notices', '', '', 'off', 'off');
INSERT INTO `pommo_config` VALUES('demo_mode', 'off', 'Demonstration Mode', 'on', 'on');
INSERT INTO `pommo_config` VALUES('smtp_1', '', '', 'off', 'off');
INSERT INTO `pommo_config` VALUES('smtp_2', '', '', 'off', 'off');
INSERT INTO `pommo_config` VALUES('smtp_3', '', '', 'off', 'off');
INSERT INTO `pommo_config` VALUES('smtp_4', '', '', 'off', 'off');
INSERT INTO `pommo_config` VALUES('throttle_DBPP', '0', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('throttle_DP', '10', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('throttle_DMPP', '0', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('throttle_BPS', '0', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('throttle_MPS', '3', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('throttle_SMTP', 'individual', '', 'off', 'on');
INSERT INTO `pommo_config` VALUES('public_history', 'off', 'Public Mailing History', 'off', 'on');
INSERT INTO `pommo_config` VALUES('version', 'Aardvark PR16', 'poMMo Version', 'on', 'off');
INSERT INTO `pommo_config` VALUES('key', '529736', 'Unique Identifier', 'on', 'off');
INSERT INTO `pommo_config` VALUES('revision', '41', 'Internal Revision', 'on', 'off');



CREATE TABLE IF NOT EXISTS `pommo_fields` (
  `field_id` smallint(5) unsigned NOT NULL auto_increment,
  `field_active` enum('on','off') NOT NULL default 'off',
  `field_ordering` smallint(5) unsigned NOT NULL default '0',
  `field_name` varchar(60) default NULL,
  `field_prompt` varchar(60) default NULL,
  `field_normally` varchar(60) default NULL,
  `field_array` text,
  `field_required` enum('on','off') NOT NULL default 'off',
  `field_type` enum('checkbox','multiple','text','date','number','comment') default NULL,
  PRIMARY KEY  (`field_id`),
  KEY `active` (`field_active`,`field_ordering`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `pommo_groups` (
  `group_id` smallint(5) unsigned NOT NULL auto_increment,
  `group_name` tinytext NOT NULL,
  PRIMARY KEY  (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `pommo_group_rules` (
  `rule_id` int(10) unsigned NOT NULL auto_increment,
  `group_id` int(10) unsigned NOT NULL default '0',
  `field_id` tinyint(3) unsigned NOT NULL default '0',
  `type` tinyint(1) NOT NULL default '0' COMMENT '0: OFF, (and), 1: ON (or)',
  `logic` enum('is','not','greater','less','true','false','is_in','not_in') NOT NULL default 'is',
  `value` text,
  PRIMARY KEY  (`rule_id`),
  KEY `group_id` (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS  `pommo_mailings` (
  `mailing_id` int(10) unsigned NOT NULL auto_increment,
  `fromname` varchar(60) NOT NULL default '',
  `fromemail` varchar(60) NOT NULL default '',
  `frombounce` varchar(60) NOT NULL default '',
  `subject` varchar(60) NOT NULL default '',
  `body` mediumtext NOT NULL,
  `altbody` mediumtext,
  `ishtml` enum('on','off') NOT NULL default 'off',
  `mailgroup` varchar(60) NOT NULL default 'Unknown',
  `subscriberCount` int(10) unsigned NOT NULL default '0',
  `started` datetime NOT NULL default '0000-00-00 00:00:00',
  `finished` datetime default NULL,
  `sent` int(10) unsigned NOT NULL default '0',
  `charset` varchar(15) NOT NULL default 'UTF-8',
  `status` tinyint(1) NOT NULL default '1' COMMENT '0: finished, 1: processing, 2: cancelled',
  PRIMARY KEY  (`mailing_id`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS  `pommo_mailing_current` (
  `current_id` int(10) unsigned NOT NULL default '0',
  `command` enum('none','restart','stop','cancel') NOT NULL default 'none',
  `serial` int(10) unsigned default NULL,
  `securityCode` varchar(32) default NULL,
  `notices` longtext,
  `current_status` enum('started','stopped') NOT NULL default 'stopped',
  `touched` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`current_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `pommo_mailing_notices` (
  `mailing_id` int(10) unsigned NOT NULL default '0',
  `notice` varchar(255) NOT NULL default '',
  `touched` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `id` smallint(5) unsigned NOT NULL default '0',
  KEY `mailing_id` (`mailing_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS  `pommo_queue` (
  `subscriber_id` int(10) unsigned NOT NULL default '0',
  `status` tinyint(1) NOT NULL default '0' COMMENT '0: unsent, 1: sent, 2: failed',
  `smtp` tinyint(1) NOT NULL default '0' COMMENT '0: none, 1-4: Designated to SMTP relay #',
  PRIMARY KEY  (`subscriber_id`),
  KEY `status` (`status`,`smtp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS  `pommo_scratch` (
  `scratch_id` int(10) unsigned NOT NULL auto_increment,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `type` smallint(5) unsigned NOT NULL default '0' COMMENT 'Used to identify row type. 0 = undifined, 1 = ',
  `int` bigint(20) default NULL,
  `str` text,
  PRIMARY KEY  (`scratch_id`),
  KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='General Purpose Table for caches, counts, etc.' AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS  `pommo_templates` (
  `template_id` smallint(5) unsigned NOT NULL auto_increment,
  `name` varchar(60) NOT NULL default 'name',
  `description` varchar(255) default NULL,
  `body` mediumtext,
  `altbody` mediumtext,
  PRIMARY KEY  (`template_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2;

INSERT INTO `pommo_templates` VALUES (1,'CSS Example','poMMo default, featuring a plain CSS based HTML design.','<style type=\"text/css\" media=\"all\">\r\n\r\n/* base styling */\r\n\r\ndiv.pommoMailing {\r\n\r\n  background-color: white; /* background color */\r\n  color: #333; /* text color */\r\n  width: 100%;\r\n  padding: 6px;\r\n\r\n}\r\n\r\ndiv.pommoMailing a, div.pommoMailing a:visited {\r\n\r\n  text-decoration: none;\r\n  color: #0067ff; /* link & visited link color */\r\n\r\n}\r\n\r\n/* header styling */\r\n\r\ndiv.pommoMailing div.pommoHeader {\r\n  \r\n  border: 1px solid black;\r\n  padding: 6px;\r\n  background-color: #DDF0BD; /* light green */\r\n  color: green;\r\n  width: 100%;\r\n  text-align: center;\r\n}\r\n\r\n/* footer styling */\r\n\r\ndiv.pommoMailing div.pommoFooter {\r\n  width: 100%;\r\n  padding: 5px 12px;\r\n  background-color: black;\r\n  color: #DDD;\r\n}\r\n\r\ndiv.pommoMailing p.smaller {\r\n  font-size: 80%;\r\n}\r\n\r\n\r\n</style>\r\n\r\n<div class=\"pommoMailing\">\r\n\r\n  <div class=\"pommoHeader\">\r\n  </div>\r\n\r\n  <h2>Bonjour!</h2>\r\n  <br />\r\n \r\n  <p>\r\n  Aliquam tempor erat eu sapien. Proin nisl lorem, hendrerit ut, venenatis vel, consequat in, est. In hac habitasse platea dictumst. Praesent malesuada tristique massa. Donec nec dui. Nulla at ligula quis diam auctor vulputate. Sed ligula ligula, elementum ac, tincidunt nec, accumsan non, risus. Aliquam convallis blandit tortor. In porta nisi interdum ante. Maecenas sem. Maecenas at felis ac massa dictum malesuada. Maecenas quis lectus. In hac habitasse platea dictumst. Ut sit amet nunc. Donec non lacus. Nulla facilisi. Vestibulum molestie. Aenean at enim sit amet augue auctor bibendum. Duis in ipsum.\r\n  </p>\r\n\r\n  <div class=\"pommoFooter\">\r\n  </div>\r\n\r\n  <p class=\"smaller\">\r\n    Message sent with <a href=\"http://www.pommo.org/\">poMMo</a>.\r\n  </p>\r\n\r\n</div>',NULL);

CREATE TABLE IF NOT EXISTS  `pommo_subscribers` (
  `subscriber_id` int(10) unsigned NOT NULL auto_increment,
  `nickname` char(60) NOT NULL default '',
  `email` char(60) NOT NULL default '',
  `time_touched` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `time_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `flag` tinyint(1) NOT NULL default '0' COMMENT '0: NULL, 1-8: REMOVE, 9: UPDATE',
  `ip` int(10) unsigned default NULL COMMENT 'Stored with INET_ATON(), Fetched with INET_NTOA()',
  `status` tinyint(1) NOT NULL default '2' COMMENT '0: Inactive, 1: Active, 2: Pending',
  PRIMARY KEY  (`subscriber_id`),
  KEY `status` (`status`,`subscriber_id`),
  KEY `status_2` (`status`,`email`),
  KEY `status_3` (`status`,`time_touched`),
  KEY `status_4` (`status`,`time_registered`),
  KEY `status_5` (`status`,`ip`),
  KEY `flag` (`flag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `pommo_subscriber_data` (
  `data_id` bigint(20) unsigned NOT NULL auto_increment,
  `field_id` int(10) unsigned NOT NULL default '0',
  `subscriber_id` int(10) unsigned NOT NULL default '0',
  `value` char(60) NOT NULL default '',
  PRIMARY KEY  (`data_id`),
  KEY `subscriber_id` (`subscriber_id`,`field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS  `pommo_subscriber_pending` (
  `pending_id` int(10) unsigned NOT NULL auto_increment,
  `subscriber_id` int(10) unsigned NOT NULL default '0',
  `pending_code` varchar(32) NOT NULL default '',
  `pending_type` enum('add','del','change','password') default NULL,
  `pending_array` text,
  PRIMARY KEY  (`pending_id`),
  KEY `code` (`pending_code`),
  KEY `subscriber_id` (`subscriber_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS  `pommo_updates` (
  `serial` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`serial`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO pommo_subscribers( subscriber_id, nickname, email, time_touched, time_registered, flag, ip, status ) 
SELECT ID, NickName, Email, '', '', '', '' , EmailNotify FROM Profiles;