' // ========================================================================
' // Projects\Trivia Demo (Music)\Triggers\Default Trigger.vbs
' // ------------------------------------------------------------------------
' // 
' // 
' // ========================================================================


Option Explicit

CONST STR_TRIVIADBFILE          = "<AXMMROOT>Projects\Trivia Demo (Music)\Database\TriviaLiveDemo.mdb"
CONST STR_DEBUGFILE             = "<AXMMROOT>Sys\Tmp\Trivia Demo (Music).txt"

CONST MAXQUESTIONS              = 9

CONST PROGRESS_INVALID          = 0
CONST PROGRESS_NEWSESSION       = 1
CONST PROGRESS_ANSWER           = 2


' Declaration of global objects
Dim g_objMessageDB, g_objDebugger, g_objConstants

' Creation of global objects
Set g_objConstants      = CreateObject( "AxMmServer.Constants" )
Set g_objMessageDB      = CreateObject( "AxMmServer.MessageDB" ) 
Set g_objDebugger       = CreateObject( "ActiveXperts.VbDebugger" )

' Set Debug file - for troubleshooting purposes
g_objDebugger.DebugFile = STR_DEBUGFILE
g_objDebugger.Enabled	= True


' // ========================================================================
' // Function: ProcessMessage
' // ------------------------------------------------------------------------
' // ProcessMessage trigger function to process incoming messages
' // ========================================================================

Function ProcessMessage( numMessageID )
  Dim objMessageIn, objMessageOut
  Dim bIsNewUser, bNoMoreQuestions
  Dim nResult, nUserID, strReply, strScore, strNextQuestion, strError

  g_objDebugger.WriteLine ">> ProcessMessage"

  ' Open the Message Database
  g_objMessageDB.Open
  If( g_objMessageDB.LastError <> 0 ) Then
    g_objDebugger.WriteLine "<< ProcessMessage,  unable to open database"
    Exit Function
  End If

  ' Retrieve the message that has just been received. If it fails then exit script 
  Set objMessageIn   = g_objMessageDB.FindFirstMessage( "ID = " & numMessageID ) 
  If g_objMessageDB.LastError <> 0 Then
    g_objMessageDB.Close
    g_objDebugger.WriteLine "<< ProcessMessage,  FindFirstMessage failed, error: [" & g_objMessageDB.LastError & "]"
    Exit Function
  End If

  ' Avoid loopback
  If( objMessageIn.FromAddress = objMessageIn.ToAddress ) Then
    objMessageIn.StatusID = g_objConstants.MESSAGESTATUS_FAILED
    Exit Function
  End If

  ' Change Status to from Pending to Success. If you don't do it, the message will be processed by subsequent triggers (if defined) because message is still pending
  objMessageIn.StatusID = g_objConstants.MESSAGESTATUS_SUCCESS
  g_objMessageDB.Save objMessageIn   

  g_objDebugger.WriteLine "Incoming message saved, result: [" & g_objMessageDB.LastError & "]"

  bIsNewUser  = False
  nResult     = 0
  strReply    = ""
  
  If( nResult = 0 ) Then
    nResult = GetUserID( objMessageIn.FromAddress, nUserID, bIsNewUser, strError )  
  End If
  
  If( nResult = 0 And bIsNewUser ) Then
    strReply = "Welcome to Music SMS Trivia! "
  End If
  
  If( nResult = 0 And Not bIsNewUser ) Then
    nResult = ProcessAnswer( nUserID,  objMessageIn.Body, strReply, strError )
  End If
   
  If( nResult = 0 ) Then
    nResult = GetNextQuestion( nUserID, strNextQuestion, bNoMoreQuestions, strError )   
    If( nResult = 0 ) Then
      strReply = strReply & strNextQuestion
    End If
  End If
 
  If( nResult <> 0 ) Then
    ReplyMessage objMessageIn, strError, 0
  ElseIf( bIsNewUser ) Then
    ReplyMessage objMessageIn, strReply, 0
  ElseIf( bNoMoreQuestions ) Then
    If( GetScore( nUserID, strScore, strError ) = 0 ) Then  
      strReply = strReply & strScore    
      ReplyMessage objMessageIn, strReply, 0 
      ReplyMessage objMessageIn, "Like SMS trivia games? Try the new Movies Quiz, it's fun! SMS 'Movies' to 1234 to join.", 1
    Else
      ReplyMessage objMessageIn, strError, 0
    End If
  Else
    ReplyMessage objMessageIn, strReply, 0
  End If     
  
  ' Close the Message Database
  g_objMessageDB.Close

  g_objDebugger.WriteLine "<< ProcessMessage"

End Function



' // ========================================================================

Function GetUserID( strMobileNumber, BYREF nUserID, BYREF bIsNewUser, BYREF strError ) 
  Dim objConn, RS, strQuery, strInsert
  Dim nQ1, nQ2, nQ3

  g_objDebugger.WriteLine( ">> GetUserID" )

  nUserID     = 0 
  bIsNewUser  = False
  strError    = ""
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_TRIVIADBFILE & ";"

  strQuery = "SELECT ID FROM TriviaUsers WHERE strMobileNumber='" & strMobileNumber & "' AND ( nA1 = 0 OR nA2 = 0 OR nA3 = 0 ) ORDER BY ID Desc"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then

    ' Try to find out the last question of the previous question
     strQuery = "SELECT nQ3 FROM TriviaUsers WHERE strMobileNumber='" & strMobileNumber & "' ORDER BY ID Desc"
     g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
    Set RS = objConn.Execute( strQuery )   
    If( Not RS.EOF ) Then
      nQ1 = CInt( RS( "nQ3" ) ) + 1
      If( nQ1 > MAXQUESTIONS ) Then
        nQ1 = 1
      End If
      nQ2 = nQ1 + 1
      If( nQ2 > MAXQUESTIONS ) Then
        nQ2 = 1
      End If   
      nQ3 = nQ2 + 1
      If( nQ3 > MAXQUESTIONS ) Then
        nQ3 = 1
      End If  
    Else
      nQ1 = 1
      nQ2 = 2
      nQ3 = 3
    End If
    
    strInsert = "INSERT INTO TriviaUsers ( strMobileNumber, nQ1, nQ2, nQ3 ) VALUES ( '" & strMobileNumber & "', " & nQ1 & ", " & nQ2 & ", " & nQ3 & " )"
    Err.Clear ' Clear a previous error
    g_objDebugger.WriteLine( "Execute: [" & strInsert & "]..." )       
    objConn.Execute( strInsert )
    
    bIsNewUser = True
    
    strQuery = "SELECT ID FROM TriviaUsers WHERE strMobileNumber='" & strMobileNumber & "' ORDER BY ID Desc"
    g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )      
    Set RS = objConn.Execute( strQuery )    
    
  End If
  
  If RS.EOF Then
    GetUserID = -1
    strError = "Failed to signup user as a Trivia user."
  Else
    nUserID = RS( "ID" ) 
    GetUserID = 0    
  End If
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< GetUserID, result: " &  GetUserID )
End Function



' // ========================================================================

Function GetScore( nUserID, BYREF strScore, BYREF strError ) 
  Dim objConn, RS, strQuery, strInsert
  Dim arrQuestions( 99 )
  Dim arrAnswers( 99 )
  Dim nAnswerID, strFormalAnswerString, iQuestion, nPositive, nTotal

  g_objDebugger.WriteLine( ">> GetScore( " & nUserID & ")" )

  strScore    = ""
  strError    = ""
  
  nPositive   = 0
  nTotal      = 3
  
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_TRIVIADBFILE & ";"

  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID & " ORDER BY ID Desc"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]" )
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    GetScore = -1
    strError = "Failed to retrieve user."
  Else
    arrQuestions( 0 ) = CInt( RS( "nQ1" ) )
    arrQuestions( 1 ) = CInt( RS( "nQ2" ) )
    arrQuestions( 2 ) = CInt( RS( "nQ3" ) )
    arrAnswers( 0 ) = CInt( RS( "nA1" ) )
    arrAnswers( 1 ) = CInt( RS( "nA2" ) )
    arrAnswers( 2 ) = CInt( RS( "nA3" ) )
    
    For iQuestion = 0 To 2
      If( arrQuestions( iQuestion ) <= 0 Or arrAnswers( iQuestion ) <= 0 ) Then
        GetScore = -1
        strError = "Error: One or more questions are not answered."
        Exit For
      End If
        
      If( GetAnswer( objConn, arrQuestions( iQuestion ), nAnswerID, strFormalAnswerString ) <> 0 ) Then
        GetScore = -1
        strError = "Error: Failed to find answer for question " & arrQuestions( i )
        Exit For
      End If
        
      If( arrAnswers( iQuestion ) = nAnswerID ) Then
        g_objDebugger.WriteLine(  "YES, Answer on Question " & arrQuestions( iQuestion ) & " = " & nAnswerID & " (" & strFormalAnswerString & ")" )    
        nPositive = nPositive + 1
      Else
        g_objDebugger.WriteLine(  "NO, Answer on Question " & arrQuestions( iQuestion ) & " <> " & nAnswerID & " (" & strFormalAnswerString & ")" )           
      End If
    Next
  End If
  
  objConn.Close
  Set objConn = Nothing
  
  If( GetScore = 0 ) Then
    strScore = "Trivia completed. Score " & nPositive & " out of 3."
    
    If( nPositive = 3 ) Then
      strScore = strScore & " Well done, you are an expert! You will receive a  coupon for a free hamburger at McDonald’s. Enjoy!"
    Else
      strScore = strScore & " Thanks for joining! You will receive a  coupon for a free hamburger at McDonald’s. Enjoy!"
    End If
  End If
  
  g_objDebugger.WriteLine( "<< GetScore, result: " &  GetScore )
End Function


' // ========================================================================

Function GetAnswer( objConn, nQuestionID, BYREF nFormalAnswerID, BYREF strFormalAnswerString )

  Dim strQuery, RS
  
  g_objDebugger.WriteLine( ">> GetAnswer( " & nQuestionID & ")" )

  nFormalAnswerID       = 0
  strFormalAnswerString = ""
        
  strQuery   = "SELECT * FROM TriviaQuestions WHERE ID=" & nQuestionID     
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )
  Set RS = objConn.Execute( strQuery ) 
  
  If( RS.EOF ) Then
    GetAnswer = -1
    strError = "Error: Failed to find answer for question " &nQuestionID
  Else  
    nFormalAnswerID = CInt( RS( "nTriviaAnswerID" ) )
    If( nFormalAnswerID = 1 ) Then
      strFormalAnswerString = RS( "strAnswerA" )
    ElseIf( nFormalAnswerID = 2 ) Then
      strFormalAnswerString = RS( "strAnswerB" )
    ElseIf( nFormalAnswerID = 3 ) Then
      strFormalAnswerString = RS( "strAnswerC" )    
    End If
    
    g_objDebugger.WriteLine(  "nAnswerID: " & nFormalAnswerID ) 
    g_objDebugger.WriteLine(  "strFormalAnswerString: " & strFormalAnswerString ) 
    GetAnswer = 0
  End If
   
  g_objDebugger.WriteLine( "<< GetAnswer, result: " &  GetAnswer )
End Function



' // ========================================================================

Function ProcessAnswer( nUserID, strAnswer, BYREF strExplanation, BYREF strError )  

  Dim objConn, RS, strQuery, strUpdate, strAField
  Dim strUAnswer, nQuestionID, nAnswer, nFormalAnswerID, strFormalAnswerString
  
  g_objDebugger.WriteLine( ">> ProcessAnswer" )
  
  ProcessAnswer         = 0
  strExplanation        = ""
  strError              = ""
  strFormalAnswerString = ""
  
  ' Translate the answer into A, B or C
  strUAnswer = Left( UCase( Trim( strAnswer ) ), 1 )
  If( strUAnswer = "A" ) Then
    nAnswer = 1
  ElseIf( strUAnswer = "B" ) Then
    nAnswer = 2
  ElseIf( strUAnswer = "C" ) Then
    nAnswer = 3
  Else  
    ProcessAnswer = -1
    strError = "Invalid answer, please type a, b or c."
    Exit Function
  End If
 
  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_TRIVIADBFILE & ";"
  
  ' Find out which answer it was
  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )  
  Set RS = objConn.Execute( strQuery )
  If RS.EOF Then
    strError = "Error: Unable to process answer."
    ProcessAnswer = -1
  Else
    If( CInt( RS( "nA1" ) ) = 0 ) Then 
      nQuestionID = RS( "nQ1" )
      strAField = "nA1"
    ElseIf( CInt( RS( "nA2" ) ) = 0 ) Then 
      nQuestionID = RS( "nQ2" )    
      strAField = "nA2"     
    ElseIf( CInt( RS( "nA3" ) ) = 0 ) Then 
      nQuestionID = RS( "nQ3" )    
      strAField = "nA3"  
    Else
      nQuestionID = 0   
      strAField = "" 
    End If 
    
    If( strAField = "" ) Then
      strError = "Error: Cannot find question associated with this answer."
      ProcessAnswer = -1     
    End If
  End If
      
  If( ProcessAnswer = 0 ) Then      
    strUpdate = "Update TriviaUsers SET " & strAField & " = " & nAnswer & " WHERE ID=" & nUserID
    g_objDebugger.WriteLine( "Execute: [" & strUpdate & "]..." )    
    Err.Clear ' Clear a previous error    
    objConn.Execute strUpdate  
  End If
  
  If( GetAnswer( objConn, nQuestionID, nFormalAnswerID, strFormalAnswerString ) = 0 ) Then
    If( nFormalAnswerID = nAnswer ) Then
      strExplanation = "Answer is correct! "
    Else
       strExplanation = "Oops, wrong answer, the correct answer was '" & strFormalAnswerString & "'. "
    End If
  End If
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< ProcessAnswer, result: " &  ProcessAnswer )
  
End Function


' // ========================================================================

Function GetNextQuestion( nUserID, BYREF strNextQuestion, BYREF bNoMoreQuestions, BYREF strError ) 
  Dim objConn, RS, strQuery, nSeq, nQuestionID

  g_objDebugger.WriteLine( ">> GetNextQuestion" )
  
  GetNextQuestion   = -1
  strNextQuestion   = ""
  bNoMoreQuestions  = False
  strError          = ""
  
  nSeq              = 0
  nQuestionID       = 0

  Set objConn = CreateObject("ADODB.Connection")
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & STR_TRIVIADBFILE & ";"

  ' Find the User and its next question to be answered
  strQuery = "SELECT * FROM TriviaUsers WHERE ID=" & nUserID & " ORDER BY ID Desc"
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )     
  Set RS = objConn.Execute( strQuery )  
  If( RS.EOF ) Then
    GetNextQuestion = -1
    strError        = "Error: Lookup user failed."
    objConn.Close    
    Exit Function  
  ElseIf( CInt( RS( "nA1" ) ) = 0 ) Then
    nSeq        = 1
    nQuestionID = CInt( RS( "nQ1" ) )
  ElseIf( CInt( RS( "nA2" ) ) = 0 ) Then
    nSeq        = 2
    nQuestionID = CInt( RS( "nQ2" ) )
  ElseIf( CInt( RS( "nA3" ) ) = 0 ) Then
    nSeq        = 3  
    nQuestionID = CInt( RS( "nQ3" ) )
  Else
    GetNextQuestion   = 0
    bNoMoreQuestions  = True
    objConn.Close    
    Exit Function
  End If
  
  ' Lookup the Question Text
  strQuery = "SELECT * FROM TriviaQuestions WHERE ID = " & nQuestionID
  g_objDebugger.WriteLine( "Execute: [" & strQuery & "]..." )   
  Set RS = objConn.Execute( strQuery )  
  If( RS.EOF ) Then
    GetNextQuestion = -1
    strError        = "Error: Failed to retrieve question."
    objConn.Close    
    Exit Function
  End If

  strNextQuestion = "Question "& nSeq & " of 3: " & RS( "strQuestion" ) & " a) " & RS( "strAnswerA" ) & " b) " & RS( "strAnswerB" )  & " c) " & RS( "strAnswerC" ) & ". Reply with a, b or c."
  GetNextQuestion = 0
  
  objConn.Close
  Set objConn = Nothing

  g_objDebugger.WriteLine( "<< GetNextQuestion, result: " &  GetNextQuestion )
End Function


' // ========================================================================
' // ReplyMessage
' // ------------------------------------------------------------------------
' // Auto reply to every incoming SMS message
' // ========================================================================

Function ReplyMessage( objMessageIn, strResponse, nDelayMinutes )

   Dim objMessageOut

   g_objDebugger.WriteLine ">> ReplyMessage"

   ' WRITE YOUR CODE HERE (for instance: forward message to an e-mail address, see below)
   Set objMessageOut = g_objMessageDB.Create
   If( g_objMessageDB.LastError = 0 ) Then
     objMessageOut.DirectionID  = g_objConstants.MESSAGEDIRECTION_OUT
     objMessageOut.StatusID     = g_objConstants.MESSAGESTATUS_PENDING
     objMessageOut.TypeID       = objMessageIn.TypeID 
     objMessageOut.ToAddress    = objMessageIn.FromAddress
     objMessageOut.ChannelID    = objMessageIn.ChannelID
     objMessageOut.BodyFormatID = objMessageIn.BodyFormatID
     objMessageOut.Body         = strResponse
     If( nDelayMinutes <> 0 ) Then
      objMessageOut.ScheduledTime = "+0d0h" & nDelayMinutes & "m"
     End If
     g_objMessageDB.Save objMessageOut
   End If

  g_objDebugger.WriteLine "<< ReplyMessage"

End Function