# --------------------------------------------------------
# VERSION UPDATING
# --------------------------------------------------------
UPDATE `setting` set value='2.7.0 beta1' WHERE name='ClientExec Version';

# -------------------------------------------------------------------------------
# ADDITIONAL STYLE SETTING
# -------------------------------------------------------------------------------
INSERT INTO `setting` VALUES (NULL, 'Use Effects', '0', 'Selecting Yes will enable effects in ClientExec for both administrators and end users', 2, 1, 1, 0, 0, 5, 0, 0, 0, 0);

INSERT INTO `customuserfields` VALUES (NULL, 'ViewSideBar', 0, 0, 1, 0, 20, 0, 0, 0, 0, 0, '');

# -------------------------------------------------------------------------------
# COUPONS
# -------------------------------------------------------------------------------

CREATE TABLE `coupons` (
  `coupons_id` int(11) NOT NULL auto_increment,
  `coupons_name` varchar(50) NOT NULL default '',
  `coupons_description` text NOT NULL,
  `coupons_code` varchar(50) NOT NULL default '',
  `coupons_quantity` int(11) NOT NULL default '0',
  `coupons_type` tinyint(4) NOT NULL default '0',
  `coupons_discount` float NOT NULL default '0',
  `coupons_start` date NOT NULL default '0000-00-00',
  `coupons_expires` date NOT NULL default '0000-00-00',
  `coupons_recurring` tinyint(4) NOT NULL default '0',
  `coupons_billingcycles` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`coupons_id`)
) ENGINE=MyISAM;

CREATE TABLE `coupons_packages` (
  `id` int(11) NOT NULL auto_increment,
  `coupons_id` int(11) NOT NULL default '0',
  `promotion_id` int(11) NOT NULL default '0',
  `package_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `setting` ( `id` , `name` , `value` , `description` , `type` , `isrequired` , `istruefalse` , `istextarea` , `isfromoptions` , `myorder` , `helpid` , `plugin` , `ispassword` , `ishidden` ) VALUES ( NULL, 'Accept Coupons', '1', 'Select YES to enable the coupons functionality.', '10', '1', '1', '0', '0', '0', '0', '0', '0', '0');

INSERT INTO `billingtype` ( `id` , `name` , `description` , `detail` ) VALUES ('-3', 'Discount', 'Discount', 'Discount generated by a discount coupon entered at signup');

# -------------------------------------------------------------------------------
# CHANGES To Invoice Transactions
# -------------------------------------------------------------------------------
ALTER TABLE `invoicetransaction`
ADD `transactionid` VARCHAR( 25 ) DEFAULT 'NA' NOT NULL,
ADD `action` VARCHAR( 10 ) DEFAULT 'none' NOT NULL,
ADD `last4` VARCHAR( 5 ) DEFAULT '0000' NOT NULL,
ADD `amount` FLOAT DEFAULT '0.0' NULL;

# -------------------------------------------------------------------------------
# ASYNCHRONOUS SERVICES LOG
# -------------------------------------------------------------------------------
CREATE TABLE `asyncservices_log` (
  `id` int(11) NOT NULL auto_increment,
  `plugin` varchar(50) NOT NULL default '',
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `manually_executed` smallint(6) NOT NULL default '0',
  `results` text NOT NULL,
  `errors` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

# -------------------------------------------------------------------------------
# MAIL QUEUE ASYNCHRONOUS SERVICE
# -------------------------------------------------------------------------------
CREATE TABLE `email_queue` (
  `id` int(11) NOT NULL auto_increment,
  `subject` varchar(255) NOT NULL default '',
  `from` varchar(50) NOT NULL default '',
  `from_name` varchar(50) NOT NULL default '',
  `userid` text NOT NULL,
  `bcc` varchar(50) NOT NULL default '',
  `priority` tinyint(4) NOT NULL default '0',
  `confirmreceipt` tinyint(4) NOT NULL default '0',
  `emailtype` varchar(50) NOT NULL default '',
  `body` text NOT NULL,
  `striptags` tinyint(4) NOT NULL default '0',
  `nl2br` tinyint(4) NOT NULL default '0',
  `dfilename` varchar(50) NOT NULL default '',
  `attachment` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `help` VALUES (8, 'Run schedule format', 'In the Run Schedule fields you can enter any format accepted by the Cron utility. For example for the field Minute: <ul> <li>* : every minute <li>Number: e.g. 4 will make it run at :04 <li>Range: e.g. 1-3 will make it run at :01, :02, :03 <li>List: e.g. 1,3,7 will make it run at :01, :03, :07 <li>Step: e.g. */3 will make it run every third minute <li>Range and step: 0-30/2 will make it run every other minute till :30 </ul> In Day of the Week you can enter a number in the range 0-7 (0 for Sunday) or a 3 letter shortcut (e.g. sun).', 'Click to view schedule format', 240, 420);

# ---------------------------------
# ADD CHECK NUMBER TO INVOICE TABLE
# ---------------------------------

ALTER TABLE `invoice` ADD `checknum` VARCHAR( 10 ) NOT NULL ;

# -----------------------------------------
# ADD DIRECTI US DOMAIN TO REGISTRARS TABLE
#------------------------------------------

INSERT INTO `registrars` VALUES ('directi', 'us', 'a:2:{s:8:"us_nexus";a:3:{s:2:"ID";s:1:"9";s:11:"description";s:14:"Nexus Category";s:7:"options";a:5:{s:10:"US Citizen";a:2:{s:11:"description";s:36:"A natural person who is a US Citizen";s:5:"value";s:3:"C11";}s:18:"Permanent Resident";a:2:{s:11:"description";s:44:"A natural person who is a Permanent Resident";s:5:"value";s:3:"C12";}s:15:"Business Entity";a:2:{s:11:"description";s:100:"An entity or organization that is (i) incorporated within one of the fifty US states, the District o";s:5:"value";s:3:"C21";}s:14:"Foreign Entity";a:3:{s:11:"description";s:100:"A foreign organization that regularly engages in lawful activities (sales of goods or services or ot";s:5:"value";s:3:"C31";s:8:"requires";a:1:{i:0;s:2:"14";}}s:15:"US Based Office";a:3:{s:11:"description";s:100:"An organization has an office or other facility in the U.S., where CC equals to the county code of t";s:5:"value";s:3:"C32";s:8:"requires";a:1:{i:0;s:2:"14";}}}}s:10:"us_purpose";a:3:{s:2:"ID";s:2:"10";s:11:"description";s:19:"Application Purpose";s:7:"options";a:5:{s:10:"For Profit";a:2:{s:11:"description";s:23:"Business use for profit";s:5:"value";s:2:"P1";}s:10:"Non-profit";a:2:{s:11:"description";s:68:"Non-profit business, club, association, religious organization, etc.";s:5:"value";s:2:"P2";}s:8:"Personal";a:2:{s:11:"description";s:12:"Personal use";s:5:"value";s:2:"P3";}s:11:"Educational";a:2:{s:11:"description";s:20:"Educational purposes";s:5:"value";s:2:"P4";}s:10:"Government";a:2:{s:11:"description";s:19:"Government purposes";s:5:"value";s:2:"P5";}}}}');

# -----------------------
# CHANGES TO DOMAIN TABLE
# -----------------------

ALTER TABLE `domains` ADD `register` TINYINT NOT NULL DEFAULT '0' ;
ALTER TABLE `domains` ADD `signup` TINYINT NOT NULL DEFAULT '0';

# --------------------------------------------------------
# SETTINGS UPGRADES
# --------------------------------------------------------

INSERT INTO `setting` VALUES ( NULL , 'Show MaxMind Logo', '1', 'Setting this to yes will show the maxmind fraud screening logo in the signup footer if credit card fraud detection or phone verification is turned on.', '12', '1', '1', '0', '0', '0', '0', '0', '0', '0');
INSERT INTO `setting` VALUES ( NULL , 'New Trouble Ticket Autoresponder Template', '[CLIENTNAME],\r\n\r\nThank you for submitting a ticket with [COMPANYNAME].  Your request has been assigned the ticket number [TICKETNUMBER].  A technician will respond to your request shortly.\r\n\r\nMember Center:\r\n[CLIENTAPPLICATIONURL]\r\n\r\nThank you,\r\n[COMPANYNAME]', 'Template for emails that get sent out to clients after a new ticket is added by email through the support pipe.', '7', '1', '0', '1', '0', '0', '6', '0', '0', '0');
INSERT INTO `setting` VALUES (NULL, 'Application Error Notification', '', 'Enter an email address if you want to be notified when a catchable error occurs in your ClientExec installation.', 1, 0, 0, 0, 0, 2, 0, 0, 0, 0);
INSERT INTO `setting` VALUES (NULL, 'Custom Logout URL', '', 'Enter the url you want clients to be redirected to after logging out.<br><b>NOTE:</b> Leave this field blank to use the login page.', 1, 0, 0, 0, 0, 2, 0, 0, 0, 0);
INSERT INTO `setting` VALUES (NULL, 'Allow Customer File Uploads', '0', 'Set this to yes if you wish to allow customers to upload files to support tickets.', 8, 1, 1, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO `setting` VALUES (NULL, 'Allowed File Extensions', 'jpg,jpeg,gif,zip,txt,log', 'Enter the file extensions that you allow uploaded to support tickets separated by commas.  * means allow all file extensions(security risk).', 8, 0, 0, 0, 0, 0, 0, 0, 0, 0);

# --------------------------------------------------------
# ADD BITMASK
# --------------------------------------------------------
ALTER TABLE `users` ADD `warningmask` INT NOT NULL DEFAULT 0;

# --------------------------------------------------------
# ADDED INDEX IN DOMAINS TABLE
# --------------------------------------------------------
ALTER TABLE `domains` ADD INDEX ( `CustomerID` );

# --------------------------------------------------------
# ADDED BALANCE IN USERS TABLE
# --------------------------------------------------------
ALTER TABLE `users` ADD `balance` FLOAT DEFAULT '0' NOT NULL ;

# --------------------------------------------------------
# ADDED PLUGIN USED TO PAY TO INVOICE TABLE
# --------------------------------------------------------
ALTER TABLE `invoice` ADD `pluginused` VARCHAR( 15 ) DEFAULT 'none' NOT NULL AFTER `processorid` ;

# -------------------------------------------------------------
# SERVERS-PACKAGES LINKING AND ROUND-ROBIN SERVER UTILIZATION
# -------------------------------------------------------------
CREATE TABLE `package_server` (
  `id` int(11) NOT NULL auto_increment,
  `package_id` int(11) NOT NULL default '0',
  `server_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

ALTER TABLE `server` ADD `last_utilization` DATETIME NOT NULL ;
ALTER TABLE `server` ADD `domains_quota` INT NOT NULL ;

# -----------------------------------------------------
# SUPPORT TICKET FILES
# -----------------------------------------------------
CREATE TABLE `troubleticket_files` (
  `id` int(11) NOT NULL auto_increment,
  `ticketid` int(11) NOT NULL,
  `filename` varchar(50) NOT NULL,
  `dateadded` datetime NOT NULL,
  `filekey` varchar(16) NOT NULL,
  `userid` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `filekey` (`filekey`)
) ENGINE=MyISAM;

# ----------------------------------------------------
# ALTER TEXT FIELDS TO DEFAULT TO NULL
# ----------------------------------------------------
ALTER TABLE `users` CHANGE `data1` `data1` TEXT NULL DEFAULT NULL ;
ALTER TABLE `users` CHANGE `data2` `data2` TEXT NULL DEFAULT NULL ;
ALTER TABLE `users` CHANGE `usernotes` `usernotes` TEXT NULL DEFAULT NULL ;
ALTER TABLE `recurringfee` CHANGE `description` `description` TEXT NULL DEFAULT NULL ;
ALTER TABLE `recurringfee` CHANGE `detail` `detail` TEXT NULL DEFAULT NULL ;
ALTER TABLE `domains` CHANGE `server_acct_properties` `server_acct_properties` TEXT NULL DEFAULT NULL ;
ALTER TABLE `domains` CHANGE `domain_extra_attr` `domain_extra_attr` TEXT NULL DEFAULT NULL ;

# -----------------------------------------------------
# OVERDUE INVOICE TEMPLATE FOR THE REBILLER SERVICE
# -----------------------------------------------------
INSERT INTO `setting` VALUES (NULL , 'Overdue Invoice Template', 'Due Date: [DATE]\nInvoice # [INVOICENUMBER]\n\nATTN: [CLIENTNAME],\n\nYou currently have an overdue balance of [AMOUNT] due on your account with [COMPANYNAME].\n\nInvoice Description:\n[INVOICEDESCRIPTION]\nTotal Due: [AMOUNT]\n\nPayment Instructions:\n1) Log into our client management site at [CLIENTAPPLICATIONURL] with your username and password\n2) Click on the "Billing" tab\n3) Select the invoice you want to pay\n4) Click on pay invoice\n\nWe greatly appreciate your efforts to ensure that payment is received in a timely matter.\n\nIf you have any questions regarding your account or if you wish to pay by check, please feel free to contact us.\n\nNote: This email will serve as an official invoice for this payment.\n\n[COMPANYNAME]\n[BILLINGEMAIL]', 'Email template used when notifying customer of outstanding invoices using the rebiller service.', '7', '1', '0', '1', '0', '0', '2', '0', '0', '0');

# -----------------------------------------------------
# ADDITIONAL FIELD FOR USERS
# -----------------------------------------------------
ALTER TABLE `users` ADD `data3` TEXT DEFAULT NULL AFTER `data2` ;

# -----------------------------------------------------
# ADDED WARNING FOR CHARSET SETTING
# -----------------------------------------------------
UPDATE `setting` SET `description` = 'Please enter the charset you want to use in your application. WARNING: If you change this, be sure to change the database connection charset accordingly (modify the database configuration file or startup options).' WHERE `name` = 'Character Set';

# -----------------------------------------------------
# ADD INDEX TO INVOICE TABLE
# -----------------------------------------------------
ALTER TABLE `invoice` ADD INDEX ( `customerid` ) ;
