create-database.sql

--
-- Script was generated by Devart dbForge Studio for MySQL, Version 10.0.150.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 29/06/2024 16:58:10
-- Server version: 8.3.0
--

--
-- 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 knowledgesharing;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Set default database
--
USE knowledgesharing;

--
-- Create table `User`
--
CREATE TABLE User (
  UserId char(36) NOT NULL COMMENT 'Id của người dùng',
  Email varchar(255) NOT NULL COMMENT 'Email đăng ký tài khoản',
  Username varchar(255) NOT NULL COMMENT 'Tên tài khoản',
  HashPassword varchar(255) NOT NULL COMMENT 'Mã băm mật khẩu',
  Role varchar(255) NOT NULL COMMENT 'Vai trò người dùng (Admin, User, Banned)',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Ngày chỉnh sửa',
  PRIMARY KEY (UserId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 682,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Thông tin đăng nhập và vai trò của người dùng hệ thống',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_User_CreatedTime` on table `User`
--
ALTER TABLE User
ADD INDEX IDX_User_CreatedTime (CreatedTime);

--
-- Create index `IDX_User_Role` on table `User`
--
ALTER TABLE User
ADD INDEX IDX_User_Role (Role);

--
-- Create index `UK_User_Email` on table `User`
--
ALTER TABLE User
ADD UNIQUE INDEX UK_User_Email (Email);

--
-- Create index `UK_User_Username` on table `User`
--
ALTER TABLE User
ADD UNIQUE INDEX UK_User_Username (Username);

--
-- Create table `UserRelation`
--
CREATE TABLE UserRelation (
  UserRelationId char(36) NOT NULL COMMENT 'Id của quan hệ người dùng',
  SenderId char(36) NOT NULL COMMENT 'Id người gửi yêu cầu',
  ReceiverId char(36) NOT NULL COMMENT 'Id người nhận yêu cầu',
  UserRelationType int NOT NULL COMMENT 'Loại quan hệ (0 - Friend, 1 - Request Friend, 2 - Follow, 3 - Block)',
  Time datetime NOT NULL COMMENT 'Thời gian quan hệ được tạo',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian chỉnh sửa',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người chỉnh sửa',
  PRIMARY KEY (UserRelationId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng lưu quan hệ giữa hai người dùng',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_UserRelation_CreatedTime` on table `UserRelation`
--
ALTER TABLE UserRelation
ADD INDEX IDX_UserRelation_CreatedTime (CreatedTime);

--
-- Create index `UK_UserRelation_UserRelationId` on table `UserRelation`
--
ALTER TABLE UserRelation
ADD UNIQUE INDEX UK_UserRelation_UserRelationId (UserRelationId);

--
-- Create foreign key
--
ALTER TABLE UserRelation
ADD CONSTRAINT FK_UserRelation_ReceiverId FOREIGN KEY (ReceiverId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE UserRelation
ADD CONSTRAINT FK_UserRelation_SenderId FOREIGN KEY (SenderId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create view `ViewTotalUserRelation`
--
CREATE VIEW ViewTotalUserRelation
AS
SELECT
  `u`.`UserId` AS `UserId`,
  COUNT((CASE WHEN (`ur`.`UserRelationType` = 0) THEN 1 END)) AS `TotalFriend`,
  COUNT((CASE WHEN ((`ur`.`UserRelationType` = 1) AND
      (`ur`.`SenderId` = `u`.`UserId`)) THEN 1 END)) AS `TotalRequester`,
  COUNT((CASE WHEN ((`ur`.`UserRelationType` = 1) AND
      (`ur`.`ReceiverId` = `u`.`UserId`)) THEN 1 END)) AS `TotalRequestee`,
  COUNT((CASE WHEN ((`ur`.`UserRelationType` = 2) AND
      (`ur`.`SenderId` = `u`.`UserId`)) THEN 1 END)) AS `TotalFollower`,
  COUNT((CASE WHEN ((`ur`.`UserRelationType` = 2) AND
      (`ur`.`ReceiverId` = `u`.`UserId`)) THEN 1 END)) AS `TotalFollowee`,
  COUNT((CASE WHEN ((`ur`.`UserRelationType` = 3) AND
      (`ur`.`SenderId` = `u`.`UserId`)) THEN 1 END)) AS `TotalBlocker`,
  COUNT((CASE WHEN ((`ur`.`UserRelationType` = 3) AND
      (`ur`.`ReceiverId` = `u`.`UserId`)) THEN 1 END)) AS `TotalBlockee`
FROM (`User` `u`
  LEFT JOIN `UserRelation` `ur`
    ON (((`u`.`UserId` = `ur`.`SenderId`)
    OR (`u`.`UserId` = `ur`.`ReceiverId`))))
GROUP BY `u`.`UserId`;

--
-- Create view `ViewTotalFriend`
--
CREATE VIEW ViewTotalFriend
AS
SELECT
  `u`.`UserId` AS `UserId`,
  COUNT(`ur`.`UserRelationId`) AS `TotalFriend`
FROM (`User` `u`
  LEFT JOIN `UserRelation` `ur`
    ON (((`u`.`UserId` = `ur`.`SenderId`)
    OR (`u`.`UserId` = `ur`.`ReceiverId`))))
WHERE (`ur`.`UserRelationType` = 0)
GROUP BY `u`.`UserId`;

--
-- Create table `UserItem`
--
CREATE TABLE UserItem (
  UserItemId char(36) NOT NULL COMMENT 'Id của đối tượng',
  UserId char(36) NOT NULL COMMENT 'Id của chủ sở hữu của đối tượng',
  UserItemType int NOT NULL COMMENT 'Loại Item (0: Knowledge, 1: Comment)',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (UserItemId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 292,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Đối tượng dữ liệu chung được sở hữu bởi một người dùng (Bình luận, câu hỏi, bài viết, khóa học...)',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_UserItem_CreatedTime` on table `UserItem`
--
ALTER TABLE UserItem
ADD INDEX IDX_UserItem_CreatedTime (CreatedTime);

--
-- Create index `IDX_UserItem_ModifiedTime` on table `UserItem`
--
ALTER TABLE UserItem
ADD INDEX IDX_UserItem_ModifiedTime (ModifiedTime);

--
-- Create index `UK_UserItem_UserItemId` on table `UserItem`
--
ALTER TABLE UserItem
ADD UNIQUE INDEX UK_UserItem_UserItemId (UserItemId);

--
-- Create foreign key
--
ALTER TABLE UserItem
ADD CONSTRAINT FK_UserItem_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create table `Star`
--
CREATE TABLE Star (
  StarId char(36) NOT NULL COMMENT 'Id của phiên cho điểm',
  UserId char(36) NOT NULL COMMENT 'Id của người dùng cho điểm',
  UserItemId char(36) NOT NULL COMMENT 'Id của phần tử nhận điểm',
  Stars int NOT NULL COMMENT 'Điểm đánh giá (0 - 5 sao)',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (StarId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng lưu điểm đánh giá của User với UserItem',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Star_Star` on table `Star`
--
ALTER TABLE Star
ADD INDEX IDX_Star_Star (Stars);

--
-- Create index `UK_Star_StarId` on table `Star`
--
ALTER TABLE Star
ADD UNIQUE INDEX UK_Star_StarId (StarId);

--
-- Create foreign key
--
ALTER TABLE Star
ADD CONSTRAINT FK_Star_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE Star
ADD CONSTRAINT FK_Star_UserItemId FOREIGN KEY (UserItemId)
REFERENCES UserItem (UserItemId) ON DELETE CASCADE;

--
-- Create view `ViewTotalUserStar`
--
CREATE VIEW ViewTotalUserStar
AS
SELECT
  `u`.`UserId` AS `UserId`,
  COUNT(0) AS `TotalStar`,
  SUM(`s`.`Stars`) AS `SumStar`
FROM (`UserItem` `u`
  JOIN `Star` `s`
    ON ((`u`.`UserItemId` = `s`.`UserItemId`)))
GROUP BY `u`.`UserId`;

--
-- Create table `Knowledge`
--
CREATE TABLE Knowledge (
  UserItemId char(36) NOT NULL COMMENT 'Id của phần tử kiến thức',
  Title varchar(255) NOT NULL COMMENT 'Tiêu đề của phần tử kiến thức',
  Abstract text DEFAULT NULL COMMENT 'Phần nội dung tóm tắt',
  Thumbnail text DEFAULT NULL COMMENT 'Ảnh đại diện',
  Views int NOT NULL COMMENT 'Số lượt xem',
  KnowledgeType int NOT NULL COMMENT 'Loại phần tử kiến thức (0: Bài đăng, 1: Khóa học)',
  Privacy int NOT NULL COMMENT 'Quyền riêng tư: (0 - private, 1 - public)',
  IsBlockComment tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Có cho phép bình luận hay không',
  PRIMARY KEY (UserItemId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 2457,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Phần tử kiến thức chung của người dùng (Bài học, Khóa học, Câu hỏi thảo luận), kế thừa UserItem',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Knowledge_Title` on table `Knowledge`
--
ALTER TABLE Knowledge
ADD INDEX IDX_Knowledge_Title (Title);

--
-- Create index `UK_Knowledge_UserItemId` on table `Knowledge`
--
ALTER TABLE Knowledge
ADD UNIQUE INDEX UK_Knowledge_UserItemId (UserItemId);

--
-- Create foreign key
--
ALTER TABLE Knowledge
ADD CONSTRAINT FK_Knowledge_UserItemId FOREIGN KEY (UserItemId)
REFERENCES UserItem (UserItemId) ON DELETE CASCADE;

--
-- Create table `StudyProgress`
--
CREATE TABLE StudyProgress (
  StudyProgressId char(36) NOT NULL COMMENT 'Id của tiến trình học tập',
  UserId char(36) NOT NULL COMMENT 'Id người dùng',
  UserItemId char(36) NOT NULL COMMENT 'Id của phần tử kiến thức',
  Progress double NOT NULL COMMENT 'Tiến trình học tập đơn vị phần trăm',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (StudyProgressId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Tiến trình học tập',
ROW_FORMAT = DYNAMIC;

--
-- Create foreign key
--
ALTER TABLE StudyProgress
ADD CONSTRAINT SK_studyProgress_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE StudyProgress
ADD CONSTRAINT SK_studyProgress_UserItemId FOREIGN KEY (UserItemId)
REFERENCES Knowledge (UserItemId) ON DELETE CASCADE;

--
-- Create table `Post`
--
CREATE TABLE Post (
  UserItemId char(36) NOT NULL COMMENT 'Id của bài đăng',
  Content longtext NOT NULL COMMENT 'Nội dung của bài đăng',
  PostType int NOT NULL COMMENT 'Phân loại bài đăng (0: Bài giảng, 1: Bài thảo luận)',
  PRIMARY KEY (UserItemId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 163,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bài đăng hệ thống (Bài giảng, Bài thảo luận), kế thừa Knowledge',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_Post_UserItemId` on table `Post`
--
ALTER TABLE Post
ADD UNIQUE INDEX UK_Post_UserItemId (UserItemId);

--
-- Create foreign key
--
ALTER TABLE Post
ADD CONSTRAINT FK_Post_UserItemId FOREIGN KEY (UserItemId)
REFERENCES Knowledge (UserItemId) ON DELETE CASCADE;

--
-- Create table `PostEditHistory`
--
CREATE TABLE PostEditHistory (
  PostEditHistoryId char(36) NOT NULL,
  PostId char(36) NOT NULL,
  Title varchar(1023) DEFAULT NULL,
  Abstract text DEFAULT NULL,
  Thumbnail varchar(1023) DEFAULT NULL,
  Content longtext DEFAULT NULL,
  CreatedBy varchar(255) DEFAULT NULL,
  CreatedTime datetime DEFAULT NULL,
  ModifiedBy varchar(255) DEFAULT NULL,
  ModifiedTime datetime DEFAULT NULL,
  PRIMARY KEY (PostEditHistoryId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lịch sử chỉnh sửa bài đăng',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_Postedithistory_PostEditHistoryId` on table `PostEditHistory`
--
ALTER TABLE PostEditHistory
ADD UNIQUE INDEX UK_Postedithistory_PostEditHistoryId (PostEditHistoryId);

--
-- Create foreign key
--
ALTER TABLE PostEditHistory
ADD CONSTRAINT FK_Postedithistory_PostId FOREIGN KEY (PostId)
REFERENCES Post (UserItemId) ON DELETE CASCADE;

--
-- Create table `Lesson`
--
CREATE TABLE Lesson (
  UserItemId char(36) NOT NULL COMMENT 'Id của bài giảng',
  EstimateTimeInMinutes int NOT NULL COMMENT 'Thời gian học ước tính',
  PRIMARY KEY (UserItemId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 327,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Danh sách các bài giảng, kế thừa Bài đăng',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_Lesson_UserItemId` on table `Lesson`
--
ALTER TABLE Lesson
ADD UNIQUE INDEX UK_Lesson_UserItemId (UserItemId);

--
-- Create foreign key
--
ALTER TABLE Lesson
ADD CONSTRAINT FK_Lesson_UserItemId FOREIGN KEY (UserItemId)
REFERENCES Post (UserItemId) ON DELETE CASCADE;

--
-- Create table `Mark`
--
CREATE TABLE Mark (
  MarkId char(36) NOT NULL,
  UserId char(36) NOT NULL,
  KnowledgeId char(36) NOT NULL,
  CreatedBy varchar(255) DEFAULT NULL,
  CreatedTime datetime DEFAULT NULL,
  ModifiedBy varchar(255) DEFAULT NULL,
  ModifiedTime datetime DEFAULT NULL,
  PRIMARY KEY (MarkId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng lưu danh sách phần tử kiến thức được lưu bởi người dùng',
ROW_FORMAT = DYNAMIC;

--
-- Create foreign key
--
ALTER TABLE Mark
ADD CONSTRAINT FK_Mark_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Create foreign key
--
ALTER TABLE Mark
ADD CONSTRAINT FK_Mark_UserItemId FOREIGN KEY (KnowledgeId)
REFERENCES Knowledge (UserItemId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Create table `Course`
--
CREATE TABLE Course (
  UserItemId char(36) NOT NULL COMMENT 'Id của khóa học',
  Introduction longtext NOT NULL COMMENT 'Bài giới thiệu của khóa học',
  Fee decimal(10, 0) NOT NULL COMMENT 'Phí tham dự khóa học (nếu có)',
  EstimateTimeInMinutes int NOT NULL COMMENT 'Thời gian học ước tính',
  IsFree tinyint(1) NOT NULL COMMENT 'Cờ đánh dấu xem khóa học có miễn phí không (0: có tính phí, 1: miễn phí)',
  PRIMARY KEY (UserItemId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 163,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu thông tin khóa học, kế thừa Knowledge',
ROW_FORMAT = DYNAMIC;

--
-- Create foreign key
--
ALTER TABLE Course
ADD CONSTRAINT FK_Course_UserItemId FOREIGN KEY (UserItemId)
REFERENCES Knowledge (UserItemId) ON DELETE CASCADE;

--
-- Create table `Question`
--
CREATE TABLE Question (
  UserItemId char(36) NOT NULL COMMENT 'Id của câu hỏi, bài thảo luận',
  CourseId char(36) DEFAULT NULL COMMENT 'Id của khóa học (nếu bài thảo luận ở trong khóa học)',
  IsAccept tinyint(1) NOT NULL COMMENT 'Cờ hiệu xem bài thảo luận đã được chủ bài đánh giá giải quyết được vấn đề chưa',
  PRIMARY KEY (UserItemId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 163,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Câu hỏi, bài thảo luận, kế thừa Post',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_Question_UserItemId` on table `Question`
--
ALTER TABLE Question
ADD UNIQUE INDEX UK_Question_UserItemId (UserItemId);

--
-- Create foreign key
--
ALTER TABLE Question
ADD CONSTRAINT FK_Question_CourseId FOREIGN KEY (CourseId)
REFERENCES Course (UserItemId) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE Question
ADD CONSTRAINT FK_Question_UserItemId FOREIGN KEY (UserItemId)
REFERENCES Post (UserItemId) ON DELETE CASCADE;

--
-- Create table `CourseRelation`
--
CREATE TABLE CourseRelation (
  CourseRelationId char(36) NOT NULL COMMENT 'Id của quan hệ',
  SenderId char(36) NOT NULL COMMENT 'Id của người gửi quan hệ',
  ReceiverId char(36) NOT NULL COMMENT 'Id của người nhận quan hệ',
  CourseId char(36) NOT NULL COMMENT 'Id của khóa học ',
  CourseRelationType int NOT NULL COMMENT 'Loại quan hệ khóa học (0 - Request, 1 - invite)',
  CreatedBy varchar(255) DEFAULT NULL,
  CreatedTime datetime DEFAULT NULL,
  ModifiedBy varchar(255) DEFAULT NULL,
  ModifiedTime datetime DEFAULT NULL,
  PRIMARY KEY (CourseRelationId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Quản lý danh sách quan hệ của khóa học gồm lời mời (invite) và yêu cầu (Request)',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_CourseRelation_CourseRelationId` on table `CourseRelation`
--
ALTER TABLE CourseRelation
ADD UNIQUE INDEX UK_CourseRelation_CourseRelationId (CourseRelationId);

--
-- Create foreign key
--
ALTER TABLE CourseRelation
ADD CONSTRAINT FK_CourseRelation_CourseId FOREIGN KEY (CourseId)
REFERENCES Course (UserItemId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE CourseRelation
ADD CONSTRAINT FK_CourseRelation_ReceiverId FOREIGN KEY (ReceiverId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE CourseRelation
ADD CONSTRAINT FK_CourseRelation_SenderId FOREIGN KEY (SenderId)
REFERENCES User (UserId);

--
-- Create table `CourseRegister`
--
CREATE TABLE CourseRegister (
  CourseRegisterId char(36) NOT NULL COMMENT 'Id của phiên người dùng đăng ký khóa học',
  UserId char(36) NOT NULL COMMENT 'Id của người dùng',
  CourseId char(36) NOT NULL COMMENT 'Id của khóa học',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tọa',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người chỉnh sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian chỉnh sửa',
  PRIMARY KEY (CourseRegisterId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu danh sách người tham gia khóa học',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_CourseRegister_CourseRegisterId` on table `CourseRegister`
--
ALTER TABLE CourseRegister
ADD UNIQUE INDEX UK_CourseRegister_CourseRegisterId (CourseRegisterId);

--
-- Create foreign key
--
ALTER TABLE CourseRegister
ADD CONSTRAINT FK_CourseRegister_CourseId FOREIGN KEY (CourseId)
REFERENCES Course (UserItemId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE CourseRegister
ADD CONSTRAINT FK_CourseRegister_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create table `CoursePayment`
--
CREATE TABLE CoursePayment (
  CoursePaymentId char(36) NOT NULL COMMENT 'Id của khoản thanh toán',
  UserId char(36) DEFAULT NULL COMMENT 'Id của người thanh toán',
  CourseId char(36) DEFAULT NULL COMMENT 'Id của khóa học',
  Fee decimal(10, 0) NOT NULL COMMENT 'Giá thanh toán',
  PaymentMethod varchar(255) NOT NULL COMMENT 'Phương thức thanh toán',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (CoursePaymentId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Danh sách thanh toán khóa học có tính phí',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_CoursePayment_CoursePaymentId` on table `CoursePayment`
--
ALTER TABLE CoursePayment
ADD UNIQUE INDEX UK_CoursePayment_CoursePaymentId (CoursePaymentId);

--
-- Create foreign key
--
ALTER TABLE CoursePayment
ADD CONSTRAINT FK_CoursePayment_CourseId FOREIGN KEY (CourseId)
REFERENCES Course (UserItemId) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE CoursePayment
ADD CONSTRAINT FK_CoursePayment_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE SET NULL;

--
-- Create table `CourseLesson`
--
CREATE TABLE CourseLesson (
  CourseLessonId char(36) NOT NULL COMMENT 'Id của bài học trong khóa học',
  CourseId char(36) NOT NULL COMMENT 'Id của khóa học',
  LessonId char(36) NOT NULL COMMENT 'Id của bài học',
  Offset int NOT NULL COMMENT 'Thứ tự bài học trong khóa học',
  LessonTitle varchar(255) NOT NULL COMMENT 'Tiêu đề của khóa học trong bài học',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người chỉnh sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (CourseLessonId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu xem khóa học có bài học nào',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_CourseLesson_Offset` on table `CourseLesson`
--
ALTER TABLE CourseLesson
ADD INDEX IDX_CourseLesson_Offset (Offset);

--
-- Create index `UK_CourseLesson_CourseLessonId` on table `CourseLesson`
--
ALTER TABLE CourseLesson
ADD UNIQUE INDEX UK_CourseLesson_CourseLessonId (CourseLessonId);

--
-- Create foreign key
--
ALTER TABLE CourseLesson
ADD CONSTRAINT FK_CourseLesson_CourseId FOREIGN KEY (CourseId)
REFERENCES Course (UserItemId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE CourseLesson
ADD CONSTRAINT FK_CourseLesson_LessonId FOREIGN KEY (LessonId)
REFERENCES Lesson (UserItemId) ON DELETE CASCADE;

--
-- Create view `ViewTotalCourseRelation`
--
CREATE VIEW ViewTotalCourseRelation
AS
SELECT
  `lqr`.`UserItemId` AS `UserItemId`,
  `lqr`.`TotalLesson` AS `TotalLesson`,
  `lqr`.`TotalQuestion` AS `TotalQuestion`,
  `lqr`.`TotalRegister` AS `TotalRegister`,
  COUNT((CASE WHEN (`crl`.`CourseRelationType` = 1) THEN 1 END)) AS `TotalInvite`,
  COUNT((CASE WHEN (`crl`.`CourseRelationType` = 0) THEN 1 END)) AS `TotalRequest`
FROM ((SELECT
    `lq`.`UserItemId` AS `UserItemId`,
    `lq`.`TotalLesson` AS `TotalLesson`,
    `lq`.`TotalQuestion` AS `TotalQuestion`,
    COUNT(`cr`.`CourseRegisterId`) AS `TotalRegister`
  FROM ((SELECT
      `lesson`.`UserItemId` AS `UserItemId`,
      `lesson`.`TotalLesson` AS `TotalLesson`,
      COUNT(`q`.`UserItemId`) AS `TotalQuestion`
    FROM ((SELECT
        `c`.`UserItemId` AS `UserItemId`,
        COUNT(`cl`.`LessonId`) AS `TotalLesson`
      FROM (`Course` `c`
        LEFT JOIN `CourseLesson` `cl`
          ON ((`c`.`UserItemId` = `cl`.`CourseId`)))
      GROUP BY `c`.`UserItemId`) `lesson`
      LEFT JOIN `Question` `q`
        ON ((`lesson`.`UserItemId` = `q`.`CourseId`)))
    GROUP BY `lesson`.`UserItemId`) `lq`
    LEFT JOIN `CourseRegister` `cr`
      ON ((`lq`.`UserItemId` = `cr`.`CourseId`)))
  GROUP BY `lq`.`UserItemId`) `lqr`
  LEFT JOIN `CourseRelation` `crl`
    ON ((`lqr`.`UserItemId` = `crl`.`CourseId`)))
GROUP BY `lqr`.`UserItemId`;

--
-- Create table `Comment`
--
CREATE TABLE Comment (
  UserItemId char(36) NOT NULL,
  KnowledgeId char(36) NOT NULL COMMENT 'Id của phần tử kiến thức mà bình luận đánh giá (Bài học, khóa học, câu hỏi)',
  Content text NOT NULL COMMENT 'Nội dung bình luận',
  ReplyId char(36) DEFAULT NULL COMMENT 'Id của bình luận khác nếu đây là bình luận phản hồi (chỉ cho phép reply cấp 1)',
  PRIMARY KEY (UserItemId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 819,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Các bình luận của người dùng, kế thừa UserItem',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_Comment_UserItemId` on table `Comment`
--
ALTER TABLE Comment
ADD UNIQUE INDEX UK_Comment_UserItemId (UserItemId);

--
-- Create foreign key
--
ALTER TABLE Comment
ADD CONSTRAINT FK_Comment_KnowledgeId FOREIGN KEY (KnowledgeId)
REFERENCES Knowledge (UserItemId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE Comment 
  ADD CONSTRAINT FK_Comment_ReplyId FOREIGN KEY (ReplyId)
    REFERENCES Comment (UserItemId) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE Comment
ADD CONSTRAINT FK_Comment_UserItemId FOREIGN KEY (UserItemId)
REFERENCES UserItem (UserItemId) ON DELETE CASCADE;

--
-- Create view `ViewTotalComment`
--
CREATE VIEW ViewTotalComment
AS
SELECT
  `c`.`UserItemId` AS `UserItemId`,
  COUNT(`c1`.`UserItemId`) AS `TotalComment`
FROM (`Comment` `c`
  LEFT JOIN `Comment` `c1`
    ON ((`c`.`UserItemId` = `c1`.`ReplyId`)))
GROUP BY `c`.`UserItemId`
UNION ALL
SELECT
  `k`.`UserItemId` AS `UserItemId`,
  COUNT(`c`.`UserItemId`) AS `TotalComment`
FROM (`Knowledge` `k`
  LEFT JOIN `Comment` `c`
    ON ((`k`.`UserItemId` = `c`.`KnowledgeId`)))
GROUP BY `k`.`UserItemId`;

--
-- Create view `ViewTotalUserItem`
--
CREATE VIEW ViewTotalUserItem
AS
SELECT
  `view1`.`UserItemId` AS `UserItemId`,
  `view1`.`TotalStar` AS `TotalStar`,
  `view1`.`SumStar` AS `SumStar`,
  `view1`.`AverageStar` AS `AverageStar`,
  `vtc`.`TotalComment` AS `TotalComment`
FROM ((SELECT
    `ui`.`UserItemId` AS `UserItemId`,
    COUNT(`s`.`StarId`) AS `TotalStar`,
    SUM(`s`.`Stars`) AS `SumStar`,
    AVG(`s`.`Stars`) AS `AverageStar`
  FROM (`UserItem` `ui`
    LEFT JOIN `Star` `s`
      ON ((`ui`.`UserItemId` = `s`.`UserItemId`)))
  GROUP BY `ui`.`UserItemId`) `view1`
  LEFT JOIN `ViewTotalComment` `vtc`
    ON ((`view1`.`UserItemId` = `vtc`.`UserItemId`)));

--
-- Create table `Session`
--
CREATE TABLE Session (
  SessionId char(36) NOT NULL COMMENT 'Id của phiên đăng nhập',
  UserId char(36) NOT NULL COMMENT 'Id của người dùng đăng nhập',
  RefreshToken varchar(1023) NOT NULL COMMENT 'Mã refresh token',
  Expired datetime NOT NULL COMMENT 'Thời hạn phiên đăng nhập',
  Time datetime NOT NULL COMMENT 'Thời gian đăng nhập',
  Place varchar(255) DEFAULT NULL COMMENT 'Vị trí đăng nhập',
  Device varchar(255) DEFAULT NULL COMMENT 'Thiết bị đăng nhập',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (SessionId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu thông tin các phiên đăng nhập người dùng',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Session_Time` on table `Session`
--
ALTER TABLE Session
ADD INDEX IDX_Session_Time (Time);

--
-- Create index `UK_Session_SessionId` on table `Session`
--
ALTER TABLE Session
ADD UNIQUE INDEX UK_Session_SessionId (SessionId);

--
-- Create foreign key
--
ALTER TABLE Session
ADD CONSTRAINT FK_Session_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create table `RequestFriend`
--
CREATE TABLE RequestFriend (
  RequestFriendId char(36) NOT NULL COMMENT 'Id của quan hệ bạn bè',
  SenderId char(36) NOT NULL COMMENT 'Id người gửi lời mời',
  ReceiverId char(36) NOT NULL COMMENT 'Id người nhận lời mời',
  Time datetime NOT NULL COMMENT 'Thời gian gửi kết bạn',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (RequestFriendId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu danh sách lời mời kết bạn',
ROW_FORMAT = DYNAMIC;

--
-- Create index `RDX_requestFriend_Time` on table `RequestFriend`
--
ALTER TABLE RequestFriend
ADD INDEX RDX_requestFriend_Time (Time);

--
-- Create index `RK_requestFriend_RequestFriendId` on table `RequestFriend`
--
ALTER TABLE RequestFriend
ADD UNIQUE INDEX RK_requestFriend_RequestFriendId (RequestFriendId);

--
-- Create foreign key
--
ALTER TABLE RequestFriend
ADD CONSTRAINT RK_requestFriend_ReceiverId FOREIGN KEY (ReceiverId)
REFERENCES User (UserId);

--
-- Create foreign key
--
ALTER TABLE RequestFriend
ADD CONSTRAINT RK_requestFriend_SenderId FOREIGN KEY (SenderId)
REFERENCES User (UserId);

--
-- Create table `Profile`
--
CREATE TABLE Profile (
  ProfileId char(36) NOT NULL COMMENT 'Id của Profile',
  UserId char(36) NOT NULL COMMENT 'Id của người dùng',
  FullName varchar(255) NOT NULL DEFAULT 'Người dùng hệ thống' COMMENT 'Họ tên đầy đủ',
  Avatar text DEFAULT NULL COMMENT 'Url của ảnh đại diện',
  Cover text DEFAULT NULL COMMENT 'Url của ảnh bìa',
  Nickname varchar(255) DEFAULT NULL COMMENT 'Biệt danh',
  Bio text DEFAULT NULL COMMENT 'Lời giới thiệu, châm ngôn sống...',
  Gender int DEFAULT NULL COMMENT 'Giới tính: 0 - Nam, 1 - Nữ, 2 - Khác',
  DateOfBirth date DEFAULT NULL COMMENT 'Ngày sinh',
  PhoneNumber varchar(255) DEFAULT NULL COMMENT 'Số điện thoại',
  ContactEmail varchar(255) DEFAULT NULL COMMENT 'Email liên lạc',
  Country varchar(255) DEFAULT NULL COMMENT 'Quê quán',
  Address varchar(255) DEFAULT NULL COMMENT 'Địa chỉ',
  SocialLink text DEFAULT NULL COMMENT 'Liên kết xã hội',
  School varchar(255) DEFAULT NULL COMMENT 'Tên trường học',
  Profession varchar(255) DEFAULT NULL COMMENT 'Tên ngành học',
  Cpa double DEFAULT NULL COMMENT 'Điểm CPA',
  Grade varchar(255) DEFAULT NULL COMMENT 'Niên khóa (K64, K65...)',
  Class varchar(255) DEFAULT NULL COMMENT 'Tên lớp học',
  Job varchar(255) DEFAULT NULL COMMENT 'Nghề nghiệp hiện tại',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian chỉnh sửa',
  PRIMARY KEY (ProfileId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 682,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Thông tin Profile chi tiết người dùng',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Profile_FullName` on table `Profile`
--
ALTER TABLE Profile
ADD INDEX IDX_Profile_FullName (FullName);

--
-- Create index `UK_Profile_ProfileId` on table `Profile`
--
ALTER TABLE Profile
ADD UNIQUE INDEX UK_Profile_ProfileId (ProfileId);

--
-- Create foreign key
--
ALTER TABLE Profile
ADD CONSTRAINT FK_Profile_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Create view `ViewUserProfile`
--
CREATE VIEW ViewUserProfile
AS
SELECT
  `u`.`UserId` AS `UserId`,
  `u`.`Email` AS `Email`,
  `u`.`Username` AS `Username`,
  `u`.`Role` AS `Role`,
  `u`.`CreatedBy` AS `CreatedBy`,
  `u`.`CreatedTime` AS `CreatedTime`,
  `u`.`ModifiedBy` AS `ModifiedBy`,
  `u`.`ModifiedTime` AS `ModifiedTime`,
  `p`.`ProfileId` AS `ProfileId`,
  `p`.`FullName` AS `FullName`,
  `p`.`Avatar` AS `Avatar`,
  `p`.`Cover` AS `Cover`,
  `p`.`Nickname` AS `Nickname`,
  `p`.`Bio` AS `Bio`,
  `p`.`Gender` AS `Gender`,
  `p`.`DateOfBirth` AS `DateOfBirth`,
  `p`.`PhoneNumber` AS `PhoneNumber`,
  `p`.`ContactEmail` AS `ContactEmail`,
  `p`.`Country` AS `Country`,
  `p`.`Address` AS `Address`,
  `p`.`SocialLink` AS `SocialLink`,
  `p`.`School` AS `School`,
  `p`.`Profession` AS `Profession`,
  `p`.`Cpa` AS `Cpa`,
  `p`.`Grade` AS `Grade`,
  `p`.`Class` AS `Class`,
  `p`.`Job` AS `Job`
FROM (`User` `u`
  LEFT JOIN `Profile` `p`
    ON ((`u`.`UserId` = `p`.`UserId`)));

--
-- Create view `ViewUserRelation`
--
CREATE VIEW ViewUserRelation
AS
SELECT
  `ur`.`UserRelationId` AS `UserRelationId`,
  `ur`.`SenderId` AS `SenderId`,
  `sender`.`Email` AS `SenderEmail`,
  `sender`.`Username` AS `SenderUsername`,
  `sender`.`Role` AS `SenderRole`,
  `sender`.`FullName` AS `SenderName`,
  `sender`.`Avatar` AS `SenderAvatar`,
  `sender`.`Cover` AS `SenderCover`,
  `ur`.`ReceiverId` AS `ReceiverId`,
  `receiver`.`Email` AS `ReceiverEmail`,
  `receiver`.`Username` AS `ReceiverUsername`,
  `receiver`.`Role` AS `ReceiverRole`,
  `receiver`.`FullName` AS `ReceiverName`,
  `receiver`.`Avatar` AS `ReceiverAvatar`,
  `receiver`.`Cover` AS `ReceiverCover`,
  `ur`.`Time` AS `Time`,
  `ur`.`CreatedBy` AS `CreatedBy`,
  `ur`.`CreatedTime` AS `CreatedTime`,
  `ur`.`ModifiedTime` AS `ModifiedTime`,
  `ur`.`ModifiedBy` AS `ModifiedBy`,
  `ur`.`UserRelationType` AS `UserRelationType`
FROM ((`UserRelation` `ur`
  JOIN `ViewUserProfile` `sender`
    ON ((`ur`.`SenderId` = `sender`.`UserId`)))
  JOIN `ViewUserProfile` `receiver`
    ON ((`ur`.`ReceiverId` = `receiver`.`UserId`)));

--
-- Create view `ViewUserItem`
--
CREATE VIEW ViewUserItem
AS
SELECT
  `ui`.`UserItemId` AS `UserItemId`,
  `ui`.`UserId` AS `UserId`,
  `ui`.`UserItemType` AS `UserItemType`,
  `ui`.`CreatedBy` AS `CreatedBy`,
  `ui`.`CreatedTime` AS `CreatedTime`,
  `ui`.`ModifiedBy` AS `ModifiedBy`,
  `ui`.`ModifiedTime` AS `ModifiedTime`,
  `vup`.`Email` AS `Email`,
  `vup`.`Username` AS `Username`,
  `vup`.`Role` AS `Role`,
  `vup`.`FullName` AS `FullName`,
  `vup`.`Avatar` AS `Avatar`,
  `vup`.`Cover` AS `Cover`,
  `vtui`.`TotalStar` AS `TotalStar`,
  `vtui`.`SumStar` AS `SumStar`,
  `vtui`.`AverageStar` AS `AverageStar`,
  `vtui`.`TotalComment` AS `TotalComment`
FROM ((`UserItem` `ui`
  LEFT JOIN `ViewUserProfile` `vup`
    ON ((`ui`.`UserId` = `vup`.`UserId`)))
  LEFT JOIN `ViewTotalUserItem` `vtui`
    ON ((`ui`.`UserItemId` = `vtui`.`UserItemId`)))
ORDER BY `ui`.`UserId`, `ui`.`CreatedTime` DESC;

--
-- Create view `ViewKnowledge`
--
CREATE VIEW ViewKnowledge
AS
SELECT
  `vui`.`UserItemId` AS `UserItemId`,
  `vui`.`UserId` AS `UserId`,
  `vui`.`UserItemType` AS `UserItemType`,
  `vui`.`CreatedBy` AS `CreatedBy`,
  `vui`.`CreatedTime` AS `CreatedTime`,
  `vui`.`ModifiedBy` AS `ModifiedBy`,
  `vui`.`ModifiedTime` AS `ModifiedTime`,
  `vui`.`Email` AS `Email`,
  `vui`.`Username` AS `Username`,
  `vui`.`Role` AS `Role`,
  `vui`.`FullName` AS `FullName`,
  `vui`.`Avatar` AS `Avatar`,
  `vui`.`Cover` AS `Cover`,
  `vui`.`TotalStar` AS `TotalStar`,
  `vui`.`SumStar` AS `SumStar`,
  `vui`.`AverageStar` AS `AverageStar`,
  `vui`.`TotalComment` AS `TotalComment`,
  `k`.`Title` AS `Title`,
  `k`.`Abstract` AS `Abstract`,
  `k`.`Thumbnail` AS `Thumbnail`,
  `k`.`Views` AS `Views`,
  `k`.`KnowledgeType` AS `KnowledgeType`,
  `k`.`Privacy` AS `Privacy`,
  `k`.`IsBlockComment` AS `IsBlockComment`
FROM (`Knowledge` `k`
  JOIN `ViewUserItem` `vui`
    ON ((`k`.`UserItemId` = `vui`.`UserItemId`)));

--
-- Create view `ViewPost2`
--
CREATE VIEW ViewPost2
AS
SELECT
  `vkn`.`UserItemId` AS `UserItemId`,
  `vkn`.`UserId` AS `UserId`,
  `vkn`.`UserItemType` AS `UserItemType`,
  `vkn`.`CreatedBy` AS `CreatedBy`,
  `vkn`.`CreatedTime` AS `CreatedTime`,
  `vkn`.`ModifiedBy` AS `ModifiedBy`,
  `vkn`.`ModifiedTime` AS `ModifiedTime`,
  `vkn`.`Username` AS `Username`,
  `vkn`.`FullName` AS `FullName`,
  `vkn`.`Avatar` AS `Avatar`,
  `vkn`.`Cover` AS `Cover`,
  `vkn`.`TotalStar` AS `TotalStar`,
  `vkn`.`SumStar` AS `SumStar`,
  `vkn`.`AverageStar` AS `AverageStar`,
  `vkn`.`TotalComment` AS `TotalComment`,
  `vkn`.`Title` AS `Title`,
  `vkn`.`Abstract` AS `Abstract`,
  `vkn`.`Thumbnail` AS `Thumbnail`,
  `vkn`.`Views` AS `Views`,
  `vkn`.`KnowledgeType` AS `KnowledgeType`,
  `vkn`.`Privacy` AS `Privacy`,
  `vkn`.`IsBlockComment` AS `IsBlockComment`,
  `p`.`Content` AS `Content`,
  `p`.`PostType` AS `PostType`
FROM (`Post` `p`
  JOIN `ViewKnowledge` `vkn`
    ON ((`p`.`UserItemId` = `vkn`.`UserItemId`)));

--
-- Create view `ViewQuestion2`
--
CREATE VIEW ViewQuestion2
AS
SELECT
  `vp`.`UserItemId` AS `UserItemId`,
  `vp`.`UserId` AS `UserId`,
  `vp`.`UserItemType` AS `UserItemType`,
  `vp`.`CreatedBy` AS `CreatedBy`,
  `vp`.`CreatedTime` AS `CreatedTime`,
  `vp`.`ModifiedBy` AS `ModifiedBy`,
  `vp`.`ModifiedTime` AS `ModifiedTime`,
  `vp`.`Username` AS `Username`,
  `vp`.`FullName` AS `FullName`,
  `vp`.`Avatar` AS `Avatar`,
  `vp`.`Cover` AS `Cover`,
  `vp`.`TotalStar` AS `TotalStar`,
  `vp`.`SumStar` AS `SumStar`,
  `vp`.`AverageStar` AS `AverageStar`,
  `vp`.`TotalComment` AS `TotalComment`,
  `vp`.`Title` AS `Title`,
  `vp`.`Abstract` AS `Abstract`,
  `vp`.`Thumbnail` AS `Thumbnail`,
  `vp`.`Views` AS `Views`,
  `vp`.`KnowledgeType` AS `KnowledgeType`,
  `vp`.`Privacy` AS `Privacy`,
  `vp`.`IsBlockComment` AS `IsBlockComment`,
  `vp`.`Content` AS `Content`,
  `vp`.`PostType` AS `PostType`,
  `q`.`CourseId` AS `CourseId`,
  `q`.`IsAccept` AS `IsAccept`
FROM (`Question` `q`
  LEFT JOIN `ViewPost2` `vp`
    ON ((`q`.`UserItemId` = `vp`.`UserItemId`)));

--
-- Create view `ViewLesson2`
--
CREATE VIEW ViewLesson2
AS
SELECT
  `vp`.`UserItemId` AS `UserItemId`,
  `vp`.`UserId` AS `UserId`,
  `vp`.`UserItemType` AS `UserItemType`,
  `vp`.`CreatedBy` AS `CreatedBy`,
  `vp`.`CreatedTime` AS `CreatedTime`,
  `vp`.`ModifiedBy` AS `ModifiedBy`,
  `vp`.`ModifiedTime` AS `ModifiedTime`,
  `vp`.`Username` AS `Username`,
  `vp`.`FullName` AS `FullName`,
  `vp`.`Avatar` AS `Avatar`,
  `vp`.`Cover` AS `Cover`,
  `vp`.`TotalStar` AS `TotalStar`,
  `vp`.`SumStar` AS `SumStar`,
  `vp`.`AverageStar` AS `AverageStar`,
  `vp`.`TotalComment` AS `TotalComment`,
  `vp`.`Title` AS `Title`,
  `vp`.`Abstract` AS `Abstract`,
  `vp`.`Thumbnail` AS `Thumbnail`,
  `vp`.`Views` AS `Views`,
  `vp`.`KnowledgeType` AS `KnowledgeType`,
  `vp`.`Privacy` AS `Privacy`,
  `vp`.`IsBlockComment` AS `IsBlockComment`,
  `vp`.`Content` AS `Content`,
  `vp`.`PostType` AS `PostType`,
  `l`.`EstimateTimeInMinutes` AS `EstimateTimeInMinutes`
FROM (`Lesson` `l`
  JOIN `ViewPost2` `vp`
    ON ((`l`.`UserItemId` = `vp`.`UserItemId`)));

--
-- Create view `ViewCourse`
--
CREATE VIEW ViewCourse
AS
SELECT
  `vk`.`UserItemId` AS `UserItemId`,
  `vk`.`UserId` AS `UserId`,
  `vk`.`UserItemType` AS `UserItemType`,
  `vk`.`CreatedBy` AS `CreatedBy`,
  `vk`.`CreatedTime` AS `CreatedTime`,
  `vk`.`ModifiedBy` AS `ModifiedBy`,
  `vk`.`ModifiedTime` AS `ModifiedTime`,
  `vk`.`Username` AS `Username`,
  `vk`.`FullName` AS `FullName`,
  `vk`.`Avatar` AS `Avatar`,
  `vk`.`Cover` AS `Cover`,
  `vk`.`TotalStar` AS `TotalStar`,
  `vk`.`SumStar` AS `SumStar`,
  `vk`.`AverageStar` AS `AverageStar`,
  `vk`.`TotalComment` AS `TotalComment`,
  `vk`.`Title` AS `Title`,
  `vk`.`Abstract` AS `Abstract`,
  `vk`.`Thumbnail` AS `Thumbnail`,
  `vk`.`Views` AS `Views`,
  `vk`.`KnowledgeType` AS `KnowledgeType`,
  `vk`.`Privacy` AS `Privacy`,
  `vk`.`IsBlockComment` AS `IsBlockComment`,
  `c`.`Introduction` AS `Introduction`,
  `c`.`Fee` AS `Fee`,
  `c`.`EstimateTimeInMinutes` AS `EstimateTimeInMinutes`,
  `c`.`IsFree` AS `IsFree`
FROM (`Course` `c`
  JOIN `ViewKnowledge` `vk`
    ON ((`c`.`UserItemId` = `vk`.`UserItemId`)));

--
-- Create view `ViewComment2`
--
CREATE VIEW ViewComment2
AS
SELECT
  `c`.`KnowledgeId` AS `KnowledgeId`,
  `c`.`Content` AS `Content`,
  `c`.`ReplyId` AS `ReplyId`,
  `vui`.`UserItemId` AS `UserItemId`,
  `vui`.`UserId` AS `UserId`,
  `vui`.`UserItemType` AS `UserItemType`,
  `vui`.`CreatedBy` AS `CreatedBy`,
  `vui`.`CreatedTime` AS `CreatedTime`,
  `vui`.`ModifiedBy` AS `ModifiedBy`,
  `vui`.`ModifiedTime` AS `ModifiedTime`,
  `vui`.`Username` AS `Username`,
  `vui`.`Role` AS `Role`,
  `vui`.`FullName` AS `FullName`,
  `vui`.`Avatar` AS `Avatar`,
  `vui`.`Cover` AS `Cover`,
  `vui`.`TotalStar` AS `TotalStar`,
  `vui`.`SumStar` AS `SumStar`,
  `vui`.`AverageStar` AS `AverageStar`,
  `vui`.`TotalComment` AS `TotalComment`
FROM (`Comment` `c`
  JOIN `ViewUserItem` `vui`
    ON ((`c`.`UserItemId` = `vui`.`UserItemId`)));

--
-- Create view `ViewUser`
--
CREATE VIEW ViewUser
AS
SELECT
  `up`.`UserId` AS `UserId`,
  `up`.`Email` AS `Email`,
  `up`.`Username` AS `Username`,
  `up`.`Role` AS `Role`,
  `up`.`CreatedBy` AS `CreatedBy`,
  `up`.`CreatedTime` AS `CreatedTime`,
  `up`.`ModifiedBy` AS `ModifiedBy`,
  `up`.`ModifiedTime` AS `ModifiedTime`,
  `up`.`ProfileId` AS `ProfileId`,
  `up`.`FullName` AS `FullName`,
  `up`.`Avatar` AS `Avatar`,
  `up`.`Cover` AS `Cover`,
  `up`.`Nickname` AS `Nickname`,
  `up`.`Bio` AS `Bio`,
  `up`.`Gender` AS `Gender`,
  `up`.`DateOfBirth` AS `DateOfBirth`,
  `up`.`PhoneNumber` AS `PhoneNumber`,
  `up`.`ContactEmail` AS `ContactEmail`,
  `up`.`Country` AS `Country`,
  `up`.`Address` AS `Address`,
  `up`.`SocialLink` AS `SocialLink`,
  `up`.`School` AS `School`,
  `up`.`Profession` AS `Profession`,
  `up`.`Cpa` AS `Cpa`,
  `up`.`Grade` AS `Grade`,
  `up`.`Class` AS `Class`,
  `up`.`Job` AS `Job`,
  `vtur`.`TotalFriend` AS `TotalFriend`,
  `vtur`.`TotalRequester` AS `TotalRequester`,
  `vtur`.`TotalRequestee` AS `TotalRequestee`,
  `vtur`.`TotalFollower` AS `TotalFollower`,
  `vtur`.`TotalFollowee` AS `TotalFollowee`,
  `vtur`.`TotalBlocker` AS `TotalBlocker`,
  `vtur`.`TotalBlockee` AS `TotalBlockee`
FROM (`ViewUserProfile` `up`
  LEFT JOIN `ViewTotalUserRelation` `vtur`
    ON ((`up`.`UserId` = `vtur`.`UserId`)));

--
-- Create view `ViewQuestion`
--
CREATE VIEW ViewQuestion
AS
SELECT
  `p1`.`FullName` AS `FullName`,
  `p1`.`Avatar` AS `Avatar`,
  `p1`.`Cover` AS `Cover`,
  `u`.`UserItemId` AS `UserItemId`,
  `u`.`UserId` AS `UserId`,
  `u1`.`Username` AS `Username`,
  `u`.`UserItemType` AS `UserItemType`,
  `u`.`CreatedBy` AS `CreatedBy`,
  `u`.`CreatedTime` AS `CreatedTime`,
  `u`.`ModifiedBy` AS `ModifiedBy`,
  `u`.`ModifiedTime` AS `ModifiedTime`,
  `k`.`Title` AS `Title`,
  `k`.`Abstract` AS `Abstract`,
  `k`.`Thumbnail` AS `Thumbnail`,
  `k`.`Views` AS `Views`,
  `k`.`KnowledgeType` AS `KnowledgeType`,
  `k`.`Privacy` AS `Privacy`,
  `k`.`IsBlockComment` AS `IsBlockComment`,
  `p`.`Content` AS `Content`,
  `p`.`PostType` AS `PostType`,
  `q`.`CourseId` AS `CourseId`,
  `q`.`IsAccept` AS `IsAccept`,
  `vtui`.`TotalStar` AS `TotalStar`,
  `vtui`.`SumStar` AS `SumStar`,
  `vtui`.`AverageStar` AS `AverageStar`,
  `vtui`.`TotalComment` AS `TotalComment`
FROM ((((((`Question` `q`
  JOIN `Post` `p`
    ON ((`q`.`UserItemId` = `p`.`UserItemId`)))
  JOIN `Knowledge` `k`
    ON ((`p`.`UserItemId` = `k`.`UserItemId`)))
  JOIN `UserItem` `u`
    ON ((`k`.`UserItemId` = `u`.`UserItemId`)))
  LEFT JOIN `Profile` `p1`
    ON ((`p1`.`UserId` = `u`.`UserId`)))
  LEFT JOIN `ViewTotalUserItem` `vtui`
    ON ((`q`.`UserItemId` = `vtui`.`UserItemId`)))
  LEFT JOIN `User` `u1`
    ON ((`u`.`UserId` = `u1`.`UserId`)))
ORDER BY `u`.`UserId`, `u`.`CreatedTime`;

--
-- Create view `ViewPost`
--
CREATE VIEW ViewPost
AS
SELECT
  `u`.`UserItemId` AS `UserItemId`,
  `u`.`UserId` AS `UserId`,
  `u`.`UserItemType` AS `UserItemType`,
  `u`.`CreatedBy` AS `CreatedBy`,
  `u`.`CreatedTime` AS `CreatedTime`,
  `u`.`ModifiedBy` AS `ModifiedBy`,
  `u`.`ModifiedTime` AS `ModifiedTime`,
  `k`.`Title` AS `Title`,
  `k`.`Abstract` AS `Abstract`,
  `k`.`Thumbnail` AS `Thumbnail`,
  `k`.`Views` AS `Views`,
  `k`.`KnowledgeType` AS `KnowledgeType`,
  `k`.`Privacy` AS `Privacy`,
  `k`.`IsBlockComment` AS `IsBlockComment`,
  `p`.`Content` AS `Content`,
  `p`.`PostType` AS `PostType`,
  `us`.`Username` AS `Username`,
  `p1`.`FullName` AS `FullName`,
  `p1`.`Avatar` AS `Avatar`,
  `p1`.`Cover` AS `Cover`,
  `vtui`.`TotalStar` AS `TotalStar`,
  `vtui`.`SumStar` AS `SumStar`,
  `vtui`.`AverageStar` AS `AverageStar`,
  `vtui`.`TotalComment` AS `TotalComment`
FROM (((((`Post` `p`
  JOIN `Knowledge` `k`
    ON ((`p`.`UserItemId` = `k`.`UserItemId`)))
  JOIN `UserItem` `u`
    ON ((`k`.`UserItemId` = `u`.`UserItemId`)))
  LEFT JOIN `Profile` `p1`
    ON ((`p1`.`UserId` = `u`.`UserId`)))
  LEFT JOIN `ViewTotalUserItem` `vtui`
    ON ((`p`.`UserItemId` = `vtui`.`UserItemId`)))
  LEFT JOIN `User` `us`
    ON ((`u`.`UserId` = `us`.`UserId`)))
ORDER BY `u`.`UserId`, `u`.`CreatedTime`;

--
-- Create view `ViewLesson`
--
CREATE VIEW ViewLesson
AS
SELECT
  `p1`.`FullName` AS `FullName`,
  `p1`.`Avatar` AS `Avatar`,
  `p1`.`Cover` AS `Cover`,
  `u`.`UserItemId` AS `UserItemId`,
  `u`.`UserId` AS `UserId`,
  `u1`.`Username` AS `Username`,
  `u`.`UserItemType` AS `UserItemType`,
  `u`.`CreatedBy` AS `CreatedBy`,
  `u`.`CreatedTime` AS `CreatedTime`,
  `u`.`ModifiedBy` AS `ModifiedBy`,
  `u`.`ModifiedTime` AS `ModifiedTime`,
  `k`.`Title` AS `Title`,
  `k`.`Abstract` AS `Abstract`,
  `k`.`Thumbnail` AS `Thumbnail`,
  `k`.`Views` AS `Views`,
  `k`.`KnowledgeType` AS `KnowledgeType`,
  `k`.`Privacy` AS `Privacy`,
  `k`.`IsBlockComment` AS `IsBlockComment`,
  `p`.`Content` AS `Content`,
  `p`.`PostType` AS `PostType`,
  `l`.`EstimateTimeInMinutes` AS `EstimateTimeInMinutes`,
  `vtui`.`TotalStar` AS `TotalStar`,
  `vtui`.`SumStar` AS `SumStar`,
  `vtui`.`AverageStar` AS `AverageStar`,
  `vtui`.`TotalComment` AS `TotalComment`
FROM ((((((`Lesson` `l`
  JOIN `Post` `p`
    ON ((`l`.`UserItemId` = `p`.`UserItemId`)))
  JOIN `Knowledge` `k`
    ON ((`p`.`UserItemId` = `k`.`UserItemId`)))
  JOIN `UserItem` `u`
    ON ((`k`.`UserItemId` = `u`.`UserItemId`)))
  LEFT JOIN `Profile` `p1`
    ON ((`p1`.`UserId` = `u`.`UserId`)))
  LEFT JOIN `ViewTotalUserItem` `vtui`
    ON ((`l`.`UserItemId` = `vtui`.`UserItemId`)))
  LEFT JOIN `User` `u1`
    ON ((`u`.`UserId` = `u1`.`UserId`)))
ORDER BY `u`.`UserId`, `u`.`CreatedTime`;

--
-- Create view `ViewCourseTotalRelation`
--
CREATE VIEW ViewCourseTotalRelation
AS
SELECT
  `us`.`Username` AS `Username`,
  `p`.`FullName` AS `FullName`,
  `p`.`Avatar` AS `Avatar`,
  `p`.`Cover` AS `Cover`,
  `c`.`UserItemId` AS `UserItemId`,
  `c`.`Introduction` AS `Introduction`,
  `c`.`Fee` AS `Fee`,
  `c`.`EstimateTimeInMinutes` AS `EstimateTimeInMinutes`,
  `c`.`IsFree` AS `IsFree`,
  `k`.`Title` AS `Title`,
  `k`.`Abstract` AS `Abstract`,
  `k`.`Thumbnail` AS `Thumbnail`,
  `k`.`Views` AS `Views`,
  `k`.`KnowledgeType` AS `KnowledgeType`,
  `k`.`Privacy` AS `Privacy`,
  `k`.`IsBlockComment` AS `IsBlockComment`,
  `u`.`UserId` AS `UserId`,
  `u`.`UserItemType` AS `UserItemType`,
  `u`.`CreatedBy` AS `CreatedBy`,
  `u`.`CreatedTime` AS `CreatedTime`,
  `u`.`ModifiedBy` AS `ModifiedBy`,
  `u`.`ModifiedTime` AS `ModifiedTime`,
  `vtui`.`TotalStar` AS `TotalStar`,
  `vtui`.`SumStar` AS `SumStar`,
  `vtui`.`AverageStar` AS `AverageStar`,
  `vtui`.`TotalComment` AS `TotalComment`,
  `vtcr`.`TotalLesson` AS `TotalLesson`,
  `vtcr`.`TotalQuestion` AS `TotalQuestion`,
  `vtcr`.`TotalRegister` AS `TotalRegister`,
  `vtcr`.`TotalInvite` AS `TotalInvite`,
  `vtcr`.`TotalRequest` AS `TotalRequest`
FROM ((((((`Course` `c`
  JOIN `Knowledge` `k`
    ON ((`c`.`UserItemId` = `k`.`UserItemId`)))
  JOIN `UserItem` `u`
    ON ((`k`.`UserItemId` = `u`.`UserItemId`)))
  LEFT JOIN `User` `us`
    ON ((`u`.`UserId` = `us`.`UserId`)))
  LEFT JOIN `Profile` `p`
    ON ((`p`.`UserId` = `u`.`UserId`)))
  LEFT JOIN `ViewTotalUserItem` `vtui`
    ON ((`c`.`UserItemId` = `vtui`.`UserItemId`)))
  LEFT JOIN `ViewTotalCourseRelation` `vtcr`
    ON ((`c`.`UserItemId` = `vtcr`.`UserItemId`)))
ORDER BY `u`.`UserId`, `u`.`CreatedTime`;

--
-- Create view `ViewCourseRegister`
--
CREATE VIEW ViewCourseRegister
AS
SELECT
  `us`.`Username` AS `Username`,
  `p`.`FullName` AS `FullName`,
  `p`.`Avatar` AS `Avatar`,
  `p`.`Cover` AS `Cover`,
  `cr`.`CourseRegisterId` AS `CourseRegisterId`,
  `cr`.`UserId` AS `UserId`,
  `cr`.`CourseId` AS `CourseId`,
  `cr`.`CreatedBy` AS `CreatedBy`,
  `cr`.`CreatedTime` AS `CreatedTime`,
  `cr`.`ModifiedBy` AS `ModifiedBy`,
  `cr`.`ModifiedTime` AS `ModifiedTime`,
  `c`.`Title` AS `Title`,
  `c`.`Abstract` AS `Abstract`,
  `c`.`Fee` AS `Fee`,
  `c`.`IsFree` AS `IsFree`,
  `c`.`Thumbnail` AS `Thumbnail`,
  `c`.`UserId` AS `CourseOwnerUserId`,
  `us2`.`Username` AS `CourseOwnerUsername`,
  `c`.`FullName` AS `CourseOwnerFullName`,
  `c`.`Avatar` AS `CourseOwnerAvatar`,
  `c`.`Cover` AS `CourseOwnerCover`
FROM ((((`CourseRegister` `cr`
  LEFT JOIN `User` `us`
    ON ((`cr`.`UserId` = `us`.`UserId`)))
  JOIN `Profile` `p`
    ON ((`cr`.`UserId` = `p`.`UserId`)))
  JOIN `ViewCourseTotalRelation` `c`
    ON ((`cr`.`CourseId` = `c`.`UserItemId`)))
  LEFT JOIN `User` `us2`
    ON ((`c`.`UserId` = `us2`.`UserId`)));

--
-- Create view `ViewCoursePayment`
--
CREATE VIEW ViewCoursePayment
AS
SELECT
  `c`.`CoursePaymentId` AS `CoursePaymentId`,
  `c`.`UserId` AS `UserId`,
  `u`.`Username` AS `Username`,
  `c`.`CourseId` AS `CourseId`,
  `c`.`Fee` AS `Fee`,
  `c`.`PaymentMethod` AS `PaymentMethod`,
  `c`.`CreatedBy` AS `CreatedBy`,
  `c`.`CreatedTime` AS `CreatedTime`,
  `c`.`ModifiedBy` AS `ModifiedBy`,
  `c`.`ModifiedTime` AS `ModifiedTime`,
  `p`.`FullName` AS `FullName`,
  `p`.`Avatar` AS `Avatar`,
  `p`.`Cover` AS `Cover`,
  `k`.`Title` AS `Title`,
  `k`.`Thumbnail` AS `Thumbnail`
FROM (((`CoursePayment` `c`
  LEFT JOIN `Profile` `p`
    ON ((`c`.`UserId` = `p`.`UserId`)))
  LEFT JOIN `User` `u`
    ON ((`u`.`UserId` = `c`.`UserId`)))
  LEFT JOIN `Knowledge` `k`
    ON ((`c`.`CourseId` = `k`.`UserItemId`)));

--
-- Create view `ViewComment`
--
CREATE VIEW ViewComment
AS
SELECT
  `c`.`KnowledgeId` AS `KnowledgeId`,
  `c`.`Content` AS `Content`,
  `c`.`ReplyId` AS `ReplyId`,
  `u`.`UserItemId` AS `UserItemId`,
  `u`.`UserId` AS `UserId`,
  `u`.`UserItemType` AS `UserItemType`,
  `u`.`CreatedBy` AS `CreatedBy`,
  `u`.`CreatedTime` AS `CreatedTime`,
  `u`.`ModifiedBy` AS `ModifiedBy`,
  `u`.`ModifiedTime` AS `ModifiedTime`,
  `u1`.`Username` AS `Username`,
  `p`.`FullName` AS `FullName`,
  `p`.`Avatar` AS `Avatar`,
  `p`.`Cover` AS `Cover`,
  `u1`.`Role` AS `Role`,
  `vtui`.`TotalStar` AS `TotalStar`,
  `vtui`.`SumStar` AS `SumStar`,
  `vtui`.`AverageStar` AS `AverageStar`,
  `vtui`.`TotalComment` AS `TotalComment`
FROM (((((`Comment` `c`
  JOIN `UserItem` `u`
    ON ((`c`.`UserItemId` = `u`.`UserItemId`)))
  LEFT JOIN `Profile` `p`
    ON ((`u`.`UserId` = `p`.`UserId`)))
  LEFT JOIN `Knowledge` `k`
    ON ((`c`.`KnowledgeId` = `k`.`UserItemId`)))
  LEFT JOIN `User` `u1`
    ON ((`u`.`UserId` = `u1`.`UserId`)))
  LEFT JOIN `ViewTotalUserItem` `vtui`
    ON ((`vtui`.`UserItemId` = `c`.`UserItemId`)))
ORDER BY `u`.`UserId`, `u`.`CreatedTime`;

--
-- Create table `Notification`
--
CREATE TABLE Notification (
  NotificationId char(36) NOT NULL COMMENT 'Id của thông báo',
  UserId char(36) NOT NULL COMMENT 'Id của chủ thông báo',
  Thumbnail varchar(255) DEFAULT NULL COMMENT 'Url của thumbnail của thông báo',
  Title varchar(255) NOT NULL COMMENT 'Tiêu đề thông báo',
  Content longtext NOT NULL COMMENT 'Nội dung thông báo',
  ReferenceLink longtext NOT NULL COMMENT 'Liên kết được tham chiếu tới của thông báo',
  Time datetime NOT NULL COMMENT 'Thời gian sự kiện của thông báo',
  IsRead tinyint(1) NOT NULL COMMENT 'Cờ hiệu báo đã đọc thông báo hay chưa (true - rồi, false - chưa)',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người chỉnh sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian chỉnh sửa',
  PRIMARY KEY (NotificationId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 655,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Danh sách các thông báo',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Notification_IsRead` on table `Notification`
--
ALTER TABLE Notification
ADD INDEX IDX_Notification_IsRead (IsRead);

--
-- Create index `IDX_Notification_Time` on table `Notification`
--
ALTER TABLE Notification
ADD INDEX IDX_Notification_Time (Time);

--
-- Create index `IDX_Notification_Title` on table `Notification`
--
ALTER TABLE Notification
ADD INDEX IDX_Notification_Title (Title);

--
-- Create index `UK_Notification_NotificationId` on table `Notification`
--
ALTER TABLE Notification
ADD UNIQUE INDEX UK_Notification_NotificationId (NotificationId);

--
-- Create foreign key
--
ALTER TABLE Notification
ADD CONSTRAINT FK_Notification_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create table `Image`
--
CREATE TABLE Image (
  ImageId char(36) NOT NULL COMMENT 'Id của tấm ảnh',
  UserId char(36) NOT NULL COMMENT 'Id của user chủ nhân tấm ảnh',
  ImageUrl text NOT NULL COMMENT 'Url của ảnh',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Ngày sửa',
  PRIMARY KEY (ImageId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 819,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu danh sách hình ảnh đã upload lên firebase của một user',
ROW_FORMAT = DYNAMIC;

--
-- Create foreign key
--
ALTER TABLE Image
ADD CONSTRAINT FK_Image_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create table `Friend`
--
CREATE TABLE Friend (
  FriendId char(36) NOT NULL COMMENT 'Id của quan hệ bạn bè',
  SenderId char(36) NOT NULL COMMENT 'Id của người gửi lời mời',
  ReceiverId char(36) NOT NULL COMMENT 'Id người nhận lời mời',
  Time datetime NOT NULL COMMENT 'Thời gian trở thành bạn bè',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (FriendId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu danh sách quan hệ bạn bè',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Friend_Time` on table `Friend`
--
ALTER TABLE Friend
ADD INDEX IDX_Friend_Time (Time);

--
-- Create index `UK_Friend_FriendId` on table `Friend`
--
ALTER TABLE Friend
ADD UNIQUE INDEX UK_Friend_FriendId (FriendId);

--
-- Create foreign key
--
ALTER TABLE Friend
ADD CONSTRAINT FK_Friend_ReceiverId FOREIGN KEY (ReceiverId)
REFERENCES User (UserId);

--
-- Create foreign key
--
ALTER TABLE Friend
ADD CONSTRAINT FK_Friend_SenderId FOREIGN KEY (SenderId)
REFERENCES User (UserId);

--
-- Create table `Follow`
--
CREATE TABLE Follow (
  FollowId char(36) NOT NULL COMMENT 'Id của quan hệ theo dõi',
  SenderId char(36) NOT NULL COMMENT 'Id người gửi theo dõi',
  ReceiverId char(36) NOT NULL COMMENT 'Id người nhận theo dõi',
  Time datetime NOT NULL COMMENT 'Thời gian bắt đầu theo dõi',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (FollowId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu quan hệ theo dõi giữa các người dùng',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Follow_Time` on table `Follow`
--
ALTER TABLE Follow
ADD INDEX IDX_Follow_Time (Time);

--
-- Create index `UK_Follow_FollowId` on table `Follow`
--
ALTER TABLE Follow
ADD UNIQUE INDEX UK_Follow_FollowId (FollowId);

--
-- Create foreign key
--
ALTER TABLE Follow
ADD CONSTRAINT FK_Follow_ReceiverId FOREIGN KEY (ReceiverId)
REFERENCES User (UserId);

--
-- Create foreign key
--
ALTER TABLE Follow
ADD CONSTRAINT FK_Follow_SenderId FOREIGN KEY (SenderId)
REFERENCES User (UserId);

--
-- Create table `Block`
--
CREATE TABLE Block (
  BlockId char(36) NOT NULL COMMENT 'Id quan hệ chặn nhau',
  SenderId char(36) NOT NULL COMMENT 'Id người chặn',
  ReceiverId char(36) NOT NULL COMMENT 'Id người bị chặn',
  Time datetime NOT NULL COMMENT 'Thời gian chặn',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (BlockId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 819,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu thông tin quan hệ Block giữa các User',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Block_Time` on table `Block`
--
ALTER TABLE Block
ADD INDEX IDX_Block_Time (Time);

--
-- Create index `UK_Block_BlockId` on table `Block`
--
ALTER TABLE Block
ADD UNIQUE INDEX UK_Block_BlockId (BlockId);

--
-- Create foreign key
--
ALTER TABLE Block
ADD CONSTRAINT FK_Block_ReceiverId FOREIGN KEY (ReceiverId)
REFERENCES User (UserId);

--
-- Create foreign key
--
ALTER TABLE Block
ADD CONSTRAINT FK_Block_SenderId FOREIGN KEY (SenderId)
REFERENCES User (UserId);

--
-- Create table `Conversation`
--
CREATE TABLE Conversation (
  ConversationId char(36) NOT NULL,
  ConversationName varchar(255) NOT NULL COMMENT 'Tên cuộc trò chuyện',
  Thumbnail varchar(1023) DEFAULT NULL,
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (ConversationId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 163,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu danh sách cuộc trò chuyện trong hệ thống',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Conversation_ConversationName` on table `Conversation`
--
ALTER TABLE Conversation
ADD INDEX IDX_Conversation_ConversationName (ConversationName);

--
-- Create index `UK_Conversation_ConversationId` on table `Conversation`
--
ALTER TABLE Conversation
ADD UNIQUE INDEX UK_Conversation_ConversationId (ConversationId);

--
-- Create table `UserConversation`
--
CREATE TABLE UserConversation (
  UserConversationId char(36) NOT NULL COMMENT 'Id quan hệ User - Conversation',
  UserId char(36) NOT NULL COMMENT 'Id của người dùng',
  ConversationId char(36) NOT NULL COMMENT 'Id của cuộc trò chuyện',
  Nickname varchar(255) DEFAULT NULL COMMENT 'Biệt danh người tham gia',
  Time datetime NOT NULL COMMENT 'Thời gian người dùng tham gia cuộc trò chuyện',
  LastReadTime datetime DEFAULT NULL COMMENT 'Thời gian lần cuối đọc tin nhắn, null = âm vô cùng',
  LastDeleteTime datetime DEFAULT NULL COMMENT 'Thời gian lần cuối xóa tin nhắn trong cuộc trò chuyện, null = âm vô cùng',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (UserConversationId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Lưu quan hệ người dùng tham gia vào cuộc trò chuyện',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_UserConversation_Time` on table `UserConversation`
--
ALTER TABLE UserConversation
ADD INDEX IDX_UserConversation_Time (Time);

--
-- Create index `UK_UserConversation_UserConversationId` on table `UserConversation`
--
ALTER TABLE UserConversation
ADD UNIQUE INDEX UK_UserConversation_UserConversationId (UserConversationId);

--
-- Create foreign key
--
ALTER TABLE UserConversation
ADD CONSTRAINT FK_UserConversation_ConversationId FOREIGN KEY (ConversationId)
REFERENCES Conversation (ConversationId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE UserConversation
ADD CONSTRAINT FK_UserConversation_UserId FOREIGN KEY (UserId)
REFERENCES User (UserId) ON DELETE CASCADE;

--
-- Create view `ViewUserConversation`
--
CREATE VIEW ViewUserConversation
AS
SELECT
  `u`.`UserConversationId` AS `UserConversationId`,
  `u`.`UserId` AS `UserId`,
  `u`.`ConversationId` AS `ConversationId`,
  `u1`.`Username` AS `Username`,
  `p`.`FullName` AS `FullName`,
  `p`.`Avatar` AS `Avatar`,
  `p`.`Cover` AS `Cover`,
  `u`.`Nickname` AS `Nickname`,
  `u`.`Time` AS `Time`,
  `u`.`LastReadTime` AS `LastReadTime`,
  `u`.`LastDeleteTime` AS `LastDeleteTime`,
  `u`.`CreatedBy` AS `CreatedBy`,
  `u`.`CreatedTime` AS `CreatedTime`,
  `u`.`ModifiedBy` AS `ModifiedBy`,
  `u`.`ModifiedTime` AS `ModifiedTime`
FROM ((`UserConversation` `u`
  JOIN `Profile` `p`
    ON ((`u`.`UserId` = `p`.`UserId`)))
  LEFT JOIN `User` `u1`
    ON ((`u1`.`UserId` = `u`.`UserId`)));

--
-- Create table `Message`
--
CREATE TABLE Message (
  MessageId char(36) NOT NULL COMMENT 'Id của tin nhắn',
  UserConversationId char(36) NOT NULL COMMENT 'Id của phiên người dùng - cuộc hội thoại của tin nhắn',
  Content longtext NOT NULL COMMENT 'Nội dung tin nhắn',
  Time datetime NOT NULL COMMENT 'Thời gian gửi tin nhắn',
  ReplyId char(36) DEFAULT NULL COMMENT 'Id của tin nhắn được reply',
  IsEdited tinyint(1) NOT NULL COMMENT 'Cờ đánh dấu tin nhắn đã được chỉnh sửa hay chưa (true - rồi, false - chưa)',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL,
  PRIMARY KEY (MessageId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 5210,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Danh sách các tin nhắn',
ROW_FORMAT = DYNAMIC;

--
-- Create index `IDX_Message_Time` on table `Message`
--
ALTER TABLE Message
ADD INDEX IDX_Message_Time (Time);

--
-- Create index `UK_Message_MessageId` on table `Message`
--
ALTER TABLE Message
ADD UNIQUE INDEX UK_Message_MessageId (MessageId);

--
-- Create foreign key
--
ALTER TABLE Message
ADD CONSTRAINT FK_Message_ReplyId FOREIGN KEY (ReplyId)
REFERENCES Message (MessageId) ON DELETE SET NULL;

--
-- Create foreign key
--
ALTER TABLE Message
ADD CONSTRAINT FK_Message_UserConversationId FOREIGN KEY (UserConversationId)
REFERENCES UserConversation (UserConversationId);

--
-- Create view `ViewMessage`
--
CREATE VIEW ViewMessage
AS
SELECT
  `m`.`MessageId` AS `MessageId`,
  `m`.`UserConversationId` AS `UserConversationId`,
  `uc`.`UserId` AS `UserId`,
  `uc`.`ConversationId` AS `ConversationId`,
  `uc`.`Nickname` AS `Nickname`,
  `u`.`Username` AS `Username`,
  `p`.`FullName` AS `FullName`,
  `p`.`Avatar` AS `Avatar`,
  `p`.`Cover` AS `Cover`,
  `m`.`Content` AS `Content`,
  `m`.`Time` AS `Time`,
  `m`.`ReplyId` AS `ReplyId`,
  `m`.`IsEdited` AS `IsEdited`,
  `m`.`CreatedBy` AS `CreatedBy`,
  `m`.`CreatedTime` AS `CreatedTime`,
  `m`.`ModifiedBy` AS `ModifiedBy`,
  `m`.`ModifiedTime` AS `ModifiedTime`
FROM (((`Message` `m`
  JOIN `UserConversation` `uc`
    ON ((`m`.`UserConversationId` = `uc`.`UserConversationId`)))
  JOIN `Profile` `p`
    ON ((`uc`.`UserId` = `p`.`UserId`)))
  LEFT JOIN `User` `u`
    ON ((`uc`.`UserId` = `u`.`UserId`)));

--
-- Create table `Category`
--
CREATE TABLE Category (
  CategoryId char(36) NOT NULL COMMENT 'Id của Category',
  CategoryName varchar(255) NOT NULL COMMENT 'Tên của Category',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (CategoryId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 163,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng lưu danh sách phân loại phần tử kiến thức',
ROW_FORMAT = DYNAMIC;

--
-- Create index `UK_Category_CategoryId` on table `Category`
--
ALTER TABLE Category
ADD UNIQUE INDEX UK_Category_CategoryId (CategoryId);

--
-- Create index `UK_Category_CategoryName` on table `Category`
--
ALTER TABLE Category
ADD UNIQUE INDEX UK_Category_CategoryName (CategoryName);

--
-- Create table `KnowledgeCategory`
--
CREATE TABLE KnowledgeCategory (
  KnowledgeCategoryId char(36) NOT NULL COMMENT 'Id của Knowledge - catergory',
  KnowledgeId char(36) NOT NULL COMMENT 'Id của phần tử kiến thức',
  CategoryId char(36) NOT NULL COMMENT 'Id của Category',
  CreatedBy varchar(255) DEFAULT NULL COMMENT 'Người tạo',
  CreatedTime datetime DEFAULT NULL COMMENT 'Thời gian tạo',
  ModifiedBy varchar(255) DEFAULT NULL COMMENT 'Người sửa',
  ModifiedTime datetime DEFAULT NULL COMMENT 'Thời gian sửa',
  PRIMARY KEY (KnowledgeCategoryId)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 491,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Bảng lưu Phần tử kiến thức có những Category nào',
ROW_FORMAT = DYNAMIC;

--
-- Create foreign key
--
ALTER TABLE KnowledgeCategory
ADD CONSTRAINT FK_KnowledgeCategory_CategoryId FOREIGN KEY (CategoryId)
REFERENCES Category (CategoryId) ON DELETE CASCADE;

--
-- Create foreign key
--
ALTER TABLE KnowledgeCategory
ADD CONSTRAINT FK_KnowledgeCategory_KnowledgeId FOREIGN KEY (KnowledgeId)
REFERENCES Knowledge (UserItemId) ON DELETE CASCADE;

--
-- Create view `ViewKnowledgeCategory`
--
CREATE VIEW ViewKnowledgeCategory
AS
SELECT
  `k`.`KnowledgeCategoryId` AS `KnowledgeCategoryId`,
  `k`.`KnowledgeId` AS `KnowledgeId`,
  `k`.`CategoryId` AS `CategoryId`,
  `k`.`CreatedBy` AS `CreatedBy`,
  `k`.`CreatedTime` AS `CreatedTime`,
  `k`.`ModifiedBy` AS `ModifiedBy`,
  `k`.`ModifiedTime` AS `ModifiedTime`,
  `c`.`CategoryName` AS `CategoryName`
FROM (`KnowledgeCategory` `k`
  JOIN `Category` `c`
    ON ((`k`.`CategoryId` = `c`.`CategoryId`)));

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

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