-- MySQL Script generated by MySQL Workbench
-- Mon Nov 16 21:23:16 2020
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema pride_db
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema pride_db
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `pride_db` DEFAULT CHARACTER SET utf8 ;
USE `pride_db` ;

-- -----------------------------------------------------
-- Table `pride_db`.`dealer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pride_db`.`dealer` (
  `id_dealer` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `posible_names` VARCHAR(500) NOT NULL,
  PRIMARY KEY USING BTREE (`id_dealer`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
ROW_FORMAT = DYNAMIC;


-- -----------------------------------------------------
-- Table `pride_db`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pride_db`.`user` (
  `id_user` INT(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `password` VARCHAR(150) NOT NULL,
  `token` VARCHAR(150) NULL,
  `name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `active` VARCHAR(45) NOT NULL DEFAULT '1',
  `vendor_name` VARCHAR(50) NULL,
  PRIMARY KEY USING BTREE (`id_user`),
  INDEX `id_vendor` USING BTREE (`vendor_id`) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
ROW_FORMAT = DYNAMIC;


-- -----------------------------------------------------
-- Table `pride_db`.`submission`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pride_db`.`submission` (
  `id_submission` INT(11) NOT NULL AUTO_INCREMENT,
  `status` VARCHAR(45) NOT NULL,
  `result` VARCHAR(500) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id_user` INT(11) NOT NULL,
  PRIMARY KEY USING BTREE (`id_submission`),
  INDEX `fk_log_user1_idx` (`id_user` ASC) VISIBLE,
  CONSTRAINT `fk_log_user1`
    FOREIGN KEY (`id_user`)
    REFERENCES `pride_db`.`user` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
ROW_FORMAT = DYNAMIC;


-- -----------------------------------------------------
-- Table `pride_db`.`invoice_line_item`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pride_db`.`invoice_line_item` (
  `id_invoice_line_item` INT(11) NOT NULL AUTO_INCREMENT,
  `invoice_date` DATE NOT NULL,
  `amount` FLOAT NOT NULL,
  `date_paid` DATE NOT NULL,
  `dealer_name` VARCHAR(45) NOT NULL,
  `extended_line_cost` FLOAT NOT NULL,
  `rebateable_flag` BIT(1) NULL DEFAULT NULL,
  `vendor_id` VARCHAR(100) NOT NULL,
  `invoice_line_type` VARCHAR(45) NOT NULL,
  `invoice_number` VARCHAR(150) NOT NULL,
  `management_fee_percent` FLOAT NOT NULL,
  `marketing_allowance_percent` FLOAT NOT NULL,
  `marketing_fee` FLOAT NULL,
  `note` VARCHAR(45) NOT NULL,
  `po_number` VARCHAR(45) NOT NULL,
  `product_category` VARCHAR(100) NOT NULL,
  `product_cost` FLOAT NULL,
  `product_description` VARCHAR(255) NOT NULL,
  `product_qty` INT NULL,
  `product_sku` VARCHAR(45) NULL,
  `rebate` FLOAT NULL,
  `rebate_month` VARCHAR(45) NOT NULL,
  `rebate_percent` FLOAT NOT NULL,
  `rebate_year` INT NOT NULL,
  `vendor_name` VARCHAR(45) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id_submission` INT(11) NOT NULL,
  `Imported` BIT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY USING BTREE (`id_invoice_line_item`),
  INDEX `fk_invoice_line_item_submission1_idx` (`id_submission` ASC) VISIBLE,
  CONSTRAINT `fk_invoice_line_item_submission1`
    FOREIGN KEY (`id_submission`)
    REFERENCES `pride_db`.`submission` (`id_submission`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
ROW_FORMAT = DYNAMIC;


-- -----------------------------------------------------
-- Table `pride_db`.`submission_error_log`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pride_db`.`submission_error_log` (
  `id_submission_error_log` INT(11) NOT NULL AUTO_INCREMENT,
  `error_type` VARCHAR(50) NOT NULL,
  `error_description` VARCHAR(500) NOT NULL,
  `field` VARCHAR(150) NOT NULL,
  `row_number` INT(11) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `id_submission` INT(11) NOT NULL,
  PRIMARY KEY USING BTREE (`id_submission_error_log`),
  INDEX `fk_submission_error_log_submission1_idx` (`id_submission` ASC) VISIBLE,
  CONSTRAINT `fk_submission_error_log_submission1`
    FOREIGN KEY (`id_submission`)
    REFERENCES `pride_db`.`submission` (`id_submission`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
ROW_FORMAT = DYNAMIC;


-- -----------------------------------------------------
-- Table `pride_db`.`inv_line_item_validation_result`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pride_db`.`inv_line_item_validation_result` (
  `id_inv_line_item` INT(11) NOT NULL,
  `id_validation_result` INT(11) NOT NULL,
  PRIMARY KEY (`id_inv_line_item`, `id_validation_result`),
  INDEX `fk_validation_result` (`id_validation_result` ASC) VISIBLE,
  CONSTRAINT `fk_inv_line_item`
    FOREIGN KEY (`id_inv_line_item`)
    REFERENCES `pride_db`.`invoice_line_item` (`id_invoice_line_item`),
  CONSTRAINT `fk_validation_result`
    FOREIGN KEY (`id_validation_result`)
    REFERENCES `pride_db`.`submission_error_log` (`id_submission_error_log`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `pride_db`.`activity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pride_db`.`activity` (
  `id_activity` VARCHAR(50) NOT NULL,
  `date_start` DATE NOT NULL,
  `date_end` DATE NULL,
  `id_dealer` VARCHAR(50) NOT NULL,
  PRIMARY KEY USING BTREE (`id_activity`),
  INDEX `fk_dealer_copy1_dealer1_idx` (`id_dealer` ASC) VISIBLE,
  CONSTRAINT `fk_dealer_copy1_dealer1`
    FOREIGN KEY (`id_dealer`)
    REFERENCES `pride_db`.`dealer` (`id_dealer`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
ROW_FORMAT = DYNAMIC;


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