fsdfds

-- MySQL Script generated by MySQL Workbench
-- Fri Nov 17 14:00:26 2023
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema taxi_handler
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema taxi_handler
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `taxi_handler` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `taxi_handler` ;

-- -----------------------------------------------------
-- Table `taxi_handler`.`drivers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `taxi_handler`.`drivers` (
  `id_driver` INT NOT NULL AUTO_INCREMENT,
  `name_first` VARCHAR(25) NOT NULL,
  `name_second` VARCHAR(25) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  `email` VARCHAR(50) NOT NULL,
  `number_license` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`id_driver`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `taxi_handler`.`fares`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `taxi_handler`.`fares` (
  `id_fare` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NOT NULL,
  `description` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_fare`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `taxi_handler`.`cars`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `taxi_handler`.`cars` (
  `id_car` INT NOT NULL AUTO_INCREMENT,
  `brand` VARCHAR(100) NOT NULL,
  `model` VARCHAR(100) NOT NULL,
  `year_release` YEAR NOT NULL,
  `color` CHAR(7) NOT NULL,
  `number_plate` VARCHAR(10) CHARACTER SET 'utf8mb3' NOT NULL,
  `id_driver` INT NULL DEFAULT NULL,
  `id_fare` INT NOT NULL,
  PRIMARY KEY (`id_car`),
  INDEX `id_driver` (`id_driver` ASC) VISIBLE,
  INDEX `id_fare` (`id_fare` ASC) VISIBLE,
  CONSTRAINT `cars_ibfk_1`
    FOREIGN KEY (`id_driver`)
    REFERENCES `taxi_handler`.`drivers` (`id_driver`),
  CONSTRAINT `cars_ibfk_2`
    FOREIGN KEY (`id_fare`)
    REFERENCES `taxi_handler`.`fares` (`id_fare`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `taxi_handler`.`clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `taxi_handler`.`clients` (
  `id_client` INT NOT NULL AUTO_INCREMENT,
  `name_first` VARCHAR(25) NOT NULL,
  `name_second` VARCHAR(25) NOT NULL,
  `phone` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id_client`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `taxi_handler`.`orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `taxi_handler`.`orders` (
  `id_order` INT NOT NULL AUTO_INCREMENT,
  `id_client` INT NOT NULL,
  `id_driver` INT NOT NULL,
  `address_source` VARCHAR(255) NOT NULL,
  `address_target` VARCHAR(255) NOT NULL,
  `id_fare` INT NOT NULL,
  PRIMARY KEY (`id_order`),
  INDEX `id_client` (`id_client` ASC) VISIBLE,
  INDEX `id_driver` (`id_driver` ASC) VISIBLE,
  INDEX `id_fare` (`id_fare` ASC) VISIBLE,
  CONSTRAINT `orders_ibfk_1`
    FOREIGN KEY (`id_client`)
    REFERENCES `taxi_handler`.`clients` (`id_client`),
  CONSTRAINT `orders_ibfk_2`
    FOREIGN KEY (`id_driver`)
    REFERENCES `taxi_handler`.`drivers` (`id_driver`),
  CONSTRAINT `orders_ibfk_3`
    FOREIGN KEY (`id_fare`)
    REFERENCES `taxi_handler`.`fares` (`id_fare`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `taxi_handler`.`order details`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `taxi_handler`.`order details` (
  `id_order_detail` INT NOT NULL AUTO_INCREMENT,
  `date_order` DATETIME NOT NULL,
  `payment_type` ENUM('cash', 'card') NOT NULL,
  `cost` DECIMAL(10,2) NOT NULL,
  `status` ENUM('delivery', 'progress', 'done') NOT NULL,
  PRIMARY KEY (`id_order_detail`),
  CONSTRAINT `order details_ibfk_1`
    FOREIGN KEY (`id_order_detail`)
    REFERENCES `taxi_handler`.`orders` (`id_order`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


-- -----------------------------------------------------
-- Table `taxi_handler`.`rates`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `taxi_handler`.`rates` (
  `id_rate` INT NOT NULL AUTO_INCREMENT,
  `id_order` INT NOT NULL,
  `id_driver` INT NOT NULL,
  `id_client` INT NOT NULL,
  `rate_client` INT NOT NULL,
  `rate_driver` INT NOT NULL,
  PRIMARY KEY (`id_rate`),
  INDEX `id_order` (`id_order` ASC) VISIBLE,
  INDEX `id_driver` (`id_driver` ASC) VISIBLE,
  INDEX `id_client` (`id_client` ASC) VISIBLE,
  CONSTRAINT `rates_ibfk_1`
    FOREIGN KEY (`id_order`)
    REFERENCES `taxi_handler`.`orders` (`id_order`),
  CONSTRAINT `rates_ibfk_2`
    FOREIGN KEY (`id_driver`)
    REFERENCES `taxi_handler`.`drivers` (`id_driver`),
  CONSTRAINT `rates_ibfk_3`
    FOREIGN KEY (`id_client`)
    REFERENCES `taxi_handler`.`clients` (`id_client`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;


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