-- MySQL Script generated by MySQL Workbench
-- Fri Apr 27 11:27:13 2018
-- 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 mydb
-- -----------------------------------------------------

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

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


-- -----------------------------------------------------
-- Table `mydb`.`hotels`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`hotels` (
  `id` INT NOT NULL,
  `cities_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_hotels_cities_idx` (`cities_id` ASC),
  CONSTRAINT `fk_hotels_cities`
    FOREIGN KEY (`cities_id`)
    REFERENCES `mydb`.`cities` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `id` INT NOT NULL,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`rooms`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`rooms` (
  `id` INT NOT NULL,
  `hotels_id` INT NOT NULL,
  `description` VARCHAR(45) NULL,
  `price` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_rooms_hotels1_idx` (`hotels_id` ASC),
  CONSTRAINT `fk_rooms_hotels1`
    FOREIGN KEY (`hotels_id`)
    REFERENCES `mydb`.`hotels` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`bills`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`bills` (
  `id` INT NOT NULL,
  `total_price` INT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`reservations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`reservations` (
  `rooms_id` INT NOT NULL,
  `bills_id` INT NOT NULL,
  `from_date` DATETIME NULL,
  `to_date` DATETIME NULL,
  `number_of_nights` INT NULL,
  `can_smoking` INT NULL,
  INDEX `fk_reservations_rooms1_idx` (`rooms_id` ASC),
  INDEX `fk_reservations_bills1_idx` (`bills_id` ASC),
  CONSTRAINT `fk_reservations_rooms1`
    FOREIGN KEY (`rooms_id`)
    REFERENCES `mydb`.`rooms` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reservations_bills1`
    FOREIGN KEY (`bills_id`)
    REFERENCES `mydb`.`bills` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`card_holders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`card_holders` (
  `users_id` INT NOT NULL,
  `title` VARCHAR(45) NULL,
  `first_name` VARCHAR(45) NULL,
  `last_name` VARCHAR(45) NULL,
  `primary_addr` VARCHAR(45) NULL,
  `secondary_addr` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `country` VARCHAR(45) NULL,
  `state` VARCHAR(45) NULL,
  `postcode` VARCHAR(45) NULL,
  `daytime_tel` VARCHAR(45) NULL,
  `evening_tel` VARCHAR(45) NULL,
  INDEX `fk_card_holders_users1_idx` (`users_id` ASC),
  PRIMARY KEY (`users_id`),
  CONSTRAINT `fk_card_holders_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`payments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`payments` (
  `bills_id` INT NOT NULL,
  `card_holders_users_id` INT NOT NULL,
  INDEX `fk_payments_bills1_idx` (`bills_id` ASC),
  INDEX `fk_payments_card_holders1_idx` (`card_holders_users_id` ASC),
  PRIMARY KEY (`bills_id`),
  CONSTRAINT `fk_payments_bills1`
    FOREIGN KEY (`bills_id`)
    REFERENCES `mydb`.`bills` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_payments_card_holders1`
    FOREIGN KEY (`card_holders_users_id`)
    REFERENCES `mydb`.`card_holders` (`users_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`credit_cards`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`credit_cards` (
  `card_holders_users_id` INT NOT NULL,
  `card_number` VARCHAR(45) NULL,
  `expiry_date` DATETIME NULL,
  `card_security_code` VARCHAR(45) NULL,
  INDEX `fk_credit_cards_card_holders1_idx` (`card_holders_users_id` ASC),
  PRIMARY KEY (`card_holders_users_id`),
  CONSTRAINT `fk_credit_cards_card_holders1`
    FOREIGN KEY (`card_holders_users_id`)
    REFERENCES `mydb`.`card_holders` (`users_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;
