---

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 mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

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


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


-- -----------------------------------------------------
-- Table `mydb`.`Device`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Device` (
  `id_device` INT NOT NULL AUTO_INCREMENT,
  `id_inventory` VARCHAR(45) NULL,
  `id_category_device` INT NULL,
  `name` VARCHAR(45) NULL,
  `cost` DECIMAL(10,2) NULL,
  `status` VARCHAR(45) NULL,
  PRIMARY KEY (`id_device`),
  INDEX `id_category_idx` (`id_category_device` ASC) VISIBLE,
  CONSTRAINT `id_category`
    FOREIGN KEY (`id_category_device`)
    REFERENCES `mydb`.`Category` (`id_category`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Zakazi`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Zakazi` (
  `id_zakazi` INT NOT NULL AUTO_INCREMENT,
  `data_vidachi` DATETIME NULL,
  `data_vozvrata` DATETIME NULL,
  `id_client_zakazi` INT NULL,
  `payment` DECIMAL(10,2) NULL,
  `id_device_topay` INT NULL,
  PRIMARY KEY (`id_zakazi`),
  INDEX `id_client_idx` (`id_client_zakazi` ASC) VISIBLE,
  INDEX `id_device_to_client_idx` (`id_device_topay` ASC) VISIBLE,
  CONSTRAINT `id_client`
    FOREIGN KEY (`id_client_zakazi`)
    REFERENCES `mydb`.`Client` (`id_client`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `id_device_to_client`
    FOREIGN KEY (`id_device_topay`)
    REFERENCES `mydb`.`Device` (`id_device`)
    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;


Create view session_not_vozvrat as
Select
pasport,
id_inventory,
`name`,
data_vidachi,
payment,
TIMESTAMPDIFF(HOUR, z.data_vidachi, NOW()) AS hours_in_use
from zakazi z
join device d on d.id_device = z.id_device_topay
left join client c on c.id_client = z.id_client_zakazi
left join category ct on ct.id_category = d.id_category_device
where name_device = 'Высокое напряжение'
and payment = 'не оплачено'
and data_vozvrata is null;

Select * from session_not_vozvrat;

DELIMITER //

CREATE TRIGGER update_device_status_on_return
AFTER UPDATE ON Zakazi
FOR EACH ROW
BEGIN
    IF NEW.data_vozvrata IS NOT NULL THEN
        UPDATE Device d
        JOIN Category c ON c.id_category = d.id_category_device
        SET d.status = CASE 
            WHEN c.name_device = 'Высокое напряжение' THEN 'разрядка'
            ELSE 'доступен'
        END
        WHERE d.id_device = NEW.id_device_topay;
    END IF;
END //

DELIMITER ;