vizinhoOnline_2.0.sql

-- MySQL Script generated by MySQL Workbench
-- Wed May 27 19:14:20 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema vizinhoOnline
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `vizinhoOnline`.`STATUSES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`STATUSES` (
  `id_status` INT NOT NULL AUTO_INCREMENT,
  `name_status` VARCHAR(45) NULL,
  `code_status` VARCHAR(45) NULL,
  PRIMARY KEY (`id_status`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`ITEMS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`ITEMS` (
  `id_item` INT(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(200) NULL,
  `code` VARCHAR(45) NULL,
  `status_code` VARCHAR(15) NULL,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  `long_description` TEXT(2500) NULL COMMENT 'full description of the item',
  `tax_fee` VARCHAR(45) NULL COMMENT 'Price that will be charged by the item',
  `internal_notes` VARCHAR(200) NULL COMMENT 'This field will be used by the system and administrators.',
  `feedback_score` INT NULL,
  `units` VARCHAR(45) NULL,
  `loan_start_date` DATETIME NULL,
  `loan_end_date` DATETIME NULL,
  `replacement_cost` FLOAT NULL,
  `id_status` INT NOT NULL,
  PRIMARY KEY (`id_item`, `id_status`),
  UNIQUE INDEX `id_Item_UNIQUE` (`id_item` ASC),
  UNIQUE INDEX `code_UNIQUE` (`code` ASC),
  INDEX `fk_ITEMS_STATUSES1_idx` (`id_status` ASC),
  CONSTRAINT `fk_ITEMS_STATUSES1`
    FOREIGN KEY (`id_status`)
    REFERENCES `vizinhoOnline`.`STATUSES` (`id_status`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`CATEGORIES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`CATEGORIES` (
  `id_category` INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `category_code` VARCHAR(200) NULL,
  `name` INT NULL,
  `description` VARCHAR(200) NULL,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  PRIMARY KEY (`id_category`),
  UNIQUE INDEX `id_category_UNIQUE` (`id_category` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`ITEM_CATEGORY`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`ITEM_CATEGORY` (
  `id_item_category` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'foreign key category',
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  `id_category` INT(4) UNSIGNED ZEROFILL NOT NULL,
  `id_item` INT(5) UNSIGNED ZEROFILL NOT NULL,
  UNIQUE INDEX `id_item_category_UNIQUE` (`id_item_category` ASC),
  INDEX `fk_ITEM_CATEGORY_CATEGORIES1_idx` (`id_category` ASC),
  INDEX `fk_ITEM_CATEGORY_ITEMS1_idx` (`id_item` ASC),
  PRIMARY KEY (`id_category`, `id_item`),
  CONSTRAINT `fk_ITEM_CATEGORY_CATEGORIES1`
    FOREIGN KEY (`id_category`)
    REFERENCES `vizinhoOnline`.`CATEGORIES` (`id_category`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ITEM_CATEGORY_ITEMS1`
    FOREIGN KEY (`id_item`)
    REFERENCES `vizinhoOnline`.`ITEMS` (`id_item`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`FEEDBACKS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`FEEDBACKS` (
  `id_feedback` INT(4) ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NULL,
  `comment` VARCHAR(200) NULL,
  `score` INT NULL,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  PRIMARY KEY (`id_feedback`),
  UNIQUE INDEX `id_feedback_UNIQUE` (`id_feedback` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`USERS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`USERS` (
  `id_user` INT(5) ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NULL,
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `cpf` VARCHAR(45) NOT NULL,
  `age` INT NULL,
  `ranking` VARCHAR(45) NULL,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  PRIMARY KEY (`id_user`),
  UNIQUE INDEX `id_user_UNIQUE` (`id_user` ASC),
  UNIQUE INDEX `cpf_UNIQUE` (`cpf` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`ITEMS_FEEDBACK`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`ITEMS_FEEDBACK` (
  `id_item_feedback` INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  `id_item` INT(5) UNSIGNED ZEROFILL NOT NULL,
  `id_feedback` INT(4) ZEROFILL UNSIGNED NOT NULL,
  `d_user` INT(5) ZEROFILL UNSIGNED NOT NULL,
  PRIMARY KEY (`id_item_feedback`, `id_item`, `id_feedback`, `d_user`),
  UNIQUE INDEX `id_Item_feedback_UNIQUE` (`id_item_feedback` ASC),
  INDEX `fk_ITEM_FEEDBACK_ITEMS_idx` (`id_item` ASC),
  INDEX `fk_ITEM_FEEDBACK_FEEDBACKS1_idx` (`id_feedback` ASC),
  INDEX `fk_ITEMS_FEEDBACK_USERS1_idx` (`d_user` ASC),
  CONSTRAINT `fk_ITEM_FEEDBACK_ITEMS`
    FOREIGN KEY (`id_item`)
    REFERENCES `vizinhoOnline`.`ITEMS` (`id_item`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ITEM_FEEDBACK_FEEDBACKS1`
    FOREIGN KEY (`id_feedback`)
    REFERENCES `vizinhoOnline`.`FEEDBACKS` (`id_feedback`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ITEMS_FEEDBACK_USERS1`
    FOREIGN KEY (`d_user`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`USERS_FEEDBACK`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`USERS_FEEDBACK` (
  `id_user_feedback` INT UNSIGNED NOT NULL,
  `title` VARCHAR(45) NULL,
  `comment` VARCHAR(45) NULL,
  `score` VARCHAR(45) NULL,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_time` DATETIME NULL,
  `id_user` INT(5) ZEROFILL UNSIGNED NOT NULL,
  `id_feedback` INT(4) ZEROFILL UNSIGNED NOT NULL,
  PRIMARY KEY (`id_user_feedback`, `id_user`, `id_feedback`),
  UNIQUE INDEX `id_users_feedback_UNIQUE` (`id_user_feedback` ASC),
  INDEX `fk_USERS_FEEDBACK_USERS1_idx` (`id_user` ASC),
  INDEX `fk_USERS_FEEDBACK_FEEDBACKS1_idx` (`id_feedback` ASC),
  CONSTRAINT `fk_USERS_FEEDBACK_USERS1`
    FOREIGN KEY (`id_user`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_USERS_FEEDBACK_FEEDBACKS1`
    FOREIGN KEY (`id_feedback`)
    REFERENCES `vizinhoOnline`.`FEEDBACKS` (`id_feedback`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`USERS_ITEM`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`USERS_ITEM` (
  `id_user_item` INT NOT NULL AUTO_INCREMENT,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_time` DATETIME NULL,
  `imagem` VARCHAR(45) NULL,
  `id_item` INT(5) UNSIGNED ZEROFILL NOT NULL,
  `id_user` INT(5) ZEROFILL UNSIGNED NOT NULL,
  PRIMARY KEY (`id_user_item`, `id_item`, `id_user`),
  UNIQUE INDEX `id_user_item_UNIQUE` (`id_user_item` ASC),
  INDEX `fk_USER_ITEM_ITEMS1_idx` (`id_item` ASC),
  INDEX `fk_USER_ITEM_USERS1_idx` (`id_user` ASC),
  CONSTRAINT `fk_USER_ITEM_ITEMS1`
    FOREIGN KEY (`id_item`)
    REFERENCES `vizinhoOnline`.`ITEMS` (`id_item`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_USER_ITEM_USERS1`
    FOREIGN KEY (`id_user`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`LOCATIONS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`LOCATIONS` (
  `id_location` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `apartment_number` INT NOT NULL,
  `build` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  `intercom_branch` INT NULL,
  `id_user` INT(5) ZEROFILL UNSIGNED NOT NULL,
  PRIMARY KEY (`id_location`, `id_user`),
  INDEX `fk_LOCATIONS_USERS1_idx` (`id_user` ASC),
  CONSTRAINT `fk_LOCATIONS_USERS1`
    FOREIGN KEY (`id_user`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`POSTS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`POSTS` (
  `id_post` DOUBLE UNSIGNED NOT NULL,
  `create_date` DATETIME NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  `comment` VARCHAR(45) NULL,
  `title` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_post`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`FAVORITES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`FAVORITES` (
  `id_favorite` INT UNSIGNED NOT NULL,
  `favorite_status` VARCHAR(45) NULL,
  `favorite_code` VARCHAR(45) NULL,
  PRIMARY KEY (`id_favorite`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`USERS_POSTS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`USERS_POSTS` (
  `id_user_post` INT UNSIGNED NOT NULL,
  `create_date` DATETIME NOT NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  `id_user` INT(5) ZEROFILL UNSIGNED NOT NULL,
  `id_post` DOUBLE UNSIGNED NOT NULL,
  PRIMARY KEY (`id_user_post`, `id_user`, `id_post`),
  INDEX `fk_USERS_POSTS_USERS1_idx` (`id_user` ASC),
  INDEX `fk_USERS_POSTS_POSTS1_idx` (`id_post` ASC),
  UNIQUE INDEX `id_user_post_UNIQUE` (`id_user_post` ASC),
  CONSTRAINT `fk_USERS_POSTS_USERS1`
    FOREIGN KEY (`id_user`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_USERS_POSTS_POSTS1`
    FOREIGN KEY (`id_post`)
    REFERENCES `vizinhoOnline`.`POSTS` (`id_post`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`IMAGES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`IMAGES` (
  `id_image` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `path_location` VARCHAR(45) NULL,
  `create_date` DATETIME NULL,
  `modify_date` DATETIME NULL,
  `delete_date` DATETIME NULL,
  `id_user` INT(5) ZEROFILL UNSIGNED NOT NULL,
  `id_user_post` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id_image`, `id_user`, `id_user_post`),
  UNIQUE INDEX `id_image_UNIQUE` (`id_image` ASC),
  INDEX `fk_IMAGES_USERS1_idx` (`id_user` ASC),
  INDEX `fk_IMAGES_USERS_POSTS1_idx` (`id_user_post` ASC),
  CONSTRAINT `fk_IMAGES_USERS1`
    FOREIGN KEY (`id_user`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_IMAGES_USERS_POSTS1`
    FOREIGN KEY (`id_user_post`)
    REFERENCES `vizinhoOnline`.`USERS_POSTS` (`id_user_post`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`USERS_FAVORITES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`USERS_FAVORITES` (
  `id_user_favorite` INT UNSIGNED NOT NULL,
  `create_date` DATETIME NULL,
  `id_users_post` INT UNSIGNED NOT NULL,
  `id_user` INT(5) ZEROFILL UNSIGNED NOT NULL,
  `id_post` DOUBLE UNSIGNED NOT NULL,
  `id_favorite` INT UNSIGNED NOT NULL,
  `id_user_item` INT(5) UNSIGNED ZEROFILL NOT NULL,
  PRIMARY KEY (`id_user_favorite`, `id_users_post`, `id_user`, `id_post`, `id_favorite`, `id_user_item`),
  INDEX `fk_USERS_FAVORITES_USERS_POSTS1_idx` (`id_users_post` ASC, `id_user` ASC, `id_post` ASC),
  INDEX `fk_USERS_FAVORITES_FAVORITES1_idx` (`id_favorite` ASC),
  INDEX `fk_USERS_FAVORITES_USERS_ITEM1_idx` (`id_user_item` ASC),
  CONSTRAINT `fk_USERS_FAVORITES_USERS_POSTS1`
    FOREIGN KEY (`id_users_post` , `id_user` , `id_post`)
    REFERENCES `vizinhoOnline`.`USERS_POSTS` (`id_user_post` , `id_user` , `id_post`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_USERS_FAVORITES_FAVORITES1`
    FOREIGN KEY (`id_favorite`)
    REFERENCES `vizinhoOnline`.`FAVORITES` (`id_favorite`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_USERS_FAVORITES_USERS_ITEM1`
    FOREIGN KEY (`id_user_item`)
    REFERENCES `vizinhoOnline`.`USERS_ITEM` (`id_item`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`ITEM_IMAGES`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`ITEM_IMAGES` (
  `id_item_image` INT NOT NULL AUTO_INCREMENT,
  `id_item` INT(5) UNSIGNED ZEROFILL NOT NULL,
  `id_image` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id_item_image`, `id_item`, `id_image`),
  INDEX `fk_ITEM_IMAGES_ITEMS1_idx` (`id_item` ASC),
  INDEX `fk_ITEM_IMAGES_IMAGES1_idx` (`id_image` ASC),
  UNIQUE INDEX `id_item_image_UNIQUE` (`id_item_image` ASC),
  CONSTRAINT `fk_ITEM_IMAGES_ITEMS1`
    FOREIGN KEY (`id_item`)
    REFERENCES `vizinhoOnline`.`ITEMS` (`id_item`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ITEM_IMAGES_IMAGES1`
    FOREIGN KEY (`id_image`)
    REFERENCES `vizinhoOnline`.`IMAGES` (`id_image`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `vizinhoOnline`.`LEND_ITEMS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `vizinhoOnline`.`LEND_ITEMS` (
  `ITEMS_id_item` INT(5) UNSIGNED ZEROFILL NOT NULL,
  `id_status` INT NOT NULL,
  `id_user_owner` INT(5) ZEROFILL UNSIGNED NOT NULL,
  `id_user_neighbor` INT(5) ZEROFILL UNSIGNED NOT NULL,
  `lend_create_date` DATETIME NULL,
  `lend_modify_date` DATETIME NULL,
  `lend_finish_date` DATETIME NULL,
  PRIMARY KEY (`ITEMS_id_item`, `id_status`, `id_user_owner`, `id_user_neighbor`),
  INDEX `fk_LEND_ITEMS_USERS1_idx` (`id_user_owner` ASC),
  INDEX `fk_LEND_ITEMS_USERS2_idx` (`id_user_neighbor` ASC),
  CONSTRAINT `fk_LEND_ITEMS_ITEMS1`
    FOREIGN KEY (`ITEMS_id_item` , `id_status`)
    REFERENCES `vizinhoOnline`.`ITEMS` (`id_item` , `id_status`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_LEND_ITEMS_USERS1`
    FOREIGN KEY (`id_user_owner`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_LEND_ITEMS_USERS2`
    FOREIGN KEY (`id_user_neighbor`)
    REFERENCES `vizinhoOnline`.`USERS` (`id_user`)
    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;