SCORE

use score; 

CREATE TABLE themes (

    id int NOT NULL AUTO_INCREMENT,

    themesGroup varchar(255) NOT NULL,

    name text NOT NULL,

    language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

    PRIMARY KEY (`id`));

CREATE TABLE learning_objectives (

    id int NOT NULL AUTO_INCREMENT,

    learningsGroup varchar(255) NOT NULL,

    name text NOT NULL,

    language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'FR',

    PRIMARY KEY (id));

CREATE TABLE files (

    id int NOT NULL AUTO_INCREMENT,

    description text NOT NULL,

    pathFile varchar(255) NOT NULL,

    extension varchar(255) NOT NULL,

    size varchar(255) NOT NULL,

    PRIMARY KEY (`id`));

 CREATE TABLE institutions(

    id int NOT NULL AUTO_INCREMENT,

    name text NOT NULL,

    department varchar(255) NOT NULL,

    email varchar(255) NOT NULL,

    country varchar(255) NULL,

    site varchar(255) NOT NULL,

    Image varchar(255) NULL DEFAULT 'default-avatar.png',

    UNIQUE INDEX IDX_8d110b8f5288cfb6d0e10d938c (email),

  PRIMARY KEY (id));

CREATE TABLE learning_blocks (

    id int NOT NULL AUTO_INCREMENT,

    learningBlocksGroup varchar(255) NOT NULL,

    title text NOT NULL,

    duration varchar(255) NOT NULL,

    views int NOT NULL,

    likes int NOT NULL,

    createdAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

    shortDescription text NOT NULL,

    longDescription text NOT NULL,

    status enum ('save', 'publish') NOT NULL DEFAULT 'save',

    language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

    institutionId int NULL,

    PRIMARY KEY (`id`),

    FOREIGN KEY (institutionId) REFERENCES institutions(id));

CREATE TABLE tasks (

  id INT NOT NULL AUTO_INCREMENT,

  userId INT NOT NULL,

  idOfType INT NOT NULL,

  titleTask TEXT NOT NULL,

  action ENUM ('Saved', 'Publish', 'Edited', 'Deleted') NOT NULL DEFAULT 'Saved',

  type ENUM ('LearningBlocks', 'CircularChallenge') NOT NULL DEFAULT 'LearningBlocks',

  title TEXT NOT NULL,

  language ENUM ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'FR',

  timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id));

CREATE TABLE learning_blocks_links (

    id int NOT NULL AUTO_INCREMENT,

    lien text NOT NULL,

    descriptionLien text NOT NULL,

    learningBlockId int NOT NULL,

    PRIMARY KEY (id),

    FOREIGN KEY (learningBlockId) REFERENCES learning_blocks(id) );

CREATE TABLE formats (

    id int NOT NULL AUTO_INCREMENT,

    formatsGroup varchar(255) NOT NULL,

    name text NOT NULL,

    language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

    PRIMARY KEY (id));

CREATE TABLE learning_blocks_steps (

  id int NOT NULL AUTO_INCREMENT,

  title text NOT NULL,

  numStep int NOT NULL,

  description text NOT NULL,

  learningBlockId int NOT NULL,

  PRIMARY KEY (id));

 CREATE TABLE hashtags (

  id int NOT NULL AUTO_INCREMENT,

  hashtagsGroup varchar(255) NOT NULL,

  name text NOT NULL,

  type enum('LearningBlocks', 'CircularChallenge') NOT NULL DEFAULT 'LearningBlocks',

  language enum('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

  PRIMARY KEY (id));

CREATE TABLE circular_strategies (

		  id INT NOT NULL AUTO_INCREMENT,

		  circularStrategiesId VARCHAR(255) NOT NULL,

		  name VARCHAR(255) NOT NULL,

		  language ENUM('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

		  PRIMARY KEY (id));

CREATE TABLE circular_challenges_steps (

  id int NOT NULL AUTO_INCREMENT,

  steps enum ('Set-up', 'Raise awareness', 'Explore practice and systems', 'Make and prototype', 'Reflect', 'Share') NOT NULL DEFAULT 'Set-up',

  description varchar(255) NOT NULL,

  circularChallengeId int NOT NULL,

  PRIMARY KEY (id)

) ENGINE=InnoDB;

CREATE TABLE circular_challenges (

  id int NOT NULL AUTO_INCREMENT,

  circularChallengesGroup varchar(255) NOT NULL,

  title text NOT NULL,

  duration varchar(255) NOT NULL,

  views int NOT NULL,

  likes int NOT NULL,

  longDescription text NOT NULL,

  bigIdea text NOT NULL,

  challenge text NOT NULL,

  shortDescription text NOT NULL,

  status enum ('save', 'publish') NOT NULL DEFAULT 'save',

  language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

  createdAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

  updateAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

  filesId int NOT NULL,

  PRIMARY KEY (id));

 CREATE TABLE circular_challenges_steps_details (

  id int NOT NULL AUTO_INCREMENT,

  learningBlockId int NOT NULL,

  orderSteps int NOT NULL,

  circularChallengeStepsId int NOT NULL,

  PRIMARY KEY (id));

  CREATE TABLE circular_economy (

  id int NOT NULL AUTO_INCREMENT,

  description text NOT NULL,

  videoDescription text NULL,

  videoUrl varchar(255)NULL,

  contentCc varchar(255) NOT NULL,

  contentLb varchar(255) NOT NULL,

  languageId enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

  PRIMARY KEY (id));

CREATE TABLE circular_economy_circular_challenges (

  circularChallenges_id int NOT NULL,

  circularEconomy_id int NOT NULL,

  INDEX IDX_fcda9016510bbc2762fa05ed63 (circularChallenges_id),

  INDEX IDX_1f9981972f85822d97269ee81b (circularEconomy_id),

  PRIMARY KEY (circularChallenges_id, circularEconomy_id)

);

CREATE TABLE circular_economy_learning_blocks (

  learningBlocks_id int NOT NULL,

  circularEconomy_id int NOT NULL,

  INDEX IDX_5f0f8c6dd1f5f0e233362de34e (learningBlocks_id),

  INDEX IDX_41b4d2fa18b3ee25e22dc619bf (circularEconomy_id),

  PRIMARY KEY (learningBlocks_id, circularEconomy_id)

);

CREATE TABLE circular_challenges_main_goals (

  id int NOT NULL AUTO_INCREMENT,

  description varchar(255) NOT NULL,

  numGoals varchar(255) NOT NULL,

  circularChallengeId int NOT NULL,

  PRIMARY KEY (id)

);

CREATE TABLE circular_challenges_main_messages (

  id int NOT NULL AUTO_INCREMENT,

  description varchar(255) NOT NULL,

  numMessages varchar(255) NOT NULL,

  circularChallengeId int NOT NULL,

  PRIMARY KEY (id)

) ENGINE=InnoDB;

CREATE TABLE sectors (

    id int NOT NULL AUTO_INCREMENT,

    sectorsGroup varchar(255) NOT NULL,

    name varchar(255) NOT NULL,

    language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

    type enum ('LearningBlocks', 'CircularChallenge') NOT NULL DEFAULT 'LearningBlocks',

    PRIMARY KEY (id));

CREATE TABLE feedbacks (

    id int NOT NULL AUTO_INCREMENT,

    session text NOT NULL,

    firstname varchar(255) NOT NULL,

    lastname varchar(255) NOT NULL,

    email varchar(255) NOT NULL,

    goodPoint text NOT NULL,

    tutorImprove text NOT NULL,

    additionalComment text NOT NULL,

    language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN',

    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',

    teaching enum ('veryGood', 'good', 'fair', 'poor', 'veryPoor') NOT NULL DEFAULT 'veryGood',

    datePublication datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

    PRIMARY KEY (id));

CREATE TABLE items (

    id int NOT NULL AUTO_INCREMENT,

    title varchar(255) NOT NULL,

    description varchar(255) NOT NULL,

    price int NOT NULL,

    PRIMARY KEY (`id`));

CREATE TABLE users (

id int NOT NULL AUTO_INCREMENT,

    name varchar(255) NOT NULL,

    lastName varchar(255) NOT NULL,

    title varchar(255) NOT NULL,

    institutionId INT NOT NULL,

    phoneCode varchar(255) NOT NULL,

    phone varchar(255) NOT NULL,

    country varchar(255) NOT NULL,

    about text NOT NULL,

    address text NOT NULL,

    email varchar(255) NOT NULL,

    password varchar(255) NOT NULL,

    role enum ('admin') NOT NULL DEFAULT 'admin',

	resetToken text NULL,

    image varchar(255) NULL DEFAULT 'default-avatar.png',

    createdAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

    updatedAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

    UNIQUE INDEX IDX_97672ac88f789774dd47f7c8be (email),

    PRIMARY KEY (id));

CREATE TABLE languages (

    id varchar(36) NOT NULL,

    name varchar(2) NOT NULL,

    PRIMARY KEY (id));

CREATE TABLE learning_blocks_formats (

    learningBlocks_id int NOT NULL,

    format_id int NOT NULL,

    INDEX IDX_e1618382b86956efe25e9eaff5 (learningBlocks_id),

    INDEX IDX_51e3bfc4f1243c9f5c750804af (format_id),

    PRIMARY KEY (learningBlocks_id, format_id));

CREATE TABLE learning_blocks_themes (

    learningBlocks_id int NOT NULL,

    themes_id int NOT NULL,

    INDEX IDX_6ebb9a65140fe73b996d6d1602 (learningBlocks_id),

    INDEX IDX_751f33a9295451d2efb5423f19 (themes_id),

    PRIMARY KEY (learningBlocks_id, themes_id));

CREATE TABLE learning_blocks_hashtags (

    learningBlocks_id int NOT NULL,

    hashtags_id int NOT NULL,

    INDEX IDX_42ef34ca3f01d27c01d1ac01f7 (learningBlocks_id),

    INDEX IDX_cb7eef04feecc6751f63228565 (hashtags_id),

    PRIMARY KEY (learningBlocks_id, hashtags_id));

CREATE TABLE learning_blocks_learning_objectives (

    learningBlocks_id int NOT NULL,

    learningObjectives_id int NOT NULL,

    INDEX IDX_037bb67658bfdc357ad96e1a76 (learningBlocks_id),

    INDEX IDX_1c7df34794f0bc901d3b299031 (learningObjectives_id),

    PRIMARY KEY (learningBlocks_id, learningObjectives_id));

CREATE TABLE learning_blocks_files (

    learningBlocks_id int NOT NULL,

    files_id int NOT NULL,

    INDEX IDX_8bf6dd01845e653d02954cc3a8 (learningBlocks_id),

    INDEX IDX_12c070ce55998ca074a4b4ba1d (files_id),

    PRIMARY KEY (learningBlocks_id, files_id));

CREATE TABLE learning_blocks_sectors (

    learningBlocks_id int NOT NULL,

    sectors_id int NOT NULL,

    INDEX IDX_11d3e5c9354829572fe66a5fd4 (learningBlocks_id),

    INDEX IDX_a342477ab1d035ee97d2073bcd (sectors_id),

    PRIMARY KEY (learningBlocks_id, sectors_id));

CREATE TABLE circular_challenges_sectors (

    circular_challenges_id int NOT NULL,

    sectors_id int NOT NULL,

    INDEX IDX_740628bddee204fb6906e44f40 (circular_challenges_id),

    INDEX IDX_1f1577eeb53e353b507c6b4be7 (sectors_id),

    PRIMARY KEY (circular_challenges_id, sectors_id));

CREATE TABLE circular_challenges_hashtags (

    circular_challenges_id int NOT NULL,

    hashtags_id int NOT NULL,

    INDEX IDX_677e9454ed323db58d1d7841ce (circular_challenges_id),

    INDEX IDX_5a4a24e21e126bd35feb772319 (hashtags_id),

    PRIMARY KEY (circular_challenges_id, hashtags_id));

CREATE TABLE circular_challenges_circular_strategies (

    circular_challenges_id int NOT NULL,

    circularStrategies_id int NOT NULL,

    INDEX IDX_ab23f24ec8e7ea8743d60debfe (circular_challenges_id),

    INDEX IDX_9e72489093c95b08a826ad516b (circularStrategies_id),

    PRIMARY KEY (circular_challenges_id, circularStrategies_id));

CREATE TABLE users_languages (

    user_id int NOT NULL,

    language_id varchar(36) NOT NULL,

    INDEX IDX_852c12b15eeb823db5474023ba (user_id),

    INDEX IDX_38005df66f42efb4850385ec8f (language_id),

    PRIMARY KEY (user_id, language_id),

    FOREIGN KEY (user_id) REFERENCES users(id),

    FOREIGN KEY (language_id) REFERENCES languages(id));

-- Add foreign key constraints to the tables --

ALTER TABLE learning_blocks_links ADD CONSTRAINT FK_a36e9c3304cd3c8641aca736184 FOREIGN KEY (learningBlockId) REFERENCES learning_blocks(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_blocks ADD CONSTRAINT FK_80f2a956764d428836068c9982a FOREIGN KEY (institutionId) REFERENCES institutions(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_steps ADD CONSTRAINT FK_3837e7ca688ee19a5c8f0fe60fa FOREIGN KEY (learningBlockId) REFERENCES learning_blocks(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE circular_challenges_steps ADD CONSTRAINT FK_2cb803d462382e3a15e7de42fb1 FOREIGN KEY (circularChallengeId) REFERENCES circular_challenges(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE circular_challenges_steps_details ADD CONSTRAINT FK_b0cdf89187b990f6a3789f04e80 FOREIGN KEY (learningBlockId) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE circular_challenges_steps_details ADD CONSTRAINT FK_50d7c6baea6c46094d19fa30fae FOREIGN KEY (circularChallengeStepsId) REFERENCES circular_challenges_steps(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE circular_challenges_main_goals ADD CONSTRAINT FK_73f1d732816d23945b355ff2cf2 FOREIGN KEY (circularChallengeId) REFERENCES circular_challenges(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE circular_challenges_main_messages ADD CONSTRAINT FK_5b097fff4c50888196ce07520bd FOREIGN KEY (circularChallengeId) REFERENCES circular_challenges(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE circular_challenges ADD CONSTRAINT FK_2c0da7cc9e31579548fabd94f3b FOREIGN KEY (filesId) REFERENCES files(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_formats ADD CONSTRAINT FK_e1618382b86956efe25e9eaff5c FOREIGN KEY (learningBlocks_id) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE learning_blocks_formats ADD CONSTRAINT FK_51e3bfc4f1243c9f5c750804afc FOREIGN KEY (format_id) REFERENCES formats(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_themes ADD CONSTRAINT FK_6ebb9a65140fe73b996d6d1602d FOREIGN KEY (learningBlocks_id) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE learning_blocks_themes ADD CONSTRAINT FK_751f33a9295451d2efb5423f19e FOREIGN KEY (themes_id) REFERENCES themes(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_hashtags ADD CONSTRAINT FK_42ef34ca3f01d27c01d1ac01f76 FOREIGN KEY (learningBlocks_id) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_hashtags ADD CONSTRAINT FK_cb7eef04feecc6751f63228565c FOREIGN KEY (hashtags_id) REFERENCES hashtags(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_learning_objectives ADD CONSTRAINT FK_037bb67658bfdc357ad96e1a76f FOREIGN KEY (learningBlocks_id) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_learning_objectives ADD CONSTRAINT FK_1c7df34794f0bc901d3b2990314 FOREIGN KEY (learningObjectives_id) REFERENCES learning_objectives(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_files ADD CONSTRAINT FK_8bf6dd01845e653d02954cc3a8f FOREIGN KEY (learningBlocks_id) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_files ADD CONSTRAINT FK_12c070ce55998ca074a4b4ba1d4 FOREIGN KEY (files_id) REFERENCES files(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_sectors ADD CONSTRAINT FK_740628bddee204fb6906e44f40d3 FOREIGN KEY (learningBlocks_id) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE learning_blocks_sectors ADD CONSTRAINT FK_1f1577eeb53e353b507c6b4be71 FOREIGN KEY (sectors_id) REFERENCES sectors(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE circular_challenges_sectors ADD CONSTRAINT FK_740628bddee204fb6906e44f40d5 FOREIGN KEY (circular_challenges_id) REFERENCES circular_challenges(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE circular_challenges_sectors ADD CONSTRAINT FK_1f1577eeb53e353b507c6b4be79 FOREIGN KEY (sectors_id) REFERENCES sectors(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE circular_challenges_hashtags ADD CONSTRAINT FK_677e9454ed323db58d1d7841ce2 FOREIGN KEY (circular_challenges_id) REFERENCES circular_challenges(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE circular_challenges_hashtags ADD CONSTRAINT FK_5a4a24e21e126bd35feb772319e FOREIGN KEY (hashtags_id) REFERENCES hashtags(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE circular_challenges_circular_strategies ADD CONSTRAINT FK_ab23f24ec8e7ea8743d60debfe7 FOREIGN KEY (circular_challenges_id) REFERENCES circular_challenges(id) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE circular_challenges_circular_strategies ADD CONSTRAINT FK_9e72489093c95b08a826ad516b6 FOREIGN KEY (circularStrategies_id) REFERENCES circular_strategies(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE circular_economy_learning_blocks ADD CONSTRAINT FK_5f0f8c6dd1f5f0e233362de34e8 FOREIGN KEY (learningBlocks_id) REFERENCES learning_blocks(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE circular_economy_learning_blocks ADD CONSTRAINT FK_41b4d2fa18b3ee25e22dc619bf0 FOREIGN KEY (circularEconomy_id) REFERENCES circular_economy(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE circular_economy_circular_challenges ADD CONSTRAINT FK_fcda9016510bbc2762fa05ed632 FOREIGN KEY (circularChallenges_id) REFERENCES circular_challenges(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE circular_economy_circular_challenges ADD CONSTRAINT FK_1f9981972f85822d97269ee81b2 FOREIGN KEY (circularEconomy_id) REFERENCES circular_economy(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE users_languages ADD CONSTRAINT FK_852c12b15eeb823db5474023ba3 FOREIGN KEY (user_id)REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE users_languages ADD CONSTRAINT FK_38005df66f42efb4850385ec8f3 FOREIGN KEY (language_id) REFERENCES languages(id)ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE users ADD CONSTRAINT FK_38005df66f42efb4850385ec8fh FOREIGN KEY (institutionId) REFERENCES institutions(id) ON DELETE CASCADE ON UPDATE CASCADE;