# loop through them, create sql record

# $cat_item_sys_id = 'c64e27af47244610b7853238436d435d'
New-ITDServiceNowSession -Environment Production -Credential $Secret:snow_vmcred

Write-Verbose -Message "Prep variable and SQL connection" -Verbose
$ServerInstance = "itdintsql22p1.nd.gov\INTSQL22P1"
$Database = "ITD-Systems-Automation"
$Table = "ServiceNow_RitmDump_ServerBuildRequestV1"

# get most recent RITM from SQL, get the opened_at value
$SqlQuery = "SELECT [RitmNum],[RitmSysId],[opened_at] FROM [$Database].[dbo].[$Table]"
$SqlExistingRecords = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $SqlQuery -Credential $Secret:sql_itdpsu1 -Verbose

$NewestRecord = ($SqlExistingRecords | Sort-Object -Descending opened_at) | select -First 1
$DateYMDFilter = ($NewestRecord.opened_at | Get-Date -UFormat "%Y-%m-%d")
$DateHMSFilter = ($NewestRecord.opened_at | Get-Date -UFormat "%H:%M:%S")

#$Filter = "cat_item=c64e27af47244610b7853238436d435d^opened_at>javascript:gs.dateGenerate('2024-07-30','23:59:59')"

#$Filter = "cat_item=c64e27af47244610b7853238436d435d"
$Filter = "cat_item=c64e27af47244610b7853238436d435d^opened_at>javascript:gs.dateGenerate('$DateYMDFilter','$DateHMSFilter')"
Write-Verbose -Message ("Filter is " + $Filter) -Verbose

# retrieve list of RITMs created since $opened_at (>opened_at)
Write-Verbose -Message "Retrieve list of RITMs created since last update" -Verbose
#$AllRitms = Get-ITDServiceNowRecord -ItemType 'Request Item' -Filter $Filter -IncludeTotalCount -IncludeCustomVariable | Sort-Object Number
$AllRitms = Get-ITDServiceNowRecord -ItemType 'Request Item' -Filter $Filter -IncludeTotalCount -IncludeCustomVariable | Sort-Object Number
#$AllRitms = Get-ITDServiceNowRecord -ItemType 'Request Item' -Number 'RITM0262097' -IncludeCustomVariable
Write-Verbose -Message ("RITMs found:  " + @($AllRitms).count) -Verbose


ForEach ($Ritm in $AllRitms) {
    Write-Verbose ("Start " + $Ritm.number) -Verbose
    # get all variable set rows
    $VariableSet = (Get-ITDServiceNowRecord -ItemType 'Request Item' -Number $Ritm.number.value -IncludeVariableSet).VariableSet
    ForEach ($VSet in $VariableSet) {
        Write-Verbose -Message ("Start " + $VSet.host_name + ' *** ' + $VSet.host_name_ref)
        # figure out values
        $RitmNum = $Ritm.number.value
        $RitmSysId = $Ritm.sys_id.value
        $opened_at = $Ritm.opened_at.display_value
        $requested_for = $Ritm.requested_for.display_value
        $request_type = $Ritm.CustomVariable.request_type.value
        $environment = $Ritm.CustomVariable.environment.value
        $host_name_ref = $VSet.host_name_ref
        $host_name = If ($VSet.host_name_ref) {
            (Get-ITDServiceNowRecord -Table cmdb_ci -SysId $VSet.host_name_ref).Name.display_value
        }
        Else {
            $VSet.host_name
        }
        $server_type = $VSet.server_type
        $operating_system = $VSet.operating_system
        $target_os_version_linux = $VSet.target_os_version_linux
        $target_os_version_windows = $VSet.target_os_version_windows
        $target_platform = $VSet.target_platform
        $processors = $VSet.processors
        $memory_gb = $VSet.memory_gb
        $cidr_block_sys_id = $VSet.cidr_block
        $cidr_block = (Get-ITDServiceNowRecord -Table 'cmdb_ci_ip_network' -SysId $cidr_block_sys_id).subnet.display_value
        $vlan_id = $VSet.vlan_id
        $data_center = $VSet.data_center
        $licensing_restrictions = $VSet.licensing_restrictions
        $application_info_sys_id = $VSet.application_info
        $application_info = (Get-ITDServiceNowRecord -Table 'cmdb_ci_service' -SysId $application_info_sys_id).name.display_value
        $support_hours = $VSet.support_hours
        $dr_protection = $VSet.dr_protection
        $startup_priority = $VSet.startup_priority
        $disk_1_os = $VSet.disk_1_os
        $disk_2_swap_disk = $VSet.disk_2_swap_disk
        $disk_3 = $VSet.disk_3
        $disk_4 = $VSet.disk_4
        $disk_5 = $VSet.disk_5
        $disk_6 = $VSet.disk_6
        $disk_7 = $VSet.disk_7
        $disk_8 = $VSet.disk_8
        $disk_9 = $VSet.disk_9
        $disk_10 = $VSet.disk_10
        $disk_11 = $VSet.disk_11
        $disk_12 = $VSet.disk_12
        $disk_13 = $VSet.disk_13
        $disk_14 = $VSet.disk_14
        $disk_15 = $VSet.disk_15
        $disk_16 = $VSet.disk_16
        $special_instructions = $Ritm.CustomVariable.special_instructions.value
        $customer_request = $Ritm.CustomVariable.customer_request.value
        $additional_comments = $Ritm.CustomVariable.additional_comments.value

        Write-Verbose -Message ("Add to SQL " + $VSet.host_name + ' *** ' + $VSet.host_name_ref)
        # add record to SQL
        $SqlQuery = "INSERT INTO [$Table] (
        RitmNum, RitmSysId, opened_at,requested_for,request_type,environment,host_name_ref,host_name,server_type,operating_system,target_os_version_linux,target_os_version_windows,target_platform,processors,memory_gb,cidr_block_sys_id,cidr_block,vlan_id,data_center,licensing_restrictions,application_info_sys_id,application_info,support_hours,dr_protection,startup_priority,disk_1_os,disk_2_swap_disk,disk_3,disk_4,disk_5,disk_6,disk_7,disk_8,disk_9,disk_10,disk_11,disk_12,disk_13,disk_14,disk_15,disk_16,special_instructions,customer_request,additional_comments) Values (
        '$RitmNum','$RitmSysId','$opened_at','$requested_for','$request_type','$environment','$host_name_ref','$host_name','$server_type','$operating_system','$target_os_version_linux','$target_os_version_windows','$target_platform','$processors','$memory_gb','$cidr_block_sys_id','$cidr_block','$vlan_id','$data_center','$licensing_restrictions','$application_info_sys_id','$application_info','$support_hours','$dr_protection','$startup_priority','$disk_1_os','$disk_2_swap_disk','$disk_3','$disk_4','$disk_5','$disk_6','$disk_7','$disk_8','$disk_9','$disk_10','$disk_11','$disk_12','$disk_13','$disk_14','$disk_15','$disk_16','$special_instructions','$customer_request','$additional_comments'
        )
        "

        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $SqlQuery -Credential $Secret:sql_itdpsu1 -Verbose
        Write-Verbose -Message ("End " + $VSet.host_name + ' *** ' + $VSet.host_name_ref)
    }
    Write-Verbose ("End " + $Ritm.number) -Verbose
}

<# scratch
Write-Verbose -Message ("Add record to SQL") -Verbose
$SqlQuery = "INSERT INTO [$SnapshotTable] (VMName, DateTime, RequestedBy, DurationHours,Status,ExpireDateTime,NotifyEmail,PSUJobIdRequest) Values ('$Name', '$StartDateTimeSql', '$RequestedBy', $DurationHours, 'Requested', '$EndDateTimeSql','$Email','$PSUJobId');SELECT SCOPE_IDENTITY();"
#Write-Verbose -Message $SqlQuery -Verbose
$SnapshotId = (Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $SqlQuery -Credential $Secret:itdpsu1 -Verbose).Column1
    



select TOP (1000) [RitmNum]
, [RitmSysId]
, [opened_at]
, [requested_for]
, [request_type]
, [environment]
, [host_name_ref]
, [host_name]
, [server_type]
, [operating_system]
, [target_os_version_linux]
, [target_os_version_windows]
, [target_platform]
, [processors]
, [memory_gb]
, [cidr_block_sys_id]
, [cidr_block]
, [vlan_id]
, [data_center]
, [licensing_restrictions]
, [application_info_sys_id]
, [application_info]
, [support_hours]
, [dr_protection]
, [startup_priority]
, [disk_1_os]
, [disk_2_swap_disk]
, [disk_3]
, [disk_4]
, [disk_5]
, [disk_6]
, [disk_7]
, [disk_8]
, [disk_9]
, [disk_10]
, [disk_11]
, [disk_12]
, [disk_13]
, [disk_14]
, [disk_15]
, [disk_16]
, [special_instructions]
, [customer_request]
, [additional_comments]
FROM [ITD-Systems-Automation].[dbo].[ServiceNow_RitmDump_ServerBuildRequestV1]
#>