CREATE TABLE `vaninfo`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `storeId` int(10) NOT NULL DEFAULT '0',
  `useraccountId` int(10) DEFAULT NULL,
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `servicejob`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `jobNo` varchar(50) NOT NULL UNIQUE default '',
    `customerId` int(10) NOT NULL DEFAULT '0',
    `contactName` varchar(50) NOT NULL DEFAULT '',
    `contactNo` varchar(50) NOT NULL DEFAULT '',
    `email` varchar(150) NOT NULL DEFAULT '',
    `useraccountId` int(10) DEFAULT NULL,
    `dueDate` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    `instruction` varchar(1000) NOT NULL DEFAULT '',
    `priorityId` int(10) NOT NULL DEFAULT '0',
    `quotefirst` tinyint(1) NOT NULL DEFAULT 0,
    `vanId` int(10) DEFAULT NULL,
    `address` varchar(255) NOT NULL DEFAULT '',
    `typeId` int(10) NOT NULL DEFAULT '0',
    `statusId` int(10) 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`),
    KEY `customerId` (`customerId`),
    KEY `contactName` (`contactName`),
    KEY `contactNo` (`contactNo`),
    KEY `email` (`email`),
    KEY `address` (`address`),
    KEY `useraccountId` (`useraccountId`),
    KEY `dueDate` (`dueDate`),
    KEY `priorityId` (`priorityId`),
    KEY `vanId` (`vanId`),
    KEY `typeId` (`typeId`),
    KEY `statusId` (`statusId`),
    KEY `quotefirst` (`quotefirst`),
    UNIQUE INDEX (`jobNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `jobstatus`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL UNIQUE 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`),
    UNIQUE INDEX (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `jobpriority`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL UNIQUE default '',
    `risklevel` int(2) unsigned 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`),
    UNIQUE INDEX (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `jobtype`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL UNIQUE 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`),
    UNIQUE INDEX (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `visit`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `userAccountId` int(10) NOT NULL DEFAULT '0',
    `statusId` int(10) NOT NULL DEFAULT '0',
    `vanId` int(10) NOT NULL DEFAULT '0',
    `startTime` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    `endTime` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    `comments` varchar(255) DEFAULT '',
    `serviceJobId` int(10) 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`),
    KEY `useraccountId` (`useraccountId`),
    KEY `statusId` (`statusId`),
    KEY `vanId` (`vanId`),
    KEY `startTime` (`startTime`),
    KEY `endTime` (`endTime`),
    KEY `serviceJobId` (`servicejobId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `visitstatus`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL UNIQUE 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`),
    UNIQUE INDEX (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `usedpart`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `productId` int(10) NOT NULL DEFAULT '0',
    `serviceJobId` int(10) NOT NULL DEFAULT '0',
    `vanId` int(10) NOT NULL DEFAULT '0',
    `qty` Double(11, 4) unsigned NOT NULL DEFAULT '0.0000',
    `value` double(10,4) unsigned NOT NULL DEFAULT '0.0000',
    `visitId` int(10) DEFAULT NULL,
    `partInstanceId` int(10) DEFAULT NULL,
    `jobitemId` int(10) DEFAULT NULL,
    `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 `qty` (`qty`),
    KEY `productId` (`productId`),
    KEY `visitId` (`visitId`),
    KEY `jobitemId` (`jobitemId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `jobitem`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `jobId` int(10) NOT NULL DEFAULT '0',
    `partInstanceId` int(10) NOT NULL DEFAULT '0',
    `comments` 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 `jobId` (`jobId`),
    KEY `partInstanceId` (`partInstanceId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `visititem`(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `jobItemId` int(10) NOT NULL DEFAULT '0',
    `visitId` int(10) NOT NULL DEFAULT '0',
    `comments` 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 `jobItemId` (`jobItemId`),
    KEY `visitId` (`visitId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into `jobstatus` (id, name, created, createdById, updated, updatedById) VALUES (1, 'NEW', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `jobstatus` (id, name, created, createdById, updated, updatedById) VALUES (2, 'OPEN', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `jobstatus` (id, name, created, createdById, updated, updatedById) VALUES (3, 'WIP', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `jobstatus` (id, name, created, createdById, updated, updatedById) VALUES (4, 'CLOSED', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `jobstatus` (id, name, created, createdById, updated, updatedById) VALUES (5, 'COMPLETED', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
-- insert into `jobstatus` (name, created, createdById, updated, updatedById) VALUES ('ON HOLD', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
-- insert into `jobstatus` (name, created, createdById, updated, updatedById) VALUES ('PROCESSING', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
-- insert into `jobstatus` (name, created, createdById, updated, updatedById) VALUES ('FINISHED', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
-- insert into `jobstatus` (name, created, createdById, updated, updatedById) VALUES ('CANCELLED', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);


insert into `jobtype` (name, created, createdById, updated, updatedById) VALUES ('Maintenance', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `jobtype` (name, created, createdById, updated, updatedById) VALUES ('Repair', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);

insert into `jobpriority` (name, risklevel, created, createdById, updated, updatedById) VALUES ('LOW', 2, '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `jobpriority` (name, risklevel, created, createdById, updated, updatedById) VALUES ('MID', 1, '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `jobpriority` (name, risklevel, created, createdById, updated, updatedById) VALUES ('HIGH', 0, '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);

insert into `visitstatus` (name, created, createdById, updated, updatedById) VALUES ('processing', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
insert into `visitstatus` (name, created, createdById, updated, updatedById) VALUES ('end', '2019-11-30 00:00:00', 10, '2019-11-30 00:00:00', 10);
