update `storetype` set name = 'Company' where id = 1;
INSERT INTO `storetype` (name, active, created, createdById, updated, updatedById) VALUES ('Store Warehouse', 1, UTC_TIMESTAMP(), 10, UTC_TIMESTAMP(), 10);

ALTER TABLE preferredlocationtype
  RENAME TO `warehouseunit`;

update `warehouseunit` set name = 'Dandenong', description = 'Dandenong' where id = 1;
INSERT INTO `warehouseunit` (name, description, active, created, createdById, updated, updatedById) VALUES ('Leventon', 'Leventon', 1, UTC_TIMESTAMP(), 10,UTC_TIMESTAMP(), 10);

ALTER TABLE `store`
ADD COLUMN `warehouseunitId` int(10) DEFAULT NULL AFTER `typeId`;

ALTER TABLE `store`
ADD COLUMN `isCanCreateOrder` tinyint(1) NOT NULL DEFAULT 0 AFTER `warehouseunitId`;

ALTER TABLE `store`
ADD COLUMN `isCanCreatePo` tinyint(1) NOT NULL DEFAULT 0 AFTER `isCanCreateOrder`;

update `store` set name = 'Scafeast Dandenong', rootStoreId = 4, typeId = 5, warehouseunitId = 1, isCanCreateOrder = 1, isCanCreatePo = 1 where id = 1;

INSERT INTO `store` (id, name, rootStoreId, typeId, warehouseunitId, isCanCreateOrder, isCanCreatePo, active, created, createdById, updated, updatedById) VALUES (3, 'Scafeast Leventon', 4, 5, 2, 1, 1, 1, UTC_TIMESTAMP(), 10, UTC_TIMESTAMP(), 10);
INSERT INTO `store` (id, name, rootStoreId, typeId, warehouseunitId, isCanCreateOrder, isCanCreatePo, active, created, createdById, updated, updatedById) VALUES (4, 'Scafeast', null, 1, null, 1, 1, 1, UTC_TIMESTAMP(), 10, UTC_TIMESTAMP(), 10);

update `store` set rootStoreId = 4 where id = 2;

create table `storeprofile`(
    `id`  int(10) NOT NULL AUTO_INCREMENT ,
    `storeId` int(10) NOT NULL UNIQUE,
    `customerId` int(10) DEFAULT NULL ,
    `supplierId` int(10) DEFAULT NULL ,
    `orderPrefix` varchar(10) NOT NULL DEFAULT '',
    `invPrefix` varchar(10) NOT NULL DEFAULT '',
    `poPrefix` varchar(10) NOT NULL DEFAULT '',
    `logoURL` varchar(255) NOT NULL DEFAULT '',
    `ABN` varchar(20) NOT NULL DEFAULT '',
    `bankAccountName` varchar(50) NOT NULL DEFAULT '',
    `bankAccountNumber` varchar(10) NOT NULL DEFAULT '',
    `bankBSB` varchar(10) NOT NULL DEFAULT '',
    `active`  tinyint(1) NOT NULL DEFAULT 1 ,
    `created`  datetime NOT NULL DEFAULT '0001-01-01 00:00:00' ,
    `createdById`  int(10) NOT NULL DEFAULT 0 ,
    `updated`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
    `updatedById`  int(10) NOT NULL DEFAULT 0 ,
    PRIMARY KEY (`id`),
    INDEX `customerId` (`customerId`) USING BTREE ,
    INDEX `supplierId` (`supplierId`) USING BTREE ,
    INDEX `orderPrefix` (`orderPrefix`) USING BTREE ,
    INDEX `invPrefix` (`invPrefix`) USING BTREE ,
    INDEX `logoURL` (`logoURL`) USING BTREE ,
    INDEX `ABN` (`ABN`) USING BTREE ,
    INDEX `bankAccountName` (`bankAccountName`) USING BTREE ,
    INDEX `bankAccountNumber` (`bankAccountNumber`) USING BTREE ,
    INDEX `bankBSB` (`bankBSB`) USING BTREE ,
    INDEX `active` (`active`) USING BTREE ,
    INDEX `created` (`created`) USING BTREE ,
    INDEX `createdById` (`createdById`) USING BTREE ,
    INDEX `updated` (`updated`) USING BTREE ,
    INDEX `updatedById` (`updatedById`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=Compact;

INSERT INTO `storeprofile` (storeId, customerId, supplierId, orderPrefix, invPrefix, poPrefix, logoURL, ABN, bankAccountName, bankAccountNumber, bankBSB, active, created, createdById, updated, updatedById) VALUES (4, 2, 1, 'SBS', 'INVS', 'SBSP', '/themes/default/images/Scafeast.png', '55 611 871 151' ,'Scafeast', '1133 0915', '063182', 1, UTC_TIMESTAMP(), 10, UTC_TIMESTAMP(), 10);


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, 1, UTC_TIMESTAMP(), '10', UTC_TIMESTAMP(), '10'),
  (3, 'CNY', 'Chinese Yuan', '¥', 0, 1, 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`);

ALTER TABLE `stocktransfer` ADD COLUMN `status` varchar(20) NOT NULL DEFAULT '' AFTER `totalAmount`;
CREATE INDEX `status` ON `stocktransfer`(`status`) USING BTREE;

ALTER TABLE `stocktransfer` ADD COLUMN `transferType` varchar(50) NOT NULL DEFAULT '' AFTER `status`;
CREATE INDEX `transferType` ON `stocktransfer`(`transferType`) USING BTREE;

ALTER TABLE `stocktransferitem` ADD COLUMN `pickedQty` float(11,4) unsigned DEFAULT 0 AFTER `qty`;
CREATE INDEX `pickedQty` ON `stocktransferitem`(`pickedQty`) USING BTREE;


ALTER TABLE `orderitem` ADD `isAutoCreatedByStockTransfer` TINYINT(1) NULL DEFAULT '0' AFTER `invoiceQty`;
ALTER TABLE `orderitem` ADD INDEX(`isAutoCreatedByStockTransfer`);


ALTER TABLE `receivingitem` ADD `isAutoCreatedByStockTransfer` TINYINT(1) NOT NULL DEFAULT '0' AFTER `syncedWithXero`;
ALTER TABLE `receivingitem` ADD INDEX(`isAutoCreatedByStockTransfer`);

ALTER TABLE `productqtylog` ADD `statusId` int(10) unsigned DEFAULT NULL after `type`;
ALTER TABLE `productqtylog` ADD INDEX(`statusId`);

INSERT INTO systemsettings (type, value, description, active, created, createdById, updated, updatedById)
VALUES ('new_customer_default_is_company', 'false', 'Whether default is a company on creating new customer', '1', UTC_TIMESTAMP, '10', UTC_TIMESTAMP, '10');

INSERT INTO tasktypes (name, isDefault, active, created, createdById, updated, updatedById)
VALUES ('Deal Activity Remainder', 1, 1, UTC_TIMESTAMP, '10', UTC_TIMESTAMP, '10');

ALTER TABLE `customer` DROP INDEX `customerInstructions`;
ALTER TABLE `customer` MODIFY COLUMN `customerInstructions` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL AFTER `hasCustomerInstruction`;

ALTER TABLE kit
ADD COLUMN `installedIntoKitComponentId` int(10) NULL DEFAULT null AFTER `autocreated`;
CREATE INDEX `installedIntoKitComponentId` ON `kit`(`installedIntoKitComponentId`) USING BTREE;

ALTER TABLE kitcomponent
  ADD COLUMN `totalCost` float(14,4) NOT NULL DEFAULT 0 AFTER `unitCost`;
CREATE INDEX `totalCost` ON `kitcomponent`(`totalCost`) USING BTREE;

update kitcomponent
set totalCost = qty * unitCost, updated = updated;

ALTER TABLE `customer` ADD COLUMN `type` json NULL DEFAULT NULL AFTER `email`;
ALTER TABLE `customer` ADD COLUMN `region` json NULL DEFAULT NULL AFTER `type`;

ALTER TABLE storeprofile
ADD COLUMN `website` varchar(50) NOT NULL DEFAULT '' AFTER `bankBSB`;
CREATE INDEX `website` ON `storeprofile`(`website`) USING BTREE;

ALTER TABLE warehouseunit
  ADD COLUMN `addressId` int(10) NULL DEFAULT null AFTER `description`;
CREATE INDEX `addressId` ON `warehouseunit`(`addressId`) USING BTREE;

insert into `partinstancestatus` (id, name, active, created, createdById, updated, updatedById) VALUES (10, 'IN TRANSFER', 1, UTC_TIMESTAMP(), 10, UTC_TIMESTAMP(), 10);

ALTER TABLE stocktransferitem
ADD `serialNos` varchar(1000) NOT NULL DEFAULT '' after `partInstanceIds`;
