CREATE TABLE `cd` (
  `ID` bigint(20) NOT NULL,
  `TYPE` VARCHAR(10) NOT NULL,
  `NAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `REMARKS` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `deletedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `cd_reservation_t` (
  `CD_ID` bigint(20) NOT NULL,
  `GENERATED_CODE` bigint(20),
  `RESERVATION_ID` bigint(20) NOT NULL,
  `WF_DOC_ID` bigint(20) DEFAULT NULL,
  `destination` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `discToDestination` int DEFAULT 0,
  `discToLE` int DEFAULT 0,
  `dataToWrite` varchar(50),
  `password` varchar(20),
  `CreatedAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `deletedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`RESERVATION_ID`,`cd_ID`),
  KEY `cd_ID` (`cd_ID`),
  CONSTRAINT `cd_reservation_t_ibfk_1` FOREIGN KEY (`CD_ID`) REFERENCES `cd` (`ID`) ON UPDATE CASCADE,
  CONSTRAINT `cd_reservation_t_ibfk_2` FOREIGN KEY (`RESERVATION_ID`) REFERENCES `reservation` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TRIGGER cd_reservation_t_code
    BEFORE INSERT
    ON cd_reservation_t FOR EACH ROW
BEGIN
    SET NEW.GENERATED_CODE =  1000 + (select * from (select count(cd_id) from cd_reservation_t) as T);
END