#Package location variables
$filename = $OctopusParameters['Octopus.Action[Deploy Nuget Package].Package.NuGetPackageId']
$fileversion = $OctopusParameters['Octopus.Action[Deploy Nuget Package].Package.NuGetPackageVersion']

#Connection Variables
#Set Target DB Name for Auto Deployment
if ($TargetDatabaseName -eq "") {
    $TargetDatabaseName = "$filename.$fileversion"
}

#Required for DB Versioning
$version = "$filename.$fileversion"
$release = $OctopusParameters['Octopus.Release.Number']
$user = $OctopusParameters['Octopus.Deployment.CreatedBy.Username']

#Get list of files
$files = Get-Childitem "$ExtractLocation\dbo\Scripts\Deployment\TestData\Item.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PriceBand.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PriceClass.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Block.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\CRC.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Entrance.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\GlobalSetting.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetConfig.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\MemberPriceClass.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Password.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PRC.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Client.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\License.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Venue.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Auditorium.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Layout.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Production.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\EventType.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Charge.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Event.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Tlog.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\MemberType.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\EventMemberTypeLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ClientType.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ClientTypeLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Price.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PriceClassClientTypeLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PriceClassMembershipLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Member.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\SeasonGroup.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\MemberPrice.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\MTlog.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Card.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\DirectDebit.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Booking.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Payment.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\BookingPayLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ProdSGLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\MemHistory.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\MemHPayLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PaymentProfile.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Finance.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\SGEventLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\TXEvent.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ChargeProfileType.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ChargeProfile.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ChargeCategory.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetPriceBand.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetPriceClass.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PackageX.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PackageXEventPriceClassLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Area.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\RowName.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ItemTag.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ItemTagLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\BestAvailArea.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ClientRatio.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ClientUpdateHistory.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetTransaction.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PanViewLog.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Permission.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\RptPayment.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UpsellItem.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UpsellPollerLog.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UsageTracking.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UsageTrackingGS.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UsageTrackingPrt.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UsageTrackingSys.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UsageTrackingVMO.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\UserLog.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\VMOption.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\VMPermList.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\WSID.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\SeatSkel.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\Seat.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\SeatHistory.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\BookingChargeLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetTransactionChargeProfileLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ContactHistory.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\BallotRegrets.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\DDInstall.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\ClientLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetBasket.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetBasketChargeProfileLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\InetBasketItem.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\MemHChargeLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\PaymentProfileInstalment.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\SEPriceClassMapLink.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\TMForward.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\TMReceive.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\TXTeamExchangeHistory.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\DEKStore.sql",
"$ExtractLocation\dbo\Scripts\Deployment\TestData\KEKStore.sql"

#Set GS key check SQL command
$GScheck="IF EXISTS(SELECT 1 FROM dbo.GlobalSetting WHERE GS_name = 'G_TestDataDeployed')
	BEGIN
		SELECT 1 AS [Res];
	END;
ELSE
	SELECT 0 AS [Res];"

#Set GS key insert SQL command
$GSinsert="INSERT INTO dbo.GlobalSetting
	(
		GS_global,GS_wsid,GS_name,GS_value
	)
VALUES (0, 0, N'G_TestDataDeployed', N'1');"

#Create a database connection
$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=$server;database=$targetDatabaseName;Integrated Security = False; User ID = admin; Password = Stubbsgat3;"

try {

    #Open SQL connection
    $SqlConnection.Open()

    #Check for existence of GS key
    $sqlCheckGS = $sqlConnection.CreateCommand()
    $sqlCheckGS.CommandText = $GScheck
    $sqlCheckGS.Connection = $SqlConnection
    $GScheckResult = $sqlCheckGS.ExecuteScalar()

    #Execute SQL if GS key is missing
    if($GScheckResult -ne $true) {

        #Loop through each of the files in the test data
        foreach ($file in $files) {

            $filename = $file.BaseName
            Write-Host "Currently deploying data to: "$filename

            #Execute SQL files on db
            $sqlExecFile = $sqlConnection.CreateCommand()
            $sqlExecFile.CommandText = Get-Content $file
			#Set timeout value in seconds
			$sqlExecFile.CommandTimeout = 2100
            $sqlExecFile.Connection = $SqlConnection
            $sqlReader = $sqlExecFile.ExecuteReader()
            $sqlReader.Close()

        }

        #Add GS key to GlobalSetting
        $sqlInsertGS = $sqlConnection.CreateCommand()
        $sqlInsertGS.CommandText = $GSinsert
        $sqlInsertGS.Connection = $SqlConnection
        $sqlReader = $sqlInsertGS.ExecuteReader()
        $sqlReader.Close()

    }

    else {

        Write-Host "Test data already deployed to $TargetDatabaseName"

    }

    #Close SQL connection
    $SqlConnection.Close()

}

catch {

    #Close SQL connection
    $sqlReader.Close()
    $SqlConnection.Close()
    Write-Host "FAILED TO EXECUTE: " $filename
	throw

}