Untitled

-- Liệt kê tất cả người dùng và thông tin tài khoản của họ
SELECT 
    U.USER_ID, 
    U.FIRST_NAME, 
    U.LAST_NAME, 
    U.EMAIL, 
    A.USERNAME, 
    A.ROLE, 
    A.STATUS
FROM USERS U
JOIN ACCOUNTS A ON U.USER_ID = A.USER_ID;

-- Tìm tất cả các khóa học do một người dùng cụ thể tạo ( tìm theo email)
SELECT 
    C.COURSE_NAME, 
    C.PRICE, 
    C.CURRENCY, 
    C.STATUS, 
    C.CREATED_TIME
FROM COURSES C
JOIN USERS U ON C.USER_ID = U.USER_ID
WHERE U.EMAIL = 'example@email.com';

--  Đếm số lượng bài giảng theo từng loại (VIDEO, DOCUMENT, QUIZ)
SELECT 
    TYPE_LECTURE, 
    COUNT(*) AS Total
FROM LECTURES
GROUP BY TYPE_LECTURE;

-- Tìm tất cả các hoạt động của một người dùng cụ thể (tìm theo họ tên)
SELECT 
    A.ACTION, 
    A.TIMESTAMP, 
    A.DETAIL
FROM ACTIVITYLOG A
JOIN USERS U ON A.USER_ID = U.USER_ID
WHERE U.FIRST_NAME = N'Nguyen' AND U.LAST_NAME = N'Van A';

--Thống kê số lượng khóa học theo trạng thái
SELECT 
    STATUS, 
    COUNT(*) AS TOTAL
FROM COURSES
GROUP BY STATUS;

--  Tìm người dùng chưa từng tạo khóa học nào
SELECT 
    U.USER_ID, 
    U.FIRST_NAME, 
    U.LAST_NAME, 
    U.EMAIL
FROM USERS U
LEFT JOIN COURSES C ON U.USER_ID = C.USER_ID
WHERE C.COURSE_ID IS NULL;

-- Tìm top 5 khóa học có nhiều bài giảng nhất
SELECT TOP 5 
    C.COURSE_NAME, 
    COUNT(L.LECTURE_ID) AS TOTAL_LECTURES
FROM COURSES C
JOIN LECTURES L ON C.COURSE_ID = L.COURSE_ID
GROUP BY C.COURSE_NAME
ORDER BY TOTAL_LECTURES DESC;

-- Hàm trả về số lượng khóa học mà người dùng đã tạo (theo USER_ID)
CREATE FUNCTION fn_CountCoursesByUser (
    @UserID UNIQUEIDENTIFIER
)
RETURNS INT
AS
BEGIN
    DECLARE @CourseCount INT;

    SELECT @CourseCount = COUNT(*)
    FROM COURSES
    WHERE USER_ID = @UserID;

    RETURN @CourseCount;
END;

-- Ghi lại hành động của người dùng vào bảng ACTIVITYLOG
CREATE PROCEDURE sp_LogUserAction
    @UserID UNIQUEIDENTIFIER,
    @Action NVARCHAR(100),
    @Detail NVARCHAR(MAX)
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM USERS WHERE USER_ID = @UserID)
    BEGIN
        RAISERROR('Không tìm thấy người dùng.', 16, 1);
        RETURN;
    END

    INSERT INTO ACTIVITYLOG (USER_ID, ACTION, DETAIL)
    VALUES (@UserID, @Action, @Detail);

    PRINT 'Ghi log thành công.';
END;

-- Tìm kiếm khóa học theo từ khóa trong tên hoặc mô tả
CREATE PROCEDURE sp_SearchCoursesByKeyword
    @Keyword NVARCHAR(100)
AS
BEGIN
    SELECT 
        COURSE_ID,
        COURSE_NAME,
        DESCRIPTION,
        STATUS,
        PRICE,
        CREATED_TIME
    FROM COURSES
    WHERE COURSE_NAME LIKE '%' + @Keyword + '%'
       OR DESCRIPTION LIKE '%' + @Keyword + '%';
END;

--Ngăn không cho xóa bài giảng nếu khóa học đang ở trạng thái PUBLISHED
CREATE TRIGGER trg_PreventLectureDeleteIfPublished
ON LECTURES
INSTEAD OF DELETE
AS
BEGIN
    IF EXISTS (
        SELECT 1 
        FROM deleted d
        JOIN COURSES c ON d.COURSE_ID = c.COURSE_ID
        WHERE c.STATUS = 'PUBLISHED'
    )
    BEGIN
        RAISERROR('Không thể xóa bài giảng thuộc khóa học đã được PUBLISHED.', 16, 1);
        RETURN;
    END

    DELETE FROM LECTURES
    WHERE LECTURE_ID IN (SELECT LECTURE_ID FROM deleted);
END;

--Không cho phép hai bài giảng cùng tên trong một khóa học
CREATE TRIGGER trg_PreventDuplicateLectureTitleInCourse
ON LECTURES
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT LECTURE_TITLE, COURSE_ID
        FROM LECTURES
        GROUP BY LECTURE_TITLE, COURSE_ID
        HAVING COUNT(*) > 1
    )
    BEGIN
        RAISERROR('Không thể có hai bài giảng cùng tên trong cùng một khóa học.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;