#
# Pushes newly created imaging sessions to an IceBear instance.
#
# For this to work, you will need an IceBear API key with "FormulatrixImport" scope. Go to your IceBear's
# administration page and open the "API Keys" tab to create one. You should lock it down to accept requests
# only from the server on which this PowerShell script is running.
#
# See config.ini for configuration, including the API key.


Remove-Variable * -ErrorAction SilentlyContinue; Remove-Module *; $error.Clear(); Clear-Host


class Log {
    static $LogLevel="INFO"

    static [void]setLogLevel($level){
        if("DEBUG" -ne $level -and "INFO" -ne $level -and "WARN" -ne $level -and "ERROR" -ne $level){
            throw "Tried to set log level to $level, should be one of DEBUG, INFO, WARN, ERROR"
        }
        [Log]::LogLevel=$level
    }

    static [void]debug([string]$msg){
        if("DEBUG" -eq [Log]::LogLevel){
            [Log]::write($msg, "DEBUG")
        }
    }

    static [void]info([string]$msg){
        if("ERROR" -ne [Log]::LogLevel -and "WARN" -ne [Log]::LogLevel){
            [Log]::write($msg, "INFO ")
        }
    }

    static [void]warn([string]$msg){
        if("ERROR" -ne [Log]::LogLevel){
            [Log]::write($msg, "WARN ")
        }
    }
    static [void]error([string]$msg){
            [Log]::write($msg, "ERROR")
    }

    static [void]write($msg, $level){
            $Now=Get-Date -Format "yyyy-MM-dd HH:mm:ss"
            $Now=$Now.Replace(".",":")
            Write-Host "$level $Now $msg"
    }
}

class Db {
    static $Inited=0
    static $Instance
    static $rmDbName
    static $riDbName
    static $Connection

    static init($Config){
        if("1" -eq $Config.SQLUseIntegratedSecurity){
            [Db]::init($Config.SqlInstance, $Config.RockMakerDatabaseName, $Config.RockImagerDatabaseName)
        } else {
            [Db]::init($Config.SqlInstance, $Config.RockMakerDatabaseName, $Config.RockImagerDatabaseName, $Config.SQLUsername, $Config.SQLPassword)
        }
    }

    static init($Instance, $RockMakerDbName, $RockImagerDbName){
        if($RockMakerDbName -match "[^a-zA-z0-9_-]"){
            throw "Rock Maker database name not allowed: $RockMakerDbName";
        }
        if($RockImagerDbName -match "[^a-zA-z0-9_-]"){
            throw "Rock Imager database name not allowed: $RockImagerDbName";
        }
        [Db]::Instance=$Instance
        [Db]::rmDbName="[$RockMakerDbName]"
        [Db]::riDbName="[$RockImagerDbName]"
        [Db]::Connection = New-Object System.Data.SqlClient.SqlConnection
        [Db]::Connection.ConnectionString = "Server=$Instance;Integrated Security=True;"
        [Db]::Connection.Open()
        [Db]::Inited=1
    }

    static init($Instance, $RockMakerDbName, $RockImagerDbName, $Username, $Password){
        [Db]::Instance=$Instance
        [Db]::rmDbName="[$RockMakerDbName]"
        [Db]::riDbName="[$RockImagerDbName]"
        [Db]::Connection = New-Object System.Data.SqlClient.SqlConnection
        [Db]::Connection.ConnectionString = "Server=$Instance;Integrated Security=False;UID=$Username;PWD=$Password"
        [Db]::Connection.Open()
        [Db]::Inited=1
    }

    static close(){
        [Db]::Connection.Close()
    }

    static [psObject]riQuery($SqlQuery, $Parameters){
        [Log]::debug("In Db::riQuery")
        return [Db]::query("USE "+[Db]::riDbName+" "+$SqlQuery, $Parameters)
    }

    static [psObject]rmQuery($SqlQuery, $Parameters){
        [Log]::debug("In Db::rmQuery")
        return [Db]::query("USE "+[Db]::rmDbName+" "+$SqlQuery, $Parameters)
    }

    static [PSObject]query($SqlQuery, $Parameters){
        if(1 -ne [Db]::Inited){
            throw "Call [Db]::init before first call to [Db]::query"
        }
        [Log]::debug("In Db::query")

        $Command = [Db]::Connection.CreateCommand()
        foreach ($k in $Parameters.Keys) {
            if( $($Parameters.Item($k)) -notmatch "^[\w\s:-]*$" ){
                 throw "Bad value $($Parameters.Item($k)) for parameter $k in Db::query"
            }
            $Command.Parameters.AddWithValue($k, $Parameters.Item($k))
        }
        $Command.CommandText = $SqlQuery
        $StartTime=$null
        if("DEBUG" -eq [Log]::LogLevel){
            $StartTime=Get-Date
        }
        $Result = $Command.ExecuteReader()
        if("DEBUG" -eq [Log]::LogLevel){
            $EndTime=Get-Date
            $Duration=New-TimeSpan -Start $StartTime -End $EndTime
            [Log]::debug("Took $Duration to execute $SqlQuery")
        }
        $Table = New-Object -TypeName System.Data.DataTable
        $Table.Load($Result)
        return $Table
    }

}

class IceBear {

    static $apiRoot
    static $apiKey
    static $MaxImageBufferLength

    static [void]init($apiRoot, $apiKey, $MaxImageBufferLength){
        [IceBear]::apiRoot=$apiRoot
        [IceBear]::apiKey=$apiKey
        [IceBear]::MaxImageBufferLength=$MaxImageBufferLength
    }


    static [PSObject]apiRequest($ApiMethod, $Parameters){
        return [IceBear]::doApiRequest($ApiMethod, $Parameters, 0)
    }
    static [PSObject]doApiRequest($ApiMethod, $Parameters, $retryCount){
        [Log]::debug("In IceBear::apiRequest")
        $maxRetries=3
        $Headers=@{
            "X-IceBear-API-Key"=[IceBear]::apiKey
        }

        try {
            $TimeString=[Math]::Round((Get-Date).ToFileTime()/10000)
            $Uri=[IceBear]::apiRoot+"/device/FormulatrixImport/"+$ApiMethod+"?t="+$TimeString
            [Log]::debug("$Uri")
            $RequestBody=ConvertTo-Json $Parameters -Compress -Depth 5
            if($ApiMethod -eq "createImage"){
                $Parameters["image"]="(base64-encoded image)"
                $Parameters["thumb"]="(base64-encoded image)"
                $LogBody=ConvertTo-Json $Parameters -Compress -Depth 5
                [Log]::debug("Request body:$LogBody")
            } elseif($ApiMethod -eq "createImages"){
                $Parameters["images"] | ForEach-Object {
                    $_["image"]="(base64-encoded image)"
                    $_["thumb"]="(base64-encoded image)"
                }
                $LogBody=ConvertTo-Json $Parameters -Compress -Depth 5
                [Log]::debug("Request body:$LogBody")
            } else {
                [Log]::debug("Request body: $RequestBody")
            }
            $StartTime=Get-Date
            $RequestBody=[System.Net.WebUtility]::UrlEncode($RequestBody)
            $Result = Invoke-WebRequest -Uri $Uri -Headers $Headers -Method POST -Body $RequestBody
            $EndTime=Get-Date
            $Duration=New-TimeSpan -Start $StartTime -End $EndTime
            if(!$Result){
                throw "No response (Result is empty after Invoke-WebRequest"
            } elseif(!$Result.Content){
                throw "No response content (Result.Content is empty after Invoke-WebRequest"
            }
            [Log]::debug("Response: "+$Result.Content)
            [Log]::debug("Took $Duration to execute IceBear API request")
            $ret=ConvertFrom-Json($Result.Content)
            if($ret["error"]){
                [Log]::error("IceBear API Error"+$ret["error"])
                throw new Exception($ret["error"])
            }
            return $ret;
        } catch {
            [Log]::error("Caught exception in apiRequest")
            if($_.Exception.Response -eq $Null){
                [Log]::error("No response body from IceBear")
                if($retryCount -ge $maxRetries){
                    [Log]::error("Retry count exceeded, throwing exception")
                    throw $_
                }
                $retryCount+=1
                [Log]::error("Retrying request - retry $retryCount of $maxRetries")
                return [IceBear]::doApiRequest($ApiMethod, $Parameters, $retryCOunt)
            } else {
                $result = $_.Exception.Response.GetResponseStream()
                $reader = New-Object System.IO.StreamReader($result)
                $reader.BaseStream.Position = 0
                $reader.DiscardBufferedData()
                $responseBody = $reader.ReadToEnd();
                [Log]::error('4xx/5xx from IceBear')
                [Log]::error("Response body: "+$responseBody)
                throw $_
            }
        }
    }

    static [PSObject]getImagedTimeAndImageCountForImagingTask($TaskId){
        [Log]::debug( "In IceBear::getImagedTimeAndImageCountForImagingTask")
        $Result=[IceBear]::apiRequest("getImagingTaskByFormulatrixId", @{ "id"=$TaskId } )
        return $Result
    }

    static [PSObject]getImagedTimeAndImageCountForLatestImagingTask(){
        [Log]::debug( "In IceBear::getImagedTimeAndImageCountForLatestImagingTask")
        return [IceBear]::apiRequest("getLatestImagingTask", @{} )
    }

    static [PSObject]getFormulatrixScores(){
        [Log]::debug( "In IceBear::getFormulatrixScores")
        $Scores=[Fx]::getScores();
        $Rows=@()
        ForEach ($Row in $Scores){
            $Rows+=@{ "color"=$Row.Color; "hotkey"=$Row.HotKey; "scoreindex"=$Row.HotKey; "name"=$Row.Name }
        }
        $Result=[IceBear]::apiRequest("createScores", @{"scores"=$Rows})
        return $Result
    }

    static $ScoringSystemId
    static [int]getScoringSystemId(){
        [Log]::debug( "In IceBear::getScoringSystemId")
        if(![IceBear]::ScoringSystemId){
            [Log]::debug( "IceBear::getScoringSystemId is not set, retrieving...")
        }
        return [IceBear]::ScoringSystemId
    }

    static $Plates=@{}
    static [PSObject]createPlate($Plate){
        [Log]::debug( "In IceBear::createPlate")
        $Barcode=$Plate["barcode"];
        if(!$Barcode){
            [Log]::warn( "Plate barcode is empty - not creating plate")
            return $null
        }
        [Log]::debug( "Plate barcode is "+$Barcode)
        $key="plate$Barcode"
        if([IceBear]::Plates.ContainsKey($key)){
           [Log]::debug( "Already have the plate, exists in IceBear, not looking it up")
            return [IceBear]::Plates[$key]
        }
        [Log]::debug( "Looking up the plate in IceBear")
        $Result=[IceBear]::apiRequest("createPlate", $Plate)
        [IceBear]::Plates.add($key,$Result)
        return $Result
    }

    static $ProfileVersions=@{}
    static [PSObject]createImagingSettings($Settings, $Imager, $ProfileName, $ProfileId, $ProfileVersionId){
        [Log]::debug( "In IceBear::createImagingSettings")
        $key="version$ProfileVersionId"+"imager"+$Imager["Name"]
        if([IceBear]::ProfileVersions.ContainsKey($key)){
           [Log]::debug( "Already have the ImagingSettings, exists in IceBear, not looking it up")
            return [IceBear]::ProfileVersions[$key]
        }
        [Log]::debug( "Looking up the ImagingSettings in IceBear")
        $ImagingSettings=@{
            "captureProfile"=@{
                "name"=$ProfileName;
                "id"=$ProfileId;
                "currentVersionId"=$ProfileVersionId;
            };
            "imager"=$Imager
        }
        $ImagingSettings["captureProfile"]["settings"]=@{}
        $Settings=[Fx]::getSettingsForCaptureProfileVersionAndImager($ProfileVersionId, $Imager["name"])
        if($Settings -ne $Null){
            $Settings | ForEach-Object {
                $ImagingSettings["captureProfile"]["settings"].Add($_.SettingName,$_.SettingValue)
            }
        }
        $Result=[IceBear]::apiRequest("createImagingSettings", $ImagingSettings)
        [IceBear]::ProfileVersions.add($key,$Result)
        return $Result
    }

    static $ImagingSessions=@{}
    static [PSObject]createImagingSession($ImagingSession){
        [Log]::debug("In createImagingSession")
        $ExistingName="task"+$ImagingSession["imagingTaskId"]+"version"+$ImagingSession["captureProfileVersionId"]
        if([IceBear]::ImagingSessions.ContainsKey($ExistingName)){
           [Log]::debug( "Already have the ImagingSession, exists in IceBear, not looking it up")
            return [IceBear]::ImagingSessions[$ExistingName]
        }
        [Log]::debug( "Looking up the ImagingSession in IceBear")
        $Result=[IceBear]::apiRequest("createImagingSession", $ImagingSession)
        if($null -eq $Result){
            throw "createImagingSession: Result was null"
        } elseif($null -eq $Result.id){
            throw "createImagingSession: Result.id was null"
        }
        [IceBear]::ImagingSessions.add($ExistingName,$Result)
        [Log]::info("Found/created IceBear imagingsession. Light type: "+$Result.lighttype)
        return $Result
    }

    static [PSObject]createImage($Image){
        [Log]::debug("In createImage")
        $Result=[IceBear]::apiRequest("createImage", $Image)
        return $Result
    }

    static [PSObject]createImages($Images){
        [Log]::debug("In createImages")
        $Result=[IceBear]::apiRequest("createImages", @{"images"=$Images })
        return $Result
    }

    static $ImageBuffer=@{}
    static [void]queueImage($Image){
        [Log]::debug("In queueImage")
        if(![IceBear]::ImageBuffer["images"]){
            [IceBear]::ImageBuffer["images"]=@()
        }
        [IceBear]::ImageBuffer["images"]+=$Image
        if([IceBear]::ImageBuffer["images"].count -lt [IceBear]::MaxImageBufferLength){
            [Log]::debug("returning from queueImage, buffer length not reached")
            return
        }
        [Log]::debug("Image buffer length reached, sending to IceBear")
        [IceBear]::flushImageBuffer()
    }
    static[void]flushImageBuffer(){
        [Log]::debug("In flushImageBuffer")
        if(0 -eq ([IceBear]::ImageBuffer["images"].Count)){
            [Log]::warn("FlushImageBuffer: No images in buffer")
            return
        }
        $Result=[IceBear]::createImages([IceBear]::ImageBuffer["images"])
        [IceBear]::ImageBuffer["images"]=@()
        [Log]::debug("Image buffer flushed")
    }

    static [void]updateImagerInventories($Inventories){
        [Log]::debug("In updateImagerInventories")
        [IceBear]::apiRequest("updateImagerInventories", @{"imagers"=$Inventories})
    }

    static[void]updateDiskSpaces($Disks){
        [Log]::debug("In updateDiskSpaces")
        [IceBear]::apiRequest("updateDiskSpaces", @{"drives"=$Disks})
    }

    static[PSObject]getPlateForReImport(){
        [Log]::debug("In getPlateForReImport")
        return [IceBear]::apiRequest("getPlateForReImport", @{})
    }

} # End IceBear

class Fx {

    static $WholeDropRegionTypeId
    static $ExtendedFocusImageTypeId

    static [void]init(){
        [Fx]::WholeDropRegionTypeId=[Fx]::getRegionTypeIdForWholeDropImages()
        [Fx]::ExtendedFocusImageTypeId=[Fx]::getImageTypeIdForExtendedFocusImages()
    }

    # Determines the import start date/time for this run.
    # Queries IceBear for the date/time of the latest Formulatrix imaging session, using this if available.
    # If there is no IceBear imaging session, IceBear has a clean database, so a start date must be defined
    # in the INI file, otherwise the importer will stop. This is because we may have a decade-old
    # RockMaker and only want to import recent plates.
    #
    static [string]getImportStartDateTime($iniStartDate, $ClockFudgeMinutes){
        [Log]::debug( "In fx::getImportStartDateTime")
        $IbTask=[IceBear]::getImagedTimeAndImageCountForLatestImagingTask()
        [Log]::debug("IbTask: $IbTask")
        $IbTaskId=$IbTask.fxImagingTaskId
        $ibTaskDateTime=$IbTask.dateTime

        [datetime]$StartDate=New-Object DateTime
        if($IbTaskId -eq "0") {
            [Log]::info("IceBear has no ImagingTasks, need date import")
            if ([DateTime]::TryParseExact($IniStartDate, "yyyy-MM-dd",[System.Globalization.CultureInfo]::InvariantCulture,
                                      [System.Globalization.DateTimeStyles]::None,
                                      [ref]$StartDate)){
                [Log]::info("Found start date from config: $StartDate")
                return $StartDate.ToString("yyyy-MM-dd")
            } else {
               throw "Bad or missing ImportStartDate ["+$IniStartDate+"]"
            }
        } else {
            [Log]::info("Icebear latest imaging task is $IbTaskId")
            [Log]::info("Icebear task date/time is $ibTaskDateTime")
            if ([DateTime]::TryParseExact($IbTaskDateTime, "yyyy-MM-dd HH:mm:ss",[System.Globalization.CultureInfo]::InvariantCulture,
                                      [System.Globalization.DateTimeStyles]::None,
                                      [ref]$StartDate)){
                [Log]::info("Subtracting $ClockFudgeMinutes minutes (account for DST change, clock drift, incomplete imports, etc.)")
                $StartDate=$StartDate.AddMinutes(-1*$ClockFudgeMinutes)
                $StartDateTime=$StartDate.ToString("yyyy-MM-dd HH:mm:ss").Replace(".",":")
                [Log]::info("Will import imaging tasks after $StartDateTime")
                return $StartDateTime
            } else {
               throw "Bad IceBear date/time string ["+$IbTaskDateTime+"]"
            }
        }

    }

    # Returns the first N ImagingTasks after ($CutoffDateTime minus $ClockFudgeMinutes).
    # Time zone: For preventing very recent inspections from importing, comparing (local) DateImaged to (local) GETDATE() is good enough.
	# However, we need to convert the GMT last imaged date in the LIMS to RockMaker database local time when deciding what to import.
    # The "State=6" means that the ImagingTask is "Completed" - not Cancelled, Skipped, or in any of the "in progress" states.
    static [PSObject]getImagingTasks($CutoffDateTime, $MaxImagingTasksPerRun){
        [Log]::debug( "In Fx::getImagingTasks")
        $MaxImagingTasksPerRun=[int]$MaxImagingTasksPerRun
        $Sql="SELECT ImagingTask.*, IncubationTemp.Temperature AS Temperature
            FROM ImagingTask, ExperimentPlate, Plate, Experiment, IncubationTemp
		    WHERE DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), DateImaged) >=@dateimaged
            AND ImagingTask.State=6
		    AND DateImaged<=DATEADD(minute, -5, GETDATE())
            AND ExperimentPlate.ID=ImagingTask.ExperimentPlateId
            AND ExperimentPlate.PlateID=Plate.ID
            AND Plate.ExperimentID=Experiment.ID
            AND Experiment.IncubationTempID=IncubationTemp.ID
            ORDER BY DateImaged ASC
            OFFSET 0 ROWS FETCH NEXT "+$MaxImagingTasksPerRun+" ROWS ONLY
            "
        return [Db]::rmQuery($Sql, @{ "@dateimaged"=$CutoffDateTime;  })
    }

    # Returns the first N ImagingTasks after ($CutoffDateTime minus $ClockFudgeMinutes).
    # Time zone: For preventing very recent inspections from importing, comparing (local) DateImaged to (local) GETDATE() is good enough.
	# However, we need to convert the GMT last imaged date in the LIMS to RockMaker database local time when deciding what to import.
    # The "State=6" means that the ImagingTask is "Completed" - not Cancelled, Skipped, or in any of the "in progress" states.
    static [PSObject]getImagingTasksForPlate($PlateBarcode){
        [Log]::debug( "In Fx::getImagingTasksForPlate")
        $Sql="SELECT ImagingTask.*, IncubationTemp.Temperature AS Temperature
            FROM ImagingTask, ExperimentPlate, Plate, Experiment, IncubationTemp
		    WHERE ImagingTask.State=6
		    AND DateImaged<=DATEADD(minute, -5, GETDATE())
            AND ExperimentPlate.ID=ImagingTask.ExperimentPlateId
            AND ExperimentPlate.PlateID=Plate.ID
            AND Plate.ExperimentID=Experiment.ID
            AND Experiment.IncubationTempID=IncubationTemp.ID
            And Plate.Barcode=@barcode
            ORDER BY DateImaged ASC
            "
        return [Db]::rmQuery($Sql, @{ "@barcode"=$PlateBarcode;  })
    }

    static[int]getRegionTypeIdForWholeDropImages(){
        [Log]::debug( "In Fx::getRegionTypeIdForWholeDropImages")
        $Result=[Db]::rmQuery("SELECT ID FROM RegionType WHERE Name='Drop'", @{} );
        [Log]::debug( $Result )
        $RegionTypeId=$Result.ID
        [Log]::debug( "Region type ID for whole-drop images: $RegionTypeId")
        return $RegionTypeId
    }

    static[int]getImageTypeIdForExtendedFocusImages(){
        [Log]::debug( "In Fx::getImageTypeIdForExtendedFocusImages")
        $Result=[Db]::rmQuery("SELECT ID FROM ImageType WHERE ShortName='ef'", @{} );
        [Log]::debug( $Result )
        $ImageTypeId=$Result.ID
        [Log]::debug( "Image type ID for extended-focus images: $ImageTypeId")
        return $ImageTypeId
    }


    static[PSObject]getImagesForImagingTask($TaskId){
        [Log]::debug( "In Fx::getImagesForImagingTask")
        return [Db]::rmQuery("
            SELECT ImageStore.BasePath, ImageStore.Name AS ImageStoreName, ImageBatch.ID AS ImageBatchID, CaptureResult.ID AS CaptureResultID,
			    CaptureProfileVersion.CaptureProfileID AS CaptureProfileID, CaptureProfileVersion.ID AS CaptureProfileVersionID,
			    Region.ID AS RegionID, Plate.Barcode, Well.PlateID, Well.WellNumber, Well.RowLetter, Well.ColumnNumber, WellDrop.DropNumber,
			    Image.PixelSize, CaptureProfile.Name AS CaptureProfileName
		    FROM Image, ImageStore, CaptureResult, CaptureProfile, CaptureProfileVersion, ImageBatch, ImagingTask, Region, WellDrop, Well, Plate
		    WHERE Image.ImageTypeID=@imagetypeid
			    AND Image.ImageStoreID=ImageStore.ID
			    AND Image.CaptureResultID=CaptureResult.ID
			    AND CaptureResult.ImageBatchID=ImageBatch.ID
			    AND CaptureResult.RegionID=Region.ID
			    AND Region.RegionTypeID=@regiontypeid
			    AND CaptureResult.CaptureProfileVersionID=CaptureProfileVersion.ID
		        AND CaptureProfileVersion.CaptureProfileID=CaptureProfile.ID
			    AND Region.WellDropID=WellDrop.ID
			    AND WellDrop.WellID=Well.ID
			    AND Well.PlateID=Plate.ID
			    AND ImageBatch.ImagingTaskID=ImagingTask.ID
			    AND ImagingTask.ID=@taskid
		    ORDER BY CaptureProfileID, Well.ID ASC
            ", @{
                "@taskid"=$TaskId;
                "@imagetypeid"=([Fx]::ExtendedFocusImageTypeId);
                "@regiontypeid"=([Fx]::WholeDropRegionTypeId);
            }
        )
    }

    static [String]getPlateBarcodeForImagingTask($TaskId){
        [Log]::debug( "In Fx::getPlateBarcodeForImagingTask")
        $Result=[Db]::rmQuery("
            SELECT Plate.Barcode AS Barcode
			FROM ImagingTask, ExperimentPlate, Plate
			WHERE ImagingTask.ExperimentPlateID=ExperimentPlate.ID
			AND ExperimentPlate.PlateID=Plate.ID
			AND ImagingTask.ID=@taskid
            ", @{
                "@taskid"=$TaskId
            }
        )
        return $Result.Barcode
    }

    static [PSObject]getScores(){
        [Log]::debug( "In Fx::getScores")
        $Result=[Db]::rmQuery("SELECT WellDropScore.Name AS Name, WellDropScore.HotKey AS HotKey, WellDropScore.Color AS Color
			FROM WellDropScore, WellDropScoreGroup
			WHERE WellDropScore.WellDropScoreGroupID=WellDropScoreGroup.ID
			AND WellDropScoreGroup.Name=@scoreGroupName
			ORDER BY OrderNumber ASC",
        @{
            "@scoreGroupName"="Default"
        })
        return $Result
    }

    static [PSObject]getPlateByExperimentPlateId($ExperimentPlateId){
        [Log]::debug( "In Fx::getPlateByExperimentPlateId($ExperimentPlateId)")
        $Result=[Db]::rmQuery("SELECT Plate.Barcode AS PlateBarcode,
                    Users.Name AS UserFullName, Users.EmailAddress AS UserEmail,
                    Containers.Name AS PlateTypeName, Containers.NumRows AS PlateTypeRows,
                    Containers.NumColumns AS PlateTypeColumns, Containers.MaxNumDrops AS PlateTypeDrops
                FROM ExperimentPlate,Plate,Experiment,Users,Containers
                WHERE ExperimentPlate.PlateID=Plate.ID
                    AND Experiment.ID=Plate.ExperimentID
                    AND Experiment.UserID=Users.ID
                    AND Experiment.ContainerID=Containers.ID
                    AND ExperimentPlate.ID=@ExperimentPlateID
            ",@{
                "@ExperimentPlateId"=$ExperimentPlateId
        });
        $Ret=@{}
        ForEach($Row in $Result){
            $Ret=@{
                "barcode"=$Row.PlateBarcode;
                "scoringSystemName"="Formulatrix";
                "plateType"=@{
                    "name"=$Row.PlateTypeName;
                    "rows"=$Row.PlateTypeRows;
                    "columns"=$Row.PlateTypeColumns;
                    "subpositions"=$Row.PlateTypeDrops;
                };
                "owner"=@{
                    "email"=$Row.UserEmail;
                    "fullName"=$Row.UserFullName;
                }
            }
        [Log]::debug($Row.UserFullName)
        [Log]::debug([uri]::EscapeDataString($Row.UserFullName)
)
        }
        return $Ret
    }

    static [int]getImageBatchIdForImagingTaskId($imagingTaskId){
        [Log]::debug("In getImageBatchIdForImagingTaskId($imagingTaskId)")
        $Result=[Db]::rmQuery(
            "SELECT ID FROM ImageBatch WHERE ImagingTaskID=@taskid",
            @{ "@taskid"=$imagingTaskId }
        )
        $batchId=0;
        $Result | ForEach-Object {
            [Log]::debug("Batch ID is "+ $_.ID);
            $batchId=$_.ID
        }
        if($batchId -eq 0){
            throw "Could not determine ImageBatchID for ImagingTaskID $imagingTaskID"
        }
        return $batchId
    }

    static $TaskIdToImagerName=@{}
    static [String]getImagerNameForImagingTask($imagingTaskId, $plateBarcode){
        [Log]::debug( "In getImagerNameForImagingTask")
        [Log]::debug( "imagingTaskId=$imagingTaskId, plateBarcode=$plateBarcode")

        $key="task"+$imagingTaskId
        if([Fx]::TaskIdToImagerName.ContainsKey($key)){
           [Log]::debug( "Already have the imager, not looking it up")
            return [Fx]::TaskIdToImagerName[$key]
        }

        $imagerName="";
        $imageBatchId=[Fx]::getImageBatchIdForImagingTaskId($imagingTaskId)
        [Log]::debug( "imagingTaskId=$imagingTaskId, plateBarcode=$plateBarcode, imageBatchId=$imageBatchId")


        #First check the imaging log
        [Log]::debug( "getImagerNameForImagingTask: Checking the imaging log")
        $Result=[Db]::riQuery("SELECT Robot.Name AS RobotName
				FROM ImagingLog, Robot
				WHERE ImagingLog.RobotID=Robot.ID
					AND ImagingLog.PlateBarcode=@barcode
					AND ImagingLog.InspectionID=@batchid
            ",@{
                "@barcode"=$plateBarcode;
                "@batchid"=$imageBatchId
            }
        )
        $Result | ForEach-Object {
            [Log]::debug("Imager name is "+ $_.RobotName);
            $imagerName=$_.RobotName
        }
        if("" -ne $imagerName){
            [Log]::debug( "getImagerNameForImagingTask: Found name in imaging log, name is "+$imagerName)
            [Fx]::TaskIdToImagerName[$key]=$imagerName
            return $imagerName
        }

        #See if the plate is in an imager right now, and assume same
        [Log]::debug( "getImagerNameForImagingTask: Checking whether plate is in an imager right now")
        $Result=[Db]::riQuery("SELECT Robot.Name AS RobotName
				FROM Plate, Address, PlateAddress, Robot
				WHERE Address.RobotID=Robot.ID
					AND PlateAddress.AddressID=Address.ID
					AND PlateAddress.PlateID=Plate.ID
					AND Plate.Barcode=@barcode
            ",@{
                "@barcode"=$plateBarcode
            }
        )
        $Result | ForEach-Object {
            [Log]::debug("Imager name is "+ $_.RobotName);
            $imagerName=$_.RobotName
        }
        if("" -ne $imagerName){
            [Log]::debug( "getImagerNameForImagingTask: Found plate in imager, imager name is "+$imagerName)
            [Fx]::TaskIdToImagerName[$key]=$imagerName
            return $imagerName
        }

        #OK, I give up.
        [Log]::debug( "getImagerNameForImagingTask: Failed to find imager name")
        return ""
    }

    static $SettingsByImagerAndVersion=@{}
    static [PSObject]getSettingsForCaptureProfileVersionAndImager($CaptureProfileVersionId, $ImagerName){
        [Log]::debug( "In Fx::getSettingsForCaptureProfileVersionAndImager")
        $key="v"+$CaptureProfileVersionId+"imager"+$ImagerName
        if([Fx]::SettingsByImagerAndVersion.ContainsKey($key)){
           [Log]::debug( "Already have the imaging settings, not looking them up")
            return [Fx]::SettingsByImagerAndVersion[$key]
        }
        $Result=[Db]::rmQuery("
            SELECT CaptureProperty.Name as SettingName, CaptureProperty.Value as SettingValue
            FROM CaptureProperty, Imager
            WHERE Imager.ID=CaptureProperty.RobotID
            AND Imager.Name=@imagername
            AND CaptureProperty.CaptureProfileVersionId=@versionid
        ", @{
            "@versionid"=$CaptureProfileVersionId;
            "@imagername"=$ImagerName
        })
        [Fx]::SettingsByImagerAndVersion[$key]=$Result
        return $Result;
    }

    static $rowLetterToRowNumber=@{
        "A"=1; "B"=2; "C"=3; "D"=4; "E"=5; "F"=6; "G"=7; "H"=8;
    }

    static [PSObject]getImagers(){
        [Log]::debug("in getImagers")
        $Imagers=[Db]::riQuery("SELECT ID, Name FROM Robot WHERE Active=1",@{});
        return $Imagers
    }

    static [PSObject]getImagerInventories(){
        [Log]::debug("in getImagerInventories")
        $rm=[Db]::rmDbName
        $ri=[Db]::riDbName
        $Imagers=[Fx]::getImagers()
        $Inventories=@()
        $Imagers | ForEach-Object {
            $Imager=$_
            if($Imager.Name.StartsWith("RI1-") -or $Imager.Name.StartsWith("RI2-")){
                [Log]::debug("Not getting inventory for small imager "+$Imager.Name)
                return #from this iteration, go to next imager
            }
            [Log]::debug("Getting inventory for imager "+$Imager.Name)
            # Don't include Skipped (state 1), Completed (6), or Cancelled (7) imaging tasks
            $InventoryRows=[Db]::riQuery("
                SELECT riPlate.Barcode AS Barcode,
    	    	    Users.Name AS Owner,
    	    	    CONVERT(varchar(25), MIN(ImagingTask.DateToImage), 120) AS NextInspection,
        		    CONVERT(varchar(25), MAX(ImagingTask.DateToImage), 120) AS FinalInspection,
    	        	COUNT(ImagingTask.DateToImage) AS InspectionsRemaining
	    	    FROM $ri.dbo.Plate AS riPlate,
    	        	$ri.dbo.PlateAddress AS PlateAddress,
	    	        $rm.dbo.Plate AS rmPlate,
    		        $rm.dbo.Experiment AS Experiment,
	    	        $rm.dbo.Users AS Users,
		            $rm.dbo.ExperimentPlate AS ExperimentPlate
		            LEFT OUTER JOIN $rm.dbo.ImagingTask AS ImagingTask
		                ON ImagingTask.ExperimentPlateID=ExperimentPlate.ID
    		            AND ImagingTask.State<>1
	    	            AND ImagingTask.State<>6
		                AND ImagingTask.State<>7
			    WHERE riPlate.ID=PlateAddress.PlateId AND rmPlate.Barcode=riPlate.Barcode
			        AND rmPlate.ID=ExperimentPlate.PlateID AND rmPlate.ExperimentId=Experiment.ID
			        AND Experiment.UserID=Users.ID AND riPlate.RobotID=@robotid
			    GROUP BY riPlate.Barcode, Users.Name
			    ORDER BY NextInspection
            ",@{
                "@robotid"=$Imager.ID
            })
            $Inventory=@()
            ForEach($Row in $InventoryRows.Rows){
                $Inventory+=@{
                    "Barcode"=$Row["Barcode"]
                    "Owner"=$Row["Owner"]
                    "NextInspection"=$Row["NextInspection"]
                    "FinalInspection"=$Row["FinalInspection"]
                    "InspectionsRemaining"=$Row["InspectionsRemaining"]
                }
            }
            $Inventories+=@{
                "name"=$Imager.Name
                "inventory"=$Inventory
            }
        }
        return $Inventories
    }

} # End Fx

class ImagePush {

    static $ImageExtension=".jpg"

    #Returns null if one or more imaging tasks were newly added to IceBear, otherwise the date/time of the latest in the
    #supplied group of ImagingTasks (the intent being that you will then call [Fx]getImagingTasks with this date as the cutoff)
    static [String]processImagingTasks($ImagingTasks){
        [Log]::info("Processing ImagingTasks....")
        $LastNonImportedDateTime=$Null
        $ImagingTasks | ForEach-Object {
            $ImagingTask=$_
            $TaskId=$_.ID
            $ImagedDateTime=($ImagingTask["DateImaged"].ToUniversalTime()).ToString("yyyy-MM-dd HH:mm:ss")

            [Log]::info("------------------------------")
            [Log]::info("ImagingTask ID: "+$TaskId+", imaged "+$ImagedDateTime+" UTC")
            $IbTask=[IceBear]::getImagedTimeAndImageCountForImagingTask($TaskId)
            $Barcode=[Fx]::getPlateBarcodeForImagingTask($TaskId);

            $Images=[Fx]::getImagesForImagingTask($TaskId)
            if(0 -eq $IbTask.fxImagingTaskId){
                [Log]::Info("Imaging task "+$TaskId+" does not yet exist in IceBear, will create IceBear imagingsession(s)")
            } else {
                [Log]::Info("Imaging task "+$TaskId+" exists in IceBear")
                [Log]::Info("Formulatrix task has "+$Images.DataRows.count+" images, IceBear has "+$IbTask.numImages)
                if($Images.DataRows.count -le $IbTask.numImages){
                    [Log]::Info("Not importing this ImagingTask because IceBear has all the images");
                    $LastNonImportedDateTime=$ImagedDateTime.Replace(".",":")
                    return #from this iteration of the For-Each. In other words, "continue" to the next ImagingTask.
                }
            }

            [Log]::debug("ExperimentPlateID: "+$_.ExperimentPlateID);
            [Log]::debug("Incubation temperature: "+$_.Temperature);
            $ExperimentPlateID=$_.ExperimentPlateID
            $Temperature=$_.Temperature

            $Plate=[Fx]::getPlateByExperimentPlateId($_.ExperimentPlateID);

            $IbPlate=[IceBear]::createPlate($Plate)
            if($null -eq $IbPlate){
                [Log]::warn( "Plate barcode is empty - not creating this ImagingTask")
                return #from this iteration of the For-Each. In other words, "continue".
            }

            [Log]::info("Processing images for ImagingTask $TaskId")
            [ImagePush]::processImages($Images)
            $LastNonImportedDateTime=""
            [Log]::debug("End of ImagingTask ForEach")

        } # End FxImagingTasks ForEach-Object
        [Log]::info("------------------------------")
        [Log]::info("Finished iterating through ImagingTasks")
        return $LastNonImportedDateTime
    }

    static [void]processImages($Images){
        $Images | ForEach-Object {

            $Image=$_
            $CaptureProfileVersionId=$Image.CaptureProfileVersionId
            $ImagingSettings=@{}

            [Log]::debug("Processing image for $Barcode well "+$Image['RowLetter']+$Image['ColumnNumber']+" drop "+$Image['DropNumber'])

            $Path=$Image["BasePath"]+"\WellImages\"+($Image["PlateID"]%1000)+"\PlateID_"+$Image["PlateID"]+"\BatchID_"+$Image["ImageBatchID"]
            $Path+="\WellNum_"+$Image["WellNumber"]+"\ProfileID_"+$Image["CaptureProfileId"]
            $Path+="\d"+$Image["DropNumber"]+"_r"+$Image["RegionID"]

            [Log]::debug("Base path for image batch: "+$Path)

            #Do thumbnail and image exist? If not, return (from this iteration)
            $imagePath=$Path+"_ef"+[ImagePush]::ImageExtension
            $thumbPath=$Path+"_th"+[ImagePush]::ImageExtension
            $imageExists=Test-Path -Path $ImagePath -PathType Leaf
            $thumbExists=Test-Path -Path $ThumbPath -PathType Leaf
            if(!$imageExists -or !$thumbExists){
                [Log]::debug("Image or thumb does not exist as $Extension")
                if(".png" -eq [ImagePush]::ImageExtension){
                    [ImagePush]::ImageExtension=".jpg"
                } else {
                    [ImagePush]::ImageExtension=".png"
                }
                [Log]::debug("Changed file extension to "+[ImagePush]::ImageExtension)
                $imagePath=$Path+"_ef"+[ImagePush]::ImageExtension
                $thumbPath=$Path+"_th"+[ImagePush]::ImageExtension
                $imageExists=Test-Path -Path $ImagePath -PathType Leaf
                $thumbExists=Test-Path -Path $ThumbPath -PathType Leaf
                if(!$imageExists -or !$thumbExists){
                    [Log]::warn("Image or thumb does not exist, tried JPEG and PNG. Not copying image to IceBear.")
                    [Log]::warn("Image path was $Path"+"_ef.[jpg|png]")
                    [Log]::warn("Thumb path was $Path"+"_th.[jpg|png]")
                    return # from this iteration, go to next image
                } else {
                    [Log]::debug("Found both image and thumb as PNG")
                }
            } else {
                [Log]::debug("Found both image and thumb as JPEG")
            }

            $ImagerName=[Fx]::getImagerNameForImagingTask($TaskId, $Barcode)
            $Imager=@{
                "name"=$ImagerName;
                "temperature"=$Temperature
            }

            if("" -eq $ImagerName){
                $Imager["name"]=$ImagerName
                $IceBearImagingSettings=[IceBear]::createImagingSettings(
                        $ImagingSettings, $Imager, $Image.CaptureProfileName, $Image.CaptureProfileId, $CaptureProfileVersionId
                )
            } else {
                $FxSettings=[Fx]::getSettingsForCaptureProfileVersionAndImager($CaptureProfileVersionId, $ImagerName)
                if($FxSettings -ne $null){
                    $FxSettings | ForEach-Object {
                        $ImagingSettings.Add($_.SettingName, $_.SettingValue)
                    }
                }
                $IceBearImagingSettings=[IceBear]::createImagingSettings(
                        $ImagingSettings, $Imager, $Image.CaptureProfileName, $Image.CaptureProfileId, $CaptureProfileVersionId
                )
            }

            #Either create the imaging session in IceBear or get it from local cache
            $ImagingSession=[IceBear]::createImagingSession(@{
                "plateBarcode"=$Barcode;
                "imagingTaskId"=$TaskId;
                "captureProfileVersionId"=$CaptureProfileVersionId;
                "imagerName"=$ImagerName;
                "temperature"=20;
                "imagedDateTime"=$ImagedDateTime
            });

            # Create IB image record
            $RowNumber=[Fx]::rowLetterToRowNumber[$Image["RowLetter"]]

            $StartTime=Get-Date
            $encodedImage=[convert]::ToBase64String((Get-Content -path $ImagePath -Encoding byte -Raw))
            $MidTime=Get-Date
            $encodedThumb=[convert]::ToBase64String((Get-Content -path $ThumbPath -Encoding byte -Raw))
            $EndTime=Get-Date
            $ImageDuration=New-TimeSpan -Start $StartTime -End $MidTime
            $ThumbDuration=New-TimeSpan -Start $MidTime -End $EndTime
            [Log]::debug("Took $ImageDuration to get and encode image")
            [Log]::debug("Took $ThumbDuration to get and encode thumbnail")

            [IceBear]::queueImage(@{
                "iceBearImagingSessionId"=$ImagingSession.id
                "iceBearPlateId"=$IbPlate.id
                "wellRowNumber"=$RowNumber
                "wellColumnNumber"=$Image["ColumnNumber"]
                "wellDropNumber"=$Image["DropNumber"]
                "micronsPerPixel"=$Image["PixelSize"]
                "image"=$encodedImage
                "thumb"=$encodedThumb
            })

            [Log]::debug("End of Image ForEach")
        } # End Images ForEach-Object

        #If the number of images is not divisible by the max buffer size, some buffered images may not have been sent.
        [Log]::debug("Flushing remaining images to IceBear")
        [IceBear]::flushImageBuffer()
        [Log]::info("Image processing complete")

    }

    static[void]checkLockFile($path){
        $LockFileExists=Test-Path -Path $path -PathType Leaf
        [Log]::debug("Checking for lock file at "+$path)
        if($LockFileExists){
            [Log]::warn("Lock file from a previous run exists at $path")
            $LockFileIsOld=Test-Path $path -OlderThan (Get-Date).AddHours(-3)
            if($LockFileIsOld){
                [Log]::warn("Lock file is old - removing it and continuing")
                Remove-Item $path
            } else {
                throw "Lock file exists and is recent. Aborting this run."
            }
        } else {
            [Log]::info("No lock file from a previous run - great, continuing")
        }
    }
    static[void]createLockFile($path){
        [Log]::debug("Creating lock file at "+$path)
        $F=New-Item -ItemType File $path
    }
    static[void]deleteLockFile($path){
        [Log]::info("Removing lock file")
         Remove-Item $path
    }


} # End ImagePush

class Disk {

    static[PSObject]getDiskSpaces(){
        [Log]::debug("In Disk::getDiskSpaces")
        $Disks=@()
        Get-PSDrive -PSProvider FileSystem | ForEach-Object {
            if($_.Free -ne $Null){
                $Disks+=@{
                    "machine"="Rock Maker"
                    "label"="$($_.Name):"
                    "mappedto"=$($_.DisplayRoot)
                    "bytesused"=$($_.Used)
                    "bytesfree"=$($_.Free)
                }
                [Log]::debug("Added disk $($_.Name)")
            }
        }
        return $Disks
    }

} # End Disk

try {

    $IniPath="$PSScriptRoot\config.ini"
    [Log]::debug("Looking for ini file at: $IniPath")
    $Ini=Get-Content $IniPath
    $Config=@{}
    ForEach($Line in $Ini){
        $Parts=$Line.split("#")
        $Parts=$Parts[0].split("=")
        if($Parts.Count -eq 2){
            $Key=$Parts[0].trim(' "')
            $Value=$Parts[1].trim(' "')
            [Log]::debug("From ini file: $Key=$Value")
            $Config.Add($Key, $Value)
        }
    }

    $RunStart=Get-Date
    [Log]::setLogLevel($Config.LogLevel)
    [Log]::info("Image push to IceBear started")
    [Db]::init($Config)
    [Fx]::init()
    [IceBear]::init($Config.IceBearRoot, $Config.IceBearApiKey, $Config.MaxImageBufferLength)

    $createdLockFile=0
    $LockFile=$PSScriptRoot+"\lockfile"
    [ImagePush]::checkLockFile($LockFile)
    [ImagePush]::createLockFile($LockFile)
    $createdLockFile=1

    #Pre-requisites
    $Scores=[IceBear]::getFormulatrixScores()

    # Determine import start date/time, rewinding by ClockFudgeMinutes to account
    # for clock drift, DST changes, etc. - this may well result in attempting to
    # re-import existing inspections, which is fine (and handled).
    $Cutoff=[Fx]::getImportStartDateTime($Config.IniStartDate, $Config.ClockFudgeMinutes)
    [Log]::info("Date/time cutoff is $Cutoff")
    $FxImagingTasks=[Fx]::getImagingTasks($Cutoff, $Config.MaxImagingTasksPerRun)


    # Get some ImagingTasks and process them, repeating until some new images/
    # inspections are imported into IceBear. We limit this to max 10 iterations,
    # because otherwise it would go infinite if all imagers have been idle.
    $BatchCount=0
    while($BatchCount -lt 10 -and "" -ne $Cutoff){
        [Log]::info("No new ImagingTasks imported, getting another batch")
        $FxImagingTasks=[Fx]::getImagingTasks($Cutoff, $Config.MaxImagingTasksPerRun)
        $Cutoff=[ImagePush]::processImagingTasks($FxImagingTasks)
        $BatchCount++
    }


    # Now update the imager inventories
    [Log]::info("Updating IceBear imager inventories")
    $Inventories=[Fx]::getImagerInventories()
    [IceBear]::updateImagerInventories($Inventories)



    # Now pull the oldest (Formulatrix) barcode off the reimport queue
    $Plate=[IceBear]::getPlateForReImport()
    [Log]::debug("Plate: $Plate")
    if($Plate){
        $PlateBarcode=$Plate.name
        [Log]::info("Plate $PlateBarcode is queued for re-import in IceBear")
        $FxImagingTasks=[Fx]::getImagingTasksForPlate($PlateBarcode)
        [ImagePush]::processImagingTasks($FxImagingTasks)

    } else {
        [Log]::info("No plates queued for re-import")
    }
    # get its imagingTasks
    # import the imagingTasks
    # TODO

    # Now update IceBear with the server's disk space
    if(1 -eq $Config.UpdateDiskSpace){
        [Log]::info("Sending disk space info to IceBear")
        $Disks=[Disk]::getDiskSpaces()
        [IceBear]::updateDiskSpaces($Disks)
    } else {
        [Log]::warn("Not sending disk space info to IceBear - disabled in config")
    }

} catch {
    $e = $_.Exception
    [Log]::error("Exception thrown in PowerShell")
    $Now=Get-Date -Format G
    [Log]::debug("In catch, time is $Now")
    [Log]::error($e.Message +" thrown at line "+ $_.InvocationInfo.ScriptLineNumber)
    if($e.Message.ToLower().Contains('lock file')) {
        [Log]::error("Caught exception pertains to lock file, not dumping trace.")
    } else {
        [Log]::error("Trace: "+ ($_|format-list -force | Out-String))
    }
} finally {
    [Log]::info("Image push to IceBear finished")
    $RunEnd=Get-Date
    $RunDuration=New-TimeSpan -Start $RunStart -End $RunEnd
    if($createdLockFile -eq 1){
        [ImagePush]::deleteLockFile($LockFile)
    }
    [Log]::info("Total run time: $RunDuration")
    [Log]::info("==============================")
    [Log]::info(" ")
    [Db]::close()
}

