################# Connect to API and get system info

Write-Output "Connecting to /api/system"

$sysResult = Invoke-restmethod -Uri "http://localhost:8081/api/system" -UseDefaultCredentials


if ($sysResult.ConnectionState -eq "Connected") {
    Write-Output "Connection established:"
    Write-Output $sysResult
}
else {
    Write-Output "Connection failed"
    Write-Output $sysResult
    Exit
}


################# Import well headers

Write-Output "Importing well headers"

$csvBody = @"
UWI,Product List,Well Type,Area,Country,State/Province
Well A1,Oil,Oil,Area A,United States,Texas
Well A2,Oil,Oil,Area A,United States,Texas
Well B1,Oil,Oil,Area B,United States,Texas
Well B2,Oil,Oil,Area B,United States,Texas
Well B3,Oil,Oil,Area B,United States,Texas
"@ 

$importParams = @{
    Import     = $csvBody
    DataArea   = "Well Info and Custom Fields"
    UpdateMode = "Merge"
    Header     = "1"
}
$importResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport" -UseDefaultCredentials -Method "POST" -Body $importParams

$jobId = @{
    jobId = $importResult.JobId
}
do {
    $statusResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport/importStatus" -UseDefaultCredentials -Body $jobId
    Start-Sleep -s 1
} while ($statusResult.Completed -lt 100)

Write-Output "Well header import complete"


################# Import production forecast (array)

Write-Output "Importing production forecasts"

$csvBody = @"
UWI,Plan,Reserves Category,Date,Oil Volume
Well A1,Working,PUD,Jan 2023,1000
Well A1,Working,PUD,Feb 2023,1000
Well A1,Working,PUD,Mar 2023,1000
Well A1,Working,PUD,Apr 2023,1000
Well A1,Working,PUD,May 2023,1000
Well A2,Working,PUD,Jan 2023,1100
Well A2,Working,PUD,Feb 2023,1100
Well A2,Working,PUD,Mar 2023,1100
Well A2,Working,PUD,Apr 2023,1100
Well A2,Working,PUD,May 2023,1100
Well B1,Working,PUD,Jan 2024,1000
Well B1,Working,PUD,Feb 2024,1000
Well B1,Working,PUD,Mar 2024,1000
Well B1,Working,PUD,Apr 2024,1000
Well B1,Working,PUD,May 2024,1000
Well B2,Working,PUD,Jan 2024,1100
Well B2,Working,PUD,Feb 2024,1100
Well B2,Working,PUD,Mar 2024,1100
Well B2,Working,PUD,Apr 2024,1100
Well B2,Working,PUD,May 2024,1100
Well B3,Working,PUD,Jan 2024,1200
Well B3,Working,PUD,Feb 2024,1200
Well B3,Working,PUD,Mar 2024,1200
Well B3,Working,PUD,Apr 2024,1200
Well B3,Working,PUD,May 2024,1200
"@ 

$importParams = @{
    Import     = $csvBody
    DataArea   = "Production Forecasts"
    UpdateMode = "Replace"
    Header     = "1"
}
$importResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport" -UseDefaultCredentials -Method "POST" -Body $importParams

$jobId = @{
    jobId = $importResult.JobId
}
do {
    $statusResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport/importStatus" -UseDefaultCredentials -Body $jobId
    Start-Sleep -s 1
} while ($statusResult.Completed -lt 100)

Write-Output "Production forecast import complete"


################# Import capital

Write-Output "Importing capital"

$csvBody = @"
UWI,Plan,Reserves Category,Date,Tangible Development Drilling,Tangible Completion Cost
Well A1,Working,PUD,Nov 2022,4000,
Well A1,Working,PUD,Dec 2022,,2500
Well A2,Working,PUD,Nov 2022,4000,
Well A2,Working,PUD,Dec 2022,,2500
Well B1,Working,PUD,Nov 2023,4000,
Well B1,Working,PUD,Dec 2023,,2500
Well B2,Working,PUD,Nov 2023,4000,
Well B2,Working,PUD,Dec 2023,,2500
Well B3,Working,PUD,Nov 2023,4000,
Well B3,Working,PUD,Dec 2023,,2500
"@ 

$importParams = @{
    Import     = $csvBody
    DataArea   = "Capital Costs"
    UpdateMode = "ReplaceAll"
    Header     = "1"
}
$importResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport" -UseDefaultCredentials -Method "POST" -Body $importParams

$jobId = @{
    jobId = $importResult.JobId
}
do {
    $statusResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport/importStatus" -UseDefaultCredentials -Body $jobId
    Start-Sleep -s 1
} while ($statusResult.Completed -lt 100)



################# Import operating costs

Write-Output "Importing operating costs (by proxy)"

$csvBody = @"
UWI,Plan,Reserves Category,Date,WI Fixed Op Cost,WI Variable Op Cost - Oil
Area A,Working,PUD,Jan 2023,3000,5
Area B,Working,PUD,Jan 2024,3500,4.5
"@ 

$importParams = @{
    Import     = $csvBody
    DataArea   = "Operating Costs"
    UpdateMode = "ReplaceAll"
    Header     = "1"
    Proxy      = "Proxy - Area"
}
$importResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport" -UseDefaultCredentials -Method "POST" -Body $importParams

$jobId = @{
    jobId = $importResult.JobId
}
do {
    $statusResult = Invoke-restmethod -Uri "http://localhost:8081/api/csvImport/importStatus" -UseDefaultCredentials -Body $jobId
    Start-Sleep -s 1
} while ($statusResult.Completed -lt 100)

Write-Output "Operating cost import complete"


################# Setup for economics run

Write-Output "Gathering configuration information for economics run"

# Get list of hierarchies and find Area hierarchy
$hierarchies = Invoke-restmethod -Uri "http://localhost:8081/api/hierarchy" -UseDefaultCredentials
$areaHierarchy = $hierarchies | Where-Object { $_.Name -EQ 'Area' }

# Get list of child nodes of Area hierarchy and find 'Area A' node
$hierarchyForChildren = @{
    hierarchyId = $areaHierarchy.Id
}
$areaChildren = Invoke-restmethod -Uri "http://localhost:8081/api/hierarchy/children" -UseDefaultCredentials -Body $hierarchyForChildren
$areaANode = $areaChildren | Where-Object { $_.Name -EQ 'Area A' }

# Get list of various global data definitions required to run economics
$jurisdiction = Invoke-restmethod -Uri "http://localhost:8081/api/jurisdiction" -UseDefaultCredentials
$plans = Invoke-restmethod -Uri "http://localhost:8081/api/plans" -UseDefaultCredentials
$working = $plans | Where-Object { $_.DisplayName -EQ 'Working' }
$rescats = Invoke-restmethod -Uri "http://localhost:8081/api/reserveCategory" -UseDefaultCredentials 
$tp = $rescats | Where-Object { $_.Name -EQ 'TP' }

Write-Output "Information gathering complete"


################# Trigger an economic calculation for the Area A node

$econRunBody = [ordered]@{
    HierarchyId         = $areaHierarchy.Id
    EntityIds           = @(    $areaANode.Id    )
    PlanIds             = @(    $working.ObjectId    )
    ReservesCategoryIds = @(    $tp.Id    )
    ScenarioIds         = @(    $jurisdiction.ObjectId    )
}
$econRunConfigString = $econRunBody | ConvertTo-Json

Write-Output "Econ run config:"
Write-Output $econRunConfigString

Write-Output "Running economics..."
$areaAResult = Invoke-restmethod -Uri "http://localhost:8081/api/economic/calculate/hierarchy" -UseDefaultCredentials -Method "POST" -Body $econRunConfigString -ContentType 'application/json'

$runIdMessage = [string]::Format("Checking run request ID {0}", $areaAResult.RequestId)
Write-Output $runIdMessage

$resultId = @{
    requestId = $areaAResult.RequestId
}
do {
    $econStatusResult = Invoke-restmethod -Uri "http://localhost:8081/api/economic/calculate/status" -UseDefaultCredentials -Body $resultId

    $statusMessage = [string]::Format("Percent complete: {0}", $econStatusResult.PercentComplete)
    Write-Output $statusMessage
    
    Start-Sleep -s 1
} while ($econStatusResult.PercentComplete -lt 100)

Write-Output "Econ run completed"



################# Retrieve econ results direct from DB


Write-Output "Connecting to database"


# If necessary, download sqlite, extract, and load library

Write-Output "Loading SQLite"

# $runLocal = true indicates integration service is installed/running on same machine as script
# $runLocal = false indicates remote service; will then manually download SQlite as can't guarantee existence from service install
$runLocal = $true

if ($runLocal) {
    Write-Output "Loading locally"
    [Reflection.Assembly]::LoadFile("C:\Program Files\Quorum Software\Value Navigator Integration Service 2021 v2\System.Data.SQLite.dll")
}
else {
    Write-Output "Check for SQLite"
    $sqliteFolder = "D:\Temp\sqlite.net"
    if (Test-Path -Path $sqliteFolder) {
        Write-Output "SQLite folder found. No download required."
    }
    else {
        Write-Output "Downloading, extracting, and loading sqlite"

        Invoke-WebRequest -Uri "https://system.data.sqlite.org/blobs/1.0.115.5/sqlite-netFx46-binary-x64-2015-1.0.115.5.zip" -OutFile "D:\temp\sqlite.zip"
        mkdir -Force $sqliteFolder
        Expand-Archive -LiteralPath "D:\temp\sqlite.zip" -DestinationPath $sqliteFolder -Force
        [Reflection.Assembly]::LoadFile([string]::Format("{0}\System.Data.SQLite.dll", $sqliteFolder) )
    }
}

# Connect to the database

Write-Output "Opening database connection"

$databaseConnectionString = [string]::Format("data source={0}", $sysResult.DatabaseFilePath)
$sqliteDBConnection = New-Object System.Data.SQLite.SQLiteConnection
$sqliteDBConnection.ConnectionString = $databaseConnectionString
$sqliteDBConnection.open()

# Query for an economic result


# Note: in this script, we have run folder-level results but are retrieving well-level results
#   (this due to potential bug in 21.2.0.62 build with folder-level result IDs)

$areaAChildrenPayload = @{
    hierarchyId = $areaHierarchy.Id
    folderId    = $areaANode.Id
}
$areaAChildren = Invoke-restmethod -Uri "http://localhost:8081/api/hierarchy/children" -UseDefaultCredentials -Body $areaAChildrenPayload


Write-Output "Getting well-level before-tax cashflow results"

$sqliteDBCommand = $sqliteDBConnection.CreateCommand()
$sqliteDBCommand.CommandType = [System.Data.CommandType]::Text

foreach ($wellNode in $areaAChildren) {

    # BTCF
    $sqliteDBCommand.Commandtext = [string]::Format("SELECT rs.BTAX_NET_REVENUE FROM RESULTS_SUMMARY rs INNER JOIN RESULTS_LOOKUP rl on rl.RESULT_ID = RS.RESULT_ID WHERE rl.ENTITY_ID = '{0}' AND SCENARIO_ID = '{1}' AND PLAN_DEFINITION_ID = '{2}' AND RESERVE_CATEGORY_ID = '{3}'", $wellNode.ResultId, $jurisdiction.ObjectId, $working.ObjectId, $tp.Id)
    $queryResult = $sqliteDBCommand.ExecuteScalar()

    $resultMessage = [string]::Format("{0} BTCF: {1}", $wellNode.Name, $queryResult)
    Write-Output $resultMessage

    # NPV 3 (10%)
    $sqliteDBCommand.Commandtext = [string]::Format("SELECT rs.BTAX_NPV3 FROM RESULTS_SUMMARY rs INNER JOIN RESULTS_LOOKUP rl on rl.RESULT_ID = RS.RESULT_ID WHERE rl.ENTITY_ID = '{0}' AND SCENARIO_ID = '{1}' AND PLAN_DEFINITION_ID = '{2}' AND RESERVE_CATEGORY_ID = '{3}'", $wellNode.ResultId, $jurisdiction.ObjectId, $working.ObjectId, $tp.Id)
    $queryResult = $sqliteDBCommand.ExecuteScalar()

    $resultMessage = [string]::Format("{0} NPV @ 10%: {1}", $wellNode.Name, $queryResult)
    Write-Output $resultMessage
}