use socle ;
/*** Solution Tables**/
-- Create languages table keep it
CREATE TABLE languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    language VARCHAR(255) NOT NULL,
    tag ENUM('en', 'fr', 'es', 'it') NOT NULL 
);
-- Create solution_languages table keep it
CREATE TABLE solution_languages(
    id INT AUTO_INCREMENT PRIMARY KEY,
    active ENUM('true', 'false') NOT NULL,
    defaultLanguage BOOLEAN NOT NULL,
    languagesId INT NOT NULL,
    FOREIGN KEY (languagesId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create solution_entities table
CREATE TABLE solution_entities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(255) NOT NULL,
	state boolean NULL,
	level text NOT NULL,
    title VARCHAR(255) NOT NULL,
    parentId VARCHAR(255) NOT NULL
);
-- Create solution_menu table
CREATE TABLE solution_menu (
    id INT AUTO_INCREMENT NOT NULL, 
    menuCode VARCHAR(255) NOT NULL,  
    menuLibelle TEXT NULL,
    parentMenu VARCHAR(255) NULL,
    type ENUM('menu', 'subMenu', 'widget', 'option') NULL,
    PRIMARY KEY (menuCode),  
    UNIQUE (id) 
);

-- Create company_solution_menu_droit table  done
CREATE TABLE solution_menu_rights (
    id INT AUTO_INCREMENT NOT NULL, 
    code VARCHAR(255) NOT NULL,  
    action VARCHAR(255) NOT NULL,
    menuCode VARCHAR(255) NOT NULL,
    PRIMARY KEY (code), 
    FOREIGN KEY (menuCode) REFERENCES solution_menu(menuCode) ON DELETE CASCADE,
      UNIQUE (id) 
);


-- Create solution_subscription table => split into two tables solution_subscription than solution_subscription_menu
CREATE TABLE solution_subscription (
    id INT NOT NULL, 
    code VARCHAR(255) NOT NULL,
    type ENUM('On The Menu', 'Essential','Advanced') NOT NULL,
	PRIMARY KEY (code) 
);

CREATE TABLE solution_subscription_menu (
    id INT AUTO_INCREMENT  NOT NULL,
    subscriptionCode VARCHAR(255) NOT NULL,
    menuCode VARCHAR(255) NOT NULL,
    state BOOLEAN,
    FOREIGN KEY (subscriptionCode) REFERENCES solution_subscription(code) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (menuCode) REFERENCES solution_menu(menuCode) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (subscriptionCode, menuCode),
    UNIQUE (id) 
);

-- Create solution_subscription_menu_rights with Composite foreign key referencing solution_subscription_menu (subscriptionCode, menuCode)
CREATE TABLE solution_subscription_menu_rights (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subscriptionCode VARCHAR(255) NOT NULL,
    menuCode VARCHAR(255) NOT NULL,
    solution_menu_rights_code VARCHAR(255) NOT NULL,
    FOREIGN KEY (subscriptionCode, menuCode) 
        REFERENCES solution_subscription_menu(subscriptionCode, menuCode) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (solution_menu_rights_code) 
        REFERENCES solution_menu_rights(code) 
        ON DELETE CASCADE ON UPDATE CASCADE,
        UNIQUE (id) 
);

-- Create solution_setting table 
CREATE TABLE solution_setting (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label TEXT NULL,
    type ENUM('country', 'business_Sector', 'company_size', 'CA', 'geographic_extent', 'value_chain') NOT NULL
);
-- Create solution_setting_item table
CREATE TABLE solution_setting_item (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label TEXT NULL,
    code TEXT NULL,
	active boolean,
    settingId INT NOT NULL,
	FOREIGN KEY (settingId) REFERENCES solution_setting(id) ON DELETE CASCADE
);
-- Create solution_compensation table
CREATE TABLE solution_compensation (
   id INT AUTO_INCREMENT PRIMARY KEY,
    column_code VARCHAR(50) NOT NULL,
    column_label VARCHAR(255) NOT NULL,
    description TEXT,
    languageId INT NOT NULL,
    FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE
);
/*** Company Tables ***/
-- Create the company table
CREATE TABLE company (
    id INT AUTO_INCREMENT PRIMARY KEY,
    raisonSociale VARCHAR(255) NOT NULL,
    logo VARCHAR(255) NULL,
    site VARCHAR(255) NULL,
    address VARCHAR(255) NULL,
    SIRET VARCHAR(255) NULL,
    type ENUM('Solution','Customer') NULL,
    parentId INT NOT NULL,
    subscriptionType VARCHAR(255) NULL,
    status VARCHAR(255),
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create the company_setting table a  a faire !!
CREATE TABLE company_setting (
    id INT AUTO_INCREMENT PRIMARY KEY,
    companyId INT NOT NULL,
    companySizeId INT,
    valueChainId INT,
    businessSectorId INT,
    geographicExtentId INT,
    cAId INT,
    countryId INT,
    FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (companySizeId) REFERENCES solution_setting_item(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (valueChainId) REFERENCES solution_setting_item(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (businessSectorId) REFERENCES solution_setting_item(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (geographicExtentId) REFERENCES solution_setting_item(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (cAId) REFERENCES solution_setting_item(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (countryId) REFERENCES solution_setting_item(id) ON DELETE CASCADE ON UPDATE CASCADE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create company_languages table
CREATE TABLE company_languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    active ENUM('true', 'false') NOT NULL,
    defaultLanguage BOOLEAN NOT NULL,
    languagesId INT NOT NULL,
    companyId INT NOT NULL,
    FOREIGN KEY (languagesId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_profile table
CREATE TABLE company_profile (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
code VARCHAR(255) NOT NULL,
companyId INT NOT NULL,
FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_users table
CREATE TABLE company_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(255) NOT NULL,
    lastName VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE, 
	password VARCHAR(255) NULL,
    phone INT NULL,
	avatar VARCHAR(255) NOT NULL,
    resetToken VARCHAR(255) NULL,
    companyId INT NOT NULL,
    code VARCHAR(255) NULL,
	actif boolean NULL,
    FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create company_entities table
CREATE TABLE company_entities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code text NOT NULL,
    title text NOT NULL,
    state boolean NULL,
	level text NOT NULL,
    parentId VARCHAR(255) NOT NULL,
    companyId INT NOT NULL,
    FOREIGN KEY (companyId) REFERENCES company(id)  ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_user_profile table
CREATE TABLE company_user_profile (
    id INT PRIMARY KEY AUTO_INCREMENT,
    profileId INT NOT NULL,
    usersId INT NOT NULL,
    FOREIGN KEY (profileId) REFERENCES company_profile(id)ON DELETE CASCADE ON UPDATE CASCADE ,
    FOREIGN KEY (usersId) REFERENCES company_users(id)ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_user_profiles_entities table
CREATE TABLE company_user_profiles_entities(
	id INT AUTO_INCREMENT PRIMARY KEY,
    entitiesCompanyId INT NOT NULL,
    userProfileId INT NOT NULL,
	state boolean  NOT NULL,
    CONSTRAINT FK_UserProfile FOREIGN KEY (userProfileId) REFERENCES company_user_profile (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FK_EntitiesCompany FOREIGN KEY (entitiesCompanyId) REFERENCES company_entities(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_solution_menu table
CREATE TABLE company_solution_menu (
    id INT AUTO_INCREMENT PRIMARY KEY,
    state BOOLEAN NOT NULL,
    companyId INT NOT NULL,
    menuCode VARCHAR(255) NOT NULL,
    FOREIGN KEY (menuCode) REFERENCES solution_menu(menuCode) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Create company_solution_menu_profils table  
create table company_solution_menu_profils(
	id INT AUTO_INCREMENT PRIMARY KEY,
	state boolean  NOT NULL,
	profileId INT NOT NULL,
	menuSolutionCompanyId INT NOT NULL,
    FOREIGN KEY (menuSolutionCompanyId) REFERENCES company_solution_menu(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (profileId) REFERENCES company_profile(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_solution_menu_profils_droit table
 CREATE TABLE company_solution_menu_profils_rights (
   id INT AUTO_INCREMENT PRIMARY KEY,
    profileId INT,
    droitId INT, 
	libelle VARCHAR(255) NOT NULL,
	actif boolean null ,
    FOREIGN KEY (droitId) REFERENCES solution_subscription_menu_rights(id) ON DELETE CASCADE,
	FOREIGN KEY (profileId) REFERENCES company_profile(id) ON DELETE CASCADE
);
-- Create company_files table
CREATE TABLE company_files (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    description TEXT,
    pathFile VARCHAR(255),
    extension VARCHAR(255),
    size VARCHAR(255),
	type VARCHAR(255),
    createdAt DATETIME NULL,
    updatedAt DATETIME NULL
);
-- Create company_profile_columns_configuration table
CREATE TABLE company_profile_columns_configuration (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userProfileId INT NOT NULL,
    type ENUM('compensation', 'rapport') NOT NULL,
    selectedColumns TEXT,
    CONSTRAINT userProfileId FOREIGN KEY (userProfileId) REFERENCES company_user_profile(id) ON DELETE CASCADE
);
-- Create prospecting_company table
CREATE TABLE prospecting_company  (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(255) NOT NULL,
    lastName VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone INT NULL,
    raisonSociale VARCHAR(255),
    businessSector VARCHAR(255) NOT NULL,
    country VARCHAR(255) NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Create demonstration_company_comments table
CREATE TABLE prospecting_company_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255),
    userId INT NOT NULL,
    prospectingId INT NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES company_users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (prospectingId) REFERENCES prospecting_company(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create table company_activities
CREATE TABLE company_activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(255),
    action ENUM('create', 'update', 'delete', 'validate', 'send email')  NULL,
    type ENUM('company', 'user', 'email', 'company_compensation')  NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    userId INT NULL,
    companyId INT NULL,
    FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (userId) REFERENCES company_users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- create notification table
CREATE TABLE company_notification (
    id INT AUTO_INCREMENT PRIMARY KEY,
    icon VARCHAR(255) DEFAULT NULL,
    image VARCHAR(255) DEFAULT NULL,
    title VARCHAR(255) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    time DATETIME NOT NULL,
    link VARCHAR(255) DEFAULT NULL,
    useRouter BOOLEAN DEFAULT TRUE,
    seen BOOLEAN DEFAULT FALSE,
    idSource INT NOT NULL,
    idReceiver INT NOT NULL
);
-- Create company_meetings table
CREATE TABLE company_meetings (
  id int NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  startDate datetime NOT NULL,
  endDate datetime NOT NULL,
  createdBy int NOT NULL,
  link varchar(255) NOT NULL,
  description text NOT NULL,
  createdAt datetime NOT NULL,
  updatedAt datetime NOT NULL,
  PRIMARY KEY (id)
);
-- Create company_user_meeting table
CREATE TABLE company_user_meeting (
  user_id int NOT NULL,
  meeting_id int NOT NULL,
  PRIMARY KEY (user_id, meeting_id),
  FOREIGN KEY (user_id) REFERENCES company_users (id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (meeting_id) REFERENCES company_meetings (id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_chats table
CREATE TABLE company_chats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  idSource int(11),
  isGroup BOOLEAN
);
-- Create company_chat_messages table
CREATE TABLE company_chat_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  chatId int(11),
  idSource int(11),
  isMine BOOLEAN,
  seen BOOLEAN,
  value TEXT,
  createdAt DATETIME,
  FOREIGN KEY (chatId) REFERENCES company_chats(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_chat_messages_file table
CREATE TABLE company_chat_messages_file (
    message_id INT,
    files_id INT,
    PRIMARY KEY (message_id, files_id),
    FOREIGN KEY (message_id) REFERENCES company_chat_messages(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (files_id) REFERENCES company_files(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create company_chat_group table
CREATE TABLE company_chat_group (
    chat_id INT,
    user_id INT,
    PRIMARY KEY (chat_id, user_id),
    FOREIGN KEY (chat_id) REFERENCES company_chats(id),
    FOREIGN KEY (user_id) REFERENCES company_users(id)
);