-- MySQL Script generated by MySQL Workbench
-- 09/11/17 12:20:56
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';


-- -----------------------------------------------------
-- Table `region`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `region` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `location` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `region_id` INT(11) NOT NULL,
  `created_at` INT(11) NULL,
  `created_by` INT(11) NULL,
  `updated_at` INT(11) NULL,
  `updated_by` INT(11) NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `fk_location_region1_idx` (`region_id` ASC),
  CONSTRAINT `fk_location_region1`
    FOREIGN KEY (`region_id`)
    REFERENCES `region` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `cleaner`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `cleaner` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `type` VARCHAR(45) NOT NULL,
  `location_id` INT(11) NOT NULL,
  `owner` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_cleaner_location1_idx` (`location_id` ASC),
  CONSTRAINT `fk_cleaner_location1`
    FOREIGN KEY (`location_id`)
    REFERENCES `location` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `rep`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rep` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `code` VARCHAR(45) NULL DEFAULT NULL,
  `is_active` TINYINT(1) NULL DEFAULT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `master_route`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_route` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `cleaner_id` INT(11) NOT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_master_route_cleaner1_idx` (`cleaner_id` ASC),
  CONSTRAINT `fk_master_route_cleaner1`
    FOREIGN KEY (`cleaner_id`)
    REFERENCES `cleaner` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `route`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `route` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `location_id` INT(11) NOT NULL,
  `master_route_id` INT(11) NOT NULL,
  `start_date` DATE NULL DEFAULT NULL,
  `telephony` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_route_location1_idx` (`location_id` ASC),
  INDEX `fk_route_master_route1_idx` (`master_route_id` ASC),
  CONSTRAINT `fk_route_location1`
    FOREIGN KEY (`location_id`)
    REFERENCES `location` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_route_master_route1`
    FOREIGN KEY (`master_route_id`)
    REFERENCES `master_route` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `customer` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL DEFAULT NULL,
  `rep_id` INT(11) NULL DEFAULT NULL,
  `route_id` INT(11) NOT NULL,
  `address` VARCHAR(45) NULL DEFAULT NULL,
  `city` VARCHAR(45) NULL DEFAULT NULL,
  `state` VARCHAR(45) NULL DEFAULT NULL,
  `zip` VARCHAR(45) NULL DEFAULT NULL,
  `phone` VARCHAR(45) NULL DEFAULT NULL,
  `stop` VARCHAR(45) NULL DEFAULT NULL,
  `first_visit_date` DATE NULL DEFAULT NULL,
  `original_route` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_customer_rep1_idx` (`rep_id` ASC),
  INDEX `fk_customer_route1_idx` (`route_id` ASC),
  CONSTRAINT `fk_customer_rep1`
    FOREIGN KEY (`rep_id`)
    REFERENCES `rep` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_customer_route1`
    FOREIGN KEY (`route_id`)
    REFERENCES `route` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `department`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `department` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created_at` INT NULL,
  `created_by` INT NULL,
  `updated_at` INT NULL,
  `updated_by` INT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT 0,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `drop_location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `drop_location` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `invoice`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoice` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `number` VARCHAR(45) NULL DEFAULT NULL,
  `code` VARCHAR(45) NULL DEFAULT NULL,
  `route_id` INT(11) NULL DEFAULT NULL,
  `department_id` INT(11) NULL DEFAULT NULL,
  `piece_count` INT(11) NULL DEFAULT NULL,
  `promised_time` DATETIME NULL DEFAULT NULL,
  `detailed_time` DATETIME NULL DEFAULT NULL,
  `ready_date` DATETIME NULL DEFAULT NULL,
  `sold_date` DATETIME NULL DEFAULT NULL,
  `order_amount` DECIMAL(10,2) NULL DEFAULT NULL,
  `tax` DECIMAL(10,2) NULL DEFAULT NULL,
  `upcharge` DECIMAL(10,2) NULL DEFAULT NULL,
  `alterations` DECIMAL(10,2) NULL DEFAULT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_invoice_route1_idx` (`route_id` ASC),
  INDEX `fk_invoice_department1_idx` (`department_id` ASC),
  CONSTRAINT `fk_invoice_department1`
    FOREIGN KEY (`department_id`)
    REFERENCES `department` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_invoice_route1`
    FOREIGN KEY (`route_id`)
    REFERENCES `route` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `rep_route`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rep_route` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `rep_id` INT(11) NOT NULL,
  `route_id` INT(11) NOT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_rep_route_rep1_idx` (`rep_id` ASC),
  INDEX `fk_rep_route_route1_idx` (`route_id` ASC),
  CONSTRAINT `fk_rep_route_rep1`
    FOREIGN KEY (`rep_id`)
    REFERENCES `rep` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rep_route_route1`
    FOREIGN KEY (`route_id`)
    REFERENCES `route` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `route_zip`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `route_zip` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `route_id` INT(11) NOT NULL,
  `zip` VARCHAR(45) NOT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_route_zip_route1_idx` (`route_id` ASC),
  CONSTRAINT `fk_route_zip_route1`
    FOREIGN KEY (`route_id`)
    REFERENCES `route` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `shirt`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `shirt` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `starch`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `starch` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `sale_customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sale_customer` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(255) NULL DEFAULT NULL,
  `last_name` VARCHAR(255) NULL DEFAULT NULL,
  `spuse` VARCHAR(45) NULL DEFAULT NULL,
  `rep_id` INT(11) NOT NULL,
  `route_id` INT(11) NOT NULL,
  `drop_location_id` INT(11) NOT NULL,
  `shirt_id` INT(11) NOT NULL,
  `starch_id` INT(11) NOT NULL,
  `street_number` VARCHAR(45) NULL DEFAULT NULL,
  `street` VARCHAR(255) NULL DEFAULT NULL,
  `city` VARCHAR(255) NULL DEFAULT NULL,
  `state` VARCHAR(45) NULL DEFAULT NULL,
  `zip` VARCHAR(45) NULL DEFAULT NULL,
  `phone` VARCHAR(45) NULL DEFAULT NULL,
  `note` TEXT NULL DEFAULT NULL,
  `is_pushed_once` TINYINT(1) NULL DEFAULT NULL,
  `is_pushed_twice` TINYINT(1) NULL DEFAULT NULL,
  `is_cancelled` TINYINT(1) NULL DEFAULT NULL,
  `rating` INT(11) NULL DEFAULT NULL,
  `is_started` TINYINT(1) NULL DEFAULT NULL,
  `is_confirmed` TINYINT(1) NULL DEFAULT NULL,
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` INT(11) NULL DEFAULT NULL,
  `created_by` INT(11) NULL DEFAULT NULL,
  `updated_at` INT(11) NULL DEFAULT NULL,
  `updated_by` INT(11) NULL DEFAULT NULL,
  `is_deleted` TINYINT(1) NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `fk_sale_customer_drop_location_idx` (`drop_location_id` ASC),
  INDEX `fk_sale_customer_shirt1_idx` (`shirt_id` ASC),
  INDEX `fk_sale_customer_starch1_idx` (`starch_id` ASC),
  INDEX `fk_sale_customer_rep1_idx` (`rep_id` ASC),
  INDEX `fk_sale_customer_route1_idx` (`route_id` ASC),
  CONSTRAINT `fk_sale_customer_drop_location`
    FOREIGN KEY (`drop_location_id`)
    REFERENCES `drop_location` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_customer_rep1`
    FOREIGN KEY (`rep_id`)
    REFERENCES `rep` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_customer_shirt1`
    FOREIGN KEY (`shirt_id`)
    REFERENCES `shirt` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_customer_starch1`
    FOREIGN KEY (`starch_id`)
    REFERENCES `starch` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_sale_customer_route1`
    FOREIGN KEY (`route_id`)
    REFERENCES `route` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
