-- 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 `FidelityType`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityType` (
  `id` INT(10) UNSIGNED NOT NULL,
  `description` VARCHAR(255) NULL,
  `freeShipping` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `freeReturn` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `extraDiscountPercentage` INT(2) NOT NULL DEFAULT 0,
  `ppeMultiplier` INT(2) UNSIGNED NOT NULL DEFAULT 1,
  `ppeSaleMultiplier` INT(2) UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Fidelity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Fidelity` (
  `userId` INT(10) UNSIGNED NOT NULL,
  `fidelityTypeId` INT(10) UNSIGNED NOT NULL,
  `issueDate` DATE NOT NULL,
  `expiration` DATE NOT NULL,
  `code` VARCHAR(255) NOT NULL,
  `memberGetMemberId` INT(10) UNSIGNED NULL,
  `maxAllowedEvents` INT(3) UNSIGNED NULL DEFAULT 10,
  PRIMARY KEY (`userId`),
  INDEX `11_idx` (`fidelityTypeId` ASC),
  INDEX `17_idx` (`memberGetMemberId` ASC),
  UNIQUE INDEX `code_UNIQUE` (`code` ASC),
  CONSTRAINT `8`
  FOREIGN KEY (`userId`)
  REFERENCES `User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `11`
  FOREIGN KEY (`fidelityTypeId`)
  REFERENCES `FidelityType` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `17`
  FOREIGN KEY (`memberGetMemberId`)
  REFERENCES `User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityBalanceAction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityBalanceAction` (
  `id` INT(10) UNSIGNED NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityCampaign`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityCampaign` (
  `id` INT(10) UNSIGNED NOT NULL,
  `startDate` DATE NOT NULL,
  `endDate` DATE NOT NULL,
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityEventType`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityEventType` (
  `id` INT(10) UNSIGNED NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityVoucher`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityVoucher` (
  `id` INT(10) UNSIGNED NOT NULL,
  `discount` INT(10) NOT NULL,
  `fidelityEventTypeId` INT(10) UNSIGNED NULL,
  `requiredPoints` INT(10) NULL,
  `onlyForFullPriceProducts` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
  `validity` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `35_idx` (`fidelityEventTypeId` ASC),
  CONSTRAINT `35`
  FOREIGN KEY (`fidelityEventTypeId`)
  REFERENCES `FidelityEventType` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityBalance`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityBalance` (
  `id` INT(10) UNSIGNED NOT NULL,
  `userId` INT(10) UNSIGNED NOT NULL,
  `fidelityCampaignId` INT(10) UNSIGNED NOT NULL,
  `fidelityVoucherId` INT(10) UNSIGNED NULL,
  `fidelityBalanceActionId` INT(10) UNSIGNED NOT NULL,
  `actionDate` DATE NOT NULL,
  `points` INT(10) NOT NULL,
  `orderId` INT(10) UNSIGNED ZEROFILL NULL,
  PRIMARY KEY (`id`, `userId`),
  INDEX `12_idx` (`userId` ASC),
  INDEX `15_idx` (`fidelityBalanceActionId` ASC),
  INDEX `16_idx` (`orderId` ASC),
  INDEX `18_idx` (`fidelityCampaignId` ASC),
  INDEX `38_idx` (`fidelityVoucherId` ASC),
  CONSTRAINT `12`
  FOREIGN KEY (`userId`)
  REFERENCES `Fidelity` (`userId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `15`
  FOREIGN KEY (`fidelityBalanceActionId`)
  REFERENCES `FidelityBalanceAction` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `16`
  FOREIGN KEY (`orderId`)
  REFERENCES `Order` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `18`
  FOREIGN KEY (`fidelityCampaignId`)
  REFERENCES `FidelityCampaign` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `38`
  FOREIGN KEY (`fidelityVoucherId`)
  REFERENCES `FidelityVoucher` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityCampaignTranslation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityCampaignTranslation` (
  `fidelityCampaignId` INT(10) UNSIGNED NOT NULL,
  `langId` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `imgUrl` VARCHAR(500) NULL,
  PRIMARY KEY (`fidelityCampaignId`, `langId`),
  INDEX `20_idx` (`langId` ASC),
  INDEX `19_idx` (`fidelityCampaignId` ASC),
  CONSTRAINT `19`
  FOREIGN KEY (`fidelityCampaignId`)
  REFERENCES `FidelityCampaign` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `20`
  FOREIGN KEY (`langId`)
  REFERENCES `Lang` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityEvent`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityEvent` (
  `id` INT(10) UNSIGNED NOT NULL,
  `userId` INT(10) UNSIGNED NOT NULL,
  `fidelityEventTypeId` INT(10) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  INDEX `22_idx` (`userId` ASC),
  PRIMARY KEY (`id`),
  INDEX `24_idx` (`fidelityEventTypeId` ASC),
  CONSTRAINT `22`
  FOREIGN KEY (`userId`)
  REFERENCES `Fidelity` (`userId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `24`
  FOREIGN KEY (`fidelityEventTypeId`)
  REFERENCES `FidelityEventType` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityEventTypeTranslation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityEventTypeTranslation` (
  `fidelityEventTypeId` INT(10) UNSIGNED NOT NULL,
  `langId` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `mailMessage` VARCHAR(255) NOT NULL,
  `textMessage` VARCHAR(255) NOT NULL,
  `imgUrl` VARCHAR(500) NULL,
  PRIMARY KEY (`fidelityEventTypeId`, `langId`),
  INDEX `26_idx` (`langId` ASC),
  INDEX `25_idx` (`fidelityEventTypeId` ASC),
  CONSTRAINT `25`
  FOREIGN KEY (`fidelityEventTypeId`)
  REFERENCES `FidelityEventType` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `26`
  FOREIGN KEY (`langId`)
  REFERENCES `Lang` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityTypeTranslation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityTypeTranslation` (
  `fidelityTypeId` INT(10) UNSIGNED NOT NULL,
  `langId` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `imgUrl` VARCHAR(500) NULL,
  PRIMARY KEY (`fidelityTypeId`, `langId`),
  INDEX `28_idx` (`langId` ASC),
  INDEX `27_idx` (`fidelityTypeId` ASC),
  CONSTRAINT `27`
  FOREIGN KEY (`fidelityTypeId`)
  REFERENCES `FidelityType` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `28`
  FOREIGN KEY (`langId`)
  REFERENCES `Lang` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityPpeThreshold`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityPpeThreshold` (
  `id` INT(10) UNSIGNED NOT NULL,
  `ppe` INT(10) NOT NULL,
  `ppeSale` INT(10) UNSIGNED NOT NULL,
  `max` DOUBLE UNSIGNED NOT NULL,
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityCampaignHasFidelityPpeThreshold`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityCampaignHasFidelityPpeThreshold` (
  `fidelityCampaignId` INT(10) UNSIGNED NOT NULL,
  `fidelityPpeThresholdId` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`fidelityCampaignId`, `fidelityPpeThresholdId`),
  INDEX `30_idx` (`fidelityPpeThresholdId` ASC),
  INDEX `29_idx` (`fidelityCampaignId` ASC),
  CONSTRAINT `29`
  FOREIGN KEY (`fidelityCampaignId`)
  REFERENCES `FidelityCampaign` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `30`
  FOREIGN KEY (`fidelityPpeThresholdId`)
  REFERENCES `FidelityPpeThreshold` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityBalanceActionTranslation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityBalanceActionTranslation` (
  `fidelityBalanceActionId` INT(10) UNSIGNED NOT NULL,
  `langId` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`fidelityBalanceActionId`, `langId`),
  INDEX `32_idx` (`langId` ASC),
  INDEX `31_idx` (`fidelityBalanceActionId` ASC),
  CONSTRAINT `31`
  FOREIGN KEY (`fidelityBalanceActionId`)
  REFERENCES `FidelityBalanceAction` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `32`
  FOREIGN KEY (`langId`)
  REFERENCES `Lang` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityVoucherActive`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityVoucherActive` (
  `fidelityUserId` INT(10) UNSIGNED NOT NULL,
  `fidelityVoucherId` INT(10) UNSIGNED NOT NULL,
  `expiration` DATE NOT NULL,
  PRIMARY KEY (`fidelityUserId`, `fidelityVoucherId`),
  INDEX `34_idx` (`fidelityVoucherId` ASC),
  INDEX `33_idx` (`fidelityUserId` ASC),
  INDEX `exp_fva_idx` (`expiration` ASC),
  CONSTRAINT `33`
  FOREIGN KEY (`fidelityUserId`)
  REFERENCES `Fidelity` (`userId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `34`
  FOREIGN KEY (`fidelityVoucherId`)
  REFERENCES `FidelityVoucher` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `FidelityVoucherTranslation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `FidelityVoucherTranslation` (
  `fidelityVoucherId` INT(10) UNSIGNED NOT NULL,
  `langId` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `imgUrl` VARCHAR(500) NULL,
  PRIMARY KEY (`fidelityVoucherId`, `langId`),
  INDEX `37_idx` (`langId` ASC),
  INDEX `36_idx` (`fidelityVoucherId` ASC),
  CONSTRAINT `36`
  FOREIGN KEY (`fidelityVoucherId`)
  REFERENCES `FidelityVoucher` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `37`
  FOREIGN KEY (`langId`)
  REFERENCES `Lang` (`id`)
    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;