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';

CREATE DATABASE IF NOT EXISTS `central_impact` DEFAULT CHARACTER SET latin1 ;
USE `central_impact`;

-- -----------------------------------------------------
-- Table `central_impact`.`invoices`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `central_impact`.`invoices` (
  `id_invoices` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `order_number` INT NOT NULL ,
  `modification_timestamp` TIMESTAMP NOT NULL ,
  `creation_timestamp` TIMESTAMP NOT NULL ,
  `creation_date` DATE NOT NULL ,
  `cust_acc_number` INT UNSIGNED NOT NULL ,
  `cust_po_number` CHAR(25) NOT NULL DEFAULT '' ,
  `online_creation_date` DATETIME NOT NULL ,
  `created_by` CHAR(45) NOT NULL ,
  `source` CHAR(45) NOT NULL DEFAULT "YStore" ,
  `sub_total` DOUBLE NOT NULL ,
  `inv_total` DOUBLE NOT NULL ,
  `ahipping_cost` DOUBLE NOT NULL ,
  `status` ENUM("New Order","Cancel","Complete","Press Complete")) NOT NULL DEFAULT "Press Complete" ,
  `sub_status` CHAR(255) NOT NULL ,
  `sub_status_internal` CHAR(255) NOT NULL ,
  `shipper` ENUM("UPS","Fedex") NOT NULL DEFAULT "UPS" ,
  `service` CHAR(45) NOT NULL ,
  `tracking` CHAR(100) NOT NULL ,
  `shipped_date` DATE NOT NULL ,
  `bill_name` CHAR(100) NOT NULL ,
  `bill_company` CHAR(100) NOT NULL ,
  `bill_phone` CHAR(50) NOT NULL ,
  `bill_address_1` CHAR(100) NOT NULL ,
  `bill_address_2` CHAR(100) NOT NULL ,
  `bill_city` CHAR(100) NOT NULL ,
  `bill_st` CHAR(10) NOT NULL ,
  `bill_zip` CHAR(10) NOT NULL ,
  `bill_country` CHAR(100) NOT NULL ,
  `ship_name` CHAR(100) NOT NULL ,
  `ship_company` CHAR(100) NOT NULL ,
  `ship_phone` CHAR(50) NOT NULL ,
  `ship_address_1` CHAR(100) NOT NULL ,
  `ship_address_2` CHAR(100) NOT NULL ,
  `ship_city` CHAR(100) NOT NULL ,
  `ship_st` CHAR(10) NOT NULL ,
  `ship_zip` CHAR(10) NOT NULL ,
  `ship_country` CHAR(100) NOT NULL ,
  `main_category` ENUM("ORDER","RMA") NOT NULL DEFAULT "ORDER" ,
  `order_xid` CHAR(50) NOT NULL ,
  `discount_amount` INT NOT NULL ,
  PRIMARY KEY (`id_invoices`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `central_impact`.`invoices_seo`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `central_impact`.`invoices_seo` (
  `id_invoices_seo` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `invoices_id_invoices` INT UNSIGNED NULL ,
  `fk_invoices_id_invoices` INT NOT NULL ,
  `referer_url` CHAR(255) NOT NULL ,
  `entry_point` CHAR(255) NOT NULL ,
  PRIMARY KEY (`id_invoices_seo`) ,
  INDEX fk_invoices_seo_invoices (`invoices_id_invoices` ASC) ,
  CONSTRAINT `fk_invoices_seo_invoices`
    FOREIGN KEY (`invoices_id_invoices` )
    REFERENCES `central_impact`.`invoices` (`id_invoices` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `central_impact`.`shipment_options`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `central_impact`.`shipment_options` (
  `id_shipment_options` INT NOT NULL AUTO_INCREMENT ,
  `invoices_id_invoices` INT UNSIGNED NOT NULL ,
  `fk_invoices_id_invoices` INT NOT NULL ,
  `sat_delivery` ENUM("Y","N") NOT NULL DEFAULT "N" ,
  `special_instructions` CHAR(100) NOT NULL ,
  `secure_payment_COD` ENUM("Y","N") NOT NULL DEFAULT "N" ,
  `reference_2` CHAR(100) NOT NULL ,
  `shipper_account` CHAR(20) NOT NULL ,
  `shipping_billing_option` ENUM("Prepaid","Freight Collect","3rd Party") NOT NULL DEFAULT "Prepaid" ,
  `cod_option` ENUM("Y","N") NOT NULL DEFAULT "N" ,
  PRIMARY KEY (`id_shipment_options`) ,
  INDEX fk_shipment_options_invoices (`invoices_id_invoices` ASC) ,
  CONSTRAINT `fk_shipment_options_invoices`
    FOREIGN KEY (`invoices_id_invoices` )
    REFERENCES `central_impact`.`invoices` (`id_invoices` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



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