DROP DATABASE IF EXISTS `testerlocal`;
DROP DATABASE IF EXISTS `test_db`;
CREATE DATABASE `test_db`; 
USE `test_db`;

CREATE TABLE `Feature` (
  `FeatureID` int NOT NULL AUTO_INCREMENT,
  `FeatureName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`FeatureID`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb3;


CREATE TABLE IF NOT EXISTS `Answer` (
  `AnswerID` INT NOT NULL AUTO_INCREMENT,
  `AnswerName` VARCHAR(45) NULL,
  `AnswerGroupID` INT NULL,
  PRIMARY KEY (`AnswerID`),
  INDEX `fk_Answer_AnswerGroup_idx` (`AnswerGroupID` ASC),
  CONSTRAINT `fk_Answer_AnswerGroup`
    FOREIGN KEY (`AnswerGroupID`)
    REFERENCES `Answer` (`AnswerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `FeatureAnswer` (
  `FeatureID` INT NOT NULL,
  `AnswerID` INT NOT NULL,
  PRIMARY KEY (`FeatureID`, `AnswerID`),
  CONSTRAINT `fk_FeatureAnswer_Feature`
    FOREIGN KEY (`FeatureID`)
    REFERENCES `Feature` (`FeatureID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_FeatureAnswer_Answer`
    FOREIGN KEY (`AnswerID`)
    REFERENCES `Answer` (`AnswerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `FeatureAnswer` (
  `FeatureID` INT NOT NULL,
  `AnswerID` INT NOT NULL,
  PRIMARY KEY (`FeatureID`, `AnswerID`),
  CONSTRAINT `fk_FeatureAnswer_Feature`
    FOREIGN KEY (`FeatureID`)
    REFERENCES `Feature` (`FeatureID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_FeatureAnswer_Answer`
    FOREIGN KEY (`AnswerID`)
    REFERENCES `Answer` (`AnswerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `FeatureAnswer` (
  `FeatureID` INT NOT NULL,
  `AnswerID` INT NOT NULL,
  PRIMARY KEY (`FeatureID`, `AnswerID`),
  CONSTRAINT `fk_FeatureAnswer_Feature`
    FOREIGN KEY (`FeatureID`)
    REFERENCES `Feature` (`FeatureID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_FeatureAnswer_Answer`
    FOREIGN KEY (`AnswerID`)
    REFERENCES `Answer` (`AnswerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `FeatureAnswer` (
  `FeatureID` INT NOT NULL,
  `AnswerID` INT NOT NULL,
  PRIMARY KEY (`FeatureID`, `AnswerID`),
  CONSTRAINT `fk_FeatureAnswer_Feature`
    FOREIGN KEY (`FeatureID`)
    REFERENCES `Feature` (`FeatureID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_FeatureAnswer_Answer`
    FOREIGN KEY (`AnswerID`)
    REFERENCES `Answer` (`AnswerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB;