Salary Market

use salarymarket ;

/*** 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,

    entity_code VARCHAR(255) NOT NULL,

	state boolean NULL,

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

    menu_type ENUM('menu', 'subMenu', 'widget', 'option') NULL,

    menu_position BOOLEAN NOT NULL,

    PRIMARY KEY (menuCode),  

    UNIQUE (id) 

);

-- Create company_solution_menu_droit table  done

CREATE TABLE solution_menu_rights (

    id INT AUTO_INCREMENT NOT NULL, 

	right_code VARCHAR(255) NOT NULL,  

    right_label VARCHAR(255) NOT NULL,

    menuCode VARCHAR(255) NOT NULL,

    PRIMARY KEY (right_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, 

    subscription_code VARCHAR(255) NOT NULL,

    subscription_type ENUM('On The Menu', 'Essential','Advanced') NOT NULL,

	PRIMARY KEY (subscription_code) 

);

CREATE TABLE solution_subscription_menu (

    id INT AUTO_INCREMENT  NOT NULL,

    subscriptionCode VARCHAR(255) NOT NULL,

    menuCode VARCHAR(255) NOT NULL,

    FOREIGN KEY (subscriptionCode) REFERENCES solution_subscription(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, -- from solution_subscription_menu conbination 

    menuCode VARCHAR(255) NOT NULL, -- from solution_subscription_menu

    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(right_code) ON DELETE CASCADE ON UPDATE CASCADE,

    UNIQUE (id) 

);

CREATE TABLE solution_setting_country (

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

active boolean,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

CREATE TABLE solution_setting_country_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

countryCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_business_sector (

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

CREATE TABLE solution_setting_business_sector_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

businessSectorCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_sales_turnover (

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

); 

CREATE TABLE solution_setting_sales_turnover_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

salesTurnoverCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (salesTurnoverCode) REFERENCES solution_setting_sales_turnover(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_company_size (

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

CREATE TABLE solution_setting_company_size_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

companySizeCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (companySizeCode) REFERENCES solution_setting_company_size(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_geographic_extent (

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

CREATE TABLE solution_setting_geographic_extent_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

geographicExtentCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (geographicExtentCode) REFERENCES solution_setting_geographic_extent(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_value_chain (

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

);

CREATE TABLE solution_setting_value_chain_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

valueChainCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (valueChainCode) REFERENCES solution_setting_value_chain(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_town(

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

countryCode VARCHAR(255) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE 

);

CREATE TABLE solution_setting_town_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

townCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (townCode) REFERENCES solution_setting_town(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_sub_sector (

id INT AUTO_INCREMENT UNIQUE,

code VARCHAR(255) NOT NULL PRIMARY KEY,

businessSectorCode VARCHAR(255) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE solution_setting_sub_sector_translate (

id  INT AUTO_INCREMENT PRIMARY KEY,

Label TEXT,

subSectorCode VARCHAR(255) NOT NULL,

languageId INT NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (subSectorCode) REFERENCES solution_setting_sub_sector(code) ON DELETE CASCADE ON UPDATE 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,

    taxRegistration VARCHAR(255) NULL,

    company_type ENUM('Solution','Customer') NULL,

    parentId INT NOT NULL,

    subscription_type VARCHAR(255) NULL,

    company_status VARCHAR(255) NULL,

    companySizeCode VARCHAR(255) NULL,

    valueChainCode VARCHAR(255) NULL,

    businessSectorCode VARCHAR(255) NULL,

    geographicExtentCode VARCHAR(255) NULL,

    salesTurnoverCode VARCHAR(255) NULL,

    subSectorCode VARCHAR(255) NULL,

    countryCode VARCHAR(255) NULL,

    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (subSectorCode) REFERENCES solution_setting_sub_sector(code) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (companySizeCode) REFERENCES solution_setting_company_size(code) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (valueChainCode) REFERENCES solution_setting_value_chain(code) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (businessSectorCode) REFERENCES solution_setting_business_sector(code) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (geographicExtentCode) REFERENCES solution_setting_geographic_extent(code) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (salesTurnoverCode) REFERENCES solution_setting_sales_turnover(code) ON DELETE CASCADE ON UPDATE CASCADE,

    FOREIGN KEY (countryCode) REFERENCES solution_setting_country(code) ON DELETE CASCADE ON UPDATE CASCADE

);

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

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

	user_password VARCHAR(255) NULL,

    phone INT NULL,

    poste VARCHAR(255) NULL,

	avatar VARCHAR(255) NOT NULL,

    resetToken VARCHAR(255) NULL,

    companyId INT NOT NULL,

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

    entity_code text NOT NULL,

    title text NOT NULL,

    state boolean NULL,

	entity_level INT  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,

    rightId INT NULL, 

	libelle VARCHAR(255) NOT NULL,

	actif boolean null ,

    FOREIGN KEY (rightId) 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),

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

    column_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),

    activity_action ENUM('create', 'update', 'delete', 'validate', 'send email')  NULL,

    activity_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)

);