# Variables
$tenantId = "7844775a-a9cc-4c33-a5ae-36dcf6660f45" #Galenica
$clientId = "d28076dd-2108-4718-802e-cd3c35fd5505" #pcpl-BAGSpezListePrd-DBBackup
$vaultName="kv-BAGSpezListePrd-bkp"
$secretName="superuser"

$maxRetries = 5
$retryInterval = 10 # seconds
$retryCount = 0
$success = $false

#Managed instance related
$MILogin='superuser'
$MIPwd=$null #fetched from key vault later
$MIInstance="sqlmi-bagspezlisteprd-sqlinstance.75ff9425ac13.database.windows.net"

function log-out{
    [CmdletBinding()] 
    param(   
        [Parameter(Mandatory=$true)]
        [string] $msg
    )
    Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") :: $msg"
}

# Log in to Azure
$retryCount = 0
$success = $false

while (-not $success -and $retryCount -lt $maxRetries) {
    try{
        $null = Connect-AzAccount -ServicePrincipal -ApplicationId $clientId -Tenant $tenantId -Subscription BAGSpezListePrd -CertificateThumbprint 7bd45f67999015c7742db25efc86bae97590c57d  
        $success=$true  
    }
    catch{
        $retryCount++
        Start-Sleep -Seconds $retryInterval
    }
}

if(-not $success){
    write-error "Error auhthenticating to azure"
    exit 1
}
  
#fetch the Managed Instance password
$retryCount = 0
$success = $false

while (-not $success -and $retryCount -lt $maxRetries) {
    try{
        $MIPwd = Get-AzKeyVaultSecret -VaultName $vaultName -Name $secretName -AsPlainText 
        $success=$true
    }
    catch{
        $retryCount++
        Start-Sleep -Seconds $retryInterval
    }
}

if(-not $success){
    write-error "Error fetching password from key vault"
    exit 1
}
  
#craft credential for MI connection
$securePassword = ConvertTo-SecureString $MIPwd -AsPlainText -Force
$credentialMI = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $MILogin, $securePassword

#start sl2007.daily_batch_update
log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") starting sl2007 daily update"
$query="
exec sl2007.dbo.usp_Daily_Batch_Update
"
$retryCount = 0
$success = $false

while (-not $success -and $retryCount -lt $maxRetries) {
    try{
        Invoke-Sqlcmd -ServerInstance $MIInstance -Query $query -Credential $credentialMI
        log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") sl2007 daily update done"
        $success=$true
    }
    catch{
        $retryCount++
        Start-Sleep -Seconds $retryInterval
    }
}

if(-not $success){
    write-error "Error starting usp_Daily_Batch_Update"
    exit 1
}

#start sl2007_superset generation
log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") starting sl2007_superset generation"
$query="
exec sl2007_superset.dbo.p_Fill__BAG__SL2007_Superset
"
$retryCount = 0
$success = $false

while (-not $success -and $retryCount -lt $maxRetries) {
    try{
        Invoke-Sqlcmd -ServerInstance $MIInstance -Query $query -Credential $credentialMI
        log-out -msg "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") sl2007_superset generation done"
        $success=$true
    }
    catch{
        $retryCount++
        Start-Sleep -Seconds $retryInterval
    }
}

if(-not $success){
    write-error "Error starting sl2007_suèerset production"
    exit 1
}