fewshot.py

examples = [
{"input": "Tình trạng cửa hàng nào là Open và Clean vào lúc 09:00 AM ngày 2024-10-10?",
     "query": """SELECT StoreId
FROM StoreStatus
WHERE Status = 'Open'
  AND CleanlinessStatus = 'Clean'
  AND OpeningTime <= '2024-10-10 09:00:00'"""},
{"input": "Có bao nhiêu ca làm việc đã được ghi nhận trong tháng 10 năm 2024?",
     "query": """SELECT COUNT(*) AS TotalShifts
FROM ShiftManagement
WHERE YYYYMMDD LIKE '202410%';"""},
{"input": "Ai là người lớn tuổi nhất công ty? Liệt kê thông tin cụ thể của người đó?",
     "query": """SELECT e.FullName,
       strftime('%Y', 'now') - substr(ed.Birthday, 1, 4) AS Age,
       ed.Wallet
FROM EmployeeDetail ed join Employee e on ed.Wallet = e.Wallet
ORDER BY Age DESC
LIMIT 1;"""},
{"input": "Có bao nhiêu nhân viên vừa xin nghỉ phép và vừa được thanh toán trong tháng trước?",
     "query": """SELECT COUNT(DISTINCT Employee) AS NumberOfEmployees
FROM ShiftStatus
WHERE IsLeavePermission = 1
  AND IsPaidLeave = 1
  AND CAST(substr(YYYYMMDD, 1, 6) AS INTEGER) = CAST(strftime('%Y%m', 'now', '-1 month') AS INTEGER);"""},
    {"input": "Những nhân viên nào rời văn phòng sớm nhiều nhất?",
     "query": """SELECT
    E.FullName,
    COUNT(SS.IsComebackSoon) AS EarlyLeaveCount
FROM
    ShiftStatus SS
JOIN
    Employee E ON SS.Employee = E.Wallet
WHERE
    SS.IsComebackSoon = 1
GROUP BY
    E.FullName
ORDER BY
    EarlyLeaveCount DESC"""},
    {"input": "Lấy danh sách tất cả các nhân viên có trạng thái 'PRESENT' trong ca làm việc hôm nay?", "query": """SELECT Employee, ShiftStatus
FROM ShiftManagement
WHERE CAST(YYYYMMDD AS INTEGER) = CAST(STRFTIME('%Y%m%d', 'now') AS INTEGER)
  AND ShiftStatus = 'PRESENT';
"""},
        {"input": "Lấy danh sách tên tất cả các nhân viên có trạng thái 'PRESENT' trong ca làm việc hôm nay?", "query": """SELECT e.FullName, sm.ShiftStatus
FROM ShiftManagement sm
JOIN Employee e ON sm.Employee = e.Wallet
WHERE CAST(substr(sm.YYYYMMDD, 1, 8) AS INTEGER) = CAST(strftime('%Y%m%d', 'now') AS INTEGER)
  AND sm.ShiftStatus = 'PRESENT';
;
"""},
    {
        "input": "Những nhân viên nào có phê duyệt đi trễ trong 7 ngày qua?",
        "query": """SELECT Employee, LatePermissionStatus, LatePermissionApproval
FROM ShiftManagement
WHERE LatePermissionStatus = 'PERMISSION'
  AND CAST(YYYYMMDD AS INTEGER) >= CAST(STRFTIME('%Y%m%d', DATE('now', '-7 days')) AS INTEGER);
""",
    },
    {
        "input": "Lấy tất cả nhân viên đã có check-in và check-out trong ngày hôm nay?",
        "query": """SELECT Employee, CheckInTime, CheckOutTime
FROM ShiftManagement
WHERE CAST(YYYYMMDD AS INTEGER) = CAST(strftime('%Y%m%d', 'now') AS INTEGER)
  AND CheckInTime IS NOT NULL
  AND CheckOutTime IS NOT NULL;
""",
    },
    {
        "input": "Lấy danh sách nhân viên đã có nghỉ phép có lương trong tháng này?",
        "query": """SELECT Employee, IsPaidLeave
FROM ShiftStatus
WHERE IsPaidLeave = 1
  AND YYYYMMDD BETWEEN STRFTIME('%Y%m%d', DATE('now', 'start of month'))
                  AND STRFTIME('%Y%m%d', DATE('now', 'start of month', '+1 month', '-1 day'));
"""
    },
      {
        "input": "Hiện tại có bao nhiêu nhân viên nam và nữ?",
        "query": """SELECT Gender, COUNT(*) AS NumberOfEmployees
FROM EmployeeDetail
GROUP BY Gender;

"""
    },
      {
        "input": "Số điện thoại của nhân viên tên Employee 2?",
        "query": """SELECT ed.PhoneNumber
FROM Employee e
JOIN EmployeeDetail ed ON e.Wallet = ed.Wallet
WHERE e.FullName = 'Employee 2';
"""
    },
    {
        "input": "Lấy danh sách tất cả các nhân viên có trạng thái 'PRESENT' trong ca làm việc hôm nay?",
        "query": """SELECT Employee, ShiftStatus
FROM ShiftManagement
WHERE CAST(YYYYMMDD AS INTEGER) = CAST(STRFTIME('%Y%m%d', 'now') AS INTEGER)
  AND ShiftStatus = 'PRESENT';
"""
    },
    {
        "input": "Lấy danh sách tên tất cả các nhân viên có trạng thái 'PRESENT' trong ca làm việc hôm nay?",
        "query": """SELECT e.FullName, sm.ShiftStatus
FROM ShiftManagement sm
JOIN Employee e ON sm.Employee = e.Wallet
WHERE sm.YYYYMMDD = STRFTIME('%Y%m%d', 'now')
  AND sm.ShiftStatus = 'PRESENT';
"""
    },
    {
        "input": "Lấy danh sách nhân viên đi trễ và có phép trong ngày hôm nay?",
        "query": """SELECT e.FullName, ss.IsLate, ss.IsLatePermission
FROM ShiftStatus ss
JOIN Employee e ON ss.Employee = e.Wallet
WHERE ss.YYYYMMDD = STRFTIME('%Y%m%d', 'now')
  AND ss.IsLate = 1
  AND ss.IsLatePermission = 1;
"""
    },
    {
        "input": "Lấy thông tin nhân viên và trạng thái đi muộn không phép trong tuần này?",
        "query": """SELECT e.FullName, sm.ShiftStatus, sm.LatePermissionStatus
        FROM ShiftManagement sm
        JOIN Employee e ON sm.Employee = e.Wallet
        WHERE sm.YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', 'weekday 0', '-6 days') AND STRFTIME('%Y%m%d', 'now', 'weekday 0')
        AND sm.LatePermissionStatus = 'NO_PERMISSION';"""
    },
    {
        "input": "Lấy danh sách nhân viên đã điểm danh làm thêm giờ hôm nay?",
        "query": """SELECT e.FullName, a.Type, a.IsOvertime
FROM Attendance a
JOIN Employee e ON a.Employee = e.Wallet
WHERE CAST(a.YYYYMMDD AS INTEGER) = CAST(STRFTIME('%Y%m%d', 'now') AS INTEGER)
  AND a.IsOvertime = 1;
"""
    },
    {
        "input": "Lấy danh sách nhân viên đã nghỉ phép hôm nay?",
        "query": """SELECT e.FullName, ss.IsLeavePermission
        FROM ShiftStatus ss
        JOIN Employee e ON ss.Employee = e.Wallet
        WHERE ss.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND ss.IsLeavePermission = TRUE;"""
    },
    {
        "input": "Lấy danh sách nhân viên nghỉ phép có lương trong tuần này?",
        "query": """SELECT e.FullName, ss.IsPaidLeave
        FROM ShiftStatus ss
        JOIN Employee e ON ss.Employee = e.Wallet
        WHERE ss.YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', 'weekday 0', '-6 days') AND STRFTIME('%Y%m%d', 'now', 'weekday 0')
        AND ss.IsPaidLeave = TRUE;"""
    },
    {
        "input": "Lấy thông tin nhân viên check-in hộ người khác hôm nay?",
        "query": """SELECT e.FullName, a.Type
        FROM Attendance a
        JOIN Employee e ON a.Employee = e.Wallet
        WHERE a.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND a.Type = 'CHECK_IN_ON_BEHALF';"""
    },
    {
        "input": "Lấy danh sách các ca làm việc của nhân viên trong ngày hôm nay?",
        "query": """SELECT e.FullName, sm.ShiftId, sm.CheckInTime, sm.CheckOutTime, sm.ShiftStatus
        FROM ShiftManagement sm
        JOIN Employee e ON sm.Employee = e.Wallet
        WHERE sm.YYYYMMDD = STRFTIME('%Y%m%d', 'now');"""
    },
    {
        "input": "Lấy danh sách nhân viên đã check-in hôm nay nhưng chưa check-out?",
        "query": """SELECT e.FullName, sm.CheckInTime, sm.CheckOutTime
        FROM ShiftManagement sm
        JOIN Employee e ON sm.Employee = e.Wallet
        WHERE sm.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND sm.CheckInTime IS NOT NULL AND sm.CheckOutTime IS NULL;"""
    },
    {
        "input": "Lấy thông tin nhân viên có làm thêm giờ hôm nay và trạng thái phê duyệt làm thêm giờ?",
        "query": """SELECT e.FullName, a.IsOvertime, a.OvertimeStatus
        FROM Attendance a
        JOIN Employee e ON a.Employee = e.Wallet
        WHERE a.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND a.IsOvertime = TRUE;"""
    },
    {
        "input": "Lấy danh sách nhân viên có mặt tại địa điểm làm việc cụ thể hôm nay?",
        "query": """SELECT e.FullName, a.WorkPlaceAttendance, w.LatLon
        FROM Attendance a
        JOIN Employee e ON a.Employee = e.Wallet
        JOIN WorkPlaceAttendance w ON a.WorkPlaceAttendance = w.WorkPlaceId
        WHERE a.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND a.Type IN ('CHECK_IN', 'CHECK_OUT');"""
    },
    {
        "input": "Lấy danh sách nhân viên có check-in tại một địa điểm làm việc cụ thể?",
        "query": """SELECT e.FullName, a.Time, w.LatLon
        FROM Attendance a
        JOIN Employee e ON a.Employee = e.Wallet
        JOIN WorkPlaceAttendance w ON a.WorkPlaceAttendance = w.WorkPlaceId
        WHERE w.LatLon = 'TOA_DO_CU_THE' AND a.Type = 'CHECK_IN';"""
    },
    {
        "input": "Lấy danh sách nhân viên có trạng thái làm thêm giờ chưa được đọc?",
        "query": """SELECT e.FullName, a.OvertimeStatus
        FROM Attendance a
        JOIN Employee e ON a.Employee = e.Wallet
        WHERE a.OvertimeStatus = 'UN_READ';"""
    },
    {
        "input": "Lấy thông tin nhân viên có đi sớm về sớm hôm nay?",
        "query": """SELECT e.FullName, sm.ComeBackSoonPermissionStatus
        FROM ShiftManagement sm
        JOIN Employee e ON sm.Employee = e.Wallet
        WHERE sm.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND sm.ComeBackSoonPermissionStatus = 'PERMISSION';"""
    },
    {
        "input": "Lấy danh sách nhân viên không đi làm trong tuần này?",
        "query": """SELECT e.FullName, ss.IsPresent
        FROM ShiftStatus ss
        JOIN Employee e ON ss.Employee = e.Wallet
        WHERE ss.YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', 'weekday 0', '-6 days') AND STRFTIME('%Y%m%d', 'now', 'weekday 0') AND ss.IsPresent = FALSE;"""
    },
    {
        "input": "Lấy danh sách nhân viên đi trễ không phép và vắng mặt hôm nay?",
        "query": """SELECT e.FullName, ss.IsLate, ss.IsLatePermission, ss.IsPresent
        FROM ShiftStatus ss
        JOIN Employee e ON ss.Employee = e.Wallet
        WHERE ss.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND ss.IsLate = TRUE AND ss.IsLatePermission = FALSE AND ss.IsPresent = FALSE;"""
    },
    {
        "input": "Lấy thông tin các ca làm việc mà nhân viên đã đi trễ hôm nay nhưng có phép?",
        "query": """SELECT e.FullName, sm.LatePermissionApproval, sm.LatePermissionStatus
        FROM ShiftManagement sm
        JOIN Employee e ON sm.Employee = e.Wallet
        WHERE sm.YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND sm.LatePermissionStatus = 'PERMISSION';"""
    },
    {
        "input": "Lấy danh sách nhân viên có ngày sinh trong tháng này?",
        "query": """SELECT e.FullName, ed.Birthday
        FROM EmployeeDetail ed
        JOIN Employee e ON ed.Wallet = e.Wallet
        WHERE STRFTIME('%m', ed.Birthday, 'unixepoch') = STRFTIME('%m', 'now');"""
    },
    {
        "input": "Lấy thông tin số người phụ thuộc của nhân viên?",
        "query": """SELECT e.FullName, ed.NumberOfDependents
        FROM EmployeeDetail ed
        JOIN Employee e ON ed.Wallet = e.Wallet;"""
    },
    {
        "input": "Lấy thông tin nhân viên có thu nhập chịu thuế và thuế thu nhập cá nhân?",
        "query": """SELECT e.FullName, ed.TaxableSalary, ed.PersonalIncomeTax
        FROM EmployeeDetail ed
        JOIN Employee e ON ed.Wallet = e.Wallet
        WHERE ed.TaxableSalary > 0;"""
    },
    {
        "input": "Lấy danh sách nhân viên chưa có sơ yếu lý lịch?",
        "query": """SELECT e.FullName, ed.HasResume
        FROM EmployeeDetail ed
        JOIN Employee e ON ed.Wallet = e.Wallet
        WHERE ed.HasResume = FALSE;"""
    },
    {
        "input": "Lấy danh sách nhân viên có giấy khám sức khỏe?",
        "query": """SELECT e.FullName, ed.HasHealthCertification
        FROM EmployeeDetail ed
        JOIN Employee e ON ed.Wallet = e.Wallet
        WHERE ed.HasHealthCertification = TRUE;"""
    },
        {
        "input": "Lấy tổng số nhân viên có trạng thái 'PRESENT' trong ca làm việc hôm nay?",
        "query": """SELECT COUNT(*) AS TotalPresentEmployees
        FROM ShiftManagement
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND ShiftStatus = 'PRESENT';"""
    },
    {
        "input": "Lấy tổng số nhân viên đi trễ trong ngày hôm nay?",
        "query": """SELECT COUNT(*) AS TotalLateEmployees
        FROM ShiftStatus
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND IsLate = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên đã nghỉ phép hôm nay?",
        "query": """SELECT COUNT(*) AS TotalLeaveEmployees
        FROM ShiftStatus
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND IsLeavePermission = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên đã làm thêm giờ hôm nay?",
        "query": """SELECT COUNT(*) AS TotalOvertimeEmployees
        FROM Attendance
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND IsOvertime = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên có làm thêm giờ mà trạng thái chưa được đọc?",
        "query": """SELECT COUNT(*) AS TotalUnreadOvertimeEmployees
        FROM Attendance
        WHERE OvertimeStatus = 'UN_READ';"""
    },
    {
        "input": "Lấy tổng số nhân viên có mặt tại một địa điểm làm việc cụ thể hôm nay?",
        "query": """SELECT COUNT(*) AS TotalPresentAtLocation
        FROM Attendance
        JOIN WorkPlaceAttendance ON Attendance.WorkPlaceAttendance = WorkPlaceAttendance.WorkPlaceId
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND Type IN ('CHECK_IN', 'CHECK_OUT') AND WorkPlaceAttendance.LatLon = 'TOA_DO_CU_THE';"""
    },
    {
        "input": "Lấy tổng số nhân viên đi sớm về sớm có phép hôm nay?",
        "query": """SELECT COUNT(*) AS TotalComeBackSoonWithPermission
        FROM ShiftManagement
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND ComeBackSoonPermissionStatus = 'PERMISSION';"""
    },
    {
        "input": "Lấy tổng số nhân viên đi làm trong tuần này?",
        "query": """SELECT COUNT(*) AS TotalEmployeesPresent
        FROM ShiftStatus
        WHERE YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', 'weekday 0', '-6 days') AND STRFTIME('%Y%m%d', 'now', 'weekday 0') AND IsPresent = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên nghỉ phép có lương trong tuần này?",
        "query": """SELECT COUNT(*) AS TotalPaidLeaveEmployees
        FROM ShiftStatus
        WHERE YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', 'weekday 0', '-6 days') AND STRFTIME('%Y%m%d', 'now', 'weekday 0') AND IsPaidLeave = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên đi làm không phép trong tuần này?",
        "query": """SELECT COUNT(*) AS TotalAbsentWithoutPermission
        FROM ShiftStatus
        WHERE YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', 'weekday 0', '-6 days') AND STRFTIME('%Y%m%d', 'now', 'weekday 0') AND IsPresent = FALSE AND IsLeavePermission = FALSE;"""
    },
    {
        "input": "Lấy tổng số nhân viên đi trễ không phép hôm nay?",
        "query": """SELECT COUNT(*) AS TotalLateWithoutPermission
        FROM ShiftStatus
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND IsLate = TRUE AND IsLatePermission = FALSE;"""
    },
    {
        "input": "Lấy tổng số nhân viên chưa có sơ yếu lý lịch?",
        "query": """SELECT COUNT(*) AS TotalWithoutResume
        FROM EmployeeDetail
        WHERE HasResume = FALSE;"""
    },
    {
        "input": "Lấy tổng số nhân viên có giấy khám sức khỏe?",
        "query": """SELECT COUNT(*) AS TotalWithHealthCertification
        FROM EmployeeDetail
        WHERE HasHealthCertification = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên có bằng tốt nghiệp?",
        "query": """SELECT COUNT(*) AS TotalWithGraduationDegree
        FROM EmployeeDetail
        WHERE HasGraduationDegree = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên có mã bảo hiểm xã hội?",
        "query": """SELECT COUNT(*) AS TotalWithSocialInsuranceId
        FROM EmployeeDetail
        WHERE SocialInsuranceId IS NOT NULL AND SocialInsuranceId <> '';"""
    },
    {
        "input": "Lấy tổng số nhân viên có mã số thuế?",
        "query": """SELECT COUNT(*) AS TotalWithTaxId
        FROM EmployeeDetail
        WHERE TaxId IS NOT NULL AND TaxId <> '';"""
    },
        {
        "input": "Lấy tổng số nhân viên đã điểm danh check-in hôm nay?",
        "query": """SELECT COUNT(*) AS TotalCheckIns
        FROM Attendance
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND Type = 'CHECK_IN';"""
    },
    {
        "input": "Lấy tổng số nhân viên đã điểm danh check-out hôm nay?",
        "query": """SELECT COUNT(*) AS TotalCheckOuts
        FROM Attendance
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND Type = 'CHECK_OUT';"""
    },
    {
        "input": "Lấy tổng số nhân viên đã check-in hộ người khác hôm nay?",
        "query": """SELECT COUNT(*) AS TotalCheckInOnBehalf
        FROM Attendance
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND Type = 'CHECK_IN_ON_BEHALF';"""
    },
    {
        "input": "Lấy tổng số nhân viên đã check-out hộ người khác hôm nay?",
        "query": """SELECT COUNT(*) AS TotalCheckOutOnBehalf
        FROM Attendance
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND Type = 'CHECK_OUT_ON_BEHALF';"""
    },
    {
        "input": "Lấy tổng số nhân viên có số người phụ thuộc lớn hơn 2?",
        "query": """SELECT COUNT(*) AS TotalEmployeesWithMoreThanTwoDependents
        FROM EmployeeDetail
        WHERE NumberOfDependents > 2;"""
    },
    {
        "input": "Lấy tổng số nhân viên có hợp đồng lao động loại dài hạn?",
        "query": """SELECT COUNT(*) AS TotalLongTermContractEmployees
        FROM EmployeeDetail
        WHERE TypeOfContractId = (SELECT TypeOfContractId FROM ContractType WHERE Name = 'Long Term');"""
    },
    {
        "input": "Lấy tổng số nhân viên có hợp đồng lao động loại ngắn hạn?",
        "query": """SELECT COUNT(*) AS TotalShortTermContractEmployees
        FROM EmployeeDetail
        WHERE TypeOfContractId = (SELECT TypeOfContractId FROM ContractType WHERE Name = 'Short Term');"""
    },
    {
        "input": "Lấy tổng số nhân viên có hợp đồng part-time?",
        "query": """SELECT COUNT(*) AS TotalPartTimeEmployees
        FROM EmployeeDetail
        WHERE TypeOfWorkId = (SELECT TypeOfWorkId FROM WorkType WHERE Name = 'Part-Time');"""
    },
    {
        "input": "Lấy tổng số nhân viên đã nghỉ phép không lương trong tháng này?",
        "query": """SELECT COUNT(*) AS TotalUnpaidLeaveEmployees
        FROM ShiftStatus
        WHERE STRFTIME('%m', YYYYMMDD) = STRFTIME('%m', 'now') AND IsPaidLeave = FALSE AND IsLeavePermission = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên đã nghỉ phép có lương trong tháng này?",
        "query": """SELECT COUNT(*) AS TotalPaidLeaveEmployees
        FROM ShiftStatus
        WHERE STRFTIME('%m', YYYYMMDD) = STRFTIME('%m', 'now') AND IsPaidLeave = TRUE AND IsLeavePermission = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên đã có giấy khai sinh?",
        "query": """SELECT COUNT(*) AS TotalWithBirthCertificate
        FROM EmployeeDetail
        WHERE HasBirthCertificate = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên có chứng minh nhân dân hoặc căn cước công dân?",
        "query": """SELECT COUNT(*) AS TotalWithCitizenIdentification
        FROM EmployeeDetail
        WHERE HasCitizenIdentification = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên có sổ hộ khẩu?",
        "query": """SELECT COUNT(*) AS TotalWithHouseRegistrationBook
        FROM EmployeeDetail
        WHERE HasHouseRegistrationBook = TRUE;"""
    },
    {
        "input": "Lấy tổng số nhân viên có thu nhập chịu thuế trên 20 triệu đồng?",
        "query": """SELECT COUNT(*) AS TotalHighTaxableSalary
        FROM EmployeeDetail
        WHERE TaxableSalary > 20000000;"""
    },
    {
        "input": "Lấy tổng số nhân viên có thu nhập chịu thuế dưới 10 triệu đồng?",
        "query": """SELECT COUNT(*) AS TotalLowTaxableSalary
        FROM EmployeeDetail
        WHERE TaxableSalary < 10000000;"""
    },
    {
        "input": "Lấy tổng số nhân viên có thuế thu nhập cá nhân trên 5 triệu đồng?",
        "query": """SELECT COUNT(*) AS TotalHighPersonalIncomeTax
        FROM EmployeeDetail
        WHERE PersonalIncomeTax > 5000000;"""
    },
    {
        "input": "Lấy tổng số nhân viên đã điểm danh làm thêm giờ hôm nay và được phê duyệt?",
        "query": """SELECT COUNT(*) AS TotalApprovedOvertimeEmployees
        FROM Attendance
        WHERE YYYYMMDD = STRFTIME('%Y%m%d', 'now') AND IsOvertime = TRUE AND OvertimeStatus = 'APPROVAL';"""
    },
      {
        "input": "Nhân viên nghỉ nhiều nhất trong 7 ngày qua?",
        "query": """SELECT e.FullName, COUNT(ss.Employee) AS TotalLeaves
FROM ShiftStatus ss
JOIN Employee e ON ss.Employee = e.Wallet
WHERE ss.YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', '-6 days') AND STRFTIME('%Y%m%d', 'now')
  AND ss.IsLeavePermission = TRUE
GROUP BY ss.Employee
ORDER BY TotalLeaves DESC
LIMIT 1;
"""
    },
    {
        "input": "Nhân viên nghỉ nhiều nhất trong 1 tháng qua?",
        "query": """SELECT e.FullName, COUNT(*) AS TotalDaysOff
FROM ShiftStatus ss
JOIN Employee e ON ss.Employee = e.Wallet
WHERE ss.IsLeavePermission = TRUE
  AND ss.YYYYMMDD BETWEEN STRFTIME('%Y%m%d', 'now', '-1 month') AND STRFTIME('%Y%m%d', 'now')
GROUP BY e.FullName
ORDER BY TotalDaysOff DESC
LIMIT 1;
"""
    },
        {
        "input": "Có bao nhiêu khách hàng nam/nữ ghé thăm cửa hàng hôm nay?",
        "query": """SELECT Gender, COUNT(*) AS CustomerCount
FROM CustomerAtCheckInOut
JOIN CheckInOut ON CustomerAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
WHERE DATE(CheckInOut.CheckTime) = DATE('now')  -- Ngày hôm nay
GROUP BY Gender;
"""
    },
{
        "input": "Có bao nhiêu khách hàng nam/nữ ghé thăm cửa hàng 2 ngày gần đây?",
        "query": """SELECT Gender, COUNT(*) AS CustomerCount
FROM CustomerAtCheckInOut
JOIN CheckInOut ON CustomerAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
WHERE DATE(CheckInOut.CheckTime) >= DATE('now', '-2 days')  -- 2 ngày gần đây
GROUP BY Gender;
"""
    },
    {
        "input": "Có sự khác biệt nào về cảm xúc của khách hàng giữa các nhóm tuổi không?",
        "query": """SELECT
    CASE
        WHEN Age BETWEEN 18 AND 30 THEN '18-30'
        WHEN Age BETWEEN 31 AND 45 THEN '31-45'
        WHEN Age BETWEEN 46 AND 60 THEN '46-60'
        ELSE '60+'
    END AS AgeGroup,
    Emotion,
    COUNT(*) AS EmotionCount
FROM CustomerAtCheckInOut
GROUP BY AgeGroup, Emotion
ORDER BY AgeGroup, Emotion;
"""
    },
        {
        "input": "Bao nhiêu khách hàng cảm thấy khó chịu hôm nay?",
        "query": """SELECT COUNT(*) AS UncomfortableCustomerCount
FROM CustomerAtCheckInOut
JOIN CheckInOut ON CustomerAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
WHERE DATE(CheckInOut.CheckTime) = DATE('now')  -- Ngày hôm nay
AND Emotion IN ('Angry', 'Sad', 'Disgust', 'Fear');
"""
    },
{
        "input": "Lý do chính khiến khách hàng bỏ về là gì?",
        "query": """SELECT CustomerReactionReason, COUNT(*) AS ReasonCount
FROM CustomerAtCheckInOut
GROUP BY CustomerReactionReason
ORDER BY ReasonCount DESC
LIMIT 1;
"""
    },
{
        "input": "Thời điểm nào trong ngày khách hàng ghé thăm nhiều nhất?",
        "query": """SELECT STRFTIME('%H', CheckTime) AS Hour, COUNT(*) AS CustomerCount
FROM CheckInOut
GROUP BY STRFTIME('%H', CheckTime)
ORDER BY CustomerCount DESC
LIMIT 1;
"""
    },
{
        "input": "Tổng số khách hàng trong cửa hàng hiện tại (hôm trước/ theo khung giờ) là bao nhiêu?",
        "query": """SELECT
    'Khách hàng hiện tại' AS Thông_số,
    (SELECT NumberofCustomer
     FROM CheckInOut
     WHERE CheckTime = (SELECT MAX(CheckTime) FROM CheckInOut)) AS Giá_trị
UNION ALL
SELECT
    'Khách hàng ngày hôm qua' AS Thông_số,
    (SELECT SUM(NumberofCustomer)
     FROM CheckInOut
     WHERE DATE(CheckTime) = DATE('now', '-1 day')) AS Giá_trị
UNION ALL
SELECT
    'Khách hàng hôm nay' AS Thông_số,
    (SELECT SUM(NumberofCustomer)
     FROM CheckInOut
     WHERE DATE(CheckTime) = DATE('now')) AS Giá_trị
"""
    },
{
        "input": "Tỉ lệ giới tính của khách hàng (nam/nữ) trong ngày hôm nay?",
        "query": """SELECT
    Gender,
    COUNT(*) AS GenderCount
FROM CustomerAtCheckInOut c
JOIN CheckInOut cio ON c.CheckInOutId = cio.CheckInOutId
WHERE DATE(cio.CheckTime) = DATE('now')  -- Filters records from today's date
GROUP BY Gender;

"""
    },
{
        "input": "Nhóm tuổi nào chiếm đa số trong cửa hàng hôm nay?",
        "query": """WITH TodayCheckInOut AS (
    SELECT CheckInOutId
    FROM CheckInOut
    WHERE DATE(CheckTime) = DATE('now')  -- Lọc các bản ghi của hôm nay
),
AgeGroups AS (
    SELECT
        CASE
            WHEN Age BETWEEN 18 AND 30 THEN '18-30'
            WHEN Age BETWEEN 31 AND 45 THEN '31-45'
            WHEN Age BETWEEN 46 AND 60 THEN '46-60'
            ELSE '60+'
        END AS AgeGroup,
        COUNT(*) AS Count
    FROM CustomerAtCheckInOut
    WHERE CheckInOutId IN (SELECT CheckInOutId FROM TodayCheckInOut)  -- Chỉ lấy các khách hàng của hôm nay
    GROUP BY AgeGroup
)
SELECT AgeGroup, Count
FROM AgeGroups
ORDER BY Count DESC
"""
    },
{
        "input": "Tỉ lệ giới tính của khách hàng (nam/nữ) trong ngày hôm nay?",
        "query": """SELECT
    Gender,
    COUNT(*) AS GenderCount
FROM CustomerAtCheckInOut c
JOIN CheckInOut cio ON c.CheckInOutId = cio.CheckInOutId
WHERE DATE(cio.CheckTime) = DATE('now')  -- Filters records from today's date
GROUP BY Gender;

"""
    },
{
        "input": "Có bao nhiêu khách hàng thuộc nhóm Gen Z trong ngày hôm nay?",
        "query": """SELECT COUNT(*) AS GenZCustomerCount
FROM CustomerAtCheckInOut c
JOIN CheckInOut ci ON c.CheckInOutId = ci.CheckInOutId
WHERE (strftime('%Y', 'now') - c.Age) BETWEEN 1997 AND 2012
AND DATE(ci.CheckTime) = DATE('now');
"""
    },
{
        "input": "Tỉ lệ khách hàng có độ tuổi trên 50 trong cửa hàng hôm nay là bao nhiêu?",
        "query": """WITH CustomersToday AS (
    SELECT
        CustomerId,
        Age,
        CheckInOut.CheckTime
    FROM
        CustomerAtCheckInOut
    INNER JOIN
        CheckInOut ON CustomerAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
    WHERE
        DATE(CheckInOut.CheckTime) = DATE('now')  -- Adjust 'now' if the date is stored in UTC or needs conversion
),
CustomersOver50 AS (
    SELECT
        COUNT(*) AS Over50Count
    FROM
        CustomersToday
    WHERE
        Age > 50
),
TotalCustomersToday AS (
    SELECT
        COUNT(*) AS TotalCustomerCount
    FROM
        CustomersToday
)
SELECT
    (CAST(Over50Count AS FLOAT) / TotalCustomerCount) * 100 AS PercentageOver50
FROM
    CustomersOver50, TotalCustomersToday;
"""
},
{
        "input": "Có bao nhiêu khách hàng thuộc nhóm Gen Z trong ngày hôm nay?",
        "query": """SELECT COUNT(*) AS GenZCustomerCount
FROM CustomerAtCheckInOut c
JOIN CheckInOut ci ON c.CheckInOutId = ci.CheckInOutId
WHERE (strftime('%Y', 'now') - c.Age) BETWEEN 1997 AND 2012
AND DATE(ci.CheckTime) = DATE('now');
"""
    },
{
        "input": "Phân bố sắc tộc của khách hàng là như thế nào?",
        "query": """SELECT
    Ethnicity,
    COUNT(CustomerId) AS CustomerCount,
    ROUND(COUNT(CustomerId) * 100.0 / (SELECT COUNT(*) FROM CustomerAtCheckInOut), 2) AS Percentage
FROM
    CustomerAtCheckInOut
GROUP BY
    Ethnicity;
"""
},
{
        "input": "Khách hàng trong cửa hàng hôm nay mặc phong cách gì?",
        "query": """SELECT CACIO.ClothingStyle, COUNT(CACIO.CustomerId) AS NumberOfCustomers
FROM CustomerAtCheckInOut CACIO
JOIN CheckInOut CIO ON CACIO.CheckInOutId = CIO.CheckInOutId
WHERE DATE(CIO.CheckTime) = DATE('now')  -- Filters records for today
GROUP BY CACIO.ClothingStyle;
"""
},
{
        "input": "Có bao nhiêu khách hàng có ngoại hình thừa cân?",
        "query": """SELECT COUNT(*) AS TotalCustomers
FROM CustomerAtCheckInOut
WHERE Appearance IN ('Overweight');
"""
},
{
        "input": "Có bao nhiêu khách hàng có ngoại hình thiếu cân?",
        "query": """SELECT COUNT(*) AS TotalCustomers
FROM CustomerAtCheckInOut
WHERE Appearance IN ('Underweight');
"""
},
    {
        "input": "Có bao nhiêu khách hàng có ngoại hình thiếu cân và thừa cân?",
        "query": """SELECT COUNT(*) AS TotalCustomers
FROM CustomerAtCheckInOut
WHERE Appearance IN ('Underweight', 'Overweight');
"""
},
{
        "input": "Phong cách ăn mặc phổ biến của khách hàng trong tuần qua là gì?",
        "query": """SELECT
    ClothingStyle,
    COUNT(ClothingStyle) AS StyleCount
FROM
    CustomerAtCheckInOut CAC
JOIN
    CheckInOut CIO ON CAC.CheckInOutId = CIO.CheckInOutId
WHERE
    CIO.CheckTime >= DATETIME('now', '-7 days')  -- Filter for the last 7 days
GROUP BY
    ClothingStyle
ORDER BY
    StyleCount DESC
LIMIT 1;  -- Return the most popular clothing style
"""
},
{
        "input": "Số lượng khách hàng quốc tế ghé thăm cửa hàng hôm nay là bao nhiêu?",
        "query": """SELECT COUNT(*) AS InternationalCustomers
FROM CustomerAtCheckInOut C
JOIN CheckInOut CI ON C.CheckInOutId = CI.CheckInOutId
WHERE C.Ethnicity IN ('White', 'Black')  -- Assuming these represent international customers
AND DATE(CI.CheckTime) = DATE('now');  -- Checking visits made today
"""
},
{
        "input": "Bao nhiêu khách hàng đến quán theo nhóm gia đình trong ngày hôm nay?",
        "query": """SELECT COUNT(*) AS FamilyGroupCustomerCount
FROM CustomerAtCheckInOut ca
JOIN CheckInOut ci ON ca.CheckInOutId = ci.CheckInOutId
WHERE ca.GroupType = 'Family group'
AND DATE(ci.CheckTime) = DATE('now');
"""
},
{
        "input": "Khách hàng solo hay đi theo nhóm đông hơn hôm nay?",
        "query": """WITH CurrentDate AS (
    -- Step 1: Lấy ngày hiện tại theo định dạng YYYYMMDD
    SELECT strftime('%Y%m%d', 'now') AS Today
)

-- Step 2: Truy vấn để đếm số lượng khách hàng Solo và Group trong ngày hiện tại
SELECT
    CASE WHEN GroupType = 'Solo' THEN 'Solo Customers' ELSE 'Group Customers' END AS CustomerType,
    COUNT(*) AS CustomerCount
FROM CustomerAtCheckInOut
JOIN CheckInOut ON CustomerAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
WHERE substr(CheckInOut.CheckTime, 1, 8) = (SELECT Today FROM CurrentDate)
GROUP BY CustomerType
ORDER BY CustomerCount DESC;

"""
},
{
        "input": "Khách hàng solo hay đi theo nhóm đông hơn hôm nay?",
        "query": """-- Step 1: Set the current date in 'YYYY-MM-DD' format
WITH CurrentDate AS (
    SELECT DATE('now') AS Today
)

-- Step 2: Query to count solo and group customers today
SELECT
    CASE WHEN GroupType = 'Solo' THEN 'Solo Customers' ELSE 'Group Customers' END AS CustomerType,
    COUNT(*) AS CustomerCount
FROM CustomerAtCheckInOut
JOIN CheckInOut ON CustomerAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
WHERE DATE(CheckInOut.CheckTime) = (SELECT Today FROM CurrentDate)
GROUP BY CustomerType
ORDER BY CustomerCount DESC;
"""
},
{
        "input": "Khách hàng thường đến cửa hàng theo nhóm hay đơn lẻ trong 1 tháng qua?",
        "query": """SELECT
    GroupType,
    COUNT(*) AS VisitCount
FROM
    CustomerAtCheckInOut
WHERE
    CheckInOutId IN (
        SELECT CheckInOutId
        FROM CheckInOut
        WHERE CheckTime >= DATE('now', '-1 month')
    )
GROUP BY
    GroupType;
"""
},
{
        "input": "Có bao nhiêu nhân viên chưa check-in hôm nay?",
        "query": """SELECT
    GroupType,
    COUNT(*) AS VisitCount
FROM
    CustomerAtCheckInOut
WHERE
    CheckInOutId IN (
        SELECT CheckInOutId
        FROM CheckInOut
        WHERE CheckTime >= DATE('now', '-1 month')
    )
GROUP BY
    GroupType;
"""
},
{
        "input": "Khoảng thời gian check-in của tất cả nhân viên hôm qua?",
        "query": """SELECT
    A.Employee AS Wallet,
    E.FullName,
    DATETIME(A.Time) AS CheckInDayTime,
    W.LatLon AS WorkPlaceLocation
FROM
    Attendance A
JOIN
    Employee E ON A.Employee = E.Wallet
JOIN
    WorkPlaceAttendance W ON A.WorkPlaceAttendance = W.WorkPlaceId
WHERE
    A.Type = 'CHECK_IN'
    AND A.YYYYMMDD = strftime('%Y%m%d', date('now', '-1 day'))  -- Lọc ngày hôm qua theo định dạng YYYYMMDD
ORDER BY
    A.Time;
"""
},
{
        "input": "Có nhân viên nào check-in sau 7h15 hôm qua không?",
        "query": """SELECT E.FullName, A.Employee, A.Time
FROM Attendance A
JOIN Employee E ON A.Employee = E.Wallet
WHERE A.Type = 'CHECK_IN'
  AND A.Time > DATETIME('now', '-1 day', 'start of day', '+7 hours', '+15 minutes')
  AND A.Time < DATETIME('now', '-1 day', 'start of day', '+1 day');

"""
},
{
        "input": "Tôi cần danh sách nhân viên check-in trước 8 giờ sáng hôm nay.",
        "query": """SELECT E.FullName, A.Time AS CheckInTime
FROM Attendance A
JOIN Employee E ON A.Employee = E.Wallet
WHERE A.Type = 'CHECK_IN'
  AND DATE(A.Time) = DATE('now')  -- Ngày hôm nay
  AND TIME(A.Time) < '08:00:00';  -- Trước 8 giờ sáng

"""
},
{
        "input": "Nhân viên Bùi Tiến Phát đã check-out lúc mấy giờ hôm nay?",
        "query": """SELECT A.Time
FROM Attendance A
JOIN Employee E ON A.Employee = E.Wallet
WHERE E.FullName = 'Bùi Tiến Phát'
  AND A.Type = 'CHECK_OUT'
  AND A.YYYYMMDD = strftime('%Y%m%d', 'now'); -- Lọc theo ngày hôm nay (định dạng YYYYMMDD)

"""
},
{
        "input": "Có nhân viên nào chưa check-out hôm nay không?",
        "query": """WITH CheckInsToday AS (
    SELECT
        Employee,
        MAX(Time) AS LastCheckInTime
    FROM Attendance
    WHERE Type = 'CHECK_IN'
        AND YYYYMMDD = STRFTIME('%Y%m%d', DATE('now')) -- Lọc những lần check-in hôm nay
    GROUP BY Employee
),
CheckOutsToday AS (
    SELECT
        Employee,
        MAX(Time) AS LastCheckOutTime
    FROM Attendance
    WHERE Type = 'CHECK_OUT'
        AND YYYYMMDD = STRFTIME('%Y%m%d', DATE('now')) -- Lọc những lần check-out hôm nay
    GROUP BY Employee
)
SELECT
    ci.Employee
FROM CheckInsToday ci
LEFT JOIN CheckOutsToday co
    ON ci.Employee = co.Employee
WHERE co.LastCheckOutTime IS NULL OR ci.LastCheckInTime > co.LastCheckOutTime; -- Kiểm tra nếu không có check-out hoặc check-in sau check-out
"""
},
{
        "input": "Tôi muốn xem thời gian check-out trung bình của nhân viên trong tháng qua.",
        "query": """WITH LastMonthCheckOuts AS (
    SELECT
        Employee,
        CheckOutTime
    FROM
        ShiftManagement
    WHERE
        ShiftStatus = 'PRESENT'
        AND strftime('%Y-%m', CheckOutTime) = strftime('%Y-%m', 'now', '-1 month')
)
SELECT
    AVG(julianday(CheckOutTime) - julianday(DATE(CheckOutTime))) * 24 AS AverageCheckOutTime
FROM
    LastMonthCheckOuts;
"""
},
{
        "input": "Nhân viên nào đã check-out sau 8h tối hôm nay?",
        "query": """SELECT e.FullName, a.Employee, a.Time AS CheckOutTime
FROM Attendance a
JOIN Employee e ON a.Employee = e.Wallet
WHERE a.Type = 'CHECK_OUT'
  AND DATE(a.Time) = DATE('now')  -- Lọc theo ngày hiện tại
  AND TIME(a.Time) > '20:00:00';  -- Lọc theo thời gian check-out sau 8 giờ tối
"""
},
{
        "input": "Nhân viên Employee 3 đã check-out trước 5h chiều vào những ngày nào?",
        "query": """SELECT
    YYYYMMDD,
    CheckOutTime
FROM
    ShiftManagement
WHERE
    Employee = 'Employee 3'
    AND strftime('%H:%M', CheckOutTime) < '17:00';

"""
},
{
        "input": "Tôi cần xem danh sách nhân viên đã check-out sau 6 giờ chiều hôm nay.",
        "query": """SELECT e.FullName, e.Wallet, a.Time AS CheckOutTime
FROM Employee e
JOIN Attendance a ON e.Wallet = a.Employee
WHERE a.Type = 'CHECK_OUT'
  AND DATE(a.Time) = DATE('now')  -- Lọc những điểm danh của ngày hôm nay
  AND TIME(a.Time) > '18:00:00';  -- Chỉ lấy những lần check-out sau 6 giờ chiều

"""
},
{
        "input": "Cảm xúc của nhân viên 'X' khi check-in hôm nay là gì?",
        "query": """WITH LastCheckIn AS (
    SELECT a.AttendanceId, a.Employee, MAX(a.Time) AS LastCheckInTime
    FROM Attendance a
    JOIN Employee e ON a.Employee = e.Wallet
    WHERE e.FullName = 'X'
      AND a.Type = 'CHECK_IN'
      AND DATE(a.Time) = DATE('now')  -- Lọc theo ngày hôm nay
    GROUP BY a.Employee
)
SELECT e.FullName, e.Wallet, e.LocationId, e.DepartmentId, e.PositionId, e.BranchId,
       e.PositionSalary, e.OnboardDateTime, ed.Gender, ed.Birthday, ed.PhoneNumber,
       ed.Email, ed.StatusOfWorkId, ed.TypeOfWorkId, ed.TypeOfContractId, ed.TaxableSalary,
       ed.PersonalIncomeTax, ed.PermanentAddress, ed.NumberOfDependents, ed.AcademicLevelId,
       ed.ID, ed.TaxId, ed.HealthInsuranceId, ed.SocialInsuranceId, ed.HasResume, ed.HasBirthCertificate,
       ed.HasCitizenIdentification, ed.HasHouseRegistrationBook, ed.HasHealthCertification,
       ed.HasGraduationDegree, sa.Emotion, sa.Behavior, sa.ClothingStyle, sa.Appearance
FROM LastCheckIn lc
JOIN Employee e ON lc.Employee = e.Wallet
JOIN EmployeeDetail ed ON e.Wallet = ed.Wallet
JOIN Attendance a ON a.AttendanceId = lc.AttendanceId
JOIN StaffAtCheckInOut sa ON sa.CheckInOutId = a.AttendanceId
WHERE lc.LastCheckInTime = (
    SELECT MAX(CheckTime)
    FROM CheckInOut ci
    WHERE ci.CheckTime <= lc.LastCheckInTime
);
"""
},
{
        "input": "Tình trạng cảm xúc phổ biến của nhân viên trong tuần này là gì?",
        "query": """WITH ThisWeek AS (
    SELECT s.Emotion
    FROM StaffAtCheckInOut s
    JOIN CheckInOut c ON s.CheckInOutId = c.CheckInOutId
    WHERE strftime('%Y-%W', c.CheckTime) = strftime('%Y-%W', 'now')  -- Lọc theo tuần hiện tại
)
SELECT Emotion, COUNT(*) as Count
FROM ThisWeek
GROUP BY Emotion
ORDER BY Count DESC;
);
"""
},
{
        "input": "Có bao nhiêu nhân viên cảm thấy căng thẳng sau giờ làm hôm nay?",
        "query": """WITH EmployeeCheckOut AS (
    SELECT A.Employee, A.Time, A.YYYYMMDD, S.Emotion
    FROM Attendance A
    JOIN StaffAtCheckInOut S ON A.Employee = S.StaffId
    WHERE A.Type = 'CHECK_OUT'
    AND S.Emotion IN ('Angry', 'Fear', 'Sad', 'Disgust')
    AND A.YYYYMMDD = strftime('%Y%m%d', 'now')  -- Kiểm tra ngày hôm nay
)
SELECT COUNT(DISTINCT Employee) AS StressedEmployees
FROM EmployeeCheckOut;
);
"""
},
{
        "input": "Nhân viên Employee 3 có thay đổi cảm xúc như thế nào trong ca làm việc hôm nay?",
        "query": """WITH EmployeeWallet AS (
    SELECT Wallet
    FROM Employee
    WHERE FullName = 'Employee 3'
),
TodayShift AS (
    SELECT ShiftId
    FROM ShiftManagement
    WHERE Employee IN (SELECT Wallet FROM EmployeeWallet)
    AND DATE(CheckInTime) = DATE('now')  -- Lấy các ca làm việc trong ngày hôm nay
),
TodayCheckInOut AS (
    SELECT CheckInOutId
    FROM CheckInOut
    WHERE CheckTime BETWEEN (SELECT MIN(CheckInTime) FROM ShiftManagement WHERE ShiftId IN (SELECT ShiftId FROM TodayShift))
                        AND (SELECT MAX(CheckOutTime) FROM ShiftManagement WHERE ShiftId IN (SELECT ShiftId FROM TodayShift))
)
SELECT Emotion
FROM StaffAtCheckInOut
WHERE StaffId IN (SELECT Wallet FROM EmployeeWallet)
AND CheckInOutId IN (SELECT CheckInOutId FROM TodayCheckInOut);

);
"""
},
{
        "input": "Tôi cần xem thống kê cảm xúc của nhân viên trong tháng qua.",
        "query": """SELECT
    Emotion,
    COUNT(*) AS EmotionCount
FROM
    StaffAtCheckInOut
JOIN
    CheckInOut ON StaffAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
WHERE
    CheckTime BETWEEN DATE('now', 'start of month') AND DATE('now', 'localtime')
GROUP BY
    Emotion;
"""
},
{
        "input": "Nhân viên Employee 3 có thay đổi cảm xúc như thế nào trong ca làm việc hôm nay?",
        "query": """WITH EmployeeWallet AS (
    SELECT Wallet
    FROM Employee
    WHERE FullName = 'Employee 3'
),
TodayShift AS (
    SELECT ShiftId
    FROM ShiftManagement
    WHERE Employee IN (SELECT Wallet FROM EmployeeWallet)
    AND DATE(CheckInTime) = DATE('now')  -- Lấy các ca làm việc trong ngày hôm nay
),
TodayCheckInOut AS (
    SELECT CheckInOutId
    FROM CheckInOut
    WHERE CheckTime BETWEEN (SELECT MIN(CheckInTime) FROM ShiftManagement WHERE ShiftId IN (SELECT ShiftId FROM TodayShift))
                        AND (SELECT MAX(CheckOutTime) FROM ShiftManagement WHERE ShiftId IN (SELECT ShiftId FROM TodayShift))
)
SELECT Emotion
FROM StaffAtCheckInOut
WHERE StaffId IN (SELECT Wallet FROM EmployeeWallet)
AND CheckInOutId IN (SELECT CheckInOutId FROM TodayCheckInOut);

);
"""
},
{
        "input": "Tôi cần xem thống kê cảm xúc của nhân viên trong tháng qua.",
        "query": """SELECT
    Emotion,
    COUNT(*) AS EmotionCount
FROM
    StaffAtCheckInOut
JOIN
    CheckInOut ON StaffAtCheckInOut.CheckInOutId = CheckInOut.CheckInOutId
WHERE
    CheckTime BETWEEN DATE('now', 'start of month') AND DATE('now', 'localtime')
GROUP BY
    Emotion;
"""
},
{
        "input": "Có nhân viên nào check-in với trạng thái cảm xúc tiêu cực trong tuần này không?",
        "query": """WITH CurrentWeek AS (
    SELECT
        DATE('now', 'weekday 0', '-7 days') AS WeekStart,  -- Bắt đầu của tuần hiện tại (Chủ nhật trước)
        DATE('now', 'weekday 0', '-1 days') AS WeekEnd  -- Kết thúc của tuần hiện tại (Thứ bảy trước)
)
SELECT
    e.FullName,
    sa.Emotion,
    sa.CheckInOutId,
    c.CheckTime
FROM
    StaffAtCheckInOut sa
JOIN
    CheckInOut c ON sa.CheckInOutId = c.CheckInOutId
JOIN
    Employee e ON sa.StaffId = e.Wallet
WHERE
    sa.Emotion IN ('Angry', 'Sad', 'Fear', 'Disgust')  -- Cảm xúc tiêu cực
    AND DATE(c.CheckTime) BETWEEN (SELECT WeekStart FROM CurrentWeek) AND (SELECT WeekEnd FROM CurrentWeek)  -- Trong tuần hiện tại
    AND c.CheckTime = (
        SELECT MIN(c2.CheckTime)
        FROM CheckInOut c2
        WHERE c2.CheckInOutId = c.CheckInOutId
    );  -- Chỉ lấy lần CHECK_IN đầu tiên
"""
},
{
        "input": "Nhân viên X có hành vi bất thường trong suốt ca làm việc hôm nay không?",
        "query": """WITH EmployeeCheck AS (
    SELECT E.Wallet, E.FullName, S.IsLate, S.IsLatePermission, S.IsComebackSoon, S.IsComebackSoonPermission,
           A.Type AS AttendanceType, A.Time AS AttendanceTime, A.IsOvertime, A.OvertimeStatus, SA.Emotion, SA.Behavior
    FROM Employee E
    JOIN ShiftStatus S ON E.Wallet = S.Employee
    JOIN Attendance A ON E.Wallet = A.Employee AND A.YYYYMMDD = strftime('%Y%m%d', 'now')
    LEFT JOIN StaffAtCheckInOut SA ON E.Wallet = SA.StaffId
    WHERE E.FullName = 'Employee 3' AND S.YYYYMMDD = strftime('%Y%m%d', 'now')
)
SELECT *
FROM EmployeeCheck
WHERE
    -- Kiểm tra nếu đi trễ không phép
    (IsLate = 1 AND IsLatePermission = 0)
    OR
    -- Kiểm tra nếu về sớm không phép
    (IsComebackSoon = 1 AND IsComebackSoonPermission = 0)
    OR
    -- Kiểm tra nếu có cảm xúc tiêu cực từ camera
    Emotion IN ('Angry', 'Sad', 'Disgust', 'Fear')
    OR
    -- Kiểm tra nếu có hành vi bất thường từ camera
    Behavior IN ('Taking photo', 'Talking/Pointing');
"""
},
{
        "input": "Tôi muốn xem danh sách nhân viên đi trễ trong tuần.",
        "query": """WITH CurrentWeek AS (
    SELECT
        STRFTIME('%Y%m%d', DATE('now', 'weekday 0', '-6 days')) AS WeekStart, -- Ngày bắt đầu tuần (thứ 2)
        STRFTIME('%Y%m%d', DATE('now', 'weekday 0')) AS WeekEnd -- Ngày kết thúc tuần (Chủ nhật)
)
SELECT
    E.FullName,
    E.Wallet,
    S.YYYYMMDD,
    S.IsLate,
    S.IsLatePermission
FROM
    ShiftStatus S
JOIN
    Employee E ON S.Employee = E.Wallet
JOIN
    CurrentWeek CW ON S.YYYYMMDD BETWEEN CW.WeekStart AND CW.WeekEnd
WHERE
    S.IsLate = TRUE AND S.IsLatePermission = FALSE; -- Chỉ lấy nhân viên đi trễ mà không có phép
"""
},
{
        "input": "Có nhân viên nào bỏ vị trí làm việc mà không check-out hôm nay không?",
        "query": """WITH CheckInToday AS (
    SELECT
        Employee,
        ShiftId,
        WorkPlaceAttendance,
        Time AS CheckInTime
    FROM Attendance
    WHERE
        Type = 'CHECK_IN'
        AND YYYYMMDD = strftime('%Y%m%d', 'now')
),
CheckOutToday AS (
    SELECT
        Employee,
        ShiftId,
        WorkPlaceAttendance,
        Time AS CheckOutTime
    FROM Attendance
    WHERE
        Type = 'CHECK_OUT'
        AND YYYYMMDD = strftime('%Y%m%d', 'now')
)
SELECT
    e.FullName,
    e.Wallet,
    c1.CheckInTime,
    s.ShiftStatus
FROM
    CheckInToday c1
    LEFT JOIN CheckOutToday c2 ON c1.Employee = c2.Employee AND c1.ShiftId = c2.ShiftId
    JOIN Employee e ON c1.Employee = e.Wallet
    JOIN ShiftManagement s ON c1.Employee = s.Employee AND c1.ShiftId = s.ShiftId
WHERE
    c2.CheckOutTime IS NULL
    AND s.ShiftStatus = 'PRESENT';
"""
},
{
        "input": "Nhân viên tên 'Employee3' đã có hành vi như thế nào khi check-out hôm nay?",
        "query": """WITH TodayAttendance AS (
    -- Lấy thông tin check-out của Employee3 hôm nay
    SELECT A.Employee, A.Type, A.Time, A.WorkPlaceAttendance, S.Behavior, S.Emotion
    FROM Attendance A
    JOIN Employee E ON A.Employee = E.Wallet
    JOIN StaffAtCheckInOut S ON A.AttendanceId = S.CheckInOutId
    WHERE E.FullName = 'Employee3'
      AND A.Type = 'CHECK_OUT'
      AND DATE(A.Time) = DATE('now')  -- Chỉ lấy dữ liệu của ngày hôm nay
)
SELECT *
FROM TodayAttendance;
"""
},
{
        "input": "Nhân viên tên 'Employee3' đã có hành vi như thế nào khi check-out hôm nay?",
        "query": """WITH TodayAttendance AS (
    -- Lấy thông tin check-out của Employee3 hôm nay
    SELECT A.Employee, A.Type, A.Time, A.WorkPlaceAttendance, S.Behavior, S.Emotion
    FROM Attendance A
    JOIN Employee E ON A.Employee = E.Wallet
    JOIN StaffAtCheckInOut S ON A.AttendanceId = S.CheckInOutId
    WHERE E.FullName = 'Employee3'
      AND A.Type = 'CHECK_OUT'
      AND DATE(A.Time) = DATE('now')  -- Chỉ lấy dữ liệu của ngày hôm nay
)
SELECT *
FROM TodayAttendance;
"""
},
{
        "input": "Tôi muốn xem báo cáo về check-in/check-out và cảm xúc của nhân viên tên 'Employee 5' trong tháng này.",
        "query": """SELECT
    A.Time AS CheckTime,
    A.Type AS CheckType,
    A.YYYYMMDD
FROM
    Attendance A
JOIN
    Employee E ON A.Employee = E.Wallet
WHERE
    E.FullName = 'Employee 5'
    AND strftime('%Y-%m', A.Time) = strftime('%Y-%m', 'now')  -- Lọc dữ liệu trong tháng hiện tại
ORDER BY
    A.Time ASC;
"""
},
{
        "input": "Có bao nhiêu nhân viên check-in muộn và có cảm xúc tiêu cực trong hôm nay?",
        "query": """WITH LateCheckIn AS (
    SELECT
        s.Employee,
        s.IsLate
    FROM
        ShiftStatus s
    WHERE
        s.IsLate = TRUE
        AND s.YYYYMMDD = strftime('%Y%m%d', 'now')  -- Chỉ lấy dữ liệu ngày hôm nay
),
NegativeEmotion AS (
    SELECT
        sc.StaffId
    FROM
        StaffAtCheckInOut sc
    WHERE
        sc.Emotion IN ('Angry', 'Sad', 'Disgust', 'Fear')  -- Chọn các cảm xúc tiêu cực
        AND EXISTS (
            SELECT 1
            FROM CheckInOut c
            WHERE c.CheckInOutId = sc.CheckInOutId
            AND strftime('%Y%m%d', c.CheckTime) = strftime('%Y%m%d', 'now')  -- Chỉ lấy dữ liệu của hôm nay
        )
)
SELECT
    COUNT(DISTINCT l.Employee) AS NumLateAndNegativeEmotion
FROM
    LateCheckIn l
JOIN
    NegativeEmotion ne ON l.Employee = ne.StaffId;
"""
},
{
        "input": "Tôi cần báo cáo về hành vi và thời gian check-in của nhân viên tên 'Employee 9' trong tháng qua.",
        "query": """WITH LastMonth AS (
    SELECT strftime('%Y-%m', 'now', '-1 month') AS LastMonth
)
SELECT DISTINCT
    E.FullName AS EmployeeName,
    E.Wallet AS EmployeeWallet,
    A.Type AS CheckType,
    A.Time AS CheckTime,
    A.IsOvertime,
    A.OvertimeStatus,
    A.Reason AS CheckReason,
    A.OnBehalfStatus,
    SM.ShiftStatus,
    SM.LatePermissionStatus,
    SM.ComeBackSoonPermissionStatus,
    SM.LeavePermissionStatus
FROM Attendance A
JOIN Employee E ON A.Employee = E.Wallet
LEFT JOIN ShiftManagement SM ON A.ShiftId = SM.ShiftId AND A.Employee = SM.Employee
JOIN LastMonth L ON strftime('%Y-%m', A.Time) = L.LastMonth
WHERE A.Type IN ('CHECK_IN', 'CHECK_IN_ON_BEHALF') and E.FullName = 'Employee 9'
ORDER BY A.Time ASC;
"""
},
{
        "input": "Nhân viên 'Employee 9' có hành vi bất thường và cảm xúc tiêu cực vào ngày hôm qua không?",
        "query": """WITH EmployeeData AS (
    SELECT e.Wallet
    FROM Employee e
    WHERE e.FullName = 'Employee 9'
),
YesterdayData AS (
    SELECT
        s.StaffId,
        s.Emotion,
        s.Behavior,
        c.CheckTime
    FROM StaffAtCheckInOut s
    JOIN CheckInOut c ON s.CheckInOutId = c.CheckInOutId
    WHERE s.StaffId = (SELECT Wallet FROM EmployeeData)
      AND DATE(c.CheckTime) = DATE('now', '-1 day')  -- Ngày hôm qua
)
SELECT
    Emotion,
    Behavior,
    CheckTime,
    CASE
        WHEN Emotion IN ('Angry', 'Fear', 'Sad', 'Disgust') THEN 'Negative Emotion'
        ELSE 'Normal Emotion'
    END AS EmotionStatus,
    CASE
        WHEN Behavior IN ('Taking photo', 'Talking/Pointing') THEN 'Unusual Behavior'
        ELSE 'Normal Behavior'
    END AS BehaviorStatus
FROM YesterdayData
WHERE Emotion IN ('Angry', 'Fear', 'Sad', 'Disgust')
   OR Behavior IN ('Taking photo', 'Talking/Pointing');
"""
},
{
        "input": "Báo cáo tổng hợp về check-out và hành vi vi phạm của nhân viên trong tuần này.",
        "query": """WITH ThisWeekAttendance AS (
    -- Lấy tất cả dữ liệu điểm danh của tuần này
    SELECT
        A.Employee,
        A.Type,
        A.Time,
        A.WorkPlaceAttendance,
        A.IsOvertime,
        A.OvertimeStatus,
        A.ShiftId,
        A.OnBehalfStatus
    FROM Attendance A
    WHERE strftime('%W', A.Time) = strftime('%W', 'now')  -- Lọc theo tuần hiện tại
),

ThisWeekShiftStatus AS (
    -- Lấy dữ liệu trạng thái làm việc của tuần này
    SELECT
        S.Employee,
        S.YYYYMMDD,
        S.IsDayOff,
        S.IsPresent,
        S.IsLate,
        S.IsLatePermission,
        S.IsComebackSoon,
        S.IsComebackSoonPermission,
        S.IsLeavePermission,
        S.IsPaidLeave
    FROM ShiftStatus S
    WHERE strftime('%W', date(substr(S.YYYYMMDD, 1, 4) || '-' ||
                               substr(S.YYYYMMDD, 5, 2) || '-' ||
                               substr(S.YYYYMMDD, 7, 2))) = strftime('%W', 'now')  -- Lọc theo tuần hiện tại
)

-- Tổng hợp thông tin điểm danh và hành vi vi phạm trong tuần này
SELECT
    E.FullName AS EmployeeName,
    E.DepartmentId,
    E.PositionId,
    COALESCE(SUM(CASE WHEN TW.Type = 'CHECK_OUT' THEN 1 ELSE 0 END), 0) AS CheckOutCount,
    COALESCE(SUM(CASE WHEN TWS.IsLate = 1 AND TWS.IsLatePermission = 0 THEN 1 ELSE 0 END), 0) AS LateWithoutPermissionCount,
    COALESCE(SUM(CASE WHEN TWS.IsComebackSoon = 1 AND TWS.IsComebackSoonPermission = 0 THEN 1 ELSE 0 END), 0) AS EarlyLeaveWithoutPermissionCount,
    COALESCE(SUM(CASE WHEN TWS.IsPresent = 0 AND TWS.IsDayOff = 0 THEN 1 ELSE 0 END), 0) AS AbsentWithoutReasonCount
FROM Employee E
LEFT JOIN ThisWeekAttendance TW ON E.Wallet = TW.Employee
LEFT JOIN ThisWeekShiftStatus TWS ON E.Wallet = TWS.Employee
GROUP BY E.FullName, E.DepartmentId, E.PositionId
ORDER BY E.FullName;

"""
},
{
        "input": "Cửa hàng hiện đang trong tình trạng gì: sạch sẽ, bẩn hay có vấn đề gì nghiêm trọng không?",
        "query": """SELECT
    StoreId,
    CleanlinessStatus,
    CASE
        WHEN CleanlinessStatus = 'Clean' THEN 'Sạch sẽ'
        WHEN CleanlinessStatus = 'Dirty' THEN 'Bẩn'
        WHEN CleanlinessStatus = 'On fire' THEN 'Cửa hàng đang cháy'
        WHEN CleanlinessStatus = 'Under attack' THEN 'Cửa hàng đang bị tấn công'
        ELSE 'Tình trạng không rõ'
    END AS StoreCondition,
    LastUpdate
FROM StoreStatus
ORDER BY LastUpdate DESC;
"""
},
{
        "input": "Có cảnh báo nào về độ sạch của quán không?",
        "query": """SELECT StoreId, CleanlinessStatus, LastUpdate
FROM StoreStatus
WHERE CleanlinessStatus IN ('Dirty', 'On fire', 'Under attack');
"""
},
{
        "input": "Tình trạng vệ sinh chung của cửa hàng hôm nay như thế nào?",
        "query": """SELECT StoreId, CleanlinessStatus, LastUpdate
FROM StoreStatus
WHERE DATE(LastUpdate) = DATE('now');
"""
},
]