<?php 	

//Exit script if user is in the installation page (_install.php).
//This script only runs when they've installed Sendy
if(currentPage()=='_install.php') return;

//================= Version 5.2.4 ===============//
//New columns in table: login
//=================================================//
//Create new column in 'login' table
$q = "SHOW COLUMNS FROM login WHERE Field = 'db_updated'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	$r2 = mysqli_query($mysqli, 'ALTER TABLE login ADD db_updated VARCHAR(100)');
	if(!$r2) error_log("[Can\'t run ALTER TABLE login ADD db_updated VARCHAR(100)]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
}

//=================================================//
//Check if db has been updated. 
//Only run this script once.
//=================================================//
$q = 'SELECT db_updated FROM login WHERE (db_updated != "'.CURRENT_VERSION.'" OR db_updated is NULL) AND license != ""';
$r = mysqli_query($mysqli, $q);
if ($r && mysqli_num_rows($r) == 1)
{	
	//================= Version 1.0.1 =================//
	//New column in table: campaigns, named wysiwyg
	//=================================================//
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'wysiwyg'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table campaigns add column wysiwyg INT (11) DEFAULT \'0\'';
		$r = mysqli_query($mysqli, $q);
		if ($r){
			$q = 'UPDATE campaigns SET wysiwyg=0';
			$r = mysqli_query($mysqli, $q);
			if ($r){}
		}
	}
	//================= Version 1.0.3 =================//
	//New column in table: login, named tied_to & app
	//=================================================//
	$q = "SHOW COLUMNS FROM login WHERE Field = 'tied_to' || Field = 'app' || Field = 'paypal'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table login add (tied_to INT (11), app INT (11), paypal VARCHAR (100))';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	//-------------------------------------------------//
	//New column in table: apps, named currency, delivery_fee & cost_per_recipient
	//-------------------------------------------------//
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'currency' || Field = 'delivery_fee' || Field = 'cost_per_recipient'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table apps add (currency VARCHAR (100), delivery_fee VARCHAR (100), cost_per_recipient VARCHAR (100))';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	//================= Version 1.0.4 =================//
	//New column in table: campaigns, named send_date, lists & timezone
	//=================================================//
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'send_date' || Field = 'lists' || Field = 'timezone'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table campaigns add (send_date VARCHAR (100), lists VARCHAR (100), timezone VARCHAR (100))';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	//-------------------------------------------------//
	//New column in table: login, named, cron
	//-------------------------------------------------//
	$q = "SHOW COLUMNS FROM login WHERE Field = 'cron'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table login add (cron INT (11) default 0)';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	//================= Version 1.0.5 =================//
	//New column in table: lists, named opt_in, subscribed_url, unsubscribed_url, thankyou, thankyou_message, goodbye, goodbye_message, unsubscribe_all_list
	//=================================================//
	$q = "SHOW COLUMNS FROM lists WHERE Field = 'opt_in' || Field = 'subscribed_url' || Field = 'unsubscribed_url' || Field = 'thankyou' || Field = 'thankyou_subject' || Field = 'thankyou_message' || Field = 'goodbye' || Field = 'goodbye_subject' || Field = 'goodbye_message' || Field = 'unsubscribe_all_list'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table lists add (opt_in INT (11) DEFAULT \'0\', subscribed_url VARCHAR (100), unsubscribed_url VARCHAR (100), thankyou int (11) DEFAULT \'0\', thankyou_subject VARCHAR(100), thankyou_message MEDIUMTEXT, goodbye INT (11) DEFAULT \'0\', goodbye_subject VARCHAR(100), goodbye_message MEDIUMTEXT, unsubscribe_all_list INT (11) DEFAULT \'1\')';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	//-------------------------------------------------//
	//New column in table: subscribers, named, confirmed
	//-------------------------------------------------//
	$q = "SHOW COLUMNS FROM subscribers WHERE Field = 'confirmed'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table subscribers add (confirmed INT (11) default 1)';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	
	//================= Version 1.0.6 =================//
	//New column in table: campaigns, to_send, to_send_lists
	//=================================================//
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'to_send' || Field = 'to_send_lists'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table campaigns ADD COLUMN to_send INT (100) AFTER sent';
		$q2 = 'alter table campaigns ADD COLUMN to_send_lists VARCHAR (100) AFTER to_send';
		$r = mysqli_query($mysqli, $q);
		$r2 = mysqli_query($mysqli, $q2);
		if ($r && $r2){}
	}
	//-------------------------------------------------//
	//New column in table: confirm_url
	//-------------------------------------------------//
	$q = "SHOW COLUMNS FROM lists WHERE Field = 'confirm_url'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table lists ADD COLUMN confirm_url VARCHAR (100) AFTER opt_in';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	
	//================= Version 1.0.7 =================//
	//New column in table: subscribers, named, complaint
	//=================================================//
	$q = "SHOW COLUMNS FROM subscribers WHERE Field = 'complaint'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table subscribers ADD COLUMN complaint INT (11) DEFAULT \'0\' AFTER bounced';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	
	//================= Version 1.0.8 =================//
	//New column in table: lists & subscribers, custom_fields & custom_fields
	//=================================================//
	$q = "SHOW COLUMNS FROM lists WHERE Field = 'custom_fields'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table lists ADD COLUMN custom_fields MEDIUMTEXT';
		$q2 = 'alter table subscribers ADD COLUMN custom_fields LONGTEXT AFTER email';
		$q3 = 'alter table subscribers ADD COLUMN join_date INT (100) AFTER timestamp';
		$r = mysqli_query($mysqli, $q);
		$r2 = mysqli_query($mysqli, $q2);
		$r3 = mysqli_query($mysqli, $q3);
		if ($r && $r2 && $r3){}
	}
	
	//================= Version 1.0.9 =================//
	//New columns in table: subscribers, login, links and new autoresponders tables
	//=================================================//
	$q = "SHOW COLUMNS FROM subscribers WHERE Field = 'bounce_soft'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table subscribers ADD COLUMN bounce_soft INT (11) DEFAULT \'0\' AFTER bounced, ADD COLUMN last_ares INT (11) AFTER last_campaign';
		$q2 = 'alter table login ADD COLUMN cron_ares INT (11) DEFAULT \'0\' AFTER cron';
		$q3 = 'alter table links ADD COLUMN ares_emails_id INT (11) AFTER campaign_id';
		$q4 = 'CREATE TABLE `ares` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `name` varchar(100) DEFAULT NULL,
		  `type` int(11) DEFAULT NULL,
		  `list` int(11) DEFAULT NULL,
		  `custom_field` varchar(100) DEFAULT NULL,
		  PRIMARY KEY (`id`)
		) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;';
		$q5 = 'CREATE TABLE `ares_emails` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `ares_id` int(11) DEFAULT NULL,
		  `from_name` varchar(100) DEFAULT NULL,
		  `from_email` varchar(100) DEFAULT NULL,
		  `reply_to` varchar(100) DEFAULT NULL,
		  `title` varchar(500) DEFAULT NULL,
		  `plain_text` mediumtext,
		  `html_text` mediumtext,
		  `time_condition` varchar(100) DEFAULT NULL,
		  `timezone` varchar(100) DEFAULT NULL,
		  `created` int(11) DEFAULT NULL,
		  `recipients` int(100) DEFAULT 0,
		  `opens` longtext,
		  `wysiwyg` int(11) DEFAULT 0,
		  `editor_mode` varchar(20) DEFAULT NULL,
		  `editor_project_data` longtext,
		  PRIMARY KEY (`id`)
		) AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;';
		$r = mysqli_query($mysqli, $q);
		$r2 = mysqli_query($mysqli, $q2);
		$r3 = mysqli_query($mysqli, $q3);
		$r4 = mysqli_query($mysqli, $q4);
		$r5 = mysqli_query($mysqli, $q5);
		if ($r && $r2 && $r3 && $r4 && $r5){}
	}
	
	//================= Version 1.1.0 =================//
	//New columns in table: login and new table, queue etc
	//=================================================//
	$q = "SHOW COLUMNS FROM login WHERE Field = 'send_rate'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table login ADD COLUMN send_rate INT (100) DEFAULT 0';
		$q2 = 'alter table login ADD COLUMN timezone VARCHAR (100)';
		$q3 = 'alter table apps ADD (smtp_host VARCHAR (100), smtp_port VARCHAR (100), smtp_ssl VARCHAR (100), smtp_username VARCHAR (100), smtp_password VARCHAR (100))';
		$q4 = 'alter table campaigns ADD COLUMN timeout_check VARCHAR (100) AFTER recipients';
		$r = mysqli_query($mysqli, $q);
		$r2 = mysqli_query($mysqli, $q2);
		$r3 = mysqli_query($mysqli, $q3);
		$r4 = mysqli_query($mysqli, $q4);
		$r5 = mysqli_query($mysqli, $q4);
		if ($r && $r2 && $r3 && $r4){}
		
		$q = 'SELECT timezone FROM login LIMIT 1';
		$r = mysqli_query($mysqli, $q);
		if ($r && mysqli_num_rows($r) > 0)
		{
			while($row = mysqli_fetch_array($r))
			{
				if($row['timezone']=='')
				{
					$q2 = 'UPDATE login SET timezone = "America/New_York" LIMIT 1';
					mysqli_query($mysqli, $q2);
				}
			}  
		}
	}
	
	$q = 'SHOW TABLES LIKE "queue"';
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 0)
	{
		$q2 = 'CREATE TABLE `queue` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `query_str` longtext,
		  `campaign_id` int(11) DEFAULT NULL,
		  `subscriber_id` int(11) DEFAULT NULL,
		  PRIMARY KEY (`id`)
		) DEFAULT CHARSET=utf8;';
		mysqli_query($mysqli, $q2);
	}
	
	//================= Version 1.1.2 =================//
	//New column in table: subscribers
	//=================================================//
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'errors'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table campaigns ADD COLUMN errors LONGTEXT';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	
	//================= Version 1.1.3 =================//
	//New column in table: subscribers
	//=================================================//
	$q = "SHOW COLUMNS FROM queue WHERE Field = 'sent'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table queue ADD COLUMN sent INT (11) DEFAULT 0';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	
	//================= Version 1.1.4 =================//
	//New column in table: subscribers
	//=================================================//
	$q = "SHOW COLUMNS FROM lists WHERE Field = 'confirmation_email'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table lists ADD COLUMN confirmation_email MEDIUMTEXT after goodbye_message';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	$q = "SHOW COLUMNS FROM lists WHERE Field = 'confirmation_subject'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table lists ADD COLUMN confirmation_subject MEDIUMTEXT after goodbye_message';
		$r = mysqli_query($mysqli, $q);
		if ($r){}
	}
	
	//================= Version 1.1.5 =================//
	//New column in table: subscribers
	//=================================================//
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'bounce_setup'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q = 'alter table login ADD language VARCHAR (100) DEFAULT "en_US", ADD cron_csv INT (11) DEFAULT 0';
		$q2 = 'alter table lists ADD prev_count INT (100) DEFAULT 0 after custom_fields, ADD currently_processing INT (100) DEFAULT 0, ADD total_records INT (100) DEFAULT 0';
		$q3 = 'alter table apps ADD bounce_setup INT (11) DEFAULT 0, ADD complaint_setup INT (11) DEFAULT 0';
		$q4 = 'alter table campaigns ADD bounce_setup INT (11) DEFAULT 0, ADD complaint_setup INT (11) DEFAULT 0';
		mysqli_query($mysqli, $q);
		mysqli_query($mysqli, $q2);
		mysqli_query($mysqli, $q3);
		mysqli_query($mysqli, $q4);
	}
	//add index to list column in subscribers table
	$q = 'SHOW INDEX FROM subscribers WHERE KEY_NAME = "s_list"';
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'CREATE INDEX s_list ON subscribers (list)');
		mysqli_query($mysqli, 'CREATE INDEX s_unsubscribed ON subscribers (unsubscribed)');
		mysqli_query($mysqli, 'CREATE INDEX s_bounced ON subscribers (bounced)');
		mysqli_query($mysqli, 'CREATE INDEX s_bounce_soft ON subscribers (bounce_soft)');
		mysqli_query($mysqli, 'CREATE INDEX s_complaint ON subscribers (complaint)');
		mysqli_query($mysqli, 'CREATE INDEX s_confirmed ON subscribers (confirmed)');
		mysqli_query($mysqli, 'CREATE INDEX s_timestamp ON subscribers (timestamp)');
		mysqli_query($mysqli, 'CREATE INDEX s_id ON queue (subscriber_id)');
		mysqli_query($mysqli, 'CREATE INDEX st_id ON queue (sent)');
	}
	
	//================= Version 1.1.7 =================//
	//New column in table: apps
	//=================================================//
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'app_key'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$q3 = 'alter table apps ADD COLUMN app_key VARCHAR (100)';
		$r3 = mysqli_query($mysqli, $q3);
		if ($r3)
		{
			$q4 = 'SELECT id FROM apps';
			$r4 = mysqli_query($mysqli, $q4);
			if (mysqli_num_rows($r4) > 0)
			{
				while($row = mysqli_fetch_array($r4))
				{
					$cid = $row['id'];
					
					$q5 = 'UPDATE apps SET app_key = "'.ran_string(30, 30, true, false, true).'" WHERE id = '.$cid;
					mysqli_query($mysqli, $q5);
				}  
			}
		}
	}
	
	//================= Version 1.1.7.2 ===============//
	//New index in table: subscribers (email column)
	//=================================================//
	//add index to email column in subscribers table
	$q = 'SHOW INDEX FROM subscribers WHERE KEY_NAME = "s_email"';
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'CREATE INDEX s_email ON subscribers (email)');
	}
	
	//================= Version 1.1.8 ===============//
	//New column in table: login
	//=================================================//
	//Create new 'ses_endpoint' in 'login' table
	$q = "SHOW COLUMNS FROM login WHERE Field = 'ses_endpoint'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$r2 = mysqli_query($mysqli, 'alter table login ADD COLUMN ses_endpoint VARCHAR (100)');
		if($r2)
		{
			$q3 = 'UPDATE login SET ses_endpoint = "email.us-east-1.amazonaws.com" LIMIT 1';
			mysqli_query($mysqli, $q3);
		}
	}
	
	//================= Version 1.1.7.3 ===============//
	//Convert to_send_lists and lists columns to TEXT type
	//=================================================//
	//add index to email column in subscribers table
	$q = 'SHOW COLUMNS FROM campaigns WHERE Field = "to_send_lists" AND Type = "VARCHAR(100)"';
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 1)
	{
		mysqli_query($mysqli, 'ALTER TABLE campaigns MODIFY COLUMN to_send_lists TEXT');
		mysqli_query($mysqli, 'ALTER TABLE campaigns MODIFY COLUMN lists TEXT');
	}
	
	//================= Version 1.1.9 ===============//
	//New column in table: login
	//=================================================//
	//Create new 'ses_endpoint' in 'login' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'allocated_quota'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'alter table apps ADD COLUMN allocated_quota INT (11) DEFAULT -1');
		mysqli_query($mysqli, 'alter table apps ADD COLUMN current_quota INT (11) DEFAULT 0');
		mysqli_query($mysqli, 'alter table apps ADD COLUMN day_of_reset INT (11) DEFAULT 1');
		mysqli_query($mysqli, 'alter table apps ADD COLUMN month_of_next_reset VARCHAR (3)');
	}
	
	//================= Version 1.1.9.1 ===============//
	//New column in table: apps
	//=================================================//
	//Create new 'test_email' in 'login' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'test_email'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'alter table apps ADD COLUMN test_email VARCHAR (100)');
	}
	
	//================= Version 1.1.9.4 ===============//
	//New column in table: subscribers
	//=================================================//
	//Create new 'test_email' in 'login' table
	$q = "SHOW COLUMNS FROM subscribers WHERE Field = 'messageID'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'alter table subscribers ADD COLUMN messageID VARCHAR (100)');
	}
	
	//================= Version 2.0 ===================//
	//New table in database: template
	//=================================================//
	//Create new 'template' table in database
	$q = "CREATE TABLE IF NOT EXISTS `template` (
	  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `userID` int(11) DEFAULT NULL,
	  `app` int(11) DEFAULT NULL,
	  `template_name` varchar(100) DEFAULT NULL,
	  `html_text` mediumtext,
	  `editor_mode` varchar(20) DEFAULT 'dragdrop',
	  `editor_project_data` longtext,
	  PRIMARY KEY (`id`)
	) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";
	mysqli_query($mysqli, $q);
	
	//Create new 'query_string' in 'campaigns' table
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'query_string'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'alter table campaigns ADD COLUMN query_string VARCHAR (500) AFTER html_text, ADD COLUMN label VARCHAR (500) AFTER title');
		mysqli_query($mysqli, 'alter table ares_emails ADD COLUMN query_string VARCHAR (500) AFTER html_text');
		mysqli_query($mysqli, 'alter table apps ADD COLUMN brand_logo_filename VARCHAR (100)');
	}
	
	//================= Version 2.0.6 ===============//
	//New column in table: apps
	//=================================================//
	//Create new 'allowed_attachments' in 'apps' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'allowed_attachments'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'alter table apps ADD COLUMN allowed_attachments VARCHAR (100) DEFAULT "jpeg,jpg,gif,png,pdf,zip"');
	}
	
	//================= Version 2.0.8 ===============//
	//New INDEX in table: subscribers and increase VARCHAR to 1500 in `link` column of `links` table
	//=================================================//
	//Create new 'allowed_attachments' in 'apps' table
	$q = "SHOW INDEX FROM subscribers WHERE Key_name = 's_last_campaign'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE subscribers ADD INDEX s_last_campaign (last_campaign DESC)');
		mysqli_query($mysqli, 'ALTER TABLE links modify link VARCHAR(1500)');
		mysqli_query($mysqli, 'ALTER TABLE apps CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
		mysqli_query($mysqli, 'ALTER TABLE ares_emails CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
		mysqli_query($mysqli, 'ALTER TABLE campaigns CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
		mysqli_query($mysqli, 'ALTER TABLE lists CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
		mysqli_query($mysqli, 'ALTER TABLE subscribers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
		mysqli_query($mysqli, 'ALTER TABLE template CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
	}
	
	//================= Version 2.1.0 ===============//
	//New column in table: auth_enabled, auth_key
	//=================================================//
	//Create new 'allowed_attachments' in 'apps' table
	$q = "SHOW COLUMNS FROM login WHERE Field = 'auth_enabled'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE login ADD COLUMN auth_enabled INT (11) DEFAULT 0');
		mysqli_query($mysqli, 'ALTER TABLE login ADD COLUMN auth_key VARCHAR (100)');
	}
	
	//================= Version 2.1.1 ===============//
	//New table: zapier
	//=================================================//
	
	//Create new 'zapier' table in database
	$q = "CREATE TABLE IF NOT EXISTS `zapier` (
	  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `subscribe_endpoint` varchar(100) DEFAULT NULL,
	  `event` varchar(100) DEFAULT NULL,
	  `list` int(11) DEFAULT NULL,
	  `app` int(11) DEFAULT NULL,
	  PRIMARY KEY (`id`)
	) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;";
	mysqli_query($mysqli, $q);
	
	//================= Version 2.1.1.5 ===============//
	//New column in 'apps' table: reports_only 
	//=================================================//
	
	//Create new 'reports_only' in 'apps' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'reports_only'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN reports_only INT (1) DEFAULT 0');
		mysqli_query($mysqli, 'ALTER TABLE campaigns ADD COLUMN opens_tracking INT (1) DEFAULT \'1\', ADD COLUMN links_tracking INT (1) DEFAULT \'1\'');
		mysqli_query($mysqli, 'ALTER TABLE ares_emails ADD COLUMN opens_tracking INT (1) DEFAULT \'1\', ADD COLUMN links_tracking INT (1) DEFAULT \'1\'');
		mysqli_query($mysqli, 'ALTER TABLE login CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
	}
	
	//================= Version 2.1.2.7 ===============//
	//New column in 'apps' table: year_of_next_reset 
	//=================================================//
	
	//Create new 'reports_only' in 'apps' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'year_of_next_reset'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN year_of_next_reset VARCHAR (4) AFTER month_of_next_reset');
		mysqli_query($mysqli, 'ALTER TABLE campaigns ADD COLUMN quota_deducted INT (11) AFTER wysiwyg');
		
		//Init
		$time_today_unix = time();
		$month_today = date("m", $time_today_unix);
		$year_today = date("Y", $time_today_unix);
		$year_last = $year_today-1;
		$month_array = array(1=>'Jan', 2=>'Feb', 3=>'Mar', 4=>'Apr', 5=>'May', 6=>'Jun', 7=>'Jul', 8=>'Aug', 9=>'Sep', 10=>'Oct', 11=>'Nov', 12=>'Dec');
		
		//Populate empty 'year_of_next_reset' columns if a monthly sending limit was set
		$q2 = 'SELECT id, allocated_quota, month_of_next_reset FROM apps';
		$r2 = mysqli_query($mysqli, $q2);
		if ($r2 && mysqli_num_rows($r2) > 0)
		{
			while($row = mysqli_fetch_array($r2))
			{
				$app_id = $row['id'];
				$allocated_quota = $row['allocated_quota'];
				
				//If a monthly sending limit was set, set 'year_of_next_reset' appropriately
				if($allocated_quota!='-1')
				{
					$month_of_next_reset = array_search($row['month_of_next_reset'], $month_array);
					$month_diff = $month_of_next_reset - $month_today;
					if($month_diff > 1) mysqli_query($mysqli, "UPDATE apps SET year_of_next_reset = $year_last WHERE id = $app_id");
					else mysqli_query($mysqli, "UPDATE apps SET year_of_next_reset = $year_today WHERE id = $app_id");
				}
			}  
		}
	}
	
	//================= Version 3.0 ===============//
	//New column in table: lists_excl
	//=================================================//
	//Create new 'allowed_attachments' in 'apps' table
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'lists_excl'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE campaigns ADD COLUMN lists_excl MEDIUMTEXT AFTER lists, ADD COLUMN segs MEDIUMTEXT AFTER lists_excl, ADD COLUMN segs_excl MEDIUMTEXT AFTER segs');
		mysqli_query($mysqli, 'CREATE TABLE IF NOT EXISTS `seg` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `name` varchar(100) DEFAULT NULL,
		  `app` int(11) DEFAULT NULL,
		  `list` int(11) DEFAULT NULL,
		  `last_updated` varchar(100) DEFAULT NULL,
		  PRIMARY KEY (`id`)
		) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;');
		mysqli_query($mysqli, 'CREATE TABLE IF NOT EXISTS `seg_cons` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `seg_id` int(11) DEFAULT NULL,
		  `group_id` int(11) DEFAULT NULL,
		  `operator` char(3) DEFAULT NULL,
		  `field` varchar(100) DEFAULT NULL,
		  `comparison` varchar(11) DEFAULT NULL,
		  `val` varchar(500) DEFAULT NULL,
		  PRIMARY KEY (`id`)
		) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;');
		mysqli_query($mysqli, 'CREATE TABLE `subscribers_seg` (
		  `seg_id` int(11) DEFAULT NULL,
		  `subscriber_id` int(11) DEFAULT NULL
		) DEFAULT CHARSET=utf8;');
		mysqli_query($mysqli, 'CREATE INDEX s_sid ON subscribers_seg (seg_id)');
		mysqli_query($mysqli, 'CREATE INDEX s_subscriber_id ON subscribers_seg (subscriber_id)');
		mysqli_query($mysqli, 'CREATE INDEX s_list ON seg (list)');
		mysqli_query($mysqli, 'CREATE INDEX s_seg_id ON seg_cons (seg_id)');
		mysqli_query($mysqli, 'ALTER TABLE subscribers ADD INDEX s_messageid (messageID)');
		mysqli_query($mysqli, 'ALTER TABLE queue ADD INDEX s_campaign_id (campaign_id)');
		mysqli_query($mysqli, 'ALTER TABLE login ADD COLUMN cron_seg INT (11) DEFAULT \'0\' AFTER cron_csv');
		
		if(mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN campaigns_only INT (1) DEFAULT 0, ADD COLUMN templates_only INT (1) DEFAULT 0, ADD COLUMN lists_only INT (1) DEFAULT 0'))
		{
			$q2 = 'SELECT id, reports_only FROM apps';
			$r2 = mysqli_query($mysqli, $q2);
			if ($r2 && mysqli_num_rows($r2) > 0)
			{
				while($row = mysqli_fetch_array($r2))
				{
					$aid = $row['id'];
					$ro = $row['reports_only'];
					if($ro==1)
					{
						mysqli_query($mysqli, 'UPDATE apps SET reports_only = 0, campaigns_only = 1, templates_only = 1, lists_only = 1 WHERE id = '.$aid);
					}
				}  
			}
		}
	}
	
	//================= Version 3.0.4 ===============//
	//New column in table: ares_emails
	//=================================================//
	//Create new 'allowed_attachments' in 'apps' table
	$q = "SHOW COLUMNS FROM ares_emails WHERE Field = 'enabled'";
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE ares_emails ADD COLUMN enabled INT (11) DEFAULT \'0\' AFTER links_tracking');
		mysqli_query($mysqli, 'UPDATE ares_emails SET enabled = 1');
		mysqli_query($mysqli, 'ALTER TABLE subscribers_seg ADD PRIMARY KEY(seg_id, subscriber_id)');
	}
	
	//================= Version 3.0.5 ===============//
	//New columns in table: subscribers
	//=================================================//
	//Create new columns in 'apps' table
	$q = "SHOW COLUMNS FROM subscribers WHERE Field = 'ip'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE subscribers 
		  ADD COLUMN ip VARCHAR (100)
		, ADD COLUMN country VARCHAR (2)
		, ADD COLUMN referrer VARCHAR (500)
		, ADD COLUMN method INT (1)
		, ADD COLUMN added_via INT (1)
		');
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN notify_campaign_sent INT (1) DEFAULT \'1\'');
		mysqli_query($mysqli, 'CREATE INDEX s_country ON subscribers (country)');
		mysqli_query($mysqli, 'CREATE INDEX s_referrer ON subscribers (referrer)');
		mysqli_query($mysqli, 'CREATE INDEX s_method ON subscribers (method)');
		mysqli_query($mysqli, 'CREATE INDEX s_added_via ON subscribers (added_via)');
	}
	
	//================= Version 3.0.6 ===============//
	//New columns in table: apps
	//=================================================//
	//Create new columns in 'apps' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'campaign_report_rows'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN campaign_report_rows INT (11) DEFAULT \'10\'');
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN query_string VARCHAR (500)');
		mysqli_query($mysqli, 'ALTER TABLE login ADD COLUMN reset_password_key VARCHAR (20)');
	}
	
	//================= Version 3.0.7 ===============//
	//New columns in table: lists
	//=================================================//
	//Create new columns in 'apps' table
	$q = "SHOW COLUMNS FROM lists WHERE Field = 'gdpr_enabled'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE lists 
		ADD COLUMN gdpr_enabled INT (1) DEFAULT \'0\', 
		ADD COLUMN marketing_permission MEDIUMTEXT, 
		ADD COLUMN what_to_expect MEDIUMTEXT, 
		ADD COLUMN gdpr INT (1) DEFAULT \'0\' AFTER total_records');
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN gdpr_only INT (1) DEFAULT \'0\', 
		ADD COLUMN gdpr_options INT (1) DEFAULT \'1\'');
		mysqli_query($mysqli, 'ALTER TABLE subscribers ADD COLUMN gdpr INT (1) DEFAULT \'0\'');
	}
	
	//================= Version 3.0.8 ===============//
	//New columns in table: apps
	//=================================================//
	//Create new columns in 'apps' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'gdpr_only_ar'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN gdpr_only_ar INT (1) DEFAULT \'0\'');
		
		$q = 'SELECT id, gdpr_only FROM apps';
		$r = mysqli_query($mysqli, $q);
		if ($r && mysqli_num_rows($r) > 0)
		{
			while($row = mysqli_fetch_array($r))
			{
				$app_id = $row['id'];
				$gdpr_only = $row['gdpr_only'];
				$q2 = 'UPDATE apps SET gdpr_only_ar = '.$gdpr_only.' WHERE id = '.$app_id;
				$r2 = mysqli_query($mysqli, $q2);
				if ($r2){}
			}  
		}
	}
	
	//================= Version 3.1.2 ===============//
	//New columns in table: apps
	//=================================================//
	//Create new columns in 'apps' table
	$q = "SHOW COLUMNS FROM apps WHERE Field = 'recaptcha_sitekey'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN recaptcha_sitekey VARCHAR (50)');
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN recaptcha_secretkey VARCHAR (50)');
	}
	
	//================= Version 3.1.2.1 ===============//
	//New index in table: apps
	//=================================================//
	//add index to gdpr column in subscribers table
	$q = 'SHOW INDEX FROM subscribers WHERE KEY_NAME = "s_gdpr"';
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'CREATE INDEX s_gdpr ON subscribers (gdpr)');
	}
	
	//================= Version 3.1.2.2 ===============//
	//New index in table: apps
	//=================================================//
	//Create new columns in 'lists' table
	$q = "SHOW COLUMNS FROM lists WHERE Field = 'unsubscribe_confirm'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE lists ADD COLUMN unsubscribe_confirm INT (1) DEFAULT \'0\'');
	}
	
	//================= Version 4.0 ===============//
	//New tables: suppression_list and blocked_domains
	//=================================================//
	//Create new columns in 'lists' table
	$q = 'SHOW TABLES LIKE "suppression_list"';
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, '
		CREATE TABLE `suppression_list` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `app` int(11) DEFAULT NULL,
		  `email` varchar(100) DEFAULT NULL,
		  `timestamp` varchar(100) DEFAULT NULL,
		  `block_attempts` int(100) DEFAULT \'0\',
		  PRIMARY KEY (`id`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8');
		
		mysqli_query($mysqli, '
		CREATE TABLE `blocked_domains` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `app` int(11) DEFAULT NULL,
		  `domain` varchar(100) DEFAULT NULL,
		  `timestamp` varchar(100) DEFAULT NULL,
		  `block_attempts` int(100) DEFAULT \'0\',
		  PRIMARY KEY (`id`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8');
		
		mysqli_query($mysqli, '
		CREATE TABLE `skipped_emails` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `app` int(11) DEFAULT NULL,
		  `list` int(11) DEFAULT NULL,
		  `email` varchar(100) DEFAULT NULL,
		  `reason` int(1) DEFAULT NULL,
		  PRIMARY KEY (`id`),
		  KEY `s_list` (`list`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
		
		mysqli_query($mysqli, 'ALTER TABLE apps 
							   ADD COLUMN custom_domain VARCHAR (100),
							   ADD COLUMN custom_domain_protocol VARCHAR (5),
							   ADD COLUMN custom_domain_enabled INT (1) DEFAULT \'0\',
							   ADD COLUMN test_email_prefix VARCHAR (100),
							   ADD COLUMN no_expiry INT (1) DEFAULT \'0\' AFTER year_of_next_reset');
		mysqli_query($mysqli, 'ALTER TABLE lists
							   ADD COLUMN no_consent_url VARCHAR (100),
							   ADD COLUMN already_subscribed_url VARCHAR (100),
							   ADD COLUMN reconsent_success_url VARCHAR (100)');
		mysqli_query($mysqli, 'ALTER TABLE template MODIFY template_name VARCHAR(500)');
		mysqli_query($mysqli, 'ALTER TABLE subscribers ADD COLUMN notes TEXT');
	}
	
	//================= Version 4.0.2 ===============//
	//
	//=================================================//
	//Create new columns in 'lists' table
	$q = "SELECT templates_lists_sorting FROM apps";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN templates_lists_sorting VARCHAR(4) DEFAULT "date"');
	}
	
	//================= Version 4.0.3 ===============//
	//New columns in table: apps
	//=================================================//
	//Create new columns in 'apps' table
	$q = "SHOW COLUMNS FROM login WHERE Field = 'brands_rows'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE login ADD COLUMN brands_rows INT (11) DEFAULT \'10\'');
	}
	
	//================= Version 4.0.4 ===============//
	//New columns in table: apps
	//=================================================//
	//Create new columns in 'apps' table
	$q = "SHOW COLUMNS FROM queue WHERE Field = 'http_headers'";
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE queue ADD COLUMN http_headers MEDIUMTEXT AFTER query_str');
	}
	
	//================= Version 4.0.6.1 ===============//
	//New columns in table: lists
	//=================================================//
	//Create new columns in 'lists' table
	$q = "SHOW COLUMNS FROM seg_cons WHERE Field = 'grouping'";
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 1)
	{
		mysqli_query($mysqli, 'ALTER TABLE seg_cons CHANGE `grouping` group_id int(11)');
	}
	
	//================= Version 4.0.9 ===============//
	//New columns in table: lists
	//=================================================//
	//Create new columns in 'lists' table
	$q = "SHOW COLUMNS FROM login WHERE Field = 'api_key_prev'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE login ADD api_key_prev VARCHAR(500) AFTER api_key');
		mysqli_query($mysqli, 'ALTER TABLE login ADD strict_delete INT(1) DEFAULT 1');
	}
	
	//================= Version 4.1.0 ===============//
	//New columns in table: lists
	//=================================================//
	//Create new columns in 'lists' table
	$q = "SHOW COLUMNS FROM skipped_emails LIKE 'id'";
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE skipped_emails ADD COLUMN id int(11) unsigned primary KEY AUTO_INCREMENT');
	}
	
	//================= Version 5 ===============//
	//New columns in table: lists
	//=================================================//
	//Create new columns in 'lists' table
	$q = "SHOW COLUMNS FROM login WHERE Field = 'dark_mode'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE login ADD dark_mode INT(1) DEFAULT 0');
		mysqli_query($mysqli, 'ALTER TABLE template ADD plain_text longtext');
		mysqli_query($mysqli, 'ALTER TABLE ares_emails ADD segs longtext, ADD segs_excl longtext');
		
		mysqli_query($mysqli, '
		CREATE TABLE `rules` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `brand` int(11) DEFAULT NULL,
		  `trigger` varchar(100) DEFAULT NULL,
		  `action` varchar(100) DEFAULT NULL,
		  `endpoint` varchar(100) DEFAULT NULL,
		  `notification_email` varchar(100) DEFAULT NULL,
		  `unsubscribe_list_id` int(11) DEFAULT NULL,
		  `list` int(11) DEFAULT NULL,
		  `app` int(11) DEFAULT NULL,
		  `ares_id` int(11) DEFAULT NULL,
		  PRIMARY KEY (`id`),
		  KEY `s_brand` (`brand`) USING BTREE,
		  KEY `s_trigger` (`trigger`) USING BTREE,
		  KEY `s_action` (`action`) USING BTREE,
		  KEY `s_list` (`list`) USING BTREE,
		  KEY `s_app` (`app`) USING BTREE
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
		
		mysqli_query($mysqli, '
		CREATE TABLE `webhooks_log` (
		  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
		  `rule` int(11) DEFAULT NULL,
		  `endpoint` varchar(100) DEFAULT NULL,
		  `payload` mediumtext,
		  `status_code` varchar(10) DEFAULT NULL,
		  `status_message` mediumtext,
		  `timestamp` varchar(100) DEFAULT NULL,
		  PRIMARY KEY (`id`),
		  KEY `s_brand` (`rule`) USING BTREE,
		  KEY `s_status_code` (`status_code`) USING BTREE 
		) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;');
		
		//Move list signup notifications and auto unsubscribe data to 'Rules' table
		$q = 'SELECT id, app, notify_new_signups, notification_email, unsubscribe_from_list, unsubscribe_list_id FROM lists';
		$r = mysqli_query($mysqli, $q);
		if ($r && mysqli_num_rows($r) > 0)
		{
			while($row = mysqli_fetch_array($r))
			{
				$list_id = $row['id'];
				$app = $row['app'];
				$notify_new_signups = $row['notify_new_signups'];
				$notification_email = $row['notification_email'];
				$unsubscribe_from_list = $row['unsubscribe_from_list'];
				$unsubscribe_list_id = $row['unsubscribe_list_id'];
				
				if($notify_new_signups)
				{
					$q2 = 'INSERT INTO rules (`brand`, `trigger`, `action`, `notification_email`, `list`) VALUES ('.$app.', "subscribe", "notify", "'.$notification_email.'", '.$list_id.')';
					$r2 = mysqli_query($mysqli, $q2);
					if (!$r2) error_log("[Unable to INSERT into 'rules' table]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
				}
				
				if($unsubscribe_from_list)
				{
					$q3 = 'INSERT INTO rules (`brand`, `trigger`, `action`, `unsubscribe_list_id`, `list`) VALUES ('.$app.', "subscribe", "unsub_from_list", "'.$unsubscribe_list_id.'", '.$list_id.')';
					$r3 = mysqli_query($mysqli, $q3);
					if (!$r3) error_log("[Unable to INSERT into 'rules' table]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
				}
			}  
			
			//Drop columns
			$q = 'ALTER TABLE lists DROP notify_new_signups, DROP notification_email, DROP unsubscribe_from_list, DROP unsubscribe_list_id;';
			$r = mysqli_query($mysqli, $q);
			if(!$r) error_log("[Unable to DROP columns in 'lists' table]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
		}
		else error_log("[Unable to SELECT data from 'lists' table]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
		
		//Create campaign sent notifications rules
		$q = 'SELECT * FROM apps';
		$r = mysqli_query($mysqli, $q);
		if ($r && mysqli_num_rows($r) > 0)
		{
			while($row = mysqli_fetch_array($r))
			{
				$app = $row['id'];
				$from_email = $row['from_email'];
				
				$q2 = 'INSERT INTO rules (`brand`, `trigger`, `action`, `notification_email`, `app`) VALUES ('.$app.', "campaign_sent", "notify", "'.$from_email.'", '.$app.')';
				$r2 = mysqli_query($mysqli, $q2);
				if (!$r2) error_log("[Unable to INSERT into 'rules' table]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
			}  
		}
		else error_log("[Unable to SELECT data from 'apps' table]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
	}
	
	$q = "SHOW COLUMNS FROM login WHERE Field = 'dark_mode_shortcut'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE login ADD dark_mode_shortcut VARCHAR(1) DEFAULT "D"');
	}

	//================= Version 5.2 ===============//
	//New columns in table: login
	//=================================================//
	//Create new column in 'login' table
	$q = "SHOW COLUMNS FROM login WHERE Field = 'auth_salt'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		$r2 = mysqli_query($mysqli, 'ALTER TABLE login ADD auth_salt VARCHAR(20) AFTER ses_endpoint');
		if($r2)
		{
			$auth_salt = ran_string(20, 20, true, false, true);
			
			$q3 = 'UPDATE login SET auth_salt = "'.$auth_salt.'" WHERE id = 1';
			$r3 = mysqli_query($mysqli, $q3);
			if (!$r3)
			{
				error_log("[Can\'t UPDATE auth_salt in login table]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
			}
		}
		else
		{
			error_log("[Can\'t run ALTER TABLE login ADD auth_salt VARCHAR(20) AFTER ses_endpoint]".mysqli_error($mysqli).': in '.__FILE__.' on line '.__LINE__);
		}
	}
	
	//================== Version 5.2.1 ==================//
	//New index in table: links
	//=================================================//
	
	//add index in links table
	$q = 'SHOW INDEX FROM links WHERE KEY_NAME = "s_campaign_id"';
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'CREATE INDEX s_campaign_id ON links (campaign_id)');
		mysqli_query($mysqli, 'CREATE INDEX s_ares_emails_id ON links (ares_emails_id)');
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN opens_tracking INT (1) DEFAULT \'1\', ADD COLUMN links_tracking INT (1) DEFAULT \'1\'');
	}
	
	//================== Version 5.2.5 ==================//
	//Change row_format of 'apps' table to 'Dynamic'
	//=================================================//
	
	//Check row_format of 'apps' table
	$q = "SELECT row_format FROM information_schema.tables WHERE table_name='apps' LIMIT 1";
	$r = mysqli_query($mysqli, $q);
	if ($r && mysqli_num_rows($r) > 0)
		while($row = mysqli_fetch_array($r))
			$apps_row_format = $row['row_format'];
	
	if($apps_row_format != 'Dynamic')
	{
		mysqli_query($mysqli, 'ALTER TABLE apps ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE ares ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE ares_emails ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE blocked_domains ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE campaigns ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE links ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE lists ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE login ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE queue ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE rules ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE seg ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE seg_cons ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE skipped_emails ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE subscribers ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE subscribers_seg ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE suppression_list ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE template ROW_FORMAT=DYNAMIC');
		mysqli_query($mysqli, 'ALTER TABLE zapier ROW_FORMAT=DYNAMIC');
	}
	
	//================== Version 6 ==================//
	//New column in table: campaigns
	//=================================================//
	
	//Add column web_version_lang in campaigns table
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'web_version_lang'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE campaigns ADD web_version_lang VARCHAR(50)');
		mysqli_query($mysqli, 'ALTER TABLE rules MODIFY COLUMN endpoint VARCHAR(255);');
		mysqli_query($mysqli, 'ALTER TABLE lists ADD hide INT(1) DEFAULT 0');
		mysqli_query($mysqli, 'ALTER TABLE apps ADD hide_lists INT(1) DEFAULT 1');
	}
	
	//================== Version 6.0.2 ==================//
	//New column in table: campaign_stopped
	//=================================================//
	
	//Add column web_version_lang in campaigns table
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'campaign_stopped'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE campaigns ADD campaign_stopped INT(1) DEFAULT 0');
	}
	
	//================= Version 6.0.5 ===============//
	//New column in table: rules
	//=================================================//
	//Create new 'allowed_attachments' in 'apps' table
	$q = "SHOW COLUMNS FROM rules WHERE Field = 'enabled'";
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE rules ADD COLUMN enabled INT (11) DEFAULT \'0\'');
		mysqli_query($mysqli, 'UPDATE rules SET enabled = 1');
	}
	
	//================= Version 6.0.6 ===============//
	//New column in table: rules
	//=================================================//
	//Create new 'allowed_attachments' in 'apps' table
	$q = "SHOW COLUMNS FROM template WHERE Field = 'from_name'";
	$r = mysqli_query($mysqli, $q);
	if (mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE template ADD COLUMN from_name VARCHAR (100)');
		mysqli_query($mysqli, 'ALTER TABLE template ADD COLUMN from_email VARCHAR (100)');
		mysqli_query($mysqli, 'ALTER TABLE template ADD COLUMN reply_to VARCHAR (100)');
		mysqli_query($mysqli, 'ALTER TABLE apps ADD COLUMN opt_in INT (1) DEFAULT 0');
	}
	
	//================== Version 6.0.7 ==================//
	//New column in table: ignore_checks
	//=================================================//
	
	//Add column web_version_lang in campaigns table
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'ignore_checks'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE campaigns ADD ignore_checks INT(1) DEFAULT 1');
	}
	
	//================== Version 6.0.7.1 ==================//
	//Set default for ignore_checks to 1
	//=================================================//
	
	//Set default for ignore_checks to 1
	mysqli_query($mysqli, 'ALTER TABLE campaigns ALTER ignore_checks SET DEFAULT 1');
	
	//================== Version 7.0 ==================//
	//New columns in email editors
	//=================================================//
	
	//Add editor_mode and editor_project_data to campaigns
	$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'editor_mode'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE campaigns ADD editor_mode VARCHAR(20) DEFAULT NULL AFTER wysiwyg, ADD editor_project_data LONGTEXT AFTER editor_mode');
		mysqli_query($mysqli, 'UPDATE campaigns SET editor_mode = IF(wysiwyg=1, "wysiwyg", "html") WHERE editor_mode IS NULL OR editor_mode = ""');
	}
	else
	{
		$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'editor_project_data'";
		$r = mysqli_query($mysqli, $q);
		if (!$r || mysqli_num_rows($r) == 0)
			mysqli_query($mysqli, 'ALTER TABLE campaigns ADD editor_project_data LONGTEXT AFTER editor_mode');
	}
	
	//Add editor_mode and editor_project_data to ares_emails
	$q = "SHOW COLUMNS FROM ares_emails WHERE Field = 'editor_mode'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE ares_emails ADD editor_mode VARCHAR(20) DEFAULT NULL AFTER wysiwyg, ADD editor_project_data LONGTEXT AFTER editor_mode');
		mysqli_query($mysqli, 'UPDATE ares_emails SET editor_mode = IF(wysiwyg=1, "wysiwyg", "html") WHERE editor_mode IS NULL OR editor_mode = ""');
	}
	else
	{
		$q = "SHOW COLUMNS FROM ares_emails WHERE Field = 'editor_project_data'";
		$r = mysqli_query($mysqli, $q);
		if (!$r || mysqli_num_rows($r) == 0)
			mysqli_query($mysqli, 'ALTER TABLE ares_emails ADD editor_project_data LONGTEXT AFTER editor_mode');
	}
	
	//Add editor_mode and editor_project_data to templates
	$q = "SHOW COLUMNS FROM template WHERE Field = 'editor_mode'";
	$r = mysqli_query($mysqli, $q);
	if (!$r || mysqli_num_rows($r) == 0)
	{
		mysqli_query($mysqli, 'ALTER TABLE template ADD editor_mode VARCHAR(20) DEFAULT "dragdrop" AFTER html_text, ADD editor_project_data LONGTEXT AFTER editor_mode');
		mysqli_query($mysqli, 'UPDATE template SET editor_mode = "wysiwyg" WHERE editor_mode IS NULL OR editor_mode = ""');
	}
	else
	{
		$q = "SHOW COLUMNS FROM template WHERE Field = 'editor_project_data'";
		$r = mysqli_query($mysqli, $q);
		if (!$r || mysqli_num_rows($r) == 0)
			mysqli_query($mysqli, 'ALTER TABLE template ADD editor_project_data LONGTEXT AFTER editor_mode');
	}

	//=================================================//
	//Don't run this script again
	//=================================================//
	$q = 'UPDATE login SET db_updated = "'.CURRENT_VERSION.'" WHERE id = 1';
	mysqli_query($mysqli, $q);
}

//=================================================//
//Post-version additive migrations
//These must run even when db_updated already equals CURRENT_VERSION
//=================================================//

//Add OpenAI API key to brands
$q = "SHOW COLUMNS FROM apps WHERE Field = 'openai_api_key'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE apps ADD openai_api_key VARCHAR(500) DEFAULT NULL AFTER smtp_password');
}

//Allow AI features to be disabled per brand
$q = "SHOW COLUMNS FROM apps WHERE Field = 'ai_disabled'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE apps ADD ai_disabled INT(1) DEFAULT 0 AFTER openai_api_key');
}

//Passkey credentials
mysqli_query($mysqli, 'CREATE TABLE IF NOT EXISTS `passkeys` (
	  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `userID` int(11) unsigned NOT NULL,
	  `credential_id` varchar(1000) NOT NULL,
	  `credential_id_hash` char(64) NOT NULL,
	  `credential_public_key` text NOT NULL,
	  `signature_counter` int(11) DEFAULT NULL,
	  `name` varchar(100) DEFAULT NULL,
	  `transports` varchar(255) DEFAULT NULL,
	  `attestation_format` varchar(100) DEFAULT NULL,
	  `created` int(11) NOT NULL,
	  `last_used` int(11) DEFAULT NULL,
	  PRIMARY KEY (`id`),
	  KEY `passkeys_userID` (`userID`),
	  UNIQUE KEY `passkeys_credential_id_hash` (`credential_id_hash`)
	) DEFAULT CHARSET=utf8mb4');

$q = "SHOW COLUMNS FROM passkeys WHERE Field = 'credential_id_hash'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE passkeys ADD credential_id_hash char(64) NOT NULL AFTER credential_id');
	$r2 = mysqli_query($mysqli, 'SELECT id, credential_id FROM passkeys');
	if($r2)
	{
		while($row = mysqli_fetch_array($r2))
		{
			$hash = mysqli_real_escape_string($mysqli, hash('sha256', base64_decode($row['credential_id'])));
			mysqli_query($mysqli, 'UPDATE passkeys SET credential_id_hash = "'.$hash.'" WHERE id = '.(int)$row['id']);
		}
	}
	mysqli_query($mysqli, 'ALTER TABLE passkeys ADD UNIQUE KEY passkeys_credential_id_hash (credential_id_hash)');
}

//Speed up Amazon SES queue lookups for large campaigns
$q = 'SHOW INDEX FROM queue WHERE KEY_NAME = "q_campaign_subscriber"';
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE queue ADD INDEX q_campaign_subscriber (campaign_id, subscriber_id)');
}

$q = 'SHOW INDEX FROM queue WHERE KEY_NAME = "q_campaign_subscriber_unique"';
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	//Remove any legacy duplicates before enforcing atomic queue deduplication.
	mysqli_query($mysqli, 'DELETE q1 FROM queue q1 INNER JOIN queue q2 ON q1.campaign_id = q2.campaign_id AND q1.subscriber_id = q2.subscriber_id AND q1.id > q2.id');
	mysqli_query($mysqli, 'ALTER TABLE queue ADD UNIQUE INDEX q_campaign_subscriber_unique (campaign_id, subscriber_id)');
}

$q = 'SHOW INDEX FROM queue WHERE KEY_NAME = "q_campaign_sent_id"';
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE queue ADD INDEX q_campaign_sent_id (campaign_id, sent, id)');
}

$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'send_status'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE campaigns ADD send_status VARCHAR(20) DEFAULT NULL, ADD send_error TEXT');
}

//Track whether built-in sample templates have already been preloaded for each brand
$q = "SHOW COLUMNS FROM apps WHERE Field = 'sample_templates_preloaded'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE apps ADD sample_templates_preloaded INT(1) DEFAULT 0');
}

//Track which built-in sample template set has been preloaded for each brand
$q = "SHOW COLUMNS FROM apps WHERE Field = 'sample_templates_version'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE apps ADD sample_templates_version INT(3) DEFAULT 0 AFTER sample_templates_preloaded');
}

//Remember built-in sample templates users deleted, so future template-set updates do not re-add them
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS `template_deleted_builtins` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`userID` int(11) DEFAULT NULL,
	`app` int(11) DEFAULT NULL,
	`template_name` varchar(191) DEFAULT NULL,
	`deleted` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `deleted_builtin` (`userID`,`app`,`template_name`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

//Ensure email editor columns exist even if the versioned migration was skipped or partially applied
$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'editor_mode'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE campaigns ADD editor_mode VARCHAR(20) DEFAULT NULL AFTER wysiwyg');
	mysqli_query($mysqli, 'UPDATE campaigns SET editor_mode = IF(wysiwyg=1, "wysiwyg", "html") WHERE editor_mode IS NULL OR editor_mode = ""');
}

$q = "SHOW COLUMNS FROM campaigns WHERE Field = 'editor_project_data'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE campaigns ADD editor_project_data LONGTEXT AFTER editor_mode');
}

$q = "SHOW COLUMNS FROM ares_emails WHERE Field = 'editor_mode'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE ares_emails ADD editor_mode VARCHAR(20) DEFAULT NULL AFTER wysiwyg');
	mysqli_query($mysqli, 'UPDATE ares_emails SET editor_mode = IF(wysiwyg=1, "wysiwyg", "html") WHERE editor_mode IS NULL OR editor_mode = ""');
}

$q = "SHOW COLUMNS FROM ares_emails WHERE Field = 'editor_project_data'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE ares_emails ADD editor_project_data LONGTEXT AFTER editor_mode');
}

$q = "SHOW COLUMNS FROM template WHERE Field = 'editor_mode'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE template ADD editor_mode VARCHAR(20) DEFAULT "dragdrop" AFTER html_text');
	mysqli_query($mysqli, 'UPDATE template SET editor_mode = "wysiwyg" WHERE editor_mode IS NULL OR editor_mode = ""');
}

$q = "SHOW COLUMNS FROM template WHERE Field = 'editor_project_data'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE template ADD editor_project_data LONGTEXT AFTER editor_mode');
}

//Set drag-and-drop as the default editor mode for templates
$q = "SHOW COLUMNS FROM template WHERE Field = 'editor_mode'";
$r = mysqli_query($mysqli, $q);
if ($r && mysqli_num_rows($r) > 0)
{
	$row = mysqli_fetch_array($r);
	if($row['Default']!='dragdrop') mysqli_query($mysqli, 'ALTER TABLE template MODIFY editor_mode VARCHAR(20) DEFAULT "dragdrop"');
}

//Add saved campaign report analyses
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS `campaign_report_analyses` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`userID` int(11) DEFAULT NULL,
	`app` int(11) DEFAULT NULL,
	`report_type` varchar(20) DEFAULT NULL,
	`report_id` int(11) DEFAULT NULL,
	`analysis` mediumtext,
	`metrics` mediumtext,
	`score` int(3) DEFAULT NULL,
	`is_open` int(1) DEFAULT 0,
	`created` int(11) DEFAULT NULL,
	`updated` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `report_owner` (`userID`,`app`,`report_type`,`report_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

$q = "SHOW COLUMNS FROM campaign_report_analyses WHERE Field = 'score'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE campaign_report_analyses ADD score INT(3) DEFAULT NULL AFTER metrics');
}

//Remember which fields should appear in subscribe forms
$q = "SHOW COLUMNS FROM lists WHERE Field = 'subscribe_form_fields'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE lists ADD COLUMN subscribe_form_fields MEDIUMTEXT AFTER what_to_expect');
}

//Add saved email content improvement analyses
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS `email_content_analyses` (
	`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	`userID` int(11) DEFAULT NULL,
	`app` int(11) DEFAULT NULL,
	`content_type` varchar(20) DEFAULT NULL,
	`content_id` int(11) DEFAULT 0,
	`parent_id` int(11) DEFAULT 0,
	`analysis` mediumtext,
	`source` mediumtext,
	`is_open` int(1) DEFAULT 0,
	`created` int(11) DEFAULT NULL,
	`updated` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `content_owner` (`userID`,`app`,`content_type`,`content_id`,`parent_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

//Preload the sample drag-and-drop template into every existing brand.
preload_sample_template_for_all_apps();

//Subscriber preference centre list settings and audit trail.
$q = "SHOW COLUMNS FROM lists WHERE Field = 'preference_visible'";
$r = mysqli_query($mysqli, $q);
if (!$r || mysqli_num_rows($r) == 0)
{
	mysqli_query($mysqli, 'ALTER TABLE lists ADD preference_visible INT(1) DEFAULT 0, ADD preference_name VARCHAR(150) DEFAULT NULL, ADD preference_description TEXT, ADD preference_sort INT(11) DEFAULT 0');
}

mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS `subscriber_preference_events` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`app` int(11) NOT NULL,
	`list` int(11) DEFAULT NULL,
	`email` varchar(255) NOT NULL,
	`action` varchar(30) NOT NULL,
	`source` varchar(30) NOT NULL DEFAULT 'preference-centre',
	`ip` varchar(45) DEFAULT NULL,
	`user_agent` varchar(500) DEFAULT NULL,
	`created` int(11) NOT NULL,
	PRIMARY KEY (`id`),
	KEY `app_email` (`app`,`email`),
	KEY `list_created` (`list`,`created`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

//Track successful autoresponder deliveries per email and subscriber. This is
//used for reliable catch-up without relying on subscribers.last_ares, which
//only records the most recently processed autoresponder email.
$q = "SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'ares_deliveries'";
$r = mysqli_query($mysqli, $q);
$ares_deliveries_exists = $r && mysqli_num_rows($r) > 0;
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS `ares_deliveries` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`ares_email_id` int(11) unsigned NOT NULL,
	`subscriber_id` int(11) unsigned NOT NULL,
	`sent` int(11) unsigned NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `ares_email_subscriber` (`ares_email_id`,`subscriber_id`),
	KEY `subscriber_id` (`subscriber_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
if(!$ares_deliveries_exists)
{
	//Preserve legacy immediate-autoresponder state during upgrade. last_ares
	//records only the most recently processed email and can therefore not be
	//used to infer delivery of a particular autoresponder step. Mark subscribers
	//who were already eligible before this migration as handled; a small number
	//of pending legacy sends may be skipped, but historical subscribers must not
	//receive an immediate email again.
	$ares_deliveries_migration_time = time();
	mysqli_query($mysqli, 'INSERT IGNORE INTO ares_deliveries (ares_email_id, subscriber_id, sent)
		SELECT ares_emails.id, subscribers.id, '.$ares_deliveries_migration_time.'
		FROM ares_emails
		INNER JOIN ares ON ares.id = ares_emails.ares_id
		INNER JOIN subscribers ON subscribers.list = ares.list
		WHERE ares.type = 1 AND ares_emails.time_condition = "immediately"
		AND subscribers.join_date >= ares_emails.created
		AND subscribers.join_date < '.$ares_deliveries_migration_time);
}

	//Composite indexes for large-list count and listing queries.
	$subscriber_indexes = array(
		's_list_status' => 'ADD INDEX s_list_status (list, unsubscribed, bounced, complaint, confirmed)',
		's_list_gdpr_status' => 'ADD INDEX s_list_gdpr_status (list, gdpr, unsubscribed, bounced, complaint, confirmed)',
		's_list_bounced' => 'ADD INDEX s_list_bounced (list, bounced)',
		's_list_unsubscribed_bounced' => 'ADD INDEX s_list_unsubscribed_bounced (list, unsubscribed, bounced)',
		's_list_timestamp_id' => 'ADD INDEX s_list_timestamp_id (list, timestamp, id)'
	);
	$missing_subscriber_indexes = array();
	foreach($subscriber_indexes as $index_name => $alter_clause)
	{
		$q = 'SHOW INDEX FROM subscribers WHERE KEY_NAME = "'.$index_name.'"';
		$r = mysqli_query($mysqli, $q);
		if(!$r || mysqli_num_rows($r) == 0) $missing_subscriber_indexes[] = $alter_clause;
	}
	if(count($missing_subscriber_indexes) > 0)
		mysqli_query($mysqli, 'ALTER TABLE subscribers '.implode(', ', $missing_subscriber_indexes));
	?>
