namespace WiDAL.Query.Wi;

public static class WiStepContentChecklistResponseQB
{
    // Index: IX_TWISTEPCONTENTCHKRESP_CONTENT_EMP on (WISTEPCONTENTID, EMPLOYEEID, TENANTID)
    public const string GET_RESPONSE = @"
        SELECT  RESPONSEJSON
        FROM    WI.TWISTEPCONTENTCHECKLISTRESPONSE
        WHERE   WISTEPCONTENTID = @WiStepContentId
          AND   EMPLOYEEID      = @EmployeeId
          AND   TENANTID        = @TenantId";

    // Upsert on the (WiStepContentId, EmployeeId, TenantId) unique key — one row per
    // employee per checklist block, overwritten on every tick.
    public const string UPSERT_RESPONSE = @"
        MERGE WI.TWISTEPCONTENTCHECKLISTRESPONSE AS T
        USING (SELECT @WiStepContentId AS WiStepContentId, @EmployeeId AS EmployeeId, @TenantId AS TenantId) AS S
            ON T.WISTEPCONTENTID = S.WiStepContentId
           AND T.EMPLOYEEID      = S.EmployeeId
           AND T.TENANTID        = S.TenantId
        WHEN MATCHED THEN
            UPDATE SET RESPONSEJSON = @ResponseJson,
                       MODIFIEDBYID = @ModifiedById,
                       MODIFIEDON   = GETDATE()
        WHEN NOT MATCHED THEN
            INSERT (WISTEPCONTENTID, EMPLOYEEID, RESPONSEJSON, TENANTID, CREATEDBYID, MODIFIEDBYID)
            VALUES (@WiStepContentId, @EmployeeId, @ResponseJson, @TenantId, @ModifiedById, @ModifiedById);";
}