<?php 
#	Core database update fcodes
#	Do not modify this file
#	Version:  3.0
#	Release date: 2020-04-01
#   Add new changes above the old ones
global $server;
#3.0.9
mysqli_query($server,"UPDATE `country_codes` SET `code` = '+51' WHERE `id` = 142");

//v3.0
mysqli_query($server,"UPDATE `api_templates` SET `setup_text` = 'Contact your SMPP gateway for the correct parameters to use here. Please note that this template may not be compatible with all SMPP providers' WHERE `title` = 'Custom SMPP Gateway';");
mysqli_query($server,"CREATE INDEX index_meslist2 on `messagedetails` (`language`, gateway_id, `date`, `cost`, customer_id, `datetime`);");
mysqli_query($server,"CREATE INDEX index_seist2 on `sentmessages` (gateway_id, `date`, `customer_id`);");
mysqli_query($server,"CREATE TABLE `whitlisted_api_ips` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `ip` VARCHAR(3000) NOT NULL , 
 `customer_id` INT NOT NULL  DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = 'stores whitelisted IP address pr API calls';");
mysqli_query($server,"CREATE INDEX index_whitlisted_api_ips on `whitlisted_api_ips` (`ip`, customer_id);");
mysqli_query($server,"INSERT INTO `country_codes` (`id`, `title`, `code`) VALUES (NULL, '+876 Jamaica', '+876');");

//v2.6.0
mysqli_query($server,"UPDATE `country_codes` SET `title` = '+82 Korea South' WHERE `country_codes`.`id` = 95;");

#v2.3.0
mysqli_query($server,"ALTER TABLE `messagedetails` ADD INDEX(`datetime`);");
mysqli_query($server,"INSERT INTO `language` (`alias`, `title`, `enabled`) VALUES
('ar', 'Arabic', 1);");
mysqli_query($server,"INSERT INTO `language` (`alias`, `title`, `enabled`) VALUES
('hi', 'Hindi', 1);");

#v2.2.2
mysqli_query($server,"ALTER TABLE `users` ADD `localsession_id` VARCHAR(100) NOT NULL DEFAULT '0' AFTER `email`;");
#v2.2.0
mysqli_query($server,"ALTER TABLE `users` ADD `billingModel` VARCHAR(100) NOT NULL DEFAULT '' AFTER `email`;");
mysqli_query($server,"ALTER TABLE `users` ADD `advancedUnitPricing` VARCHAR(3000) NOT NULL DEFAULT '' AFTER `billingModel`;");

#v2.1.1
mysqli_query($server,"DELETE FROM `country_codes` WHERE `title` = '+1 US Minor Outlying Islands';");
mysqli_query($server,"DELETE FROM `country_codes` WHERE `title` = '+1 U.S. Virgin Islands';");
mysqli_query($server,"INSERT INTO `country_codes` (`id`, `title`, `code`) VALUES (NULL, '+1 US Minor Outlying Islands', '+1'), (NULL, '+1 U.S. Virgin Islands', '+1');");
mysqli_query($server,"UPDATE `countries` SET `country_name` = 'US Minor Outlying Islands' WHERE `countries`.`id` = 234;");
mysqli_query($server,"ALTER TABLE `api_templates` ADD `unicode_encode` VARCHAR(30) NOT NULL DEFAULT 'none' AFTER `title`;");
mysqli_query($server,"ALTER TABLE `sms_gateways` ADD `unicode_encode` VARCHAR(30) NOT NULL DEFAULT 'none' AFTER `title`;");
mysqli_query($server,"UPDATE `country_codes` SET `title` = '+243 Democratic Republic of Congo', `code` = '+243' WHERE `id` = 37;
");
mysqli_query($server,"UPDATE `countries` SET `country_name` = 'Democratic Republic of Congo' WHERE `id` = 41;");

#v2.1.0
mysqli_query($server,"CREATE TABLE `pending_contact` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `filename` VARCHAR(3000) NOT NULL , 
`status` VARCHAR(30) NOT NULL DEFAULT 'queued' , 
`phonebooks` INT NOT NULL DEFAULT '0' , 
`marketinglist` INT NOT NULL  DEFAULT '0',
`customer_id` INT NOT NULL  DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = 'stores contact uploads';");
mysqli_query($server,"CREATE TABLE `admin_roles` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `admin_roles` VARCHAR(6000) NOT NULL , 
 `customer_id` INT NOT NULL  DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = 'stores admin roles';");

#v2.0.7
mysqli_query($server,"UPDATE `country_codes` SET `title` = '+242 Republic of the Congo', `code` = '+2420' WHERE `id` = 38;");
mysqli_query($server,"DELETE FROM `countries` WHERE `country_name` = 'Republic of the Congo';");
mysqli_query($server,"INSERT INTO `countries` (`country_code`, `country_name`, `id`) VALUES ('CG', 'Republic of the Congo', NULL);");

#v2.0.6
mysqli_query($server,"DELETE FROM `api_templates` WHERE `title` = 'Twilio Voice Gateway';");
mysqli_query($server,"INSERT INTO `api_templates` (`title`, `base_url`, `success_word`, `json_encode`, `request_type`, `sender_field`, `recipient_field`, `message_field`, `param1_field`, `param2_field`, `param3_field`, `param4_field`, `type`, `language_field`, `audio_field`, `timeout_field`, `speed_field`, `authentication`, `success_logic`, `dlr_enabled`, `dlr_callback`, `alias`, `param5_field`, `param5_label`, `param4_label`, `param3_label`, `param2_label`, `param1_label`, `tts_enabled`, `file_enabled`, `module_id`, `status`, `base64_encode`, `setup_text`) VALUES ('Twilio Voice Gateway', 'https://api.twilio.com/2010-04-01', 'queued', 1, 'POST', 'From', 'To', 'Url', '', '', '', '', 'voice', '', 'Url', '', '', '1', 'contain', 0, '', 'twilio', '', '', '', '', '', 'Url', 0, 0, '0', 1, 1, 'You will need to signup with https://twilio.com to use this gateway. <br>Use your Twilio SID as Username and Token as Password on this page to setup your gateway');");

#v2.0.4
mysqli_query($server,"ALTER TABLE `scheduledmessages` CHANGE `phonebook_id` `phonebook_id` VARCHAR(2500) NOT NULL DEFAULT '0', CHANGE `marketinglist_id` `marketinglist_id` VARCHAR(2500) NOT NULL DEFAULT '0';");

#v2.0.2
mysqli_query($server,"ALTER TABLE `users` ADD `uservar` VARCHAR(100) NOT NULL DEFAULT '' AFTER `email`;");
mysqli_query($server,"ALTER TABLE `users` CHANGE `email` `email` VARCHAR(300) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, CHANGE `address` `address` VARCHAR(522) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, CHANGE `email_verified` `email_verified` VARCHAR(333) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL, CHANGE `language_id` `language_id` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'en', CHANGE `affiliate_id` `affiliate_id` VARCHAR(331) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;");
#v2.0.1
mysqli_query($server,"CREATE INDEX index_contact_list on `contacts` (first_name, last_name, phone, phonebook_id);");
mysqli_query($server,"CREATE INDEX index_marketingcontact_list on `marketingcontacts` (name, marketinglist, phone);");
mysqli_query($server,"CREATE INDEX index_messagedetails_list on `messagedetails` (message_id, recipient, sender_id, `status`, type);");
mysqli_query($server,"CREATE INDEX index_sentm_list on `sentmessages` (to_count, sender_id, `status`, type);");
mysqli_query($server,"CREATE INDEX index_contact_list on `phonebooks` (title, customer_id);");
mysqli_query($server,"CREATE INDEX index_transactions_list on `transactions` (transaction_reference, `status`, credits, cost);");
mysqli_query($server,"CREATE INDEX index_tickets_list on `tickets` (admin_reply, customer_reply, parent_id, subject);");

#v 2.0.0
if(empty(getSetting('TextEnabled',0))) {
  saveSettings('TextEnabled',1,0);
}
mysqli_query($server,"ALTER TABLE `scheduledmessages` ADD `language` VARCHAR(222) NOT NULL DEFAULT 'en' AFTER `retry`;");
mysqli_query($server,"ALTER TABLE `messagedetails` ADD `language` VARCHAR(222) NOT NULL DEFAULT 'en' AFTER `message`;");
mysqli_query($server,"ALTER TABLE `sentmessages` ADD `language` VARCHAR(111) NOT NULL DEFAULT 'en' AFTER `message`;");
mysqli_query($server,"ALTER TABLE `tickets` ADD `media` VARCHAR(555) NOT NULL AFTER `last_updated`;");

mysqli_query($server,"DELETE FROM `api_templates` WHERE `title` = 'Custom SMPP Gateway';");
mysqli_query($server,"INSERT INTO `api_templates` (`title`, `base_url`, `success_word`, `json_encode`, `request_type`, `sender_field`, `recipient_field`, `message_field`, `param1_field`, `param2_field`, `param3_field`, `param4_field`, `type`, `language_field`, `audio_field`, `timeout_field`, `speed_field`, `authentication`, `success_logic`, `dlr_enabled`, `dlr_callback`, `alias`, `param5_field`, `param5_label`, `param4_label`, `param3_label`, `param2_label`, `param1_label`, `tts_enabled`, `file_enabled`, `module_id`, `status`, `base64_encode`, `setup_text`) VALUES ('Custom SMPP Gateway', '', 'ok', 0, 'POST', 'from', 'to', 'message', 'port', '', '', '', 'sms', '', '', '', '', '1', 'contain', 0, '', 'smpp', '', '', '', '', '', 'SMPP Port', 0, 0, '0', 1, 0, 'Contact your SMS gateway for the correct parameters to use here');");


#v 1.8.7
mysqli_query($server,"ALTER TABLE `users` ADD `account_manager` int(11) NOT NULL DEFAULT '0' AFTER `referrer`;");
mysqli_query($server,"ALTER TABLE `users` ADD `birthday` varchar(22) NOT NULL DEFAULT '0' AFTER `account_manager`;");

#v 1.7.4
mysqli_query($server,"ALTER TABLE `currencies` ADD `zeros` INT NOT NULL DEFAULT '2' AFTER `code`;");

#v1.7.3
mysqli_query($server,"UPDATE `paymentgateway_templates` SET `param1_label` = 'Publishable Key' WHERE `alias` = 'stripe';");
mysqli_query($server,"UPDATE `paymentgateway_templates` SET `param1_labe2` = 'Secret Key' WHERE `alias` = 'stripe';");


#v 1.7.0
mysqli_query($server,"CREATE TABLE `message_types` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `title` VARCHAR(300) NOT NULL , 
`alias` VARCHAR(300) NOT NULL , 
`enabled` INT NOT NULL DEFAULT '1' , 
`defaut_gateway` INT NOT NULL  DEFAULT '0',
`is_custom` INT NOT NULL  DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = 'stores message types';");
mysqli_query($server,"ALTER TABLE `shortcodes` ADD `type` VARCHAR(444) NOT NULL DEFAULT 'sms' AFTER `resold`;");
mysqli_query($server,"ALTER TABLE `sms_gateways` ADD `group_id` INT NOT NULL DEFAULT '1' AFTER `base64_encode`;");
mysqli_query($server,"ALTER TABLE `sms_gateways` ADD `d_rate` VARCHAR(5055) NOT NULL DEFAULT '0' AFTER `group_id`;");


#v 1.6.0
//add custom fields tabloes
mysqli_query($server,"CREATE TABLE `customfields` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `field_type` varchar(333) NOT NULL DEFAULT 'Text Box',
  `required` varchar(33) NOT NULL DEFAULT 'No',
  `show_form` varchar(33) NOT NULL DEFAULT '0',
  `enabled` varchar(33) NOT NULL DEFAULT '1',
  `name` varchar(444) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
mysqli_query($server,"ALTER TABLE `customfields`  ADD PRIMARY KEY (`id`);");
mysqli_query($server,"ALTER TABLE `customfields`  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;");

mysqli_query($server,"CREATE TABLE `customfield_values` (
  `id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `value` varchar(444) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;  ");
mysqli_query($server,"ALTER TABLE `customfield_values` ADD PRIMARY KEY (`id`); ");
mysqli_query($server,"ALTER TABLE `customfield_values` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; ");
  

#	v 1.4.2
//add new settings field
//set label names for custom module
mysqli_query($server,"UPDATE `api_templates` SET `param1_label` = 'Parameter 1 Value' WHERE `alias` = 'custom';");
mysqli_query($server,"UPDATE `api_templates` SET `param2_label` = 'Parameter 2 Value' WHERE `alias` = 'custom';");
mysqli_query($server,"UPDATE `api_templates` SET `param3_label` = 'Parameter 3 Value' WHERE `alias` = 'custom';");
mysqli_query($server,"UPDATE `api_templates` SET `param4_label` = 'Parameter 4 Value' WHERE `alias` = 'custom';");
mysqli_query($server,"UPDATE `api_templates` SET `param5_label` = 'Parameter 5 Value' WHERE `alias` = 'custom';");

#  v 1.4.1
mysqli_query($server,"ALTER TABLE `smschat` DROP `id`;");
mysqli_query($server,"ALTER TABLE `smschat`  ADD `id` INT NOT NULL AUTO_INCREMENT  FIRST,  ADD   PRIMARY KEY  (`id`);");


#   Verson 1.4.0
//Alter scheduled message structure to add new features from 1.4.0
mysqli_query($server,"ALTER TABLE `scheduledmessages` ADD `repeats` VARCHAR(30) NOT NULL DEFAULT '0' AFTER `to_count`, ADD `day` VARCHAR(30) NOT NULL AFTER `repeats`;");
//Create structure for sms chat
mysqli_query($server,"CREATE TABLE `smschat` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL DEFAULT '0',
  `sender` varchar(30) NOT NULL,
  `receiver` varchar(30) NOT NULL,
  `message` varchar(666) NOT NULL,
  `date` varchar(44) NOT NULL,
  `sent` INT NOT NULL DEFAULT '0',
  `number` VARCHAR(44) NOT NULL,
  `seen` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;");


#   Verson 1.3.2
//correct cron job instructions
mysqli_query($server,"DROP TABLE IF EXISTS `crons`;");
mysqli_query($server,"CREATE TABLE `crons` (
  `id` int(11) NOT NULL,
  `description` varchar(666) NOT NULL,
  `location` varchar(555) NOT NULL,
  `period` varchar(333) NOT NULL DEFAULT 'Once per day'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;");

mysqli_query($server,"TRUNCATE TABLE `crons`;");
mysqli_query($server,"INSERT INTO `crons` (`id`, `description`, `location`, `period`) VALUES
(1, 'System Hour Cron', 'cronsH.php', 'Once per Hour'),
(2, 'System Minute Cron', 'crons.php', 'Once per Minute'),
(3, 'Infobip Gateway DLR Cron', 'smsapi/infobip/cron.php', 'Once per Minute');");
?>