[CmdletBinding()]
Param(
   
   [Parameter(Mandatory=$True)]
   [emDash.Workflow.Step.Interface.PowershellContext]$ctx
)
 
try
{
    ####  UPDATE THESE VARIABLES FOR YOUR SPECIFIC ENDUR/FINDUR IMPLEMENTATION ####
    ####                                                                       ####
    ####  1.  Run this SQL Statement against your Endur/Findur Database        ####
    ####      select * from personnel_license_type                             ####
    ####                                                                       #### 
    ####  2.  Identify the License Type for the Licence Type you want to       ####
    ####      update from (e.g. 'Read Only')                                   ####
    ####                                                                       ####
    ####  3.  Identify the License Type for the Licence Type you want to       ####
    ####      update to (e.g. 'Full Access')                                   ####
    ####                                                                       ####
    
    $fromLicenceType =  'Read Only'     ## Update with result from Step 2 above
    $toLicenceType =  'Full Access'     ## Update with result from Step 3 above

    ###############################################################################


    $emdashStepName="EndurFindur_UpdateUserLicenseType"
    $emdashStepVersion="0.1"
 
    # Stop on error
    $ErrorActionPreference = 'Stop'
    $VerbosePreference='SilentlyContinue'
    $WarningPreference = 'SilentlyContinue'
 
    Import-Module Sqlps -DisableNameChecking
   
    $WarningPreference = 'Continue'
    $VerbosePreference='Continue'
              
 
    $envName = $ctx.TargetEnvironment.Name
    # get the Endur database details
    $endurDatabase = $ctx.TargetEnvironment.DatabaseAllocations | Where-Object {  $_.Databases.Type.ToLower() -eq "endur" } | Select -first 1
 
    $sqlServerInstance = $endurDatabase.Databases.ServerName
    $ctx.LogProgress("Endur database server is $sqlServerInstance")
 
    $endurDB = $endurDatabase.Databases.Name
    $ctx.LogProgress("Endur database name is $endurDB")

    if($ctx.TargetEnvironment.EnvType.ToLower() -ne "production")
    {                         
 
        $ctx.LogProgress("Getting license type IDs...")
        $sqlResult = Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database $endurDB -Query "select type_id as Answer from personnel_license_type where type_name = ‘$($fromLicenceType)‘"
        $fromLicenceTypeId = $sqlResult.Answer
        $ctx.LogProgress("Licence Type ID for $($fromLicenceType) is $($fromLicenceTypeId)")

        $sqlResult = Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database $endurDB -Query "select type_id as Answer from personnel_license_type where type_name = ’$($toLicenceType)’"
        $toLicenceTypeId = $sqlResult.Answer
        $ctx.LogProgress("Licence Type ID for $($toLicenceType) is $($toLicenceTypeId)")
        
        $ctx.LogProgress("Updating User Licence Types to be $($toLicenceType)")
        Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database $endurDB -Query "update pers_license_types_link set license_type = $($toLicenceTypeId) where license_type = $($fromLicenceTypeId)"

        $ctx.LogProgress("Checking result of SQL Command...")
        $sqlResult = Invoke-sqlcmd -ServerInstance $sqlServerInstance -Database $endurDB -Query "select count(*) as Answer from pers_license_types_link where license_type = $($fromLicenceTypeId)"

        if($sqlResult.Answer -eq 0)
        {
            $ctx.LogProgress("Number of users with $($fromLicenceType) License is 0 as expected.")
        }
        else
        {
            $ctx.LogError("Check $($fromLicenceType) License Types.  There are $($sqlResult.Answer) users with $($fromLicenceType) in the database.")
        }

    }
    else
    {
        $ctx.LogProgress("This is a PRODUCTION environment.  We will not update Users' License Types")
    }
 
    Return $True
}
catch [Exception]
{
    Write-Error ("Error: {0} {1}" -f $_.Exception.Message, $_.Exception.StackTrace)
               # returning False will set the step status to Failed
    Return $False
}