pvo_v1_2023_202306110956.sql

--
-- Script was generated by Devart dbForge Studio for MySQL, Version 9.2.34.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 11-Jun-23 9:56:15 AM
-- Server version: 10.6.13
-- Client version: 4.1
--

-- 
-- Disable foreign keys
-- 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

-- 
-- Set SQL mode
-- 
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- 
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE pvo_v1_2023;

--
-- Drop event `Event_DeleteCacheSql`
--
DROP EVENT IF EXISTS Event_DeleteCacheSql;

--
-- Drop table `cache_sql`
--
DROP TABLE IF EXISTS cache_sql;

--
-- Drop view `view_example`
--
DROP VIEW IF EXISTS view_example CASCADE;

--
-- Drop procedure `Proc_Account_CreateActivatedAccountData`
--
DROP PROCEDURE IF EXISTS Proc_Account_CreateActivatedAccountData;

--
-- Drop table `tone`
--
DROP TABLE IF EXISTS tone;

--
-- Drop table `sys_tone`
--
DROP TABLE IF EXISTS sys_tone;

--
-- Drop table `mode`
--
DROP TABLE IF EXISTS mode;

--
-- Drop table `sys_mode`
--
DROP TABLE IF EXISTS sys_mode;

--
-- Drop table `register`
--
DROP TABLE IF EXISTS register;

--
-- Drop table `sys_register`
--
DROP TABLE IF EXISTS sys_register;

--
-- Drop table `nuance`
--
DROP TABLE IF EXISTS nuance;

--
-- Drop table `sys_nuance`
--
DROP TABLE IF EXISTS sys_nuance;

--
-- Drop table `dialect`
--
DROP TABLE IF EXISTS dialect;

--
-- Drop table `sys_dialect`
--
DROP TABLE IF EXISTS sys_dialect;

--
-- Drop view `view_concept_relationship`
--
DROP VIEW IF EXISTS view_concept_relationship CASCADE;

--
-- Drop procedure `Proc_Dictionary_TransferDictionaryData`
--
DROP PROCEDURE IF EXISTS Proc_Dictionary_TransferDictionaryData;

--
-- Drop procedure `Proc_Dictionary_CloneDictionaryData`
--
DROP PROCEDURE IF EXISTS Proc_Dictionary_CloneDictionaryData;

--
-- Drop procedure `Proc_Dictionary_DeleteDictionaryData`
--
DROP PROCEDURE IF EXISTS Proc_Dictionary_DeleteDictionaryData;

--
-- Drop table `concept_relationship`
--
DROP TABLE IF EXISTS concept_relationship;

--
-- Drop table `concept_link`
--
DROP TABLE IF EXISTS concept_link;

--
-- Drop table `sys_concept_link`
--
DROP TABLE IF EXISTS sys_concept_link;

--
-- Drop procedure `Proc_Log_GetLogsFilterPaging`
--
DROP PROCEDURE IF EXISTS Proc_Log_GetLogsFilterPaging;

--
-- Drop table `audit_log`
--
DROP TABLE IF EXISTS audit_log;

--
-- Drop view `view_example_relationship`
--
DROP VIEW IF EXISTS view_example_relationship CASCADE;

--
-- Drop procedure `dvrt_4308f566-743d-415a-9b73-05b190e3e787`
--
DROP PROCEDURE IF EXISTS `dvrt_4308f566-743d-415a-9b73-05b190e3e787`;

--
-- Drop procedure `dvrt_4870c659-05f0-4536-b2b3-838c6a58a059`
--
DROP PROCEDURE IF EXISTS `dvrt_4870c659-05f0-4536-b2b3-838c6a58a059`;

--
-- Drop procedure `Proc_Example_SearchExample`
--
DROP PROCEDURE IF EXISTS Proc_Example_SearchExample;

--
-- Drop table `example_relationship`
--
DROP TABLE IF EXISTS example_relationship;

--
-- Drop procedure `Proc_Concept_SearchConcept`
--
DROP PROCEDURE IF EXISTS Proc_Concept_SearchConcept;

--
-- Drop procedure `Proc_Dictionary_GetNumberRecord`
--
DROP PROCEDURE IF EXISTS Proc_Dictionary_GetNumberRecord;

--
-- Drop table `concept`
--
DROP TABLE IF EXISTS concept;

--
-- Drop table `example`
--
DROP TABLE IF EXISTS example;

--
-- Drop table `dictionary`
--
DROP TABLE IF EXISTS dictionary;

--
-- Drop table `example_link`
--
DROP TABLE IF EXISTS example_link;

--
-- Drop table `user_setting`
--
DROP TABLE IF EXISTS user_setting;

--
-- Drop table `user`
--
DROP TABLE IF EXISTS user;

--
-- Drop table `sys_example_link`
--
DROP TABLE IF EXISTS sys_example_link;

--
-- Set default database
--
USE pvo_v1_2023;

--
-- Create table `sys_example_link`
--
CREATE TABLE sys_example_link (
  sys_example_link_id char(36) NOT NULL COMMENT 'id khóa chính',
  example_link_name varchar(255) DEFAULT NULL COMMENT 'Tên loại liên kết',
  example_link_type int(11) DEFAULT NULL COMMENT 'Loại liên kết',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (sys_example_link_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1820,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng sys loại liên kết giữa example với concept';

--
-- Create table `user`
--
CREATE TABLE user (
  user_id char(36) NOT NULL COMMENT 'id khóa chính',
  user_name varchar(100) DEFAULT NULL COMMENT 'Tên đăng nhập',
  password char(60) DEFAULT NULL COMMENT 'Mã hash mật khẩu',
  email varchar(50) DEFAULT NULL,
  full_name varchar(255) DEFAULT NULL COMMENT 'Tên đầy đủ',
  display_name varchar(100) DEFAULT NULL COMMENT 'Tên hiển thị',
  birthday datetime DEFAULT NULL COMMENT 'Ngày sinh',
  `position` varchar(255) DEFAULT NULL COMMENT 'Công việc',
  avatar text DEFAULT NULL COMMENT 'Link ảnh đại diện',
  status int(11) DEFAULT NULL COMMENT 'Trạng thái tài khoản',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (user_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin người dùng';

--
-- Create index `email` on table `user`
--
ALTER TABLE user
ADD UNIQUE INDEX email (email);

--
-- Create index `user_name` on table `user`
--
ALTER TABLE user
ADD UNIQUE INDEX user_name (user_name);

--
-- Create table `user_setting`
--
CREATE TABLE user_setting (
  user_setting_id char(36) NOT NULL COMMENT 'id khóa chính',
  user_id char(36) DEFAULT NULL COMMENT 'id người dùng',
  setting_key varchar(255) DEFAULT NULL COMMENT 'key',
  setting_value varchar(255) DEFAULT NULL COMMENT 'value',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (user_setting_id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin cấu hình của người dùng';

--
-- Create foreign key
--
ALTER TABLE user_setting
ADD CONSTRAINT FK_user_setting_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `example_link`
--
CREATE TABLE example_link (
  example_link_id char(36) NOT NULL COMMENT 'id khóa chính',
  sys_example_link_id char(36) DEFAULT NULL COMMENT 'sys id',
  user_id char(36) DEFAULT NULL COMMENT 'id người dùng',
  example_link_name varchar(255) DEFAULT NULL COMMENT 'tên loại liên kết',
  example_link_type int(11) DEFAULT NULL COMMENT 'loại liên kết',
  sort_order int(11) DEFAULT NULL COMMENT 'thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (example_link_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1820,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin loại liên kết example-content';

--
-- Create foreign key
--
ALTER TABLE example_link
ADD CONSTRAINT FK_example_link_sys_example_link_id FOREIGN KEY (sys_example_link_id)
REFERENCES sys_example_link (sys_example_link_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE example_link
ADD CONSTRAINT FK_example_link_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `dictionary`
--
CREATE TABLE dictionary (
  dictionary_id char(36) NOT NULL COMMENT 'id khóa chính',
  user_id char(36) DEFAULT NULL COMMENT 'id người dùng (tham chiếu bảng user)',
  dictionary_name varchar(255) DEFAULT NULL COMMENT 'Tên dictionary',
  last_view_at datetime DEFAULT NULL COMMENT 'Thời điểm truy cập gần nhất',
  created_date datetime DEFAULT CURRENT_TIMESTAMP(),
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (dictionary_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin dictionary';

--
-- Create foreign key
--
ALTER TABLE dictionary
ADD CONSTRAINT FK_dictionary_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `example`
--
CREATE TABLE example (
  example_id char(36) NOT NULL,
  dictionary_id char(36) DEFAULT NULL,
  detail text DEFAULT NULL,
  detail_html text DEFAULT NULL,
  note varchar(255) DEFAULT NULL,
  tone_id char(36) DEFAULT NULL,
  register_id char(36) DEFAULT NULL,
  dialect_id char(36) DEFAULT NULL,
  mode_id char(36) DEFAULT NULL,
  nuance_id char(36) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (example_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1489,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin example';

--
-- Create index `IX_fulltext_detail_note` on table `example`
--
ALTER TABLE example
ADD FULLTEXT INDEX IX_fulltext_detail_note (detail, note);

--
-- Create index `detail_note_ndx` on table `example`
--
ALTER TABLE example
ADD FULLTEXT INDEX detail_note_ndx (detail, note);

--
-- Create foreign key
--
ALTER TABLE example
ADD CONSTRAINT FK_example_dictionary_id FOREIGN KEY (dictionary_id)
REFERENCES dictionary (dictionary_id) ON DELETE CASCADE;

--
-- Create table `concept`
--
CREATE TABLE concept (
  concept_id char(36) NOT NULL COMMENT 'id khóa chính',
  dictionary_id char(36) DEFAULT NULL COMMENT 'id dictionary (tham chiếu bảng dictionary)',
  title varchar(255) DEFAULT NULL COMMENT 'Tên concept',
  description varchar(500) DEFAULT NULL COMMENT 'Mô tả concept',
  normalized_title varchar(255) DEFAULT NULL,
  soundex_title varchar(50) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (concept_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1560,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin concept';

--
-- Create foreign key
--
ALTER TABLE concept
ADD CONSTRAINT FK_concept_dictionary_id FOREIGN KEY (dictionary_id)
REFERENCES dictionary (dictionary_id) ON DELETE CASCADE;

DELIMITER $$

--
-- Create procedure `Proc_Dictionary_GetNumberRecord`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Dictionary_GetNumberRecord (IN $DictionaryId char(36), OUT $NumberConcept int, OUT $NumberExample int)
BEGIN
  SET $NumberConcept = (SELECT
      COUNT(1)
    FROM concept
    WHERE dictionary_id = $DictionaryId);

  SET $NumberExample = (SELECT
      COUNT(1)
    FROM example
    WHERE dictionary_id = $DictionaryId);

END
$$

--
-- Create procedure `Proc_Concept_SearchConcept`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Concept_SearchConcept (IN $DictionaryId char(36), IN $SearchKey varchar(255), IN $IsSearchSoundex bit)
COMMENT 'Store lấy danh sách concept trong từ điển theo điều kiện tìm kiếm'
BEGIN
  SELECT
    concept_id,
    dictionary_id,
    title,
    description,
    normalized_title,
    soundex_title,
    created_date,
    modified_date
  FROM concept c
  WHERE c.dictionary_id = $DictionaryId
  AND ($SearchKey IS NULL
  OR $SearchKey = ''
  OR c.normalized_title LIKE CONCAT('%', $SearchKey, '%')
  OR ($IsSearchSoundex = TRUE
  AND c.soundex_title = SOUNDEX($SearchKey)));
END
$$

DELIMITER ;

--
-- Create table `example_relationship`
--
CREATE TABLE example_relationship (
  example_relationship_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id khóa chính',
  dictionary_id char(36) DEFAULT NULL COMMENT 'id dictionary',
  concept_id char(36) DEFAULT NULL,
  example_id char(36) DEFAULT NULL,
  example_link_id char(36) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (example_relationship_id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 14,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa liên kết example-content';

--
-- Create foreign key
--
ALTER TABLE example_relationship
ADD CONSTRAINT FK_example_relationship_concept_id FOREIGN KEY (concept_id)
REFERENCES concept (concept_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE example_relationship
ADD CONSTRAINT FK_example_relationship_dictionary_id FOREIGN KEY (dictionary_id)
REFERENCES dictionary (dictionary_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE example_relationship
ADD CONSTRAINT FK_example_relationship_example_id FOREIGN KEY (example_id)
REFERENCES example (example_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE example_relationship
ADD CONSTRAINT FK_example_relationship_example_link_id FOREIGN KEY (example_link_id)
REFERENCES example_link (example_link_id) ON DELETE CASCADE;

DELIMITER $$

--
-- Create procedure `Proc_Example_SearchExample`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Example_SearchExample (IN $DictionaryId char(36), IN $Keyword varchar(500), IN $ToneId char(36), IN $ModeId char(36), IN $RegisterId char(36), IN $NuanceId char(36), IN $DialectId char(36), IN $ListLinkedConceptId longtext, IN $IsSearchUndecided bit, IN $IsFulltextSearch bit)
COMMENT 'Tìm kiếm example'
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmpExample;
  CREATE TEMPORARY TABLE tmpExample (
    example_id char(36),
    detail text,
    detail_html text
  );

  INSERT INTO tmpExample (example_id, detail, detail_html)
    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM example e
    WHERE e.dictionary_id = $DictionaryId
    AND ($ToneId IS NULL
    OR e.tone_id = $ToneId)
    AND ($ModeId IS NULL
    OR e.mode_id = $ModeId)
    AND ($RegisterId IS NULL
    OR e.register_id = $RegisterId)
    AND ($NuanceId IS NULL
    OR e.nuance_id = $NuanceId)
    AND ($DialectId IS NULL
    OR e.dialect_id = $DialectId)
    AND ($Keyword IS NULL
    OR $Keyword = ''
    OR e.detail LIKE CONCAT('%', $Keyword, '%')
    OR e.note LIKE CONCAT('%', $Keyword, '%')
    OR ($IsFulltextSearch = TRUE
    AND MATCH(e.detail, e.note) AGAINST($Keyword)));

  IF $IsSearchUndecided = TRUE THEN -- Trường hợp tìm các example không có liên kết

    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e
      LEFT JOIN example_relationship er
        ON e.example_id = er.example_id
    WHERE er.example_relationship_id IS NULL;

  ELSEIF $ListLinkedConceptId IS NULL THEN -- Trường hợp tìm tất cả các example
    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e;

  ELSE -- Trường hợp search example có liên kết cụ thể
  BEGIN
    DECLARE $NumberLink int DEFAULT 0;

    DROP TEMPORARY TABLE IF EXISTS tmpListConcept;
    CREATE TEMPORARY TABLE tmpListConcept (
      concept_id char(36)
    );

    INSERT INTO tmpListConcept (concept_id)
      SELECT DISTINCT
        concept_id
      FROM JSON_TABLE ($ListLinkedConceptId, '$[*]' COLUMNS (concept_id char(36) PATH '$')) AS jt;

    SET $NumberLink = (SELECT
        COUNT(1)
      FROM tmpListConcept);

    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e
      JOIN example_relationship er
        ON e.example_id = er.example_id
      JOIN tmpListConcept lc
        ON er.concept_id = lc.concept_id
    GROUP BY e.example_id,
             e.detail,
             e.detail_html
    HAVING COUNT(1) = $NumberLink;
  END;
  END IF;
END
$$

--
-- Create procedure `dvrt_4870c659-05f0-4536-b2b3-838c6a58a059`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE `dvrt_4870c659-05f0-4536-b2b3-838c6a58a059` (IN $DictionaryId char(36), IN $Keyword varchar(500), IN $ToneId char(36), IN $ModeId char(36), IN $RegisterId char(36), IN $NuanceId char(36), IN $DialectId char(36), IN $ListLinkedConceptId longtext, IN $IsSearchUndecided bit, IN $IsFulltextSearch bit)
COMMENT 'Tìm kiếm example'
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmpExample;
  CREATE TEMPORARY TABLE tmpExample (
    example_id char(36),
    detail text,
    detail_html text
  );

  INSERT INTO tmpExample (example_id, detail, detail_html)
    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM example e
    WHERE e.dictionary_id = $DictionaryId
    AND ($ToneId IS NULL
    OR e.tone_id = $ToneId)
    AND ($ModeId IS NULL
    OR e.mode_id = $ModeId)
    AND ($RegisterId IS NULL
    OR e.register_id = $RegisterId)
    AND ($NuanceId IS NULL
    OR e.nuance_id = $NuanceId)
    AND ($DialectId IS NULL
    OR e.dialect_id = $DialectId)
    AND ($Keyword IS NULL
    OR $Keyword = ''
    OR e.detail LIKE CONCAT('%', $Keyword, '%')
    OR e.note LIKE CONCAT('%', $Keyword, '%')
    OR ($IsFulltextSearch = TRUE
    AND MATCH(e.detail, e.note) AGAINST($Keyword)));

  IF $IsSearchUndecided = TRUE THEN -- Trường hợp tìm các example không có liên kết

    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e
      LEFT JOIN example_relationship er
        ON e.example_id = er.example_id
    WHERE er.example_relationship_id IS NULL;

  ELSEIF $ListLinkedConceptId IS NULL THEN -- Trường hợp tìm tất cả các example
    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e;

  ELSE -- Trường hợp search example có liên kết cụ thể
  BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmpListConcept;
    CREATE TEMPORARY TABLE tmpListConcept (
      concept_id char(36)
    );

    INSERT INTO tmpListConcept (concept_id)
      SELECT
        concept_id
      FROM JSON_TABLE ($ListLinkedConceptId, '$[*]' COLUMNS (concept_id char(36) PATH '$')) AS jt;

    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e;
  END;
  END IF;
END
$$

--
-- Create procedure `dvrt_4308f566-743d-415a-9b73-05b190e3e787`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE `dvrt_4308f566-743d-415a-9b73-05b190e3e787` (IN $DictionaryId char(36), IN $Keyword varchar(500), IN $ToneId char(36), IN $ModeId char(36), IN $RegisterId char(36), IN $NuanceId char(36), IN $DialectId char(36), IN $ListLinkedConceptId longtext, IN $IsSearchUndecided bit, IN $IsFulltextSearch bit)
COMMENT 'Tìm kiếm example'
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmpExample;
  CREATE TEMPORARY TABLE tmpExample (
    example_id char(36),
    detail text,
    detail_html text
  );

  INSERT INTO tmpExample (example_id, detail, detail_html)
    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM example e
    WHERE e.dictionary_id = $DictionaryId
    AND ($ToneId IS NULL
    OR e.tone_id = $ToneId)
    AND ($ModeId IS NULL
    OR e.mode_id = $ModeId)
    AND ($RegisterId IS NULL
    OR e.register_id = $RegisterId)
    AND ($NuanceId IS NULL
    OR e.nuance_id = $NuanceId)
    AND ($DialectId IS NULL
    OR e.dialect_id = $DialectId)
    AND ($Keyword IS NULL
    OR $Keyword = ''
    OR e.detail LIKE CONCAT('%', $Keyword, '%')
    OR e.note LIKE CONCAT('%', $Keyword, '%')
    OR ($IsFulltextSearch = TRUE
    AND MATCH(e.detail, e.note) AGAINST($Keyword)));

  IF $IsSearchUndecided = TRUE THEN -- Trường hợp tìm các example không có liên kết

    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e
      LEFT JOIN example_relationship er
        ON e.example_id = er.example_id
    WHERE er.example_relationship_id IS NULL;

  ELSEIF $ListLinkedConceptId IS NULL THEN -- Trường hợp tìm tất cả các example
    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e;

  ELSE -- Trường hợp search example có liên kết cụ thể
  BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmpListConcept;
    CREATE TEMPORARY TABLE tmpListConcept (
      concept_id char(36)
    );

    INSERT INTO tmpListConcept (concept_id)
      SELECT
        concept_id
      FROM JSON_TABLE ($ListLinkedConceptId, '$[*]' COLUMNS (concept_id char(36) PATH '$')) AS jt;

    SELECT
      e.example_id,
      e.detail,
      e.detail_html
    FROM tmpExample e;
  END;
  END IF;
END
$$

DELIMITER ;

--
-- Create view `view_example_relationship`
--
CREATE
DEFINER = 'root'@'localhost'
VIEW view_example_relationship
AS
SELECT
  `er`.`dictionary_id` AS `dictionary_id`,
  `c`.`concept_id` AS `concept_id`,
  `c`.`title` AS `concept`,
  `e`.`example_id` AS `example_id`,
  `e`.`detail` AS `example`,
  `e`.`detail_html` AS `example_html`,
  `el`.`example_link_id` AS `example_link_id`,
  `el`.`example_link_name` AS `example_link_name`,
  `c`.`created_date` AS `concept_created_date`,
  `e`.`created_date` AS `example_created_date`,
  `er`.`created_date` AS `relation_created_date`
FROM (((`example_relationship` `er`
  JOIN `concept` `c`
    ON (`er`.`concept_id` = `c`.`concept_id`))
  JOIN `example` `e`
    ON (`er`.`example_id` = `e`.`example_id`))
  LEFT JOIN `example_link` `el`
    ON (`er`.`example_link_id` = `el`.`example_link_id`));

--
-- Create table `audit_log`
--
CREATE TABLE audit_log (
  audit_log_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id khóa chính',
  user_id char(36) DEFAULT NULL COMMENT 'id người dùng',
  screen_info varchar(255) DEFAULT NULL COMMENT 'Thông tin màn hình',
  action_type int(11) DEFAULT NULL COMMENT 'Loại hành động',
  reference varchar(255) DEFAULT NULL COMMENT 'Thông tin tham chiếu, vd: id dictionary',
  description varchar(500) DEFAULT NULL COMMENT 'Mô tả',
  user_agent varchar(255) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date varchar(255) DEFAULT NULL,
  PRIMARY KEY (audit_log_id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 14,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin lịch sử truy cập';

--
-- Create foreign key
--
ALTER TABLE audit_log
ADD CONSTRAINT FK_audit_log_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

DELIMITER $$

--
-- Create procedure `Proc_Log_GetLogsFilterPaging`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Log_GetLogsFilterPaging (IN $UserId char(36), IN $SearchFilter varchar(255), IN $PageIndex int, IN $PageSize int, IN $DateFrom datetime, IN $DateTo datetime, OUT $TotalRecord int, OUT $TotalPage int)
COMMENT 'Store lấy dữ liệu filter, phân trang audit log'
BEGIN
  IF $PageSize > 0 THEN
    SELECT
      al.audit_log_id,
      al.user_id,
      al.screen_info,
      al.action_type,
      al.reference,
      al.description,
      al.user_agent,
      al.created_date
    FROM audit_log al
    WHERE al.user_id = $UserId
    AND (
    $SearchFilter IS NULL
    OR $SearchFilter = ""
    OR (
    al.screen_info LIKE CONCAT('%', $SearchFilter, '%')
    OR al.description LIKE CONCAT('%', $SearchFilter, '%')
    OR al.user_agent LIKE CONCAT('%', $SearchFilter, '%')
    -- action_type ???
    )
    )
    AND (
    $DateFrom IS NULL
    OR al.created_date >= $DateFrom
    )
    AND (
    $DateTo IS NULL
    OR al.created_date <= $DateTo
    )
    ORDER BY al.created_date DESC
    LIMIT $PageSize OFFSET $PageIndex;

    -- Tổng số bản ghi
    SET $TotalRecord = (SELECT
        COUNT(al.audit_log_id)
      FROM audit_log al
      WHERE al.user_id = $UserId
      AND (
      $SearchFilter IS NULL
      OR $SearchFilter = ""
      OR (
      al.screen_info LIKE CONCAT('%', $SearchFilter, '%')
      OR al.description LIKE CONCAT('%', $SearchFilter, '%')
      OR al.user_agent LIKE CONCAT('%', $SearchFilter, '%')
      -- action_type ???
      )
      )
      AND (
      $DateFrom IS NULL
      OR al.created_date >= $DateFrom
      )
      AND (
      $DateTo IS NULL
      OR al.created_date <= $DateTo
      ));

    -- Tổng số trang
    SET $TotalPage = CEILING(1.0 * $TotalRecord / $PageSize);
  END IF;
END
$$

DELIMITER ;

--
-- Create table `sys_concept_link`
--
CREATE TABLE sys_concept_link (
  sys_concept_link_id char(36) NOT NULL COMMENT 'id khóa chính',
  concept_link_name varchar(255) DEFAULT NULL COMMENT 'Tên loại liên kết',
  concept_link_type int(11) DEFAULT NULL COMMENT 'Loại liên kết',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (sys_concept_link_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 5461,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng sys loại liên kết giữa concept với concept';

--
-- Create table `concept_link`
--
CREATE TABLE concept_link (
  concept_link_id char(36) NOT NULL COMMENT 'id khóa chính',
  sys_concept_link_id char(36) DEFAULT NULL COMMENT 'id bảng sys',
  user_id char(36) DEFAULT NULL COMMENT 'id người dùng',
  concept_link_name varchar(255) DEFAULT NULL COMMENT 'Tên loại liên kết',
  concept_link_type int(11) DEFAULT NULL COMMENT 'Loại liên kết',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (concept_link_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 5461,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa thông tin loại liên kết concept-concept';

--
-- Create foreign key
--
ALTER TABLE concept_link
ADD CONSTRAINT FK_concept_link_sys_concept_link_id FOREIGN KEY (sys_concept_link_id)
REFERENCES sys_concept_link (sys_concept_link_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE concept_link
ADD CONSTRAINT FK_concept_link_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `concept_relationship`
--
CREATE TABLE concept_relationship (
  concept_relationship_id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id khóa chính',
  dictionary_id char(36) DEFAULT NULL COMMENT 'id dictionảy',
  concept_id char(36) DEFAULT NULL COMMENT 'id concept',
  parent_id char(36) DEFAULT NULL COMMENT 'id concept cha',
  concept_link_id char(36) DEFAULT NULL COMMENT 'id loại liên kết concept-concept',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (concept_relationship_id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 40,
AVG_ROW_LENGTH = 910,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng chứa liên kết concept-concept';

--
-- Create foreign key
--
ALTER TABLE concept_relationship
ADD CONSTRAINT FK_concept_relationship_concept_id FOREIGN KEY (concept_id)
REFERENCES concept (concept_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE concept_relationship
ADD CONSTRAINT FK_concept_relationship_concept_link_id FOREIGN KEY (concept_link_id)
REFERENCES concept_link (concept_link_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE concept_relationship
ADD CONSTRAINT FK_concept_relationship_dictionary_id FOREIGN KEY (dictionary_id)
REFERENCES dictionary (dictionary_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE concept_relationship
ADD CONSTRAINT FK_concept_relationship_parent_id FOREIGN KEY (parent_id)
REFERENCES concept (concept_id) ON DELETE CASCADE;

DELIMITER $$

--
-- Create procedure `Proc_Dictionary_DeleteDictionaryData`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Dictionary_DeleteDictionaryData (IN $DictionaryId char(36))
COMMENT 'Store thực hiện xóa dữ liệu của 1 dictionary'
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
    -- ==================================================================================================================
    -- ====================================== Xóa dữ liệu của dictionary =======================================
    -- ==================================================================================================================
    DELETE
      FROM concept_relationship
    WHERE dictionary_id = $DictionaryId;

    DELETE
      FROM example_relationship
    WHERE dictionary_id = $DictionaryId;

    DELETE
      FROM concept
    WHERE dictionary_id = $DictionaryId;

    DELETE
      FROM example
    WHERE dictionary_id = $DictionaryId;
  COMMIT;
END
$$

--
-- Create procedure `Proc_Dictionary_CloneDictionaryData`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Dictionary_CloneDictionaryData (IN $SourceDictionaryId char(36), IN $DestDictionaryId char(36))
COMMENT 'Store thực hiện clone dữ liệu từ điển (có xóa dữ liệu từ điển đích)'
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
    -- ==================================================================================================================
    -- ====================================== Xóa dữ liệu của dictionary đích ===================================
    -- ==================================================================================================================
    DELETE
      FROM concept_relationship
    WHERE dictionary_id = $DestDictionaryId;

    DELETE
      FROM example_relationship
    WHERE dictionary_id = $DestDictionaryId;

    DELETE
      FROM concept
    WHERE dictionary_id = $DestDictionaryId;

    DELETE
      FROM example
    WHERE dictionary_id = $DestDictionaryId;

    -- ==================================================================================================================
    -- ====================================== Chuẩn bị bảng tạm map id phần tử cũ và phần tử mới ========================
    -- ==================================================================================================================
    -- Bảng tạm map id concept cũ và concept mới
    DROP TEMPORARY TABLE IF EXISTS tmpMapConcept;
    CREATE TEMPORARY TABLE tmpMapConcept (
      old_concept_id char(36),
      new_concept_id char(36)
    );

    INSERT INTO tmpMapConcept (old_concept_id, new_concept_id)
      SELECT
        c.concept_id,
        UUID()
      FROM concept c
      WHERE c.dictionary_id = $SourceDictionaryId;

    -- Bảng tạm map id example cũ và example mới
    DROP TEMPORARY TABLE IF EXISTS tmpMapExample;
    CREATE TEMPORARY TABLE tmpMapExample (
      old_example_id char(36),
      new_example_id char(36)
    );

    INSERT INTO tmpMapExample (old_example_id, new_example_id)
      SELECT
        e.example_id,
        UUID()
      FROM example e
      WHERE e.dictionary_id = $SourceDictionaryId;

    -- ==================================================================================================================
    -- ====================================== Insert dữ liệu clone ======================================================
    -- ==================================================================================================================
    INSERT INTO concept (concept_id, dictionary_id, title, description, created_date, modified_date)
      SELECT
        mc.new_concept_id,
        $DestDictionaryId,
        c.title,
        c.description,
        NOW(),
        NULL
      FROM concept c
        JOIN tmpMapConcept mc
          ON c.concept_id = mc.old_concept_id
      WHERE c.dictionary_id = $SourceDictionaryId;

    INSERT INTO concept_relationship (dictionary_id, concept_id, parent_id, concept_link_id, created_date, modified_date)
      SELECT
        $DestDictionaryId,
        mc.new_concept_id,
        mc1.new_concept_id,
        cr.concept_link_id,
        NOW(),
        NULL
      FROM concept_relationship cr
        JOIN tmpMapConcept mc
          ON cr.concept_id = mc.old_concept_id
        JOIN tmpMapConcept mc1
          ON cr.parent_id = mc1.old_concept_id
      WHERE cr.dictionary_id = $SourceDictionaryId;

    INSERT INTO example (example_id, dictionary_id, detail, detail_html, note, tone_id, register_id, dialect_id, mode_id, nuance_id, created_date, modified_date)
      SELECT
        me.new_example_id,
        $DestDictionaryId,
        e.detail,
        e.detail_html,
        e.note,
        e.tone_id,
        e.register_id,
        e.dialect_id,
        e.mode_id,
        e.nuance_id,
        NOW(),
        NULL
      FROM example e
        JOIN tmpMapExample me
          ON e.example_id = me.old_example_id
      WHERE e.dictionary_id = $SourceDictionaryId;

    INSERT INTO example_relationship (dictionary_id, concept_id, example_id, example_link_id, created_date, modified_date)
      SELECT
        $DestDictionaryId,
        mc.new_concept_id,
        me.new_example_id,
        er.example_link_id,
        NOW(),
        NULL
      FROM example_relationship er
        JOIN tmpMapConcept mc
          ON er.concept_id = mc.old_concept_id
        JOIN tmpMapExample me
          ON er.example_id = me.old_example_id
      WHERE er.dictionary_id = $SourceDictionaryId;
  COMMIT;
END
$$

--
-- Create procedure `Proc_Dictionary_TransferDictionaryData`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Dictionary_TransferDictionaryData (IN $SourceDictionaryId char(36), IN $DestDictionaryId char(36), IN $IsDeleteData bit)
COMMENT 'Store thực hiện copy dữ liệu từ từ điển nguồn và gộp vào dữ liệu ở từ điển đích'
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  IF $IsDeleteData = TRUE THEN
    CALL Proc_Dictionary_CloneDictionaryData($SourceDictionaryId, $DestDictionaryId);
  ELSE
  BEGIN
    START TRANSACTION;
      -- ==================================================================================================================
      -- ====================================== Chuẩn bị bảng tạm map id phần tử cũ và phần tử mới ========================
      -- ==================================================================================================================
      -- Bảng tạm map id concept nguồn và concept đích
      DROP TEMPORARY TABLE IF EXISTS tmpMapConcept;
      CREATE TEMPORARY TABLE tmpMapConcept (
        source_concept_id char(36),
        dest_concept_id char(36),
        new_concept_id char(36)
      );

      INSERT INTO tmpMapConcept (source_concept_id, dest_concept_id, new_concept_id)
        SELECT
          c.concept_id,
          c1.concept_id,
          IFNULL(c1.concept_id, UUID())
        FROM concept c
          LEFT JOIN concept c1
            ON c.title = c1.title
            AND c1.dictionary_id = $DestDictionaryId
        WHERE c.dictionary_id = $SourceDictionaryId;

      -- Bảng tạm map id example cũ và example mới
      DROP TEMPORARY TABLE IF EXISTS tmpMapExample;
      CREATE TEMPORARY TABLE tmpMapExample (
        source_example_id char(36),
        dest_example_id char(36),
        new_example_id char(36)
      );

      INSERT INTO tmpMapExample (source_example_id, dest_example_id, new_example_id)
        SELECT
          e.example_id,
          e1.example_id,
          IFNULL(e1.example_id, UUID())
        FROM example e
          LEFT JOIN example e1
            ON e.detail_html = e1.detail_html
            AND e1.dictionary_id = $DestDictionaryId
        WHERE e.dictionary_id = $SourceDictionaryId;

      -- ==================================================================================================================
      -- ====================================== Insert dữ liệu copy =======================================================
      -- ==================================================================================================================
      INSERT INTO concept (concept_id, dictionary_id, title, description, created_date, modified_date)
        SELECT
          mc.new_concept_id,
          $DestDictionaryId,
          c.title,
          c.description,
          NOW(),
          NULL
        FROM concept c
          JOIN tmpMapConcept mc
            ON c.concept_id = mc.source_concept_id
            AND mc.dest_concept_id IS NULL
        WHERE c.dictionary_id = $SourceDictionaryId;

      INSERT INTO example (example_id, dictionary_id, detail, detail_html, note, tone_id, register_id, dialect_id, mode_id, nuance_id, created_date, modified_date)
        SELECT
          me.new_example_id,
          $DestDictionaryId,
          e.detail,
          e.detail_html,
          e.note,
          e.tone_id,
          e.register_id,
          e.dialect_id,
          e.mode_id,
          e.nuance_id,
          NOW(),
          NULL
        FROM example e
          JOIN tmpMapExample me
            ON e.example_id = me.source_example_id
            AND me.dest_example_id IS NULL
        WHERE e.dictionary_id = $SourceDictionaryId;

      INSERT INTO concept_relationship (dictionary_id, concept_id, parent_id, concept_link_id, created_date, modified_date)
        SELECT
          $DestDictionaryId,
          tmpSrc.id1,
          tmpSrc.id2,
          tmpSrc.link_id,
          NOW(),
          NULL
        FROM (SELECT
            mc.new_concept_id id1,
            mc1.new_concept_id id2,
            cr.concept_link_id link_id
          FROM concept_relationship cr
            JOIN tmpMapConcept mc
              ON cr.concept_id = mc.source_concept_id
            JOIN tmpMapConcept mc1
              ON cr.parent_id = mc1.source_concept_id
          WHERE cr.dictionary_id = $SourceDictionaryId) tmpSrc
          LEFT JOIN (SELECT
              cr1.concept_id id1,
              cr1.parent_id id2
            FROM concept_relationship cr1
            WHERE cr1.dictionary_id = $DestDictionaryId
            UNION
            SELECT
              cr1.parent_id id1,
              cr1.concept_id id2
            FROM concept_relationship cr1
            WHERE cr1.dictionary_id = $DestDictionaryId) tmpDest
            ON tmpSrc.id1 = tmpDest.id1
            AND tmpSrc.id2 = tmpDest.id2
        WHERE tmpDest.id1 IS NULL
        AND tmpDest.id2 IS NULL;

      INSERT INTO example_relationship (dictionary_id, concept_id, example_id, example_link_id, created_date, modified_date)
        SELECT
          $DestDictionaryId,
          tmpSrc.c_id,
          tmpSrc.e_id,
          tmpSrc.link_id,
          NOW(),
          NULL
        FROM (SELECT
            mc.new_concept_id c_id,
            me.new_example_id e_id,
            er.example_link_id link_id
          FROM example_relationship er
            JOIN tmpMapConcept mc
              ON er.concept_id = mc.source_concept_id
            JOIN tmpMapExample me
              ON er.example_id = me.source_example_id
          WHERE er.dictionary_id = $SourceDictionaryId) tmpSrc
          LEFT JOIN (SELECT
              er1.concept_id c_id,
              er1.example_id e_id
            FROM example_relationship er1
            WHERE er1.dictionary_id = $DestDictionaryId) tmpDest
            ON tmpSrc.c_id = tmpDest.c_id
            AND tmpSrc.e_id = tmpDest.e_id
        WHERE tmpDest.c_id IS NULL
        AND tmpDest.e_id IS NULL;
    COMMIT;
  END;
  END IF;

END
$$

DELIMITER ;

--
-- Create view `view_concept_relationship`
--
CREATE
DEFINER = 'root'@'localhost'
VIEW view_concept_relationship
AS
SELECT
  `cr`.`dictionary_id` AS `dictionary_id`,
  `c`.`concept_id` AS `child_id`,
  `c`.`title` AS `child_name`,
  `c1`.`concept_id` AS `parent_id`,
  `c1`.`title` AS `parent_name`,
  `cl`.`concept_link_id` AS `concept_link_id`,
  `cl`.`concept_link_name` AS `concept_link_name`,
  `c`.`created_date` AS `child_created_date`,
  `c1`.`created_date` AS `parent_created_date`,
  `cr`.`created_date` AS `relation_created_date`
FROM (((`concept_relationship` `cr`
  JOIN `concept` `c`
    ON (`cr`.`concept_id` = `c`.`concept_id`))
  JOIN `concept` `c1`
    ON (`cr`.`parent_id` = `c1`.`concept_id`))
  LEFT JOIN `concept_link` `cl`
    ON (`cr`.`concept_link_id` = `cl`.`concept_link_id`));

--
-- Create table `sys_dialect`
--
CREATE TABLE sys_dialect (
  sys_dialect_id char(36) NOT NULL COMMENT 'id khóa chính',
  dialect_name varchar(255) DEFAULT NULL COMMENT 'Tên dialect',
  dialect_type int(11) DEFAULT NULL COMMENT 'Loại dialect',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (sys_dialect_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng sys loại dialect';

--
-- Create table `dialect`
--
CREATE TABLE dialect (
  dialect_id char(36) NOT NULL,
  sys_dialect_id char(36) DEFAULT NULL,
  user_id char(36) DEFAULT NULL,
  dialect_name varchar(255) DEFAULT NULL,
  dialect_type int(11) DEFAULT NULL,
  sort_order int(11) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (dialect_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng danh sách dialect';

--
-- Create foreign key
--
ALTER TABLE dialect
ADD CONSTRAINT FK_dialect_sys_dialect_id FOREIGN KEY (sys_dialect_id)
REFERENCES sys_dialect (sys_dialect_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE dialect
ADD CONSTRAINT FK_dialect_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `sys_nuance`
--
CREATE TABLE sys_nuance (
  sys_nuance_id char(36) NOT NULL COMMENT 'id khóa chính',
  nuance_name varchar(255) DEFAULT NULL COMMENT 'Tên nuance',
  nuance_type int(11) DEFAULT NULL COMMENT 'Loại nuance',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (sys_nuance_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng sys loại nuance';

--
-- Create table `nuance`
--
CREATE TABLE nuance (
  nuance_id char(36) NOT NULL,
  sys_nuance_id char(36) DEFAULT NULL,
  user_id char(36) DEFAULT NULL,
  nuance_name varchar(255) DEFAULT NULL,
  nuance_type int(11) DEFAULT NULL,
  sort_order int(11) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (nuance_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng danh sách nuance';

--
-- Create foreign key
--
ALTER TABLE nuance
ADD CONSTRAINT FK_nuance_sys_nuance_id FOREIGN KEY (sys_nuance_id)
REFERENCES sys_nuance (sys_nuance_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE nuance
ADD CONSTRAINT FK_nuance_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `sys_register`
--
CREATE TABLE sys_register (
  sys_register_id char(36) NOT NULL COMMENT 'id khóa chính',
  register_name varchar(255) DEFAULT NULL COMMENT 'Tên register',
  register_type varchar(255) DEFAULT NULL COMMENT 'Loại register',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (sys_register_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1820,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng sys loại register';

--
-- Create table `register`
--
CREATE TABLE register (
  register_id char(36) NOT NULL,
  sys_register_id char(36) DEFAULT NULL,
  user_id char(36) DEFAULT NULL,
  register_name varchar(255) DEFAULT NULL,
  register_type int(11) DEFAULT NULL,
  sort_order int(11) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (register_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 1820,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng danh sách register';

--
-- Create foreign key
--
ALTER TABLE register
ADD CONSTRAINT FK_register_sys_register_id FOREIGN KEY (sys_register_id)
REFERENCES sys_register (sys_register_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE register
ADD CONSTRAINT FK_register_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `sys_mode`
--
CREATE TABLE sys_mode (
  sys_mode_id char(36) NOT NULL COMMENT 'id khóa chính',
  mode_name varchar(255) DEFAULT NULL COMMENT 'Tên mode',
  mode_type int(11) DEFAULT NULL COMMENT 'Loại mode',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (sys_mode_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 5461,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng sys loại mode';

--
-- Create table `mode`
--
CREATE TABLE mode (
  mode_id char(36) NOT NULL,
  sys_mode_id char(36) DEFAULT NULL,
  user_id char(36) DEFAULT NULL,
  mode_name varchar(255) DEFAULT NULL,
  mode_type int(11) DEFAULT NULL,
  sort_order int(11) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (mode_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 5461,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng danh sách mode';

--
-- Create foreign key
--
ALTER TABLE mode
ADD CONSTRAINT FK_mode_sys_mode_id FOREIGN KEY (sys_mode_id)
REFERENCES sys_mode (sys_mode_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE mode
ADD CONSTRAINT FK_mode_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

--
-- Create table `sys_tone`
--
CREATE TABLE sys_tone (
  sys_tone_id char(36) NOT NULL COMMENT 'id khóa chính',
  tone_name varchar(255) DEFAULT NULL COMMENT 'Tên tone',
  tone_type int(11) DEFAULT NULL COMMENT 'Loại tone',
  sort_order int(11) DEFAULT NULL COMMENT 'Thứ tự sắp xếp',
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (sys_tone_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng sys loại tone';

--
-- Create table `tone`
--
CREATE TABLE tone (
  tone_id char(36) NOT NULL,
  sys_tone_id char(36) DEFAULT NULL,
  user_id char(36) DEFAULT NULL,
  tone_name varchar(255) DEFAULT NULL,
  tone_type int(11) DEFAULT NULL,
  sort_order int(11) DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (tone_id)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng danh sách tone';

--
-- Create foreign key
--
ALTER TABLE tone
ADD CONSTRAINT FK_tone_sys_tone_id FOREIGN KEY (sys_tone_id)
REFERENCES sys_tone (sys_tone_id) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE tone
ADD CONSTRAINT FK_tone_user_id FOREIGN KEY (user_id)
REFERENCES user (user_id) ON DELETE CASCADE;

DELIMITER $$

--
-- Create procedure `Proc_Account_CreateActivatedAccountData`
--
CREATE
DEFINER = 'root'@'localhost'
PROCEDURE Proc_Account_CreateActivatedAccountData (IN $UserId char(36))
COMMENT 'Store khởi tạo bộ dữ liệu người dùng'
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
    IF (SELECT
          EXISTS (SELECT
              1
            FROM user u
            WHERE u.user_id = $UserId
            AND u.status = 2)) THEN
      UPDATE user
      SET status = 1
      WHERE user_id = $UserId;

      INSERT INTO dictionary (dictionary_id, user_id, dictionary_name, last_view_at, created_at, modified_at)
        VALUES (UUID(), $UserId, 'My PVO', NULL, NOW(), NULL);

      INSERT INTO concept_link (concept_link_id, sys_concept_link_id, user_id, concept_link_name, concept_link_type, sort_order, created_date, modified_date)
        SELECT
          UUID(),
          scl.sys_concept_link_id,
          $UserId,
          scl.concept_link_name,
          scl.concept_link_type,
          scl.sort_order,
          NOW(),
          NULL
        FROM sys_concept_link scl;

      INSERT INTO example_link (example_link_id, sys_example_link_id, user_id, example_link_name, example_link_type, sort_order, created_date, modified_date)
        SELECT
          UUID(),
          sel.sys_example_link_id,
          $UserId,
          sel.example_link_name,
          sel.example_link_type,
          sel.sort_order,
          NOW(),
          NULL
        FROM sys_example_link sel;

      INSERT INTO tone (tone_id, sys_tone_id, user_id, tone_name, tone_type, sort_order, created_date, modified_date)
        SELECT
          UUID(),
          st.sys_tone_id,
          $UserId,
          st.tone_name,
          st.tone_type,
          st.sort_order,
          NOW(),
          NULL
        FROM sys_tone st;

      INSERT INTO mode (mode_id, sys_mode_id, user_id, mode_name, mode_type, sort_order, created_date, modified_date)
        SELECT
          UUID(),
          sm.sys_mode_id,
          $UserId,
          sm.mode_name,
          sm.mode_type,
          sm.sort_order,
          NOW(),
          NULL
        FROM sys_mode sm;

      INSERT INTO register (register_id, sys_register_id, user_id, register_name, register_type, sort_order, created_date, modified_date)
        SELECT
          UUID(),
          sr.sys_register_id,
          $UserId,
          sr.register_name,
          sr.register_type,
          sr.sort_order,
          NOW(),
          NULL
        FROM sys_register sr;

      INSERT INTO nuance (nuance_id, sys_nuance_id, user_id, nuance_name, nuance_type, sort_order, created_date, modified_date)
        SELECT
          UUID(),
          sn.sys_nuance_id,
          $UserId,
          sn.nuance_name,
          sn.nuance_type,
          sn.sort_order,
          NOW(),
          NULL
        FROM sys_nuance sn;

      INSERT INTO dialect (dialect_id, sys_dialect_id, user_id, dialect_name, dialect_type, sort_order, created_date, modified_date)
        SELECT
          UUID(),
          sd.sys_dialect_id,
          $UserId,
          sd.dialect_name,
          sd.dialect_type,
          sd.sort_order,
          NOW(),
          NULL
        FROM sys_dialect sd;
    END IF;
  COMMIT;
END
$$

DELIMITER ;

--
-- Create view `view_example`
--
CREATE
DEFINER = 'root'@'localhost'
VIEW view_example
AS
SELECT
  `e`.`dictionary_id` AS `dictionary_id`,
  `e`.`example_id` AS `example_id`,
  `e`.`detail` AS `detail`,
  `e`.`detail_html` AS `detail_html`,
  IFNULL(`t`.`tone_name`, 'Neutral') AS `tone_name`,
  IFNULL(`m`.`mode_name`, 'Neutral') AS `mode_name`,
  IFNULL(`r`.`register_name`, 'Neutral') AS `register_name`,
  IFNULL(`n`.`nuance_name`, 'Neutral') AS `nuance_name`,
  IFNULL(`d`.`dialect_name`, 'Neutral') AS `dialect_name`,
  `e`.`note` AS `note`,
  `e`.`created_date` AS `created_date`,
  `e`.`modified_date` AS `modified_date`
FROM (((((`example` `e`
  LEFT JOIN `tone` `t`
    ON (`e`.`tone_id` = `t`.`tone_id`))
  LEFT JOIN `mode` `m`
    ON (`e`.`mode_id` = `m`.`mode_id`))
  LEFT JOIN `register` `r`
    ON (`e`.`register_id` = `r`.`register_id`))
  LEFT JOIN `nuance` `n`
    ON (`e`.`nuance_id` = `n`.`nuance_id`))
  LEFT JOIN `dialect` `d`
    ON (`e`.`dialect_id` = `d`.`dialect_id`));

--
-- Create table `cache_sql`
--
CREATE TABLE cache_sql (
  cache_sql_id int(11) NOT NULL AUTO_INCREMENT,
  cache_key varchar(255) DEFAULT NULL,
  value longtext DEFAULT NULL,
  user_id char(36) DEFAULT NULL,
  cache_type int(11) DEFAULT NULL,
  start_time timestamp NULL DEFAULT NULL,
  end_time timestamp NULL DEFAULT NULL,
  created_date datetime DEFAULT NULL,
  modified_date datetime DEFAULT NULL,
  PRIMARY KEY (cache_sql_id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 11,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Cache dữ liệu';

DELIMITER $$

--
-- Create event `Event_DeleteCacheSql`
--
CREATE 
	DEFINER = 'root'@'localhost'
EVENT Event_DeleteCacheSql
	ON SCHEDULE EVERY '12' HOUR
	STARTS '2023-06-07 23:14:27'
	DO 
BEGIN
  DELETE FROM cache_sql WHERE end_time < NOW();
END
$$

ALTER 
	DEFINER = 'root'@'localhost'
EVENT Event_DeleteCacheSql
	ENABLE
$$

DELIMITER ;

-- 
-- Dumping data for table user
--
INSERT INTO user VALUES
('8c38ae6f-223c-493a-b7a3-6a4122986848', 'hieu.pt183535@gmail.com', '$2a$11$AFRMhDiyFknixU019YEKiucLrfNLWD2A4AWXjwjOlO3gaItG4IKZW', 'hieu.pt183535@gmail.com', 'Phạm Trung Hiếu', 'Hieu Pham 1', '2000-11-03 00:00:00', 'Lập trình viên', 'https://firebasestorage.googleapis.com/v0/b/hust-pvo.appspot.com/o/Development%2Favatar%2F8c38ae6f-223c-493a-b7a3-6a4122986848?alt=media&token=bde1b499-b83b-4b1b-9ba9-48286471bf6b', 1, '2023-05-21 21:13:08', NULL),
('d2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'thienthan.bayvut@gmail.com', '$2a$11$brGDuTjZh1oPvOnuw1V90ObBY8Kkyu0yzCZobw.sx80E18E5niyw6', 'thienthan.bayvut@gmail.com', NULL, NULL, NULL, NULL, NULL, 1, '2023-05-03 18:30:35', NULL);

-- 
-- Dumping data for table sys_example_link
--
INSERT INTO sys_example_link VALUES
('2d1bc576-dbfd-11ed-a1e6-a44cc8756a37', 'Idiom', 2, 2, '2023-04-16 09:19:32', NULL),
('2d1bc640-dbfd-11ed-a1e6-a44cc8756a37', 'Nominal', 3, 3, '2023-04-16 09:19:32', NULL),
('2d1bc69a-dbfd-11ed-a1e6-a44cc8756a37', 'Agent', 4, 4, '2023-04-16 09:19:32', NULL),
('2d1bc6d7-dbfd-11ed-a1e6-a44cc8756a37', 'Patient', 5, 5, '2023-04-16 09:19:32', NULL),
('2d1bc70f-dbfd-11ed-a1e6-a44cc8756a37', 'Action', 6, 6, '2023-04-16 09:19:32', NULL),
('2d1bc745-dbfd-11ed-a1e6-a44cc8756a37', 'Described by', 7, 7, '2023-04-16 09:19:32', NULL),
('2d1bc783-dbfd-11ed-a1e6-a44cc8756a37', 'Describing', 8, 8, '2023-04-16 09:19:32', NULL),
('2d1bc88c-dbfd-11ed-a1e6-a44cc8756a37', 'Other phrase', 9, 9, '2023-04-16 09:19:32', NULL);

-- 
-- Dumping data for table dictionary
--
INSERT INTO dictionary VALUES
('0be11c6f-fb13-11ed-9dc8-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Dictionary 1', '2023-05-25 22:44:25', '2023-05-25 22:44:25', '2023-05-25 22:44:25'),
('2706a468-fb13-11ed-9dc8-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Dictionary 2', '2023-06-08 19:35:15', '2023-05-25 23:29:19', '2023-05-25 23:29:19'),
('753b1529-e9ae-11ed-9acb-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'PVO', NULL, '2023-05-03 19:31:24', NULL),
('91348451-fb19-11ed-9dc8-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Dictionary 3', '2023-06-04 00:16:07', '2023-05-25 23:42:10', '2023-05-25 23:42:10');

-- 
-- Dumping data for table sys_concept_link
--
INSERT INTO sys_concept_link VALUES
('a84e6654-dbfc-11ed-a1e6-a44cc8756a37', 'Association', 2, 2, '2023-04-16 09:15:50', NULL),
('a84e6885-dbfc-11ed-a1e6-a44cc8756a37', 'Type of', 3, 3, '2023-04-16 09:15:50', NULL);

-- 
-- Dumping data for table sys_tone
--
INSERT INTO sys_tone VALUES
('53f4ef4e-dbfd-11ed-a1e6-a44cc8756a37', 'Neutral', 1, 1, '2023-04-16 09:20:38', NULL),
('53f4f6a1-dbfd-11ed-a1e6-a44cc8756a37', 'Informal', 2, 2, '2023-04-16 09:20:38', NULL),
('53f4f751-dbfd-11ed-a1e6-a44cc8756a37', 'Formal', 3, 3, '2023-04-16 09:20:38', NULL),
('53f4f77f-dbfd-11ed-a1e6-a44cc8756a37', 'Slightly informal', 4, 4, '2023-04-16 09:20:38', NULL),
('53f4f7b3-dbfd-11ed-a1e6-a44cc8756a37', 'Slightly formal', 5, 5, '2023-04-16 09:20:38', NULL);

-- 
-- Dumping data for table sys_register
--
INSERT INTO sys_register VALUES
('aafb738e-dbfd-11ed-a1e6-a44cc8756a37', 'Neutral', '1', 1, '2023-04-16 09:23:04', NULL),
('aafb845c-dbfd-11ed-a1e6-a44cc8756a37', 'Academic', '2', 2, '2023-04-16 09:23:04', NULL),
('aafb862d-dbfd-11ed-a1e6-a44cc8756a37', 'Literature', '3', 3, '2023-04-16 09:23:04', NULL),
('aafb86cd-dbfd-11ed-a1e6-a44cc8756a37', 'Business', '4', 4, '2023-04-16 09:23:04', NULL),
('aafb8745-dbfd-11ed-a1e6-a44cc8756a37', 'Law', '5', 5, '2023-04-16 09:23:04', NULL),
('aafb87b8-dbfd-11ed-a1e6-a44cc8756a37', 'Journalism', '6', 6, '2023-04-16 09:23:04', NULL),
('aafb8832-dbfd-11ed-a1e6-a44cc8756a37', 'Medicine', '7', 7, '2023-04-16 09:23:04', NULL),
('aafb88dd-dbfd-11ed-a1e6-a44cc8756a37', 'IT', '8', 8, '2023-04-16 09:23:04', NULL),
('aafb896f-dbfd-11ed-a1e6-a44cc8756a37', 'Other', '9', 9, '2023-04-16 09:23:04', NULL);

-- 
-- Dumping data for table sys_nuance
--
INSERT INTO sys_nuance VALUES
('c59eaacb-dbfd-11ed-a1e6-a44cc8756a37', 'Neutral', 1, 1, '2023-04-16 09:23:48', NULL),
('c59eb46d-dbfd-11ed-a1e6-a44cc8756a37', 'Old fashioned', 2, 2, '2023-04-16 09:23:48', NULL),
('c59eb542-dbfd-11ed-a1e6-a44cc8756a37', 'Humorous', 3, 3, '2023-04-16 09:23:48', NULL),
('c59eb58d-dbfd-11ed-a1e6-a44cc8756a37', 'Oft positive', 4, 4, '2023-04-16 09:23:48', NULL),
('c59eb5be-dbfd-11ed-a1e6-a44cc8756a37', 'Oft negative', 5, 5, '2023-04-16 09:23:48', NULL);

-- 
-- Dumping data for table sys_mode
--
INSERT INTO sys_mode VALUES
('6799339c-dbfd-11ed-a1e6-a44cc8756a37', 'Neutral', 1, 1, '2023-04-16 09:21:11', NULL),
('67993cef-dbfd-11ed-a1e6-a44cc8756a37', 'Spoken', 2, 2, '2023-04-16 09:21:11', NULL),
('67993dd2-dbfd-11ed-a1e6-a44cc8756a37', 'Written', 3, 3, '2023-04-16 09:21:11', NULL);

-- 
-- Dumping data for table example_link
--
INSERT INTO example_link VALUES
('753d5d84-e9ae-11ed-9acb-a44cc8756a37', '2d1bc576-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Idiom', 2, 2, '2023-05-03 19:31:24', NULL),
('753d5f4f-e9ae-11ed-9acb-a44cc8756a37', '2d1bc640-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Nominal', 3, 3, '2023-05-03 19:31:24', NULL),
('753d6039-e9ae-11ed-9acb-a44cc8756a37', '2d1bc69a-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Agent', 4, 4, '2023-05-03 19:31:24', NULL),
('753d6104-e9ae-11ed-9acb-a44cc8756a37', '2d1bc6d7-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Patient', 5, 5, '2023-05-03 19:31:24', NULL),
('753d7391-e9ae-11ed-9acb-a44cc8756a37', '2d1bc70f-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Action', 6, 6, '2023-05-03 19:31:24', NULL),
('753e1691-e9ae-11ed-9acb-a44cc8756a37', '2d1bc745-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Described by', 7, 7, '2023-05-03 19:31:24', NULL),
('753e1841-e9ae-11ed-9acb-a44cc8756a37', '2d1bc783-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Describing', 8, 8, '2023-05-03 19:31:24', NULL),
('753e1a83-e9ae-11ed-9acb-a44cc8756a37', '2d1bc88c-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Other phrase', 9, 9, '2023-05-03 19:31:24', NULL),
('e3335b2b-fa46-11ed-9dc8-a44cc8756a37', '2d1bc576-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Idiom', 2, 2, '2023-05-24 22:23:03', NULL),
('e3335c5f-fa46-11ed-9dc8-a44cc8756a37', '2d1bc640-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Nominal', 3, 3, '2023-05-24 22:23:03', NULL),
('e3335ea1-fa46-11ed-9dc8-a44cc8756a37', '2d1bc69a-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Agent', 4, 4, '2023-05-24 22:23:03', NULL),
('e3335f72-fa46-11ed-9dc8-a44cc8756a37', '2d1bc6d7-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Patient', 5, 5, '2023-05-24 22:23:03', NULL),
('e333607c-fa46-11ed-9dc8-a44cc8756a37', '2d1bc70f-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Action', 6, 6, '2023-05-24 22:23:03', NULL),
('e3336163-fa46-11ed-9dc8-a44cc8756a37', '2d1bc745-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Described by', 7, 7, '2023-05-24 22:23:03', NULL),
('e3336742-fa46-11ed-9dc8-a44cc8756a37', '2d1bc783-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Describing', 8, 8, '2023-05-24 22:23:03', NULL),
('e3336a43-fa46-11ed-9dc8-a44cc8756a37', '2d1bc88c-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Other phrase', 9, 9, '2023-05-24 22:23:03', NULL);

-- 
-- Dumping data for table example
--
INSERT INTO example VALUES
('22329bc8-d279-4d7f-b817-92a2816f88d5', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'fallen hero', '<h>fallen hero</h>', 'Anh hùng liệt sỹ', 'e3341f6e-fa46-11ed-9dc8-a44cc8756a37', 'e335e13e-fa46-11ed-9dc8-a44cc8756a37', 'e337ac12-fa46-11ed-9dc8-a44cc8756a37', 'e33546e8-fa46-11ed-9dc8-a44cc8756a37', 'e3370b14-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 11:34:49', NULL),
('2ab3208d-5c1a-4f9f-9353-84c5bd5d9949', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'I love green', 'I love <h>green</h>', 'Tôi yêu màu xanh lá cây', 'e3341f6e-fa46-11ed-9dc8-a44cc8756a37', 'e335e13e-fa46-11ed-9dc8-a44cc8756a37', 'e337ac12-fa46-11ed-9dc8-a44cc8756a37', 'e33546e8-fa46-11ed-9dc8-a44cc8756a37', 'e3370b14-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 17:44:04', NULL),
('47b9d42f-0093-11ee-9dc8-a44cc8756a37', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'nice to meet you', '<hl>nice to meet you</hl>', NULL, NULL, NULL, NULL, NULL, NULL, '2023-06-01 22:44:31', NULL),
('47b9d4e4-0093-11ee-9dc8-a44cc8756a37', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'fallen hero', '<hl>fallen</hl> hero', NULL, NULL, NULL, NULL, NULL, NULL, '2023-06-01 22:44:31', NULL),
('5932b989-01b7-11ee-aac4-a44cc8756a37', '0be11c6f-fb13-11ed-9dc8-a44cc8756a37', 'This is a red pen', 'This is a <h>red pen</h>', 'Đây là một cái bút màu đỏ', 'e3342712-fa46-11ed-9dc8-a44cc8756a37', 'e335ef77-fa46-11ed-9dc8-a44cc8756a37', 'e337b202-fa46-11ed-9dc8-a44cc8756a37', 'e335523e-fa46-11ed-9dc8-a44cc8756a37', 'e33714e3-fa46-11ed-9dc8-a44cc8756a37', '2023-06-03 09:35:44', '2023-06-03 09:35:44'),
('72162ef3-fb19-11ed-9dc8-a44cc8756a39', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'how do you do', '<hl>how do you do</hl>', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('72162ef3-fb19-11ed-9dc8-a44cc8756a40', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'fallen hero', '<hl>fallen hero</hl>', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('72162ef3-fb19-11ed-9dc8-a44cc8756a42', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'fee people', '<hl>free</hl> people', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
('b30d50c3-ea3c-42e5-8a05-87c23ee90bf4', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'I love black', 'I love <h>black</h>', 'Tôi yêu màu đen', 'e3341f6e-fa46-11ed-9dc8-a44cc8756a37', 'e335e13e-fa46-11ed-9dc8-a44cc8756a37', 'e337ac12-fa46-11ed-9dc8-a44cc8756a37', 'e335523e-fa46-11ed-9dc8-a44cc8756a37', 'e3370b14-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 17:42:26', '2023-06-10 18:51:51'),
('d3901d57-1add-42bb-b6c7-d9369f9de539', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'I love blue', 'I love <h>blue</h>', NULL, 'e3341f6e-fa46-11ed-9dc8-a44cc8756a37', 'e335e13e-fa46-11ed-9dc8-a44cc8756a37', 'e337ac12-fa46-11ed-9dc8-a44cc8756a37', 'e33546e8-fa46-11ed-9dc8-a44cc8756a37', 'e3370b14-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 17:42:26', NULL),
('dc705b3c-02be-42b5-85e7-19b9a2eb180e', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'This is a red pen', 'This is a <h>red pen</h>', 'Đây là một cái bút màu đỏ', 'e3341f6e-fa46-11ed-9dc8-a44cc8756a37', 'e335e13e-fa46-11ed-9dc8-a44cc8756a37', 'e337ac12-fa46-11ed-9dc8-a44cc8756a37', 'e33546e8-fa46-11ed-9dc8-a44cc8756a37', 'e3370b14-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 12:59:28', NULL);

-- 
-- Dumping data for table sys_dialect
--
INSERT INTO sys_dialect VALUES
('d9016880-dbfd-11ed-a1e6-a44cc8756a37', 'Neutral', 1, 1, '2023-04-16 09:24:21', NULL),
('d901737b-dbfd-11ed-a1e6-a44cc8756a37', 'American', 2, 2, '2023-04-16 09:24:21', NULL),
('d9017459-dbfd-11ed-a1e6-a44cc8756a37', 'British', 3, 3, '2023-04-16 09:24:21', NULL),
('d90174af-dbfd-11ed-a1e6-a44cc8756a37', 'Other', 4, 4, '2023-04-16 09:24:21', NULL);

-- 
-- Dumping data for table concept_link
--
INSERT INTO concept_link VALUES
('753bd9e4-e9ae-11ed-9acb-a44cc8756a37', 'a84e6654-dbfc-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Association', 2, 2, '2023-05-03 19:31:24', NULL),
('753bdda8-e9ae-11ed-9acb-a44cc8756a37', 'a84e6885-dbfc-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Type of', 3, 3, '2023-05-03 19:31:24', NULL),
('e332e615-fa46-11ed-9dc8-a44cc8756a37', 'a84e6654-dbfc-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Association', 2, 2, '2023-05-24 22:23:03', NULL),
('e332e708-fa46-11ed-9dc8-a44cc8756a37', 'a84e6885-dbfc-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Type of', 3, 3, '2023-05-24 22:23:03', NULL);

-- 
-- Dumping data for table concept
--
INSERT INTO concept VALUES
('005fe3ae-2755-4a76-90f4-329ccf033c0a', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'hello', NULL, 'hello', 'H400', '2023-06-10 11:34:49', NULL),
('23ec6729-ff3c-4fec-9420-bc7f7409c815', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'red', 'màu đỏ', 'red', 'R300', '2023-06-10 11:34:49', NULL),
('3f10bd46-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'monk', NULL, 'monk', 'M200', '2023-05-21 14:51:08', NULL),
('3f10c497-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'religion', NULL, 'religion', 'R425', '2023-05-21 14:51:08', NULL),
('3f10c5ca-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'hair', NULL, 'hair', 'H600', '2023-05-21 14:51:08', NULL),
('3f10c64e-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'shave', NULL, 'shave', 'S100', '2023-05-21 14:51:08', NULL),
('3f10c6c2-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'priest', NULL, 'priest', 'P623', '2023-05-21 14:51:08', NULL),
('3f10c733-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'pagoda', NULL, 'pagoda', 'P230', '2023-05-21 14:51:08', NULL),
('3f10c795-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'Buddhism', NULL, 'buddhism', 'B325', '2023-05-21 14:51:08', NULL),
('3f10c7f6-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'Christian', NULL, 'christian', 'C6235', '2023-05-21 14:51:08', NULL),
('3f10c8f6-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'secular', NULL, 'secular', 'S460', '2023-05-21 14:51:08', NULL),
('3f10c95c-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'cut', NULL, 'cut', 'C300', '2023-05-21 14:51:08', NULL),
('3f10c9b8-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'barber', NULL, 'barber', 'B616', '2023-05-21 14:51:08', NULL),
('3f10ca18-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'fur', NULL, 'fur', 'F600', '2023-05-21 14:51:08', NULL),
('3f10ca79-f7ac-11ed-9dc8-a44cc8756a37', '753b1529-e9ae-11ed-9acb-a44cc8756a37', 'tonsure', NULL, 'tonsure', 'T526', '2023-05-21 14:51:08', NULL),
('47b9bc0f-0093-11ee-9dc8-a44cc8756a37', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'communicate', NULL, 'communicate', 'C523', NULL, NULL),
('47b9bcd0-0093-11ee-9dc8-a44cc8756a37', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'greeting', '', 'greeting', 'G6352', NULL, NULL),
('62ce0365-fd6e-11ed-9dc8-a44cc8756a37', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'meet', '', 'meet', 'M300', '2023-05-28 22:43:04', '2023-05-28 22:43:04'),
('632741de-fd6e-11ed-9dc8-a44cc8756a37', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'hello', '', 'hello', 'H400', '2023-05-28 22:43:05', '2023-05-28 22:43:05'),
('653f8cb8-01b6-11ee-aac4-a44cc8756a37', '0be11c6f-fb13-11ed-9dc8-a44cc8756a37', 'red', 'màu đỏ', 'red', 'R300', '2023-06-03 09:28:55', '2023-06-03 09:28:55'),
('6540418c-01b6-11ee-aac4-a44cc8756a37', '0be11c6f-fb13-11ed-9dc8-a44cc8756a37', 'color', 'màu sắc', 'color', 'C460', '2023-06-03 09:28:55', '2023-06-03 09:28:55'),
('7e9b93fb-1894-404d-9627-331814d9c653', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'fallen', NULL, 'fallen', 'F450', '2023-06-10 11:34:49', NULL),
('b41df3f2-940c-4739-9864-73a441eb0abc', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'hero', 'anh hùng', 'hero', 'H600', '2023-06-10 11:34:49', NULL),
('e0609c62-01e4-11ee-aac4-a44cc8756a37', '0be11c6f-fb13-11ed-9dc8-a44cc8756a37', 'reaSON - WHY', 'lý do', 'reason  why', 'R250', '2023-06-03 15:01:37', '2023-06-03 15:01:37'),
('fb1ef182-fd6d-11ed-9dc8-a44cc8756a37', '91348451-fb19-11ed-9dc8-a44cc8756a37', 'fallen', '', 'fallen', 'F450', '2023-05-28 22:40:10', '2023-05-28 22:40:10'),
('fea336cc-8dc3-43a1-a768-c31d05310440', '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'color', 'màu sắc', 'color', 'C460', '2023-06-10 11:34:49', NULL);

-- 
-- Dumping data for table user_setting
--
-- Table pvo_v1_2023.user_setting does not contain any data (it is empty)

-- 
-- Dumping data for table tone
--
INSERT INTO tone VALUES
('753e8bb2-e9ae-11ed-9acb-a44cc8756a37', '53f4ef4e-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Neutral', 1, 1, '2023-05-03 19:31:24', NULL),
('753eb17f-e9ae-11ed-9acb-a44cc8756a37', '53f4f6a1-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Informal', 2, 2, '2023-05-03 19:31:24', NULL),
('753eb3b2-e9ae-11ed-9acb-a44cc8756a37', '53f4f751-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Formal', 3, 3, '2023-05-03 19:31:24', NULL),
('753eb4d1-e9ae-11ed-9acb-a44cc8756a37', '53f4f77f-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Slightly informal', 4, 4, '2023-05-03 19:31:24', NULL),
('753eb5f5-e9ae-11ed-9acb-a44cc8756a37', '53f4f7b3-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Slightly formal', 5, 5, '2023-05-03 19:31:24', NULL),
('e3341f6e-fa46-11ed-9dc8-a44cc8756a37', '53f4ef4e-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Neutral', 1, 1, '2023-05-24 22:23:03', NULL),
('e33425b8-fa46-11ed-9dc8-a44cc8756a37', '53f4f6a1-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Informal', 2, 2, '2023-05-24 22:23:03', NULL),
('e3342712-fa46-11ed-9dc8-a44cc8756a37', '53f4f751-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Formal', 3, 3, '2023-05-24 22:23:03', NULL),
('e334290a-fa46-11ed-9dc8-a44cc8756a37', '53f4f77f-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Slightly informal', 4, 4, '2023-05-24 22:23:03', NULL),
('e33429d5-fa46-11ed-9dc8-a44cc8756a37', '53f4f7b3-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Slightly formal', 5, 5, '2023-05-24 22:23:03', NULL);

-- 
-- Dumping data for table register
--
INSERT INTO register VALUES
('753fc35f-e9ae-11ed-9acb-a44cc8756a37', 'aafb738e-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Neutral', 1, 1, '2023-05-03 19:31:24', NULL),
('75402ece-e9ae-11ed-9acb-a44cc8756a37', 'aafb845c-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Academic', 2, 2, '2023-05-03 19:31:24', NULL),
('7540317b-e9ae-11ed-9acb-a44cc8756a37', 'aafb862d-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Literature', 3, 3, '2023-05-03 19:31:24', NULL),
('75403268-e9ae-11ed-9acb-a44cc8756a37', 'aafb86cd-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Business', 4, 4, '2023-05-03 19:31:24', NULL),
('75403333-e9ae-11ed-9acb-a44cc8756a37', 'aafb8745-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Law', 5, 5, '2023-05-03 19:31:24', NULL),
('754033fd-e9ae-11ed-9acb-a44cc8756a37', 'aafb87b8-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Journalism', 6, 6, '2023-05-03 19:31:24', NULL),
('754034b8-e9ae-11ed-9acb-a44cc8756a37', 'aafb8832-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Medicine', 7, 7, '2023-05-03 19:31:24', NULL),
('75403567-e9ae-11ed-9acb-a44cc8756a37', 'aafb88dd-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'IT', 8, 8, '2023-05-03 19:31:24', NULL),
('75403642-e9ae-11ed-9acb-a44cc8756a37', 'aafb896f-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Other', 9, 9, '2023-05-03 19:31:24', NULL),
('e335e13e-fa46-11ed-9dc8-a44cc8756a37', 'aafb738e-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Neutral', 1, 1, '2023-05-24 22:23:04', NULL),
('e335e809-fa46-11ed-9dc8-a44cc8756a37', 'aafb845c-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Academic', 2, 2, '2023-05-24 22:23:04', NULL),
('e335e8d5-fa46-11ed-9dc8-a44cc8756a37', 'aafb862d-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Literature', 3, 3, '2023-05-24 22:23:04', NULL),
('e335ead8-fa46-11ed-9dc8-a44cc8756a37', 'aafb86cd-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Business', 4, 4, '2023-05-24 22:23:04', NULL),
('e335ec2b-fa46-11ed-9dc8-a44cc8756a37', 'aafb8745-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Law', 5, 5, '2023-05-24 22:23:04', NULL),
('e335eda0-fa46-11ed-9dc8-a44cc8756a37', 'aafb87b8-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Journalism', 6, 6, '2023-05-24 22:23:04', NULL),
('e335ee94-fa46-11ed-9dc8-a44cc8756a37', 'aafb8832-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Medicine', 7, 7, '2023-05-24 22:23:04', NULL),
('e335ef11-fa46-11ed-9dc8-a44cc8756a37', 'aafb88dd-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'IT', 8, 8, '2023-05-24 22:23:04', NULL),
('e335ef77-fa46-11ed-9dc8-a44cc8756a37', 'aafb896f-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Other', 9, 9, '2023-05-24 22:23:04', NULL);

-- 
-- Dumping data for table nuance
--
INSERT INTO nuance VALUES
('7540ab3e-e9ae-11ed-9acb-a44cc8756a37', 'c59eaacb-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Neutral', 1, 1, '2023-05-03 19:31:24', NULL),
('7540cbf3-e9ae-11ed-9acb-a44cc8756a37', 'c59eb46d-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Old fashioned', 2, 2, '2023-05-03 19:31:24', NULL),
('7540cdd0-e9ae-11ed-9acb-a44cc8756a37', 'c59eb542-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Humorous', 3, 3, '2023-05-03 19:31:24', NULL),
('7540ce95-e9ae-11ed-9acb-a44cc8756a37', 'c59eb58d-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Oft positive', 4, 4, '2023-05-03 19:31:24', NULL),
('7540cf46-e9ae-11ed-9acb-a44cc8756a37', 'c59eb5be-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Oft negative', 5, 5, '2023-05-03 19:31:24', NULL),
('e3370b14-fa46-11ed-9dc8-a44cc8756a37', 'c59eaacb-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Neutral', 1, 1, '2023-05-24 22:23:04', NULL),
('e337117c-fa46-11ed-9dc8-a44cc8756a37', 'c59eb46d-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Old fashioned', 2, 2, '2023-05-24 22:23:04', NULL),
('e33712e7-fa46-11ed-9dc8-a44cc8756a37', 'c59eb542-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Humorous', 3, 3, '2023-05-24 22:23:04', NULL),
('e33714e3-fa46-11ed-9dc8-a44cc8756a37', 'c59eb58d-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Oft positive', 4, 4, '2023-05-24 22:23:04', NULL),
('e337169b-fa46-11ed-9dc8-a44cc8756a37', 'c59eb5be-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Oft negative', 5, 5, '2023-05-24 22:23:04', NULL);

-- 
-- Dumping data for table mode
--
INSERT INTO mode VALUES
('753f1f2c-e9ae-11ed-9acb-a44cc8756a37', '6799339c-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Neutral', 1, 1, '2023-05-03 19:31:24', NULL),
('753f4378-e9ae-11ed-9acb-a44cc8756a37', '67993cef-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Spoken', 2, 2, '2023-05-03 19:31:24', NULL),
('753f4665-e9ae-11ed-9acb-a44cc8756a37', '67993dd2-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Written', 3, 3, '2023-05-03 19:31:24', NULL),
('e33546e8-fa46-11ed-9dc8-a44cc8756a37', '6799339c-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Neutral', 1, 1, '2023-05-24 22:23:03', NULL),
('e33550c1-fa46-11ed-9dc8-a44cc8756a37', '67993cef-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Spoken', 2, 2, '2023-05-24 22:23:03', NULL),
('e335523e-fa46-11ed-9dc8-a44cc8756a37', '67993dd2-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Written', 3, 3, '2023-05-24 22:23:03', NULL);

-- 
-- Dumping data for table example_relationship
--
INSERT INTO example_relationship VALUES
(4, '0be11c6f-fb13-11ed-9dc8-a44cc8756a37', '653f8cb8-01b6-11ee-aac4-a44cc8756a37', '5932b989-01b7-11ee-aac4-a44cc8756a37', 'e3336742-fa46-11ed-9dc8-a44cc8756a37', '2023-06-03 09:37:38', '2023-06-03 09:37:38'),
(8, '2706a468-fb13-11ed-9dc8-a44cc8756a37', '23ec6729-ff3c-4fec-9420-bc7f7409c815', 'dc705b3c-02be-42b5-85e7-19b9a2eb180e', 'e3335c5f-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 12:59:28', NULL),
(9, '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'fea336cc-8dc3-43a1-a768-c31d05310440', 'dc705b3c-02be-42b5-85e7-19b9a2eb180e', 'e3335c5f-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 12:59:28', NULL),
(10, '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'fea336cc-8dc3-43a1-a768-c31d05310440', 'd3901d57-1add-42bb-b6c7-d9369f9de539', 'e3335c5f-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 17:42:26', NULL),
(13, '2706a468-fb13-11ed-9dc8-a44cc8756a37', 'fea336cc-8dc3-43a1-a768-c31d05310440', 'b30d50c3-ea3c-42e5-8a05-87c23ee90bf4', 'e3335c5f-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 18:51:51', NULL);

-- 
-- Dumping data for table dialect
--
INSERT INTO dialect VALUES
('75414610-e9ae-11ed-9acb-a44cc8756a37', 'd9016880-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Neutral', 1, 1, '2023-05-03 19:31:24', NULL),
('7541adb4-e9ae-11ed-9acb-a44cc8756a37', 'd901737b-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'American', 2, 2, '2023-05-03 19:31:24', NULL),
('7541af82-e9ae-11ed-9acb-a44cc8756a37', 'd9017459-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'British', 3, 3, '2023-05-03 19:31:24', NULL),
('7541b0d9-e9ae-11ed-9acb-a44cc8756a37', 'd90174af-dbfd-11ed-a1e6-a44cc8756a37', 'd2a85e00-4fc4-4cc9-845d-4ad90da292d5', 'Other', 4, 4, '2023-05-03 19:31:24', NULL),
('e337ac12-fa46-11ed-9dc8-a44cc8756a37', 'd9016880-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Neutral', 1, 1, '2023-05-24 22:23:04', NULL),
('e337b202-fa46-11ed-9dc8-a44cc8756a37', 'd901737b-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'American', 2, 2, '2023-05-24 22:23:04', NULL),
('e337b365-fa46-11ed-9dc8-a44cc8756a37', 'd9017459-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'British', 3, 3, '2023-05-24 22:23:04', NULL),
('e337b496-fa46-11ed-9dc8-a44cc8756a37', 'd90174af-dbfd-11ed-a1e6-a44cc8756a37', '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Other', 4, 4, '2023-05-24 22:23:04', NULL);

-- 
-- Dumping data for table concept_relationship
--
INSERT INTO concept_relationship VALUES
(1, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10bd46-f7ac-11ed-9dc8-a44cc8756a37', '3f10c6c2-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(2, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10bd46-f7ac-11ed-9dc8-a44cc8756a37', '3f10c733-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(3, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10bd46-f7ac-11ed-9dc8-a44cc8756a37', '3f10c795-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(4, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c497-f7ac-11ed-9dc8-a44cc8756a37', '3f10c7f6-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(5, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c497-f7ac-11ed-9dc8-a44cc8756a37', '3f10c795-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(6, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c497-f7ac-11ed-9dc8-a44cc8756a37', '3f10c8f6-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(7, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c5ca-f7ac-11ed-9dc8-a44cc8756a37', '3f10c95c-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(8, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c5ca-f7ac-11ed-9dc8-a44cc8756a37', '3f10c9b8-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(9, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c5ca-f7ac-11ed-9dc8-a44cc8756a37', '3f10ca18-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(10, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c733-f7ac-11ed-9dc8-a44cc8756a37', '3f10c795-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(11, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10c9b8-f7ac-11ed-9dc8-a44cc8756a37', '3f10c95c-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(12, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10ca79-f7ac-11ed-9dc8-a44cc8756a37', '3f10bd46-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(13, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10ca79-f7ac-11ed-9dc8-a44cc8756a37', '3f10c497-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(14, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10ca79-f7ac-11ed-9dc8-a44cc8756a37', '3f10c5ca-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(15, '753b1529-e9ae-11ed-9acb-a44cc8756a37', '3f10ca79-f7ac-11ed-9dc8-a44cc8756a37', '3f10c64e-f7ac-11ed-9dc8-a44cc8756a37', '753bd9e4-e9ae-11ed-9acb-a44cc8756a37', NULL, NULL),
(29, '91348451-fb19-11ed-9dc8-a44cc8756a37', '62ce0365-fd6e-11ed-9dc8-a44cc8756a37', '632741de-fd6e-11ed-9dc8-a44cc8756a37', NULL, NULL, NULL),
(35, '0be11c6f-fb13-11ed-9dc8-a44cc8756a37', '653f8cb8-01b6-11ee-aac4-a44cc8756a37', '6540418c-01b6-11ee-aac4-a44cc8756a37', 'e332e708-fa46-11ed-9dc8-a44cc8756a37', '2023-06-03 09:31:26', '2023-06-03 09:31:26'),
(36, '91348451-fb19-11ed-9dc8-a44cc8756a37', '47b9bc0f-0093-11ee-9dc8-a44cc8756a37', '47b9bcd0-0093-11ee-9dc8-a44cc8756a37', 'e332e615-fa46-11ed-9dc8-a44cc8756a37', '2023-06-04 00:24:32', NULL),
(39, '2706a468-fb13-11ed-9dc8-a44cc8756a37', '23ec6729-ff3c-4fec-9420-bc7f7409c815', 'fea336cc-8dc3-43a1-a768-c31d05310440', 'e332e708-fa46-11ed-9dc8-a44cc8756a37', '2023-06-10 11:34:49', NULL);

-- 
-- Dumping data for table cache_sql
--
-- Table pvo_v1_2023.cache_sql does not contain any data (it is empty)

-- 
-- Dumping data for table audit_log
--
INSERT INTO audit_log VALUES
(5, '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Add Concept', 1, NULL, 'Add concept: free', 'PostmanRuntime/7.28.3', '2023-05-23 23:06:31', NULL),
(10, '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Add Concept', 2, NULL, 'Add concept: free', 'PostmanRuntime/7.28.3', '2023-05-23 23:46:16', NULL),
(11, '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Add Concept', 2, NULL, 'Add concept: freedom', 'PostmanRuntime/7.28.3', '2023-03-01 00:00:00', NULL),
(12, '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Add Example', 2, NULL, 'fallen hero', 'PostmanRuntime/7.28.3', '2023-02-05 06:00:00', NULL),
(13, '8c38ae6f-223c-493a-b7a3-6a4122986848', 'Dictionary', 2, NULL, 'load new', 'PostmanRuntime/7.28.3', '2023-04-02 09:00:00', NULL);

--
-- Set default database
--
USE pvo_v1_2023;

--
-- Drop trigger `Trigger_Concept_AutoAddNormalizeAndSoundex`
--
DROP TRIGGER IF EXISTS Trigger_Concept_AutoAddNormalizeAndSoundex;

--
-- Set default database
--
USE pvo_v1_2023;

DELIMITER $$

--
-- Create trigger `Trigger_Concept_AutoAddNormalizeAndSoundex`
--
CREATE
DEFINER = 'root'@'localhost'
TRIGGER Trigger_Concept_AutoAddNormalizeAndSoundex
BEFORE INSERT
ON concept
FOR EACH ROW
BEGIN
  SET new.normalized_title = REGEXP_REPLACE(LOWER(TRIM(IFNULL(new.title, ''))), '[^0-9a-z ]', '');
  SET new.soundex_title = SOUNDEX(new.normalized_title);
END
$$

DELIMITER ;

-- 
-- Restore previous SQL mode
-- 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

-- 
-- Enable foreign keys
-- 
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;