-- MySQL Script generated by MySQL Workbench
-- lun. 10 févr. 2020 00:56:46 CET
-- 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 nsaservices_db
-- -----------------------------------------------------

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

-- -----------------------------------------------------
-- Table `nsaservices_db`.`membership`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`membership` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `price` VARCHAR(45) NOT NULL,
  `timeQuota` INT NOT NULL,
  `openDays` VARCHAR(45) NOT NULL,
  `closeDays` VARCHAR(45) NOT NULL,
  `openHours` VARCHAR(45) NOT NULL,
  `closeHours` VARCHAR(45) NOT NULL,
  `description` LONGTEXT NOT NULL,
  `duration` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nsaservices_db`.`user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NOT NULL,
  `email` VARCHAR(80) NOT NULL,
  `password` VARCHAR(65) NOT NULL,
  `phone_number` VARCHAR(20) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `city` VARCHAR(45) NOT NULL,
  `rank` INT NOT NULL DEFAULT 0,
  `profile_picture` VARCHAR(255) NULL,
  `signup_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  `signup_token` VARCHAR(80) NULL,
  `confirm_date` DATETIME NULL,
  `pass_reset_token` VARCHAR(80) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE UNIQUE INDEX `email_UNIQUE` ON `nsaservices_db`.`user` (`email` ASC);

CREATE UNIQUE INDEX `phone_number_UNIQUE` ON `nsaservices_db`.`user` (`phone_number` ASC);


-- -----------------------------------------------------
-- Table `nsaservices_db`.`role`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`role` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `nbForDiscount` INT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nsaservices_db`.`memberships_history`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`memberships_history` (
  `customer_id` INT NOT NULL,
  `membership_id` INT NOT NULL,
  `beginning` DATETIME NOT NULL,
  `end` DATETIME NOT NULL,
  `status` VARCHAR(45) NULL,
  PRIMARY KEY (`customer_id`, `membership_id`),
  CONSTRAINT `fk_User_has_Membership_User1`
    FOREIGN KEY (`customer_id`)
    REFERENCES `nsaservices_db`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_User_has_Membership_Membership1`
    FOREIGN KEY (`membership_id`)
    REFERENCES `nsaservices_db`.`membership` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_User_has_Membership_Membership1_idx` ON `nsaservices_db`.`memberships_history` (`membership_id` ASC);


-- -----------------------------------------------------
-- Table `nsaservices_db`.`category`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nsaservices_db`.`service`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`service` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `price` DOUBLE NOT NULL,
  `discountPrice` DOUBLE NULL,
  `description` LONGTEXT NOT NULL,
  `category_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_Service_category1`
    FOREIGN KEY (`category_id`)
    REFERENCES `nsaservices_db`.`category` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_Service_Category1_idx` ON `nsaservices_db`.`service` (`category_id` ASC);


-- -----------------------------------------------------
-- Table `nsaservices_db`.`partner`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`partner` (
  `partner_id` INT NOT NULL AUTO_INCREMENT,
  `corporation_name` VARCHAR(80) NULL,
  `corporation_id` VARCHAR(45) NULL,
  `lastname` VARCHAR(45) NULL,
  `firstname` VARCHAR(45) NULL,
  `role_id` INT NOT NULL,
  `address` TEXT NOT NULL,
  `city` VARCHAR(45) NOT NULL,
  `email` VARCHAR(80) NOT NULL,
  `password` VARCHAR(255) NULL,
  `phone` VARCHAR(20) NOT NULL,
  `qrcode` VARCHAR(1024) NOT NULL,
  `pricing` DOUBLE NULL,
  `disponibility_begin` DATETIME NULL,
  `disponibility_end` DATETIME NULL,
  PRIMARY KEY (`partner_id`),
  CONSTRAINT `fk_partner_role1`
    FOREIGN KEY (`role_id`)
    REFERENCES `nsaservices_db`.`role` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_partner_role1_idx` ON `nsaservices_db`.`partner` (`role_id` ASC);

CREATE UNIQUE INDEX `email_UNIQUE` ON `nsaservices_db`.`partner` (`email` ASC);

CREATE UNIQUE INDEX `phone_UNIQUE` ON `nsaservices_db`.`partner` (`phone` ASC);


-- -----------------------------------------------------
-- Table `nsaservices_db`.`contract`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`contract` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `beginning` DATETIME NOT NULL,
  `end` DATETIME NOT NULL,
  `clauses` LONGTEXT NULL,
  `partner_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_contract_partner1`
    FOREIGN KEY (`partner_id`)
    REFERENCES `nsaservices_db`.`partner` (`partner_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_contract_partner1_idx` ON `nsaservices_db`.`contract` (`partner_id` ASC);


-- -----------------------------------------------------
-- Table `nsaservices_db`.`order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`order` (
  `order_id` INT NOT NULL AUTO_INCREMENT,
  `customer_id` INT NOT NULL,
  `order_date` DATETIME NOT NULL,
  `nbHours` DOUBLE NULL,
  `service_id` INT NOT NULL,
  `payment_status` TINYINT(1) NULL,
  `reservation_date` DATETIME NULL,
  `order_status` VARCHAR(45) NULL,
  PRIMARY KEY (`order_id`),
  CONSTRAINT `fk_order_user1`
    FOREIGN KEY (`customer_id`)
    REFERENCES `nsaservices_db`.`user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_order_service1`
    FOREIGN KEY (`service_id`)
    REFERENCES `nsaservices_db`.`service` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_order_user1_idx` ON `nsaservices_db`.`order` (`customer_id` ASC);

CREATE INDEX `fk_order_service1_idx` ON `nsaservices_db`.`order` (`service_id` ASC);


-- -----------------------------------------------------
-- Table `nsaservices_db`.`intervention`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nsaservices_db`.`intervention` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `partner_id` INT NOT NULL,
  `order_id` INT NOT NULL,
  `intervention_date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_intervention_partner1`
    FOREIGN KEY (`partner_id`)
    REFERENCES `nsaservices_db`.`partner` (`partner_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_intervention_order1`
    FOREIGN KEY (`order_id`)
    REFERENCES `nsaservices_db`.`order` (`order_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_intervention_partner1_idx` ON `nsaservices_db`.`intervention` (`partner_id` ASC);

CREATE INDEX `fk_intervention_order1_idx` ON `nsaservices_db`.`intervention` (`order_id` ASC);


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