use score;

CREATE TABLE circular_economy_cards (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title TEXT NOT NULL,
    shortDescription TEXT NOT NULL,
    description TEXT NOT NULL,
    image VARCHAR(255) NOT NULL,
    icon VARCHAR(255) NOT NULL,
    link VARCHAR(255) NOT NULL,
    circularEconomyId INT NOT NULL,
    FOREIGN KEY (circularEconomyId) REFERENCES circular_economy(id),
    INDEX (circularEconomyId)
);

drop table feedbacks;
 CREATE TABLE feedbacks (
	  id int NOT NULL AUTO_INCREMENT,
	  firstname varchar(255) NOT NULL, 
	  lastname varchar(255) NOT NULL, email varchar(255) NOT NULL,
	  type enum ('LearningBlocks', 'Feedback', 'CircularChallenges') NOT NULL DEFAULT 'Feedback', 
	  datePublication datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	  language ENUM('FR', 'EN', 'ES', 'LT') NULL DEFAULT 'EN',
	  referencesId varchar(255) NOT NULL,
	  PRIMARY KEY (id));
      
CREATE TABLE feedbacks_feedback (
  id INT NOT NULL AUTO_INCREMENT,
  additionalComment TEXT NOT NULL,
  missedPoint TEXT NOT NULL,
  usefulness ENUM('veryGood', 'good', 'fair', 'poor', 'veryPoor') NOT NULL DEFAULT 'veryGood',
  confident ENUM('yes', 'no', 'unsure') NOT NULL DEFAULT 'no',
  content ENUM('veryGood', 'good', 'fair', 'poor', 'veryPoor') NOT NULL DEFAULT 'veryGood',
  PRIMARY KEY (id)
);

ALTER TABLE circular_challenges
CHANGE status status ENUM('save', 'publish', 'feedback') NOT NULL DEFAULT 'save';

ALTER TABLE learning_blocks
CHANGE status status ENUM('save', 'publish', 'feedback') NOT NULL DEFAULT 'save';

ALTER TABLE learning_blocks
modify institutionId INT null;

ALTER TABLE learning_blocks
MODIFY COLUMN duration VARCHAR(255) null,
MODIFY COLUMN views INT null,
MODIFY COLUMN likes INT null;

ALTER TABLE circular_challenges
MODIFY COLUMN duration VARCHAR(255) null,
MODIFY COLUMN views INT null ,
MODIFY COLUMN likes INT null;