--
-- Host: localhost    Database: transitall
-- ------------------------------------------------------


--
-- Table structure for table `broker`
--

CREATE TABLE `broker` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company` varchar(255) DEFAULT NULL,
  `address1` varchar(255) DEFAULT NULL,
  `address2` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `zip` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `fax` varchar(255) DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `broker`
--

INSERT INTO `broker` VALUES (1,'Broker Company 1','Broker Address 1','Broker Address 2','Broker City','CA','90210','5555555555','5555555555','Some Broker Notes');

--
-- Table structure for table `contact`
--

CREATE TABLE `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `broker_id` int(11) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `ext` varchar(255) DEFAULT NULL,
  `cell` varchar(255) DEFAULT NULL,
  `fax` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`id`),
  KEY `fk_contactBroker` (`broker_id`),
  CONSTRAINT `fk_contactBroker` FOREIGN KEY (`broker_id`) REFERENCES `broker` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `contact`
--

INSERT INTO `contact` VALUES (1,1,'Angela','Breaker','6666666666','666','6666666666','6666666666','angela@transitall.com','Angela notes');

--
-- Table structure for table `driver`
--

CREATE TABLE `driver` (
  `id` char(3) NOT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `citizenship` varchar(255) DEFAULT NULL,
  `fastID` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `cell` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `driver`
--

INSERT INTO `driver` VALUES ('001','Jerry','Rollins','Male','1980-01-09','Canadian','','jerry@transitall.com','5555555555','5555555555');

--
-- Table structure for table `file`
--

CREATE TABLE `file` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `file`
--

INSERT INTO `file` VALUES (1,'1401901038_Test PDF File.pdf'),(2,'1401901095_Test PDF File.pdf'),(3,'1406647212_ttttt.pdf');

--
-- Table structure for table `fileLink`
--

CREATE TABLE `fileLink` (
  `file_id` int(11) DEFAULT NULL,
  `load_id` int(11) DEFAULT NULL,
  KEY `fk_fileLink_file` (`file_id`),
  KEY `fk_fileLink_load` (`load_id`),
  CONSTRAINT `fk_fileLink_load` FOREIGN KEY (`load_id`) REFERENCES `load` (`id`),
  CONSTRAINT `fk_fileLink_file` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fileLink`
--

INSERT INTO `fileLink` VALUES (1,1),(2,1),(3,2);

--
-- Table structure for table `load`
--

CREATE TABLE `load` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contact_id` int(11) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `confID` varchar(255) DEFAULT NULL,
  `puCompany` varchar(255) DEFAULT NULL,
  `puAddress` varchar(255) DEFAULT NULL,
  `puCity` varchar(255) DEFAULT NULL,
  `puState` varchar(255) DEFAULT NULL,
  `puDatetime` datetime DEFAULT NULL,
  `puID` varchar(255) DEFAULT NULL,
  `puNotes` text,
  `commodity` varchar(255) DEFAULT NULL,
  `pieces` int(11) DEFAULT NULL,
  `weight` int(11) DEFAULT NULL,
  `rateCurrency` varchar(255) DEFAULT NULL,
  `rate` decimal(10,0) DEFAULT NULL,
  `customs` varchar(255) DEFAULT NULL,
  `customsPhone` varchar(255) DEFAULT NULL,
  `customsFax` varchar(255) DEFAULT NULL,
  `delCompany` varchar(255) DEFAULT NULL,
  `delAddress` varchar(255) DEFAULT NULL,
  `delCity` varchar(255) DEFAULT NULL,
  `delState` varchar(255) DEFAULT NULL,
  `delDatetime` datetime DEFAULT NULL,
  `delNotes` text,
  `trip_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_loadContact` (`contact_id`),
  CONSTRAINT `fk_loadContact` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `load`
--

INSERT INTO `load` VALUES (1,1,'FTL','C1','PuComanyNam','PuAddress','PuCity','AS','2014-06-12 14:50:00','123','Some notes for PU','',NULL,123,'CAD',NULL,'customs','','','DelCompany','DelAddress','DelCity','DA','2014-06-12 18:30:00','Some notes',2),(2,1,'FTL','132','tst','test','test','te','2014-07-17 15:50:00','test','test','test',2,24,'CAD',4,'test','2353223552','1231231231','test','test','test','te','2014-07-10 14:50:00','te',NULL);

--
-- Table structure for table `migration`
--

CREATE TABLE `migration` (
  `version` varchar(180) NOT NULL,
  `apply_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `migration`
--

INSERT INTO `migration` VALUES ('m000000_000000_base',1401900270),('m130524_201442_init',1401900271),('m140520_215434_initial_schema',1401900271),('m140729_142604_load_columns',1406646801),('m140729_145821_drop_indexes',1406646801);

--
-- Table structure for table `trailer`
--

CREATE TABLE `trailer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `use` varchar(255) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `length` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `axle` varchar(255) DEFAULT NULL,
  `floor` varchar(255) DEFAULT NULL,
  `door` varchar(255) DEFAULT NULL,
  `rate` decimal(10,0) DEFAULT NULL,
  `puAddress` varchar(255) DEFAULT NULL,
  `puCity` varchar(255) DEFAULT NULL,
  `puState` varchar(255) DEFAULT NULL,
  `puDate` date DEFAULT NULL,
  `delAddress` varchar(255) DEFAULT NULL,
  `delCity` varchar(255) DEFAULT NULL,
  `delState` varchar(255) DEFAULT NULL,
  `delDate` date DEFAULT NULL,
  `notes` text,
  `trip_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `trailer`
--

INSERT INTO `trailer` VALUES (1,'One-Way','Available','53','Van','Single','Wood','Swing',NULL,'PuAddress','PuCity','SA',NULL,'DAddress','DelCity','DA','2014-06-09','Some notes',2),(2,'One-Way','Available','53','Van','Single','Wood','Swing',NULL,'test','Test','TE',NULL,'test','Test','te','2014-07-30','',NULL);

--
-- Table structure for table `trip`
--

CREATE TABLE `trip` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `truck_id` char(3) DEFAULT NULL,
  `driver_id` char(3) DEFAULT NULL,
  `trailer_id` int(11) DEFAULT NULL,
  `load_id` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tripTruck` (`truck_id`),
  KEY `fk_tripDriver` (`driver_id`),
  KEY `fk_tripTrailer` (`trailer_id`),
  KEY `fk_tripLoad` (`load_id`),
  CONSTRAINT `fk_tripDriver` FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`),
  CONSTRAINT `fk_tripLoad` FOREIGN KEY (`load_id`) REFERENCES `load` (`id`),
  CONSTRAINT `fk_tripTrailer` FOREIGN KEY (`trailer_id`) REFERENCES `trailer` (`id`),
  CONSTRAINT `fk_tripTruck` FOREIGN KEY (`truck_id`) REFERENCES `truck` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `trip`
--

INSERT INTO `trip` VALUES (1,'001','001',1,1,'En Route To PU'),(2,'001','001',1,1,'Delivered');

--
-- Table structure for table `tripStatus`
--

CREATE TABLE `tripStatus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trip_id` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tripStatus_trip` (`trip_id`),
  KEY `fk_tripStatus_user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tripStatus`
--

INSERT INTO `tripStatus` VALUES (1,1,'Draft','2014-06-04 17:01:41',2),(2,1,'Driver Dispatched','2014-06-04 17:01:50',2),(3,1,'En Route To PU','2014-06-05 04:30:02',1),(4,2,'Draft','2014-07-23 14:15:08',1),(5,3,'Draft','2014-07-29 15:13:44',1),(6,1,'En Route To PU','2014-07-29 15:16:50',1),(7,2,'En Route To PU','2014-07-29 15:16:56',1),(8,2,'Loaded','2014-07-29 15:17:07',1),(9,2,'Delivered','2014-07-29 15:17:49',1);

--
-- Table structure for table `truck`
--

CREATE TABLE `truck` (
  `id` char(3) NOT NULL,
  `vin` varchar(255) DEFAULT NULL,
  `make` varchar(255) DEFAULT NULL,
  `colour` varchar(255) DEFAULT NULL,
  `transponder` varchar(255) DEFAULT NULL,
  `plate` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `truck`
--

INSERT INTO `truck` VALUES ('001','12345678901234567','Man','Black','1234567890123456','CA0009');


