<%
'=======================================================================================================================
' MIGRATION: seed_sample_boards_and_layout
'=======================================================================================================================

Sub Migration_Up(migration)
    Call EnsureBoardWithLayout(migration, "Website Redesign", "website-redesign", "sample.seed")
    Call EnsureBoardWithLayout(migration, "Q3 Launch Plan", "q3-launch-plan", "sample.seed")
End Sub

Sub Migration_Down(migration)
    Call DbExecute(migration, "DELETE FROM [swim_lanes] WHERE [created_by] = ?", Array("sample.seed"))
    Call DbExecute(migration, "DELETE FROM [board_columns] WHERE [created_by] = ?", Array("sample.seed"))
    Call DbExecute(migration, "DELETE FROM [boards] WHERE [created_by] = ?", Array("sample.seed"))
End Sub

Private Sub EnsureBoardWithLayout(migration, boardName, boardSlug, seedUser)
    Dim boardId
    boardId = GetBoardIdBySlug(migration, boardSlug)

    If boardId = 0 Then
        Call DbExecute(migration, _
            "INSERT INTO [boards] ([name],[slug],[created_at],[created_by],[updated_at],[updated_by]) VALUES (?,?,?,?,?,?)", _
            Array(boardName, boardSlug, Now(), seedUser, Now(), seedUser))

        boardId = GetBoardIdBySlug(migration, boardSlug)
    End If

    If boardId > 0 Then
        Call EnsureColumn(migration, boardId, "Backlog", 0, seedUser)
        Call EnsureColumn(migration, boardId, "In Progress", 1, seedUser)
        Call EnsureColumn(migration, boardId, "Review", 2, seedUser)
        Call EnsureColumn(migration, boardId, "Done", 3, seedUser)

        Call EnsureSwimLane(migration, boardId, "Expedite", 0, seedUser)
        Call EnsureSwimLane(migration, boardId, "Standard", 1, seedUser)
        Call EnsureSwimLane(migration, boardId, "Blocked", 2, seedUser)
    End If
End Sub

Private Sub EnsureColumn(migration, boardId, columnName, columnPosition, seedUser)
    If GetColumnIdByName(migration, boardId, columnName) = 0 Then
        Call DbExecute(migration, _
            "INSERT INTO [board_columns] ([board_id],[name],[position],[created_at],[created_by],[updated_at],[updated_by]) VALUES (?,?,?,?,?,?,?)", _
            Array(boardId, columnName, columnPosition, Now(), seedUser, Now(), seedUser))
    End If
End Sub

Private Sub EnsureSwimLane(migration, boardId, laneName, lanePosition, seedUser)
    If GetSwimLaneIdByName(migration, boardId, laneName) = 0 Then
        Call DbExecute(migration, _
            "INSERT INTO [swim_lanes] ([board_id],[name],[position],[created_at],[created_by],[updated_at],[updated_by]) VALUES (?,?,?,?,?,?,?)", _
            Array(boardId, laneName, lanePosition, Now(), seedUser, Now(), seedUser))
    End If
End Sub

Private Function GetBoardIdBySlug(migration, boardSlug)
    Dim rs
    Set rs = DbQuery(migration, "SELECT TOP 1 [id] FROM [boards] WHERE [slug] = ?", Array(boardSlug))
    If rs.EOF Then
        GetBoardIdBySlug = 0
    Else
        GetBoardIdBySlug = CLng(rs(0))
    End If
    Call CloseRecordset(rs)
End Function

Private Function GetColumnIdByName(migration, boardId, columnName)
    Dim rs
    Set rs = DbQuery(migration, "SELECT TOP 1 [id] FROM [board_columns] WHERE [board_id] = ? AND [name] = ?", Array(boardId, columnName))
    If rs.EOF Then
        GetColumnIdByName = 0
    Else
        GetColumnIdByName = CLng(rs(0))
    End If
    Call CloseRecordset(rs)
End Function

Private Function GetSwimLaneIdByName(migration, boardId, laneName)
    Dim rs
    Set rs = DbQuery(migration, "SELECT TOP 1 [id] FROM [swim_lanes] WHERE [board_id] = ? AND [name] = ?", Array(boardId, laneName))
    If rs.EOF Then
        GetSwimLaneIdByName = 0
    Else
        GetSwimLaneIdByName = CLng(rs(0))
    End If
    Call CloseRecordset(rs)
End Function

Private Sub CloseRecordset(ByRef rs)
    If IsObject(rs) Then
        If Not rs Is Nothing Then
            On Error Resume Next
            rs.Close
            Set rs = Nothing
            On Error GoTo 0
        End If
    End If
End Sub

Private Sub DbExecute(migration, sql, params)
    On Error Resume Next
    migration.DB.Execute sql, params
    If Err.Number = 0 Then
        On Error GoTo 0
        Exit Sub
    End If
    Err.Clear
    On Error GoTo 0

    Dim conn, cmd
    Set conn = migration.Connection
    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql

    If IsArray(params) Then
        cmd.Execute , params
    ElseIf IsEmpty(params) Then
        cmd.Execute
    Else
        cmd.Execute , Array(params)
    End If

    Set cmd = Nothing
End Sub

Private Function DbQuery(migration, sql, params)
    On Error Resume Next
    Set DbQuery = migration.DB.Query(sql, params)
    If Err.Number = 0 Then
        On Error GoTo 0
        Exit Function
    End If
    Err.Clear
    On Error GoTo 0

    Dim conn, cmd, rs
    Set conn = migration.Connection
    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn
    cmd.CommandText = sql

    If IsArray(params) Then
        Set rs = cmd.Execute(, params)
    ElseIf IsEmpty(params) Then
        Set rs = cmd.Execute()
    Else
        Set rs = cmd.Execute(, Array(params))
    End If

    Set DbQuery = rs
    Set cmd = Nothing
End Function
%>