CREATE TABLE `dblocations`(  
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(4) NOT NULL,
  `description` VARCHAR(200),
  PRIMARY KEY (`id`)
);

CREATE TABLE `devicetypes`(  
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(4) NOT NULL,
  `description` VARCHAR(200),
  PRIMARY KEY (`id`)
);


CREATE TABLE `aboutus`(  
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(4) NOT NULL,
  `description` VARCHAR(200),
  PRIMARY KEY (`id`)
);


CREATE TABLE `workorders`(
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `iddblocation` BIGINT(20) UNSIGNED NOT NULL,
  `code` VARCHAR(20) NOT NULL,
  `date` DATETIME,
  `estimateddate` DATE,
  `name` VARCHAR(200) NOT NULL,
  `phone` VARCHAR(100),
  `email` VARCHAR(250),
  `idaboutus` BIGINT(20) UNSIGNED NOT NULL,
  `otherdescription` VARCHAR(200),
  `iddevicetype` BIGINT(20) UNSIGNED NOT NULL,
  `model` VARCHAR(200),
  `serial` VARCHAR(100),
  `charger` BIT,
  `description` VARCHAR(500),
  `password` VARCHAR(100),
  `lockcode` BLOB,
  `picture` BLOB,
  `created` DATETIME NOT NULL,
  `modified` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);


ALTER TABLE `workorders`   
  ADD  INDEX `fk_workorder_dblocation` (`iddblocation`),
  ADD  INDEX `fk_workorder_aboutus` (`idaboutus`),
  ADD  INDEX `fk_workorder_devicetype` (`iddevicetype`),
  ADD CONSTRAINT `fk_workorder_dblocation` FOREIGN KEY (`iddblocation`) REFERENCES `dblocations`(`id`),
  ADD CONSTRAINT `fk_workorder_aboutus` FOREIGN KEY (`idaboutus`) REFERENCES `aboutus`(`id`),
  ADD CONSTRAINT `fk_workorder_devicetype` FOREIGN KEY (`iddevicetype`) REFERENCES `devicetypes`(`id`);

ALTER TABLE `dblocations`   
  ADD  INDEX `ix_code` (`code`);

ALTER TABLE `aboutus`   
  ADD  INDEX `ix_code` (`code`);


ALTER TABLE `devicetypes`   
  ADD  INDEX `ix_code` (`code`);


ALTER TABLE `workorders`   
  ADD  INDEX `ix_code` (`code`);



INSERT INTO dblocations(`code`, `description`)
VALUES ('0001', 'DB Location 1')
;

INSERT INTO aboutus(`code`, `description`)
VALUES 
('01', 'Repeat Client'),
('02', 'Bing'),
('03', 'World of mouth referral'),
('04', 'Google'),
('05', 'Yahoo'),
('06', 'Walk-in '),
('07', 'Google Maps'),
('08', 'Yelp'),
('99', 'Other')
;

INSERT INTO devicetypes(`code`, `description`)
VALUES 
('01', 'Laptop'),
('02', 'PC'),
('03', 'Phone'),
('04', 'Tablet'),
('99', 'Other')
;

