DROP TABLE IF EXISTS `currencycode`;
CREATE TABLE `currencycode` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(5) NOT NULL DEFAULT '',
  `name` varchar(50) NOT NULL DEFAULT '',
  `symbol` varchar(5) NOT NULL DEFAULT '',
  `isDefault` TINYINT(1) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `createdById` int(10) UNSIGNED NOT NULL DEFAULT 0 ,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updatedById` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `code` (`code`) USING BTREE,
  INDEX `name` (`name`) USING BTREE,
  INDEX `symbol` (`symbol`) USING BTREE,
  INDEX `isDefault` (`isDefault`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO currencycode (id, code, name, symbol, isDefault, active, created, createdById, updated, updatedById)
  VALUES (1, 'AUD', 'Australian Dollar', '$', 1, 1, UTC_TIMESTAMP(), '10', UTC_TIMESTAMP(), '10'),
  (2, 'USD', 'United State Dollar', '$', 0, 0, UTC_TIMESTAMP(), '10', UTC_TIMESTAMP(), '10'),
  (3, 'CNY', 'Chinese Yuan', '¥', 0, 0, UTC_TIMESTAMP(), '10', UTC_TIMESTAMP(), '10');

ALTER TABLE `purchaseorder` ADD COLUMN `currencyCodeId` int(10) NOT NULL DEFAULT 1 AFTER `comments`;
ALTER TABLE `purchaseorder` ADD COLUMN `currencyRate` varchar(20) NULL DEFAULT '' AFTER `currencyCodeId`;

ALTER TABLE `purchaseorder` DROP INDEX `eta`;
ALTER TABLE `purchaseorder` DROP INDEX `CNYAmount`;
ALTER TABLE `purchaseorder` DROP COLUMN `eta`;
ALTER TABLE `purchaseorder` DROP COLUMN `CNYAmount`;

ALTER TABLE ecommerceinfo ADD COLUMN `storeId` INT(10) NOT NULL DEFAULT 1 AFTER `id`, ADD INDEX(`storeId`);
DROP INDEX `username` ON `ecommerceinfo`;
CREATE INDEX `username` ON `ecommerceinfo`(`username`) USING BTREE ;
DROP INDEX `email` ON `ecommerceinfo`;
CREATE INDEX `email` ON `ecommerceinfo`(`email`) USING BTREE ;

ALTER TABLE productextracost ADD COLUMN `receivingItemId` INT(10) NULL DEFAULT NULL AFTER `extraCost`, ADD INDEX(`receivingItemId`);
