Untitled

-- Tạo cơ sở dữ liệu
CREATE DATABASE QLKH_QTCSDL;
GO

USE QLKH_QTCSDL;
GO



-- Tạo bảng USERS
CREATE TABLE USERS (
    USER_ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    FIRST_NAME NVARCHAR(10) NOT NULL,
    LAST_NAME NVARCHAR(50) NOT NULL,
    EMAIL VARCHAR(50) NOT NULL UNIQUE,
    HOMETOWN NVARCHAR (255) NOT NULL,
    BIRTH_DATE DATE CHECK (BIRTH_DATE <= GETDATE()) NULL,
    PHONE VARCHAR(11) NOT NULL UNIQUE,
    CREATED_AT DATETIME2 NOT NULL DEFAULT GETDATE(),
    UPDATED_AT DATETIME2 NULL,
    CONSTRAINT CHK_AGE CHECK (DATEDIFF(YEAR, BIRTH_DATE, GETDATE()) >= 18),

);
GO




-- Tạo bảng ACCOUNTS
CREATE TABLE ACCOUNTS (
    ACCOUNT_ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    USER_ID UNIQUEIDENTIFIER NOT NULL UNIQUE,
    USERNAME VARCHAR(50) NOT NULL UNIQUE,
    PASSWORD VARCHAR(255) NOT NULL,
    ROLE VARCHAR (50) NOT NULL,
    STATUS VARCHAR(50) CHECK (STATUS IN ('ACTIVE', 'INACTIVE', 'LOCKED')) NOT NULL,
    LAST_LOGIN DATETIME2 NULL,
    
    CONSTRAINT FK_ACCOUNTS_USERS FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID),
   
);
GO



-- Tạo bảng COURSES
CREATE TABLE COURSES (
    COURSE_ID INT PRIMARY KEY IDENTITY(1,1),
    COURSE_NAME NVARCHAR(100) NOT NULL,
    DESCRIPTION NVARCHAR(MAX) NULL,
    IMAGE VARCHAR(255) NULL,
    PRICE DECIMAL(10,2) DEFAULT 0 CHECK (PRICE >= 0),
    CURRENCY VARCHAR (4) NOT NULL,
    USER_ID UNIQUEIDENTIFIER NULL,
    STATUS VARCHAR(20) CHECK (STATUS IN ('DRAFT', 'PUBLISHED', 'ARCHIVED')),
    CREATED_TIME DATETIME2 DEFAULT CURRENT_TIMESTAMP,
    UPDATED_TIME DATETIME2 NULL,
    CONSTRAINT FK_COURSES_USERS FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID),
    
);
GO

-- Tạo bảng LECTURES
CREATE TABLE LECTURES (
    LECTURE_ID INT PRIMARY KEY IDENTITY(1,1),
    LECTURE_TITLE NVARCHAR(100) NOT NULL,
    DESCRIPTION NVARCHAR(MAX) NULL,
    FILE_PATH VARCHAR(255) NULL,
    TYPE_LECTURE VARCHAR(20) CHECK (TYPE_LECTURE IN ('VIDEO', 'DOCUMENT', 'QUIZ')) NOT NULL,
    COURSE_ID INT NULL,
    
    
    CONSTRAINT FK_LECTURES_COURSES FOREIGN KEY (COURSE_ID) REFERENCES COURSES(COURSE_ID),
   
);
GO

-- Tạo bảng ACTIVITYLOG
CREATE TABLE ACTIVITYLOG (
    LOG_ID INT PRIMARY KEY IDENTITY(1,1),
    USER_ID UNIQUEIDENTIFIER NULL,
    ACTION NVARCHAR(100) NOT NULL,
    TIMESTAMP DATETIME2 DEFAULT CURRENT_TIMESTAMP,
    DETAIL NVARCHAR(MAX) NULL,
    CONSTRAINT FK_ACTIVITYLOG_USERS FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID)
);
GO

-- Tạo trigger để tự động cập nhật UPDATED_AT khi cập nhật dữ liệu trong bảng USERS
CREATE TRIGGER trg_Users_Update
ON USERS
AFTER UPDATE
AS
BEGIN
    UPDATE USERS
    SET UPDATED_AT = GETDATE()
    FROM USERS u
    INNER JOIN inserted i ON u.USER_ID = i.USER_ID;
END;
GO

-- Tạo trigger để tự động cập nhật UPDATED_AT khi cập nhật dữ liệu trong bảng COURSES
CREATE TRIGGER trg_Courses_Update
ON COURSES
AFTER UPDATE
AS
BEGIN
    UPDATE COURSES
    SET UPDATED_TIME = GETDATE()
    FROM COURSES c
    INNER JOIN inserted i ON c.COURSE_ID = i.COURSE_ID;
END;
GO