
alter table product ADD `ifTrackable` tinyint(1) NOT NULL DEFAULT 0 AFTER `ifManageStock`, ADD INDEX (`ifTrackable`);
alter table store ADD `typeId` int(10) unsigned NOT NULL AFTER `name`, ADD INDEX (`typeId`);


alter table store ADD `rootStoreId` int(10) DEFAULT NULL AFTER `name`, ADD INDEX (`rootStoreId`);


update `store` set typeId = 1, rootStoreId = null where id = 1;
insert into `store` (name, typeId, rootStoreId, created, createdById, updated, updatedById) VALUES ('Hiring Warehouse', 2, 1, '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);
insert into `store` (name, typeId, rootStoreId, created, createdById, updated, updatedById) VALUES ('Service Stock', 3, 1, '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);
insert into `store` (name, typeId, rootStoreId, created, createdById, updated, updatedById) VALUES ('Van1', 4, 1, '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);
insert into `store` (name, typeId, rootStoreId, created, createdById, updated, updatedById) VALUES ('Van2', 4, 1, '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);



CREATE TABLE `stocktransfer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stockLocationFromId` int(10) NOT NULL DEFAULT '1',
  `stockLocationToId` int(10) NOT NULL DEFAULT '1',
  `stockTransferNo` varchar(50) NOT NULL DEFAULT '',
  `stockTransferDate` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `totalAmount` Double(10, 4) unsigned NOT NULL DEFAULT '0.0000',
  `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`),
  KEY `stockLocationFromId` (`stockLocationFromId`),
  KEY `stockLocationToId` (`stockLocationToId`),
  KEY `stockTransferDate` (`stockTransferDate`),
  KEY `totalAmount` (`totalAmount`),
  UNIQUE INDEX (`stockTransferNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `stocktransferitem` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stockTransferId` int(10) NOT NULL DEFAULT '0',
  `productId` int(10) NOT NULL DEFAULT '0',
  `qty` Double(11, 4) unsigned NOT NULL DEFAULT '0.0000',
  `unitCost` Double(10, 4) unsigned NOT NULL DEFAULT '0.0000',
  `totalAmount` Double(10, 4) unsigned NOT NULL DEFAULT '0.0000',
  `itemDescription` text NOT NULL,
  `partInstanceIds` varchar(255) NOT NULL DEFAULT '',
  `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`),
  KEY `stockTransferId` (`stockTransferId`),
  KEY `productId` (`productId`),
  KEY `qty` (`qty`),
  KEY `unitCost` (`unitCost`),
  KEY `totalAmount` (`totalAmount`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `storetype` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '',
  `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`),
  KEY `name` (`name`),
  KEY `createdById` (`createdById`),
  KEY `updatedById` (`updatedById`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into `storetype` (name, created, createdById, updated, updatedById) VALUES('Store Warehouse', '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);
insert into `storetype` (name, created, createdById, updated, updatedById) VALUES('Hiring Warehouse', '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);
insert into `storetype` (name, created, createdById, updated, updatedById) VALUES('Service Stock', '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);
insert into `storetype` (name, created, createdById, updated, updatedById) VALUES('Vehicle Stock', '2019-10-08 00:00:00', 10, '2019-10-08 00:00:00', 10);




CREATE TABLE `partinstance`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `productId` int(10) DEFAULT NULL,
  `receivingItemId` int(10) DEFAULT NULL,
  `sellingItemId` int(10) DEFAULT NULL,
  `ownerId` int(10) DEFAULT NULL,
  `serialNo` varchar(100) NOT NULL DEFAULT '',
  `assetNo` varchar(20) NOT NULL DEFAULT '',
  `hiringStatusId` int(10) DEFAULT NULL,
  `sku` varchar(80) DEFAULT '',
  `name` varchar(100) DEFAULT '',
  `belongedStoreId` int(10) DEFAULT NULL,
  `location` varchar(255) DEFAULT '',
  `model` varchar(100) DEFAULT '',
  `warrantyDate` datetime DEFAULT NULL,
  `extraInfo` varchar(255) DEFAULT '',
  `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`),
  KEY `serialNo` (`serialNo`),
  KEY `sku` (`sku`),
  KEY `name` (`name`),
  KEY `assetNo` (`assetNo`),
  KEY `model` (`model`),
  KEY `warrantyDate` (`warrantyDate`),
  KEY `productId` (`productId`),
  KEY `ownerId` (`ownerId`),
  KEY `belongedStoreId` (`belongedStoreId`),
  KEY `hiringStatusId` (`hiringStatusId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



