#
# Assign "Storage Blob Data Contributor" role
#
$targetSubscriptionName = "xxx"
$targetSubscriptionId = (Get-AzSubscription -SubscriptionName $targetSubscriptionName).Id
#echo $targetSubscriptionId

$resourceGroupName = "xxx"
$storageAccountName = "xxx"
$containerName = "sql-backup-to-url-test"

$accountIdAsSignInName = ((Get-AzContext).Account).Id
#echo $accountIdAsSignInName

$containerScope = "/subscriptions/$targetSubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Storage/storageAccounts/$storageAccountName/blobServices/default/containers/$containerName"
#echo $containerScope

$targetRoleDefinitionName = "Storage Blob Data Contributor"

New-AzRoleAssignment `
-SignInName $accountIdAsSignInName `
-RoleDefinitionName $targetRoleDefinitionName `
-Scope $containerScope

#
#
# Get script path and check arguments json file
#
#
$scriptFolder = ""
if ($psISE) { # If using ISE
    $scriptFolder = Split-Path -Parent $psISE.CurrentFile.FullPath
} elseif($PSVersionTable.PSVersion.Major -gt 3) { # If Using PowerShell 3 or greater
    $scriptFolder = $PSScriptRoot
} else { # If using PowerShell 2 or lower
    $scriptFolder = split-path -parent $MyInvocation.MyCommand.Path
}

$argumentsFileNameWithExtension = 'script_arguments.json'
$argumentsFilePath = "$scriptFolder\$argumentsFileNameWithExtension"

if (!(Test-Path $argumentsFilePath)) {
  Write-Host "'$argumentsFileNameWithExtension' file is required in the same folder of PowerShell script"
  $key = Read-Host "Exit (y/n)?"
  if($key -eq "y") { 
    Exit
  } else {
    echo "Breaking the execution flow (returning...)"
    Return
  }
}



#
#
# Load values form arguments json file
#
#
$argumentsJsonObject = Get-Content -Path $argumentsFilePath | ConvertFrom-Json


#
#
# Define variables & assign values loaded from arguments json file
# Set default value if value is not present in arguments json file
#
#
$subscriptionName = $argumentsJsonObject.subscriptionName
if( [string]::IsNullOrEmpty($subscriptionName) ){
  $subscriptionName = 'demo-subscription'
}

$resourceGroupName = $argumentsJsonObject.resourceGroupName
if( [string]::IsNullOrEmpty($resourceGroupName) ){
  $resourceGroupName = 'demo-rg'
}

$storageAccountName = $argumentsJsonObject.storageAccountName
if( [string]::IsNullOrEmpty($storageAccountName) ){
  $storageAccountName = 'demosa'
}

$containerName = $argumentsJsonObject.containerName
if( [string]::IsNullOrEmpty($containerName) ){
  $containerName = 'demo-container'
}

# the name of the Stored Access Policy (policy that will be used to generate SAS)
$storedAccessPolicyName = $argumentsJsonObject.storedAccessPolicyName
if( [string]::IsNullOrEmpty($storedAccessPolicyName) ){
  $storedAccessPolicyName = 'demo-stored-access-policy'
}

$targetDbName = $argumentsJsonObject.targetDbName
if( [string]::IsNullOrEmpty($targetDbName) ){
  $targetDbName = 'demo-stored-access-policy'
}

$tsqlSnippetFileNameWithExtension = $argumentsJsonObject.tsqlSnippetFileNameWithExtension
if( [string]::IsNullOrEmpty($tsqlSnippetFileNameWithExtension) ){
  $tsqlSnippetFileNameWithExtension = 'demo_backup_to_url_tsql_snippet.sql'
}



#
#
# Interpolated variables
#
#
$storageContainerUrl = "https://$($storageAccountName).blob.core.windows.net/$containerName"
$backupFileNameWithExtension = "$($targetDbName)_backup_$(Get-Date -Format 'yyyyMMddHHmmss')"
$backupUrl = "$storageContainerUrl/$backupFileNameWithExtension"
$tsqlSnippetFilePath = "$scriptFolder\$tsqlSnippetFileNameWithExtension"



#
#
# Connect/Login to azure & select correct subscription
#
#
$Credential = Get-Credential
Connect-AzAccount -Credential $Credential

Set-AzContext -Subscription (Get-AzSubscription -SubscriptionName $subscriptionName).Id



#
#
# Create 'storage account context' (context will be used to create Stored Access Policy)
# Account keys are needed
#
#
$accountKeys = Get-AzStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName  
$storageContext = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys[0].value 



#
#
# Creates a new container if needed
#
# $container = New-AzStorageContainer -Context $storageContext -Name $containerName  
#
#
$container = Get-AzStorageContainer -Context $storageContext -Name $containerName
$cbc = $container.CloudBlobContainer



#
# 
# Create Stored Access Policy if does not exist, use existing policy if exists
# SAS will refer to Stored Access Policy by name
# 
# $existingPolicies = Get-AzStorageContainerStoredAccessPolicy -Container $containerName -Context $storageContext
#
#
$storedAccessPolicyForSasToken = ""
$existingPolicyWithSameName = Get-AzStorageContainerStoredAccessPolicy -Container $containerName -Policy $storedAccessPolicyName -Context $storageContext
$noPolicyWithSameName = (([string]::IsNullOrEmpty($existingPolicyWithSameName)) -eq $true)
if($noPolicyWithSameName){
    $storedAccessPolicyForSasToken = New-AzStorageContainerStoredAccessPolicy -Container $containerName -Policy $storedAccessPolicyName -Context $storageContext -ExpiryTime $(Get-Date).ToUniversalTime().AddYears(10) -Permission "rwld"
}



#
# Generate SAS
#
#
# $sas = New-AzStorageContainerSASToken -Policy $storedAccessPolicyName -Context $storageContext -Container $containerName
# Write-Host 'Shared Access Signature = '$($sas.Substring(1))''
#
#
$sasString = New-AzStorageContainerSASToken -Policy $storedAccessPolicyName -Context $storageContext -Container $containerName
$sasToken = "$($sasString.Substring(1))" # removing '?' in the token string



#
#
# T-SQL string for creating credential
#
#
$credentialName = $storageContainerUrl # SQL credential for T-SQL backup command
$credentialTsqlString = @"
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = '$($credentialName)')
BEGIN
    EXEC('CREATE CREDENTIAL [$($credentialName)] WITH IDENTITY = ''Shared Access Signature'', SECRET = ''$($sasToken)''')
END
"@



#
#
# T-SQL string for performing backup command
#
#
$backupTsqlString = @"
BACKUP DATABASE [$($targetDbName)] 
TO URL = N'$($backupUrl)' 
WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 1;
GO
"@



#
#
# Generate output string -> will be written to .sql file
#
#
$sqlScriptOutputString = @"

$($credentialTsqlString)

$($backupTsqlString)

"@



#
#
# Create snippet file it doed not exist -> you might need to open PowerShell as Admin
# (To save TSQL snippet)
#
#
if (!(Test-Path $tsqlSnippetFilePath)) {
  New-Item -ItemType File -Path $scriptFolder -Name $tsqlSnippetFileNameWithExtension
}



#
#
# Now, save TSQL snippet to file
#
#
$sqlScriptOutputString > $tsqlSnippetFilePath