mwb to .env

[Sahan Taraka jayasundara]

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

-- -----------------------------------------------------
-- Table `mydb`.`address`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`address` (
`id` INT NOT NULL AUTO_INCREMENT,
`first_line` VARCHAR(45) NOT NULL,
`second_line` VARCHAR(45) NOT NULL,
`street` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;


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


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


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


-- -----------------------------------------------------
-- Table `mydb`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customer` (
`passport_number` INT NOT NULL,
`nic` VARCHAR(45) NOT NULL,
`first_name` VARCHAR(45) NULL,
`birthday` DATE NULL,
`address_id` INT NOT NULL,
`profile_img_id` INT NOT NULL,
`country_id` INT NOT NULL,
`city_id` INT NOT NULL,
PRIMARY KEY (`passport_number`),
INDEX `fk_customer_address1_idx` (`address_id` ASC) VISIBLE,
INDEX `fk_customer_profile_img1_idx` (`profile_img_id` ASC) VISIBLE,
INDEX `fk_customer_country1_idx` (`country_id` ASC) VISIBLE,
INDEX `fk_customer_city1_idx` (`city_id` ASC) VISIBLE,
CONSTRAINT `fk_customer_address1`
FOREIGN KEY (`address_id`)
REFERENCES `mydb`.`address` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_customer_profile_img1`
FOREIGN KEY (`profile_img_id`)
REFERENCES `mydb`.`profile_img` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_customer_country1`
FOREIGN KEY (`country_id`)
REFERENCES `mydb`.`country` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_customer_city1`
FOREIGN KEY (`city_id`)
REFERENCES `mydb`.`city` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`contact_details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`contact_details` (
`id` INT NOT NULL AUTO_INCREMENT,
`mobile_number` VARCHAR(45) NOT NULL,
`email` VARCHAR(45) NOT NULL,
`whatsapp_number` VARCHAR(45) NOT NULL,
`customer_passport_number` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_contact_details_customer_idx` (`customer_passport_number` ASC) VISIBLE,
CONSTRAINT `fk_contact_details_customer`
FOREIGN KEY (`customer_passport_number`)
REFERENCES `mydb`.`customer` (`passport_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


-- -----------------------------------------------------
-- Table `mydb`.`visiting_place`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`visiting_place` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL,
`country_id` INT NOT NULL,
`location_id` INT NOT NULL,
`city_id` INT NOT NULL,
`address_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_visiting_place_country1_idx` (`country_id` ASC) VISIBLE,
INDEX `fk_visiting_place_location1_idx` (`location_id` ASC) VISIBLE,
INDEX `fk_visiting_place_city1_idx` (`city_id` ASC) VISIBLE,
INDEX `fk_visiting_place_address1_idx` (`address_id` ASC) VISIBLE,
CONSTRAINT `fk_visiting_place_country1`
FOREIGN KEY (`country_id`)
REFERENCES `mydb`.`country` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_visiting_place_location1`
FOREIGN KEY (`location_id`)
REFERENCES `mydb`.`location` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_visiting_place_city1`
FOREIGN KEY (`city_id`)
REFERENCES `mydb`.`city` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_visiting_place_address1`
FOREIGN KEY (`address_id`)
REFERENCES `mydb`.`address` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


-- -----------------------------------------------------
-- Table `mydb`.`Price`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Price` (
`id` INT NOT NULL AUTO_INCREMENT,
` price` VARCHAR(45) NOT NULL,
` package_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_Price_ package1_idx` (` package_id` ASC) VISIBLE,
CONSTRAINT `fk_Price_ package1`
FOREIGN KEY (` package_id`)
REFERENCES `mydb`.` package` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`travel_expenses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`travel_expenses` (
`id` INT NOT NULL AUTO_INCREMENT,
`price` VARCHAR(45) NOT NULL,
`From this country` INT NOT NULL,
`to this country` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_travel_expenses_country1_idx` (`From this country` ASC) VISIBLE,
INDEX `fk_travel_expenses_country2_idx` (`to this country` ASC) VISIBLE,
CONSTRAINT `fk_travel_expenses_country1`
FOREIGN KEY (`From this country`)
REFERENCES `mydb`.`country` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_travel_expenses_country2`
FOREIGN KEY (`to this country`)
REFERENCES `mydb`.`country` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Cost_within_the_country.`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Cost_within_the_country.` (
`id` INT NOT NULL AUTO_INCREMENT,
`price` VARCHAR(45) NOT NULL,
`from_place` INT NOT NULL,
`to_place` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_Cost_within_the_country._visiting_place1_idx` (`from_place` ASC) VISIBLE,
INDEX `fk_Cost_within_the_country._visiting_place2_idx` (`to_place` ASC) VISIBLE,
CONSTRAINT `fk_Cost_within_the_country._visiting_place1`
FOREIGN KEY (`from_place`)
REFERENCES `mydb`.`visiting_place` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Cost_within_the_country._visiting_place2`
FOREIGN KEY (`to_place`)
REFERENCES `mydb`.`visiting_place` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


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


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


-- -----------------------------------------------------
-- Table `mydb`.`skils_to_price`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`skils_to_price` (
`id` INT NOT NULL AUTO_INCREMENT,
`count` VARCHAR(45) NOT NULL,
`price` VARCHAR(45) NOT NULL,
`customer_passport_number` INT NOT NULL,
`skills_id` INT NOT NULL,
` package_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_skils_to_price_customer1_idx` (`customer_passport_number` ASC) VISIBLE,
INDEX `fk_skils_to_price_skills1_idx` (`skills_id` ASC) VISIBLE,
INDEX `fk_skils_to_price_ package1_idx` (` package_id` ASC) VISIBLE,
CONSTRAINT `fk_skils_to_price_customer1`
FOREIGN KEY (`customer_passport_number`)
REFERENCES `mydb`.`customer` (`passport_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_skils_to_price_skills1`
FOREIGN KEY (`skills_id`)
REFERENCES `mydb`.`Skill_types` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_skils_to_price_ package1`
FOREIGN KEY (` package_id`)
REFERENCES `mydb`.` package` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`adming`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`adming` (
`nic` INT NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(45) NULL,
`last_name` VARCHAR(45) NULL,
`passport_num` VARCHAR(45) NULL,
`address_id` INT NOT NULL,
`profile_img_id` INT NOT NULL,
`contact_details_id` INT NOT NULL,
`country_id` INT NOT NULL,
PRIMARY KEY (`nic`, `profile_img_id`),
INDEX `fk_adming_address1_idx` (`address_id` ASC) VISIBLE,
INDEX `fk_adming_profile_img1_idx` (`profile_img_id` ASC) VISIBLE,
INDEX `fk_adming_contact_details1_idx` (`contact_details_id` ASC) VISIBLE,
INDEX `fk_adming_country1_idx` (`country_id` ASC) VISIBLE,
CONSTRAINT `fk_adming_address1`
FOREIGN KEY (`address_id`)
REFERENCES `mydb`.`address` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_adming_profile_img1`
FOREIGN KEY (`profile_img_id`)
REFERENCES `mydb`.`profile_img` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_adming_contact_details1`
FOREIGN KEY (`contact_details_id`)
REFERENCES `mydb`.`contact_details` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_adming_country1`
FOREIGN KEY (`country_id`)
REFERENCES `mydb`.`country` (`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