<?php


/**
 * Schema for mysql databases.
 *
 * @version $Id: schema.mysql.php,v 1.53 2008/02/06 06:54:54 chris Exp $
 * @author Chris <chris@interspire.com>
 * @author Imran Khan <imran.khan@interspire.com>
 *
 * @package SendStudio
 * @subpackage Language
 */

/**
 * Schema for mysql databases.
 * DO NOT CHANGE BELOW THIS LINE.
 */

$queries = [];

$queries[] = "CREATE TABLE %%TABLEPREFIX%%usergroups (
   groupid INT NOT NULL AUTO_INCREMENT,
   groupname VARCHAR(255) NOT NULL,
   createdate INT NOT NULL,

   limit_list INT DEFAULT 0,
   limit_hourlyemailsrate INT DEFAULT 0,
   limit_emailspermonth INT DEFAULT 0,
   limit_totalemailslimit INT DEFAULT NULL,

   forcedoubleoptin CHAR(1) DEFAULT '0',
   forcespamcheck CHAR(1) DEFAULT '0',

   systemadmin CHAR(1) DEFAULT '0',
   listadmin CHAR(1) DEFAULT '0',
   segmentadmin CHAR(1) DEFAULT '0',
   templateadmin CHAR(1) DEFAULT '0',

   PRIMARY KEY (groupid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%usergroups_permissions (
   groupid INT NOT NULL,
   area VARCHAR(255) NOT NULL,
   subarea VARCHAR(255) DEFAULT NULL,

   FOREIGN KEY (groupid) REFERENCES %%TABLEPREFIX%%usergroups(groupid) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%usergroups_access (
   groupid INT NOT NULL,
   resourcetype VARCHAR(100) NOT NULL,
   resourceid INT NOT NULL,

   FOREIGN KEY (groupid) REFERENCES %%TABLEPREFIX%%usergroups(groupid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%users (
   userid INT AUTO_INCREMENT NOT NULL,
   groupid INT NOT NULL,
   trialuser CHAR(1) DEFAULT '0',
   username VARCHAR(255) NOT NULL,
   unique_token VARCHAR(128) DEFAULT NULL,
   password VARCHAR(255) DEFAULT NULL,
   admintype CHAR(1) DEFAULT NULL,
   listadmintype CHAR(1) DEFAULT NULL,
   templateadmintype CHAR(1) DEFAULT NULL,
   segmentadmintype CHAR(1) DEFAULT NULL,
   status CHAR(1) DEFAULT '0',
   fullname VARCHAR(255) DEFAULT NULL,
   emailaddress VARCHAR(100) DEFAULT NULL,
   settings TEXT DEFAULT NULL,
   editownsettings CHAR(1) DEFAULT '0',
   usertimezone VARCHAR(10) DEFAULT NULL,
   textfooter TEXT DEFAULT NULL,
   htmlfooter TEXT DEFAULT NULL,
   infotips CHAR(1) DEFAULT '1',
   smtpserver VARCHAR(255) DEFAULT NULL,
   smtpusername VARCHAR(255) DEFAULT NULL,
   smtppassword VARCHAR(255) DEFAULT NULL,
   smtpport INT DEFAULT 0,
   createdate INT DEFAULT 0,
   lastloggedin INT DEFAULT 0,
   forgotpasscode CHAR(32) DEFAULT '',
   usewysiwyg CHAR(1) DEFAULT '1',
   enableactivitylog CHAR(1) DEFAULT '0',
   enableunlayer CHAR(1) DEFAULT '0',
   xmlapi CHAR(1) DEFAULT '0',
   xmltoken CHAR(40) DEFAULT NULL,
   gettingstarted INT NOT NULL DEFAULT 0,
   user_language VARCHAR(25) NOT NULL DEFAULT 'default',
   eventactivitytype LONGTEXT DEFAULT NULL,
   credit_warning_time INT DEFAULT NULL, -- The last time credit_warning was sent out to this user
   credit_warning_percentage INT DEFAULT NULL, -- At which percentage
   credit_warning_fixed INT DEFAULT NULL, -- or at which credit level
   adminnotify_email VARCHAR(100) DEFAULT NULL,
   adminnotify_send_flag CHAR(1) DEFAULT '0',
   adminnotify_send_threshold INT DEFAULT NULL,
   adminnotify_send_emailtext TEXT DEFAULT NULL,
   adminnotify_import_flag CHAR(1) DEFAULT '0',
   adminnotify_import_threshold INT DEFAULT NULL,
   adminnotify_import_emailtext TEXT DEFAULT NULL,
   otp VARCHAR(100) DEFAULT NULL,
   linkclick_bot_duration INT(5) DEFAULT 0,
   mfa_retry TINYINT(1) DEFAULT NULL,
   mfa_secret VARCHAR(255) DEFAULT NULL,

   PRIMARY KEY (userid),
   FOREIGN KEY (groupid) REFERENCES %%TABLEPREFIX%%usergroups(groupid) ON DELETE RESTRICT
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

//Table for Google oauth tempdata
$queries[] = "CREATE TABLE %%TABLEPREFIX%%refresh_token (
   id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   refresh_token varchar(255) default NULL,
   client_id varchar(255) default NULL,
   client_secret varchar(255) default NULL,
   google_oauth_bounce_email varchar(100) default NULL 
    
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%lists (
   listid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(255) default NULL,
   ownername varchar(255) default NULL,
   owneremail varchar(100) default NULL,
   bounceemail varchar(100) default NULL,
   replytoemail varchar(100) default NULL,
   surveyid INT(11) default NULL,
   bounceserver varchar(100) default NULL,
   bounceusername varchar(100) default NULL,
   bouncepassword varchar(100) default NULL,
   extramailsettings varchar(100) default NULL,
   companyname varchar(255) default NULL,
   companyaddress varchar(255) default NULL,
   companyphone varchar(20) default NULL,
   
   refresh_token varchar(255) default NULL,
   refresh_token_status TINYINT(1) default NULL,
   google_oauth_bounce_email varchar(255) default NULL,
   client_secret varchar(255) default NULL,
   client_id varchar(255) default NULL,
   
   format char(1) default NULL,
   notifyowner char(1) default 0,
   imapaccount char(1) default 0,
   createdate int(11) default 0,
   subscribecount int(11) default '0',
   unsubscribecount int(11) default '0',
   bouncecount int(11) default '0',
   processbounce char(1) default '0',
   agreedelete char(1) default '0',
   agreedeleteall char(1) default '0',
   visiblefields text not null,
   ownerid int(11) default 0 references %%TABLEPREFIX%%users(userid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%customfields (
   fieldid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(255) default NULL,
   fieldtype varchar(100) default NULL,
   defaultvalue varchar(255) default NULL,
   required char(1) default 0,
   fieldsettings mediumtext,
   createdate int(11) default 0,
   ownerid int(11) default 0 references %%TABLEPREFIX%%users(userid),
   isglobal char(1) default '0'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%forms (
   formid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(255) default NULL,
   design varchar(255) default NULL,
   formhtml longtext,
   chooseformat varchar(2) default NULL,
   changeformat varchar(1) default 0,
   sendthanks varchar(1) default 0,
   requireconfirm varchar(1) default 0,
   ownerid int(11) default 0 references %%TABLEPREFIX%%users(userid),
   formtype char(1) default NULL,
   createdate int(11) default 0,
   contactform varchar(1) default 0,
   usecaptcha varchar(1) default 0
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%jobs (
   jobid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   jobtype varchar(255) default NULL,
   jobstatus char(1) default NULL,
   jobtime int(11) default 0,
   jobdetails text,
   fkid int(11) default '0',
   lastupdatetime int(11) default '0',
   fktype varchar(255) default NULL,
   queueid int(11) default '0',
   ownerid int(11) default 0,
   approved int default 0,
   authorisedtosend int default 0,
   resendcount int default 0
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%autoresponders (
   autoresponderid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(255) default NULL,
   subject varchar(255) default NULL,
   format char(1) default NULL,
   textbody longtext,
   htmlbody longtext,
   jsonbody longtext default NULL,
   createdate int(11) default 0,
   active int default 0,
   pause int default 0,
   editor TINYINT(1) default 1,
   hoursaftersubscription int(11) default 0,
   ownerid int(11) NOT NULL references %%TABLEPREFIX%%users(userid),
   searchcriteria mediumtext,
   listid int(11) default 0,
   tracklinks char(1) default 1,
   trackopens char(1) default 1,
   multipart char(1) default 1,
   queueid int(11) default 0,
   sendfromname varchar(255) default NULL,
   sendfromemail varchar(255) default NULL,
   replytoemail varchar(255) default NULL,
   bounceemail varchar(255) default NULL,
   charset varchar(255) default NULL,
   embedimages char(1) default '0',
   to_firstname int default 0 ,
   to_lastname int default 0 ,
   autorespondersize int default 0,

   FOREIGN KEY (listid) REFERENCES %%TABLEPREFIX%%lists(listid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%banned_emails (
   banid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   emailaddress varchar(255) default NULL,
   list varchar(10) default NULL,
   bandate int(11) default NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%customfield_lists (
   cflid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   fieldid int(11) NOT NULL default '0',
   listid int(11) NOT NULL default '0',
   FOREIGN KEY (fieldid) REFERENCES  %%TABLEPREFIX%%customfields(fieldid) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY (listid) REFERENCES %%TABLEPREFIX%%lists(listid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";



// field id cannot FK to %%TABLEPREFIX%%customfields(fieldid) cuz we store
// references to 'e'
// 'cl' choose list
// 'cf' choose format 
// in this table as well
$queries[] = "CREATE TABLE %%TABLEPREFIX%%form_customfields (
   formid int(11) default 0,
   fieldid varchar(11) DEFAULT '1',
   fieldorder int default 0,

   FOREIGN KEY (formid) REFERENCES %%TABLEPREFIX%%forms (formid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%form_lists (
	formid int(11) default 0 ,
	listid int(11) default 0,

	FOREIGN KEY (formid) REFERENCES %%TABLEPREFIX%%forms (formid) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (listid) REFERENCES %%TABLEPREFIX%%lists (listid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%form_pages (
   pageid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   formid int(11) default 0 ,
   pagetype varchar(100) default NULL,
   html longtext,
   url varchar(2048) default NULL,
   sendfromname varchar(255) default NULL,
   sendfromemail varchar(255) default NULL,
   replytoemail varchar(255) default NULL,
   bounceemail varchar(255) default NULL,
   emailsubject varchar(255) default NULL,
   emailhtml longtext,
   emailtext longtext,
   json longtext default NULL,
   emailjson longtext default NULL,
   FOREIGN KEY (formid) REFERENCES %%TABLEPREFIX%%forms (formid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";



$queries[] = "CREATE TABLE %%TABLEPREFIX%%jobs_lists (
   jobid int(11) default 0,
   listid int(11) default 0,

   FOREIGN KEY (jobid) REFERENCES %%TABLEPREFIX%%jobs (jobid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%list_subscribers (
   subscriberid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   listid int(11) NOT NULL default '0',
   emailaddress varchar(200),
   domainname varchar(100),
   format char(1) default NULL,
   confirmed char(1) default 0,
   confirmcode varchar(32) default NULL,
   requestdate int(11) default '0',
   requestip varchar(45) default NULL,
   confirmdate int(11) default '0',
   confirmip varchar(45) default NULL,
   subscribedate int(11) default '0',
   bounced int(11) default '0',
   unsubscribed int(11) default '0',
   unsubscribeconfirmed char(1) default 0,
   unsubscribecode VARCHAR(35) default NULL,
   subscriberconfirmcode VARCHAR(37) default NULL,
   formid int(11) default '0',

   FOREIGN KEY (listid) REFERENCES %%TABLEPREFIX%%lists(listid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%list_subscribers_unsubscribe (
   subscriberid int(11) NOT NULL default '0',
   unsubscribetime int(11) NOT NULL default '0',
   unsubscribeip varchar(45) default NULL,
   unsubscriberequesttime int(11) default '0',
   unsubscriberequestip varchar(45) default NULL,
   listid int(11) NOT NULL default '0' references %%TABLEPREFIX%%lists(listid),
   statid int(11) default '0',
   unsubscribearea varchar(20) default NULL,

   FOREIGN KEY (subscriberid) REFERENCES %%TABLEPREFIX%%list_subscribers(subscriberid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%newsletters (
   newsletterid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(255) default NULL,
   format char(1) default NULL,
   subject varchar(255) default NULL,
   preheader varchar(150) default NULL,
   textbody longtext,
   htmlbody longtext,
   jsonbody longtext default NULL,
   createdate int(11) default 0,
   editor TINYINT(1) default 1,
   active int default 0 ,
   archive int default 0,
   ownerid int(11) NOT NULL default '0' references %%TABLEPREFIX%%users(userid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%queues (
   queueid int(11) default '0',
   queuetype varchar(255) default NULL,
   ownerid int(11) NOT NULL default '0' references %%TABLEPREFIX%%users(userid),
   recipient int(11) default 0 ,
   processed char(1) default '0',
   sent char(1) default '0',
   processtime datetime
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%queues_sequence (
   id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
$queries[] = "INSERT INTO %%TABLEPREFIX%%queues_sequence(id) VALUES (0)";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%settings (
   cronok char(1) default '0',
   cronrun1 int(11) default '0',
   cronrun2 int(11) default '0',
   database_version int default '0'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
$queries[] = "INSERT INTO %%TABLEPREFIX%%settings(cronok, cronrun1, cronrun2, database_version) VALUES (0, 0, 0, " . SENDSTUDIO_DATABASE_VERSION . ")";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%config_settings(area varchar(255), areavalue text) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%settings_cron_schedule (
   jobtype VARCHAR(20),
   lastrun INT default '0'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
$queries[] = "INSERT INTO %%TABLEPREFIX%%settings_cron_schedule (jobtype, lastrun) VALUES ('send', '-1')";
$queries[] = "INSERT INTO %%TABLEPREFIX%%settings_cron_schedule (jobtype, lastrun) VALUES ('bounce', '-1')";
$queries[] = "INSERT INTO %%TABLEPREFIX%%settings_cron_schedule (jobtype, lastrun) VALUES ('autoresponder', '-1')";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%list_subscriber_bounces (
   bounceid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   subscriberid int(11) default 0,
   statid int(11) default 0,
   listid int(11) default 0 references %%TABLEPREFIX%%lists(listid),
   bouncetime int(11) default 0,
   bouncetype varchar(255) default NULL,
   bouncerule varchar(255) default NULL,
   bouncemessage longtext,

   FOREIGN KEY (subscriberid) REFERENCES %%TABLEPREFIX%%list_subscribers(subscriberid) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%links (
   linkid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   url varchar(2048) default NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_autoresponders (
   statid int(11) NOT NULL default '0',
   htmlrecipients int(11) default '0',
   textrecipients int(11) default '0',
   multipartrecipients int(11) default '0',
   bouncecount_soft int(11) default '0',
   bouncecount_hard int(11) default '0',
   bouncecount_unknown int(11) default '0',
   unsubscribecount int(11) default '0',
   autoresponderid int(11) default '0',
   linkclicks int(11) default '0',
   emailopens int(11) default '0',
   emailforwards int(11) default '0',
   emailopens_unique int(11) default '0',
   htmlopens int default 0,
   htmlopens_unique int default 0,
   textopens int default 0,
   textopens_unique int default 0,
   hiddenby int default 0,

   PRIMARY KEY(statid),
   FOREIGN KEY (autoresponderid) REFERENCES %%TABLEPREFIX%%autoresponders (autoresponderid) ON  DELETE CASCADE ON UPDATE CASCADE 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_emailopens (
   openid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   subscriberid int(11) default 0,
   statid int(11) default 0,
   opentime int(11) default 0,
   openip varchar(45) default NULL,
   fromlink CHAR(1) DEFAULT 0,
   opentype char(1) DEFAULT 'u'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_linkclicks (
   clickid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   clicktime int(11) default 0,
   clickip varchar(45) default NULL,
   subscriberid int(11) default 0,
   statid int(11) default 0,
   bot TINYINT(1) DEFAULT 0,
   linkid int(11) default 0
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_links (
   statid int(11) default 0,
   linkid int(11) default 0 references %%TABLEPREFIX%%links(linkid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

  // FOREIGN KEY (newsletterid)  not used because making issue in trigger    
  $queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_newsletters (
   statid int(11) NOT NULL default '0',
   queueid int(11) default 0,
   jobid int(11) default 0,
   starttime int(11) default 0,
   finishtime int(11) default 0,
   htmlrecipients int(11) default '0',
   textrecipients int(11) default '0',
   multipartrecipients int(11) default '0',
   trackopens char(1) default '0',
   tracklinks char(1) default '0',
   bouncecount_soft int(11) default '0',
   bouncecount_hard int(11) default '0',
   bouncecount_unknown int(11) default '0',
   unsubscribecount int(11) default '0',
   newsletterid int(11) default 0 ,
   sendfromname varchar(200) default NULL,
   sendfromemail varchar(200) default NULL,
   bounceemail varchar(200) default NULL,
   email_extra_header VARCHAR(2048) DEFAULT NULL,
   replytoemail varchar(200) default NULL,
   charset varchar(200) default NULL,
   sendinformation mediumtext,
   sendsize int(11) default 0,
   sentby int(11) default 0 ,
   notifyowner char(1) default 0,
   linkclicks int(11) default '0',
   emailopens int(11) default '0',
   emailforwards int(11) default '0',
   emailopens_unique int(11) default '0',
   htmlopens int default 0,
   htmlopens_unique int default 0,
   textopens int default 0,
   textopens_unique int default 0,
   hiddenby int(11) default 0 ,
   sendtestmode int default 0,
   sendtype varchar(100) default 'newsletter',
   PRIMARY KEY (statid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

 //Delete by query because some issues in trigger with statid and listid
$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_newsletter_lists (
   statid int(11) default NULL,
   listid int(11) default 0 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_sequence (
   id int(11) NOT NULL auto_increment,
   PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
$queries[] = "INSERT INTO %%TABLEPREFIX%%stats_sequence(id) VALUES (0)";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_users (
   userid int(11) default 0 references %%TABLEPREFIX%%users(userid),
   statid int(11) default 0,
   jobid int(11) default 0,
   queuesize int(11) default 0,
   queuetime int(11) default 0,

   FOREIGN KEY (jobid) REFERENCES %%TABLEPREFIX%%jobs (jobid) ON DELETE CASCADE ON UPDATE CASCADE
 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%subscribers_data (
   subscriberid int(11) NOT NULL default '0',
   fieldid int(11) NOT NULL default '0' references %%TABLEPREFIX%%customfields(fieldid),
   data text
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%templates (
   templateid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name varchar(255) default NULL,
   format char(1) default NULL,
   textbody longtext,
   htmlbody longtext,
   jsonbody longtext default NULL,
   createdate int(11) default 0,
   editor TINYINT(1) default 1,
   active int default 0,
   isglobal int default 0,
   ownerid int(11) NOT NULL default '0' references %%TABLEPREFIX%%users(userid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%editors (
   id varchar(30) NOT NULL PRIMARY KEY,
   name varchar(50) NOT NULL,
   description varchar(100) NOT NULL,
   status tinyint(1) NOT NULL DEFAULT 1,
   `default` tinyint(1) NOT NULL DEFAULT 0
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "INSERT INTO %%TABLEPREFIX%%usergroups(
   groupname, createdate, systemadmin
   ) VALUES (
   'System Admin',
   UNIX_TIMESTAMP(NOW()), '1'
   )
";

$queries[] = "INSERT INTO %%TABLEPREFIX%%users(
   groupid, username, password, status,
   admintype, listadmintype, templateadmintype,
   createdate, lastloggedin,
   infotips, usewysiwyg, gettingstarted
   ) VALUES (
   1, 'admin', md5('password'), '1',
   'a', 'a', 'a',
   UNIX_TIMESTAMP(NOW()), 0,
   '1', '3', '0'
  )
";
$queries[] = "INSERT INTO %%TABLEPREFIX%%editors (id, name, description, status, `default`) VALUES
('1', 'WYSIWYG', 'TinyMCE Editor', 1, 0),
('2', 'Unlayer', 'Use the Unlayer Editor', 1, 0),
('3', 'BuilderJS', 'The BuilderJS Editor', 1, 1),
('4', 'TextArea', 'Use the TextArea', 1, 0)
";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_emailforwards (
   forwardid INT AUTO_INCREMENT PRIMARY KEY,
   forwardtime INT default 0,
   forwardip varchar(45),
   subscriberid INT(11),
   statid INT default 0,
   subscribed INT default 0,
   listid INT(11),
   emailaddress VARCHAR(255)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%user_stats_emailsperhour (
   summaryid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   statid INT DEFAULT '0',
   sendtime INT DEFAULT '0',
   emailssent INT DEFAULT '0',
   userid INT DEFAULT '0' references %%TABLEPREFIX%%users(userid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%queues_unsent (
   recipient int default 0 references %%TABLEPREFIX%%list_subscribers(subscriberid) ON DELETE CASCADE,
   queueid int default 0,
   reasoncode int default 0,
   reason text
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%modules (
   modulename VARCHAR(50) NOT NULL,
   moduleversion INT DEFAULT 0,
   PRIMARY KEY(modulename)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%log_system_administrator (
   logid int not null primary key auto_increment,
   loguserid int NOT NULL,
   logip varchar(45) NOT NULL default '',
   logdate int NOT NULL default '0',
   logtodo varchar(100) NOT NULL default '',
   logdata text NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%log_system_system (
   logid int not null primary key auto_increment,
   logtype varchar(20),
   logmodule varchar(100) NOT NULL default '',
   logseverity char(1) NOT NULL default '4',
   logsummary varchar(250) NOT NULL,
   logmsg text NOT NULL,
   logdate int NOT NULL default '0'
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_autoresponders_recipients (
   statid INT DEFAULT 0,
   autoresponderid INT DEFAULT 0,
   send_status CHAR(1),
   recipient INT DEFAULT 0,
   reason VARCHAR(20),
   sendtime INT
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%segments (
   segmentid INT AUTO_INCREMENT NOT NULL,
   segmentname VARCHAR(255) NOT NULL,
   createdate INT(11) DEFAULT 0,
   ownerid INT(11) NOT NULL,
   searchinfo TEXT NOT NULL,

   PRIMARY KEY (segmentid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%list_subscriber_events (
   eventid int(11) NOT NULL auto_increment,
   subscriberid int(11) NOT NULL,
   listid int(11) NOT NULL,
   eventtype text NOT NULL,
   eventsubject text NOT NULL,
   eventdate int(11) NOT NULL,
   lastupdate int(11) NOT NULL,
   eventownerid int(11) NOT NULL,
   eventnotes text NOT NULL,

   PRIMARY KEY (eventid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "create table %%TABLEPREFIX%%addons (
   addon_id varchar(200) not null primary key,
   installed int default 0,
   configured int default 0,
   enabled int default 0,
   addon_version VARCHAR(10) default '0',
   settings text
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%folders (
   folderid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255),
   type CHAR(1),
   createdate INT(11) DEFAULT 0,
   ownerid INT(11)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%folder_item (
   folderid INT(11) NOT NULL REFERENCES %%TABLEPREFIX%%folders(folderid) ON DELETE CASCADE,
   itemid INT(11) NOT NULL,
   PRIMARY KEY (folderid, itemid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%folder_user (
   folderid INT(11) NOT NULL REFERENCES %%TABLEPREFIX%%folders(folderid) ON DELETE CASCADE,
   userid INT(11) NOT NULL REFERENCES %%TABLEPREFIX%%users(userid) ON DELETE CASCADE,
   expanded CHAR(1) NOT NULL DEFAULT '1',
   ordering INT(11),

   PRIMARY KEY (folderid, userid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%triggeremails (
   triggeremailsid INT AUTO_INCREMENT NOT NULL,
   active CHAR(1) NOT NULL DEFAULT '0',
   createdate INT NOT NULL,
   ownerid INT NOT NULL,
   name VARCHAR(100) NOT NULL,
   triggertype CHAR(1) NOT NULL,

   triggerhours INT DEFAULT 0,
   triggerinterval INT DEFAULT 0,

   queueid INT NOT NULL,
   statid INT NOT NULL,

   PRIMARY KEY (triggeremailsid),
   UNIQUE (queueid),
   UNIQUE (statid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%triggeremails_data (
   triggeremailsid INT NOT NULL,
   datakey VARCHAR(25) NOT NULL,
   datavaluestring VARCHAR(255) DEFAULT NULL,
   datavalueinteger INT DEFAULT NULL,

   FOREIGN KEY (triggeremailsid) REFERENCES %%TABLEPREFIX%%triggeremails (triggeremailsid) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%triggeremails_actions (
   triggeremailsactionid INT AUTO_INCREMENT NOT NULL,
   triggeremailsid INT NOT NULL,
   action VARCHAR(25) NOT NULL,

   PRIMARY KEY (triggeremailsactionid),
   FOREIGN KEY (triggeremailsid) REFERENCES %%TABLEPREFIX%%triggeremails (triggeremailsid) ON DELETE CASCADE,

   UNIQUE (triggeremailsid, action)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%triggeremails_actions_data (
   triggeremailsactionid INT NOT NULL,
   datakey VARCHAR(25) NOT NULL,
   datavaluestring VARCHAR(255) DEFAULT NULL,
   datavalueinteger INT DEFAULT NULL,
   triggeremailsid INT NOT NULL,

   FOREIGN KEY (triggeremailsactionid) REFERENCES %%TABLEPREFIX%%triggeremails_actions (triggeremailsactionid) ON DELETE CASCADE,
   FOREIGN KEY (triggeremailsid) REFERENCES %%TABLEPREFIX%%triggeremails (triggeremailsid) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%triggeremails_log (
   triggeremailsid INT NOT NULL,
   subscriberid INT NOT NULL,
   action VARCHAR(25) NOT NULL,
   timestamp INT NOT NULL,
   note VARCHAR(255) DEFAULT NULL,

   FOREIGN KEY (triggeremailsid) REFERENCES %%TABLEPREFIX%%triggeremails (triggeremailsid) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
 
$queries[] = "CREATE TABLE %%TABLEPREFIX%%triggeremails_log_summary (
   triggeremailsid INT NOT NULL,
   subscriberid INT NOT NULL,
   actionedoncount INT DEFAULT 0,
   lastactiontimestamp INT DEFAULT NULL,

   PRIMARY KEY (triggeremailsid, subscriberid),
   FOREIGN KEY (triggeremailsid) REFERENCES %%TABLEPREFIX%%triggeremails (triggeremailsid) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%user_activitylog (
	lastviewid INT AUTO_INCREMENT NOT NULL,
	userid INT NOT NULL,
	icon VARCHAR(255) DEFAULT NULL,
	text VARCHAR(255) DEFAULT NULL,
	url VARCHAR(255) NOT NULL,
	viewed INT NOT NULL,

	PRIMARY KEY (lastviewid),
	FOREIGN KEY (userid) REFERENCES %%TABLEPREFIX%%users (userid) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%user_credit (
   usercreditid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
   userid INT NOT NULL,
   transactiontype VARCHAR(25) NOT NULL,
   transactiontime INT UNSIGNED NOT NULL,
   credit BIGINT NOT NULL,
   jobid INT DEFAULT NULL,
   statid INT DEFAULT NULL,
   expiry INT DEFAULT NULL,

   PRIMARY KEY (usercreditid),
   FOREIGN KEY (userid) REFERENCES %%TABLEPREFIX%%users (userid) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%user_credit_summary (
   usagesummaryid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
   userid INT NOT NULL,
   startperiod INT NOT NULL,
   credit_used INT NOT NULL DEFAULT 0,
   PRIMARY KEY (usagesummaryid),
   FOREIGN KEY (userid) REFERENCES %%TABLEPREFIX%%users (userid) ON DELETE CASCADE,
   UNIQUE KEY (userid, startperiod)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%settings_credit_warnings (
   creditwarningid INT NOT NULL AUTO_INCREMENT,
   enabled CHAR(1) NOT NULL DEFAULT '0',
   creditlevel INT NOT NULL DEFAULT 0,
   aspercentage CHAR(1) NOT NULL DEFAULT '1',
   emailsubject VARCHAR(255) NOT NULL,
   emailcontents MEDIUMTEXT NOT NULL,
   PRIMARY KEY (creditwarningid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%login_attempt (
   timestamp INTEGER NOT NULL,
   ipaddress VARCHAR(45) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%login_banned_ip (
   ipaddress VARCHAR(45) NOT NULL,
   bantime INTEGER NOT NULL,
   PRIMARY KEY(ipaddress)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%whitelabel_settings (
   name VARCHAR(100) NOT NULL,
   value TEXT,
   PRIMARY KEY (name)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%webhooks (
   webhookid int(11) NOT NULL AUTO_INCREMENT,
   webhook_type_id int(11) NOT NULL,
   webhook_event_type_id int(11) NOT NULL,
   id int(11) DEFAULT 0 COMMENT '(listid or newsletterid)',
   webhook_url varchar(2048) NOT NULL,
   createdate int(11) NOT NULL,
   active int(1) NOT NULL DEFAULT 1,
   ownerid int(11) NOT NULL DEFAULT 0,
   PRIMARY KEY (webhookid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%webhook_types (
   webhook_type_id 		int(11) 		NOT NULL,
   webhook_type_name 	varchar(100) 	NOT NULL,
   PRIMARY KEY (webhook_type_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "INSERT INTO %%TABLEPREFIX%%webhook_types (webhook_type_id, webhook_type_name) VALUES 
   (1, 'List'), 
   (2, 'Campaign')";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%webhook_event_types (
   event_type_id int(11)	NOT NULL,
   event_type_name varchar(100) NOT NULL,
   PRIMARY KEY (event_type_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "INSERT INTO %%TABLEPREFIX%%webhook_event_types (event_type_id, event_type_name) VALUES
   (1, 'Subscribe'),
   (2, 'Unsubscribe'),
   (3, 'Bounce'),
   (4, 'ScheduleCampaign'),
   (5, 'CampaignSent')";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%webhook_logs (
   logid int(11) NOT NULL AUTO_INCREMENT,
   webhook_type_id int(11) NOT NULL,
   webhook_event_type_id int(11) NOT NULL,
   logmsg text NOT NULL,
   webhook_response VARCHAR(255) NULL,
   logdate int(11) DEFAULT 0,

   PRIMARY KEY (logid)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

$queries[] = "CREATE TABLE %%TABLEPREFIX%%stats_newsletters_recipients (
   id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	statid int(11) DEFAULT '0',
	newsletterid int(11) DEFAULT '0',
	recipient int(11) DEFAULT '0',
	send_status char(1) DEFAULT NULL,
	reason varchar(20) DEFAULT NULL,
	sendtime int(11) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";


require(dirname(__FILE__) . '/schema.indexes.php');
