#################
# 28th April 2011
# Luis
# made Message field larger to handle long errors coming back from CMT.

ALTER TABLE `xoomdblive`.`xoommessage` MODIFY COLUMN `Message` VARCHAR(500) CHARACTER SET latin1 COLLATE latin1_swedish_ci
, AUTO_INCREMENT = 2047543;


#################
# 12th July 2011
# Luis
# IDcountry was missing in local DB, but in use on LIVE DB (sync error) - thus I recreated in local DB.

ALTER TABLE `xoomdblive`.`iddatabase` ADD COLUMN `IDCountry` VARCHAR(100) AFTER `IDnumber`;


#################
# 12th July 2011
# Luis
# added 2 new columns for the UK new SOURCE OF FUNDS threshold mechanism.

ALTER TABLE `xoomdblive`.`parametersx` ADD COLUMN `SourceThresholdDAYS` INTEGER DEFAULT 0 AFTER `TRN_US`,
 ADD COLUMN `SourceThresholdAMOUNT` DOUBLE DEFAULT 0 AFTER `SourceThresholdDAYS`;

#################
# 22nd December 2011
# Luis
# added column for Recipient occupation when amount > j$400k
ALTER TABLE `xoomdblive`.`xoomcashlog` ADD COLUMN `Occupation` VARCHAR(100) AFTER `ChequePayee`;

####################################################################
####################################################################
# 2012 - all changes file/db from now onwards are documented.
# Luis
####################################################################
####################################################################


#################
# March 2012
# Luis
# added 2 new database-columns for the xoomDBLIVE/XoomDB & XoomdbBACKUP tables for the new batch file bank export mechanism.

ALTER TABLE `xoomdblive`.`xoomdb` ADD COLUMN `accType` VARCHAR(50) AFTER `accCurrency`,
 ADD COLUMN `accExportedByBatch` INTEGER DEFAULT 0 AFTER `accType`;

ALTER TABLE `xoomdblive`.`xoomdbBACKUP` ADD COLUMN `accType` VARCHAR(50) AFTER `accCurrency`,
 ADD COLUMN `accExportedByBatch` INTEGER DEFAULT 0 AFTER `accType`;
 
# added 1 new database-column for the xoomDBLIVE/recipientDB table for the new batch file bank export mechanism.

ALTER TABLE `xoomdblive`.`recipientDB` ADD COLUMN `RecipientBankAccType` VARCHAR(50) AFTER `RecipientBankAccNumber`;

# added 1 new table to track batch numbers
# 000000000000000000000000000000000000000
# 000000000000000 DEPRECATED 000000000000
# 000000000000000000000000000000000000000
# CREATE TABLE `xoomdblive`.`VM_Batch_Tracker` (
#  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
#  `yymmdd` VARCHAR(6) NOT NULL,
#  `counter` INTEGER UNSIGNED DEFAULT 0,
#  PRIMARY KEY (`ID`, `yymmdd`, `counter`)
# )
# ENGINE = InnoDB;


#################
# May 2012
# Luis
# added new column to store which bank batch file a transaction was exported in.

ALTER TABLE `xoomdblive`.`xoomdb` ADD COLUMN `BankBatchExport` VARCHAR(8) NOT NULL AFTER `xoom_TU_Version`;

# created basic table to log Batch creation; activity etc.

CREATE TABLE `xoomdblive`.`BankBatchLog` (
  `LogID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `BatchName` VARCHAR(8) NOT NULL,
  `BatchTimeStamp` TIMESTAMP NOT NULL,
  PRIMARY KEY (`LogID`)
)
ENGINE = InnoDB;

#################
# June 2012
# Luis
# added new column to store if a bank transaction has been approved.
ALTER TABLE `xoomdblive`.`xoomdb` ADD COLUMN `bank_approved` BOOLEAN DEFAULT 0 AFTER `BankBatchExport`;

#################
# June 2012
# Luis
# added new table for SMS_QUEUE system
CREATE TABLE  `xoomdblive`.`sms_queue` (
  `sms_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sms_number` varchar(45) NOT NULL,
  `sms_message` varchar(160) NOT NULL,
  `sms_carrier` varchar(45) DEFAULT NULL,
  `sms_status` int(10) unsigned NOT NULL DEFAULT '0',
  `sms_recipient` varchar(255) NOT NULL,
  `sms_invoice` varchar(100) NOT NULL,
  `sms_insert_date` varchar(45) NOT NULL DEFAULT '0000-00-00',
  `sms_send_date` varchar(45) NOT NULL DEFAULT '0000-00-00',
  `sms_code_source` varchar(45) NOT NULL,
  PRIMARY KEY (`sms_ID`,`sms_status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;


#################
# June 2012
# Luis
# Added new search index for XoomDB
ALTER TABLE `xoomdblive`.`xoomdb` ADD INDEX `searchIndex2`(`txn_type`, `createdgmt`, `statusnum`);
ALTER TABLE `xoomdblive`.`recipientdb` ADD INDEX `searchIndex`(`RecipientAccountNumber`);
ALTER TABLE `xoomdblive`.`recipientdb_non_vm` ADD INDEX `searchIndex`(`non_vm_RecipientAccountNumber`);
ALTER TABLE `xoomdblive`.`recipientxoombankdb` ADD INDEX `searchIndex`(`BankRecipientAuth`);

#################
# June 2012
# Luis
# Added column for bank verification + INDEX
ALTER TABLE `xoomdblive`.`xoomdb` ADD COLUMN `bank_verified` BOOLEAN NOT NULL DEFAULT 0 AFTER `xoom_TU_Version`;
ALTER TABLE `xoomdblive`.`xoomdb` ADD INDEX `searchIndex3`(`bank_approved`);

#################
# July 2012
# Luis
# Added new search index for SMS messages.
ALTER TABLE `xoomdblive`.`xoomdb` ADD INDEX `searchIndex_SMS`(`InboundCompany`, `statusnum`, `xoom_sms_processed`);

# Added sms_txn_type column to support non-cash transactions
ALTER TABLE `xoomdblive`.`sms_queue` ADD COLUMN `sms_txn_type` SMALLINT UNSIGNED NOT NULL DEFAULT 3 AFTER `sms_code_source`, ROW_FORMAT = DYNAMIC;

# Added key for SMS search (with txn_types)
ALTER TABLE `xoomdblive`.`sms_queue` ADD INDEX `Index_SMS`(`sms_status`,`sms_txn_type`,`sms_insert_date`) USING BTREE;

#################
# July 2012
# Luis
# Added Choice_Coms_Queue table to process outgoing messages to Choice

CREATE TABLE  `xoomdblive`.`choice_coms_queue` (
  `ch_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ch_type` varchar(45) NOT NULL,
  `ch_status` smallint(5) unsigned NOT NULL DEFAULT '0',
  `ch_txnid` varchar(45) NOT NULL,
  `ch_createdgmt` datetime NOT NULL,
  `ch_processedgmt` datetime DEFAULT NULL,
  `ch_message` varchar(500) DEFAULT NULL,
  `ch_failMessage` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`ch_id`),
  KEY `Index_search_id` (`ch_txnid`),
  KEY `Index_status_type` (`ch_status`,`ch_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#################
# July 2012
# Luis
# added flags for messages to support CHOICE
ALTER TABLE `xoomdblive`.`xoommessage` ADD COLUMN `FromCHOICE` TINYINT(1) NOT NULL DEFAULT 0 AFTER `ToXoom`,
 ADD COLUMN `ToCHOICE` TINYINT(1) NOT NULL DEFAULT 0 AFTER `FromCHOICE`;