/* Drop existing standard schedule for job */
declare @schedule_id int
declare c_schedules cursor local forward_only static for
    select ss.schedule_id
      from msdb.dbo.sysjobschedules sjs
INNER JOIN msdb.dbo.sysschedules ss
        ON sjs.schedule_id = ss.schedule_id
       AND ss.name NOT LIKE '%#SPEC#'
INNER JOIN msdb.dbo.sysjobs sj
        ON sjs.job_id = sj.job_id
     WHERE sj.name = N'ZZ - TSC - TPDT-676 - connectivity check to POS'

open c_schedules

FETCH NEXT FROM c_schedules into @schedule_id
while @@fetch_status = 0
begin
  IF ((select COUNT(*) from msdb.dbo.sysjobschedules where schedule_id=@schedule_id) = 1)
    EXEC msdb.dbo.sp_delete_schedule @schedule_id=@schedule_id, @force_delete = 1
FETCH NEXT FROM c_schedules into @schedule_id
end

close c_schedules

deallocate c_schedules

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'ZZ - TSC - TPDT-676 - connectivity check to POS')
EXEC msdb.dbo.sp_delete_job @job_name = N'ZZ - TSC - TPDT-676 - connectivity check to POS', @delete_unused_schedule=0
GO

/* Creation Job and Steps*/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Data Collector' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Data Collector'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

/* Add Job */
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ZZ - TSC - TPDT-676 - connectivity check to POS', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		/* Add Step */
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'logic', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=N'$tcpPort = 1433     # Specify the port number to test (e.g., HTTP = 80, HTTPS = 443)
$rootDrive = "U:\"  #root drive where files will be stored
$subFolder = "TPDT-676" #subfolder where all files will be stored on the $rootDrive

#ensure we have a working drive
if(-not(Test-Path -Path $rootDrive)){
    $rootDrive = "D:\"
}

#create the destination folder if missing
$dest = "$rootDrive\$subFolder".Replace("\\","\")
if (-not (Test-Path -Path $dest)) {
    New-Item -ItemType Directory -Path $dest
}

$outputCsvLAT = "$dest\tpdt-676-POS.csv" #name of the csv file with teh results
$logFile = "$dest\tpdt-676-POS.log"   #place of log file (for debugging sql job)

$maxFiles = 3       #how many csv file we keep after doing a rotation
$maxFileSizeMB = 10 #What is the max size of a csv file before we do a rotation in MB
$testCount = 5      # Number of connection attempts per iteration 

#list of POS to check
$psList = @(
    "CAMA34945659T05",
    "CAMA04140508T10",
    "CSUN89545123T13",
    "CAMA32144422T04",
    "WSU186A07",
    "WAM083A04",
    "CAMA00180002T07",
    "CAMA26338993T08",
    "CAMA25680308T12",
    "CAMA10680305T05",
    "WCV853A04",
    "CAMA31345688T05",
    "CAMA22080072T13",
    "CCVI81639628T03",
    "WAM060B03",
    "CSUN17180460T04",
    "WCV806A10",
    "CAMA01740468T08",
    "CCVI83342696T11",
    "CCVI84339629T04",
    "WAM216A06",
    "CSUN14334905T03",
    "WAM269A03",
    "WAM080A03",
    "WCV832A04",
    "WCV848A08",
    "CAMA28336544T05",
    "CAMA60180481T10",
    "WSU721A08",
    "CAMA02980066T05",
    "CAMA89843155T12",
    "CAMA29632238T05",
    "CAMA09038344T03",
    "WCV836B04",
    "WAM601A03",
    "CAMA23334459T10",
    "WAM065B05",
    "WCV812B09",
    "WAM626A10",
    "WCV267A05",
    "WSU141B09",
    "WCV50705",
    "CAMA24946011T04",
    "CSUN12145469T05",
    "CAMA08136638T10",
    "CAMA51080448T04",
    "WCV844A04",
    "WSU841A02",
    "CSUN64580053T02",
    "CCVI24335530T06"
)

# Function to check the size of the CSV file
function Check-CsvFileSize {
    param ($filePath)
    
    if (Test-Path $filePath) {
        $fileSizeMB = (Get-Item $filePath).length / 1MB
        return $fileSizeMB
    } else {
        return 0
    }
}

# Function to rotate files
function Rotate-CsvFile {
    param ($filePath, $rotationPath, $maxFiles)
    
    #trailing backslash needed for concatenation of the path
    if (-not $rotationPath.EndsWith("\")) {
        $rotationPath += "\"
    }

    # Create rotation directory if it doesn''''t exist
    if (-not (Test-Path $rotationPath)) {
        New-Item -ItemType Directory -Path $rotationPath
    }

    # Get the current timestamp to append to the rotated filename
    $timestamp = Get-Date -Format "yyyyMMdd-HHmmss"
    $fileName = [System.IO.Path]::GetFileNameWithoutExtension($filePath)
    $fileExtension = [System.IO.Path]::GetExtension($filePath)

    # Generate new rotated filename
    $rotatedFileName = "$rotationPath$fileName-$timestamp$fileExtension"
    
    # Rotate the file
    Rename-Item -Path $filePath -NewName $rotatedFileName

    # Get all rotated files sorted by creation time
    $rotatedFiles = Get-ChildItem -Path $rotationPath -Filter "$fileName-*$fileExtension" | Sort-Object LastWriteTime -Descending

    # Keep only the most recent 3 files
    $filesToDelete = $rotatedFiles | Select-Object -Skip $maxFiles
    foreach ($file in $filesToDelete) {
        Remove-Item -Path $file.FullName
    }
}

#write to log
function write-log {
    param(
        [Parameter(ValueFromPipeline = $true)]
        [string]$msg,
        [string]$log = $logFile
    )

    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss.fff"
    $line = "$timestamp`t$msg"

    Add-Content -Path $log -Value $line
    write-host $line
}

#Get the replini-backup URI and hostname from ActiveSystemServer settings
function get-target {
    $shareDS = Invoke-Sqlcmd -ServerInstance "(local)" -Database master -Query @"
SELECT SettingValue AS backupSrc, SettingId
FROM ActiveSystemServer.cfg.Settings
WHERE SettingId LIKE ''''Values.Modules.Replication.DbInitializationBackupPath%''''
AND LEN(SettingValue) > 1;
"@
    $shareValue = $shareDS.backupSrc.Trim()
    $machineName = ($shareValue -split "\\") | Where-Object { $_ -ne "" } | Select-Object -First 1
    $result = [PSCustomObject]@{
        smbShare    = "$shareValue"
        machineName = "$machineName"
    }

    return $result
}

# Function to perform SMB share reachability test
function Test-SmbShareAccessibility {
    param(
        [string]$sharePath,
        [int]$count
    )
    
    $successfulConnections = 0
    $latencyTimes = @()
    write-log "to check: $sharePath"

    for ($i = 1; $i -le $count; $i++) {
        # Measure the time taken to check share accessibility
        $startTime = [DateTime]::Now

        # Check if the SMB share is accessible using Test-Path
        if (Test-Path -Path $sharePath) {
            $successfulConnections++
            $latencyTimes += (([DateTime]::Now) - $startTime).TotalMilliseconds
        }
        else {
            # Log failed connection as N/A latency
            $latencyTimes += "N/A"
        }

        Start-Sleep -Milliseconds 500  # Slight delay between attempts
    }

    # Calculate packet loss percentage
    $packetLoss = (($count - $successfulConnections) / $count) * 100

    # Calculate average latency excluding N/A entries
    $avgLatency = ($latencyTimes | Where-Object { $_ -ne "N/A" } | Measure-Object -Average).Average

    return [pscustomobject]@{
        Timestamp  = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
        SMBShare   = $sharePath
        PacketLoss = "$packetLoss"
        AvgLatency = if ($null -ne $avgLatency) { $avgLatency } else { "N/A" }
    }
}

# Function to perform TCP latency and packet loss test
function Test-TcpLatencyAndPacketLoss {
    param(
        [string]$target,
        [int]$port,
        [int]$count
    )
    
    $successfulConnections = 0
    $latencyTimes = @()

    for ($i = 1; $i -le $count; $i++) {
        # Perform TCP connection test using Test-NetConnection
        $tcpTest = Test-NetConnection -ComputerName $target -Port $port -InformationLevel Quiet 
        
        if ($tcpTest) {
            $successfulConnections++
            # Simulate latency as the connection time (since Test-NetConnection does not give latency directly)
            $connectionStart = [DateTime]::Now
            $tcpTest = Test-NetConnection -ComputerName $target -Port $port -InformationLevel Quiet
            $connectionEnd = [DateTime]::Now
            $latency = ($connectionEnd - $connectionStart).TotalMilliseconds
            $latencyTimes += $latency
        }
        else {
            # Log failed connection as N/A latency
            $latencyTimes += "N/A"
        }

        Start-Sleep -Milliseconds 500  # Slight delay between attempts
    }

    # Calculate packet loss percentage
    $packetLoss = (($count - $successfulConnections) / $count) * 100

    # Calculate average latency excluding N/A entries
    $avgLatency = ($latencyTimes | Where-Object { $_ -ne "N/A" } | Measure-Object -Average).Average

    return [pscustomobject]@{
        Timestamp  = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
        Machine    = $target
        Port       = $port
        PacketLoss = "$packetLoss"
        AvgLatency = if ($null -ne $avgLatency) { $avgLatency } else { "N/A" }
    }
}

if (-not (Test-Path $rootDrive)) {
    write-log "No $rootDrive drive is present, skipping checks."
    return -1
}
else {

    $psList | ForEach-Object {
        $pos_name = $_
        Write-Output "pos name: $pos_name"

        # Run the TCP latency and packet loss test
        $result = Test-TcpLatencyAndPacketLoss -target $pos_name -port $tcpPort -count $testCount

        # Check the CSV file size 
        $csvFileSize = Check-CsvFileSize -filePath $outputCsvLAT

        # If the file size exceeds the threshold, rotate the file. Force casting of the values to [double] to ensure it''s not a string comparison
        if ([double]$csvFileSize -ge [double]$maxFileSizeMB) {
            Rotate-CsvFile -filePath $outputCsvLAT -rotationPath $dest -maxFiles $maxFiles
        }

        # Check if the CSV file exists, if not create it
        if (-not (Test-Path $outputCsvLAT)) {
            # Create CSV file with headers
            $result | Export-Csv -Path $outputCsvLAT -NoTypeInformation
        }
        else {
            # Append the result to the CSV file
            $result | Export-Csv -Path $outputCsvLAT -NoTypeInformation -Append
        }
    }

    

}


', 
		@database_name=N'master', 
		@output_file_name=NULL, 
		@flags=0, 
		@database_user_name=NULL, 
		@server=NULL, 
		@additional_parameters=NULL, 
		@proxy_id=NULL, 
		@proxy_name=NULL
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/* Add Standard Schedule */
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'ZZ - TSC - TPDT-676 - connectivity check to POS-R', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=3, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20240822, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/* Attach existing specific schedule for job */
declare @enabled_schedule int,
        @schedule_name nvarchar(50)
declare c_schedules cursor local forward_only static for
select enabled, name
  from msdb.dbo.sysschedules
 where name LIKE 'ZZ%'
   and name LIKE '%#SPEC#'

open c_schedules

FETCH NEXT FROM c_schedules into @enabled_schedule, @schedule_name
while @@fetch_status = 0
begin
  EXEC @ReturnCode = msdb.dbo.sp_attach_schedule @job_id = @jobId, @schedule_name=@schedule_name
  IF(@enabled_schedule = 1)
  begin
    SET @schedule_name = SUBSTRING(@schedule_name,0,LEN(@schedule_name)-5)
    IF EXISTS (select name from msdb.dbo.sysschedules where name = @schedule_name)
      EXEC @ReturnCode = msdb.dbo.sp_update_schedule @name=@schedule_name, @enabled=0
  end
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
FETCH NEXT FROM c_schedules into @enabled_schedule, @schedule_name
end

close c_schedules

deallocate c_schedules

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO