# This section defines some starting variables that we'll use here....
param ($DBServerName='localhost', $DBName='GSSyncDB', $LogLevel=3, $SleepSeconds=60, $PathToGSSync=".\GSSync.exe")

function Write-Log {
    param (
        [string[]]$ParameterName
    )
    $ThisLogLevel = $ParameterName[0]
    $ParameterName = $ParameterName[1..($ParameterName.Count-1)]
    if($ThisLogLevel -le $LogLevel)
    {
        foreach ($Parameter in $ParameterName) 
        {
            if($ThisLogLevel -eq 5) { Write-Host $Parameter -NoNewLine -ForegroundColor "Gray" }
            if($ThisLogLevel -eq 4) { Write-Host $Parameter -NoNewLine -ForegroundColor "Cyan" }
            if($ThisLogLevel -eq 3) { Write-Host $Parameter -NoNewLine -ForegroundColor "Yellow" }
            if($ThisLogLevel -eq 2) { Write-Host $Parameter -NoNewLine -ForegroundColor "Red" }
            if($ThisLogLevel -eq 1) { Write-Host $Parameter -NoNewLine -ForegroundColor "White" }
        }
        Write-Host 
    }
}

function Format-Timestamp {
    param ([string]$InputDate)
    $FormattedDate = Get-Date $InputDate -Format "yyyy-MM-dd HH:mm:ss"
    $returnValue = "{ts '" + $FormattedDate + "'}"
    return $returnValue
}

function Format-StartStopTimestamp {
    param ([string]$InputDate)
    $returnValue = Get-Date $InputDate -Format "yyyyMMddHHmmss"
    return $returnValue
}

# Start up!
Write-Log 1, "GSSync Scheduler v1.00 (C)2022 Goldstar Software Inc."

# Load Assembly
#[void]
Write-Log 5, "Loading the database SQL components..."
$junk = [System.Reflection.Assembly]::LoadWithPartialName("Pervasive.Data.SqlClient")

# Open a connection to the GSSyncDB Database so that we are ready to go....
Write-Log 4, "Connecting to Database ", $DBName, " on ", $DBServerName
$SqlConnection = New-Object Pervasive.Data.SqlClient.PsqlConnection
$SqlConnection.ConnectionString = 'ServerName = ' + $DBServerName + '; DatabaseName = ' + $DBName + ';'
try
{
    Write-Log 5, "SQL Connection String is ", $SqlConnection.ConnectionString
    $SqlConnection.Open();
}
catch
{
    Write-Log 2, 'Scheduler database ', $DBName, ' not found on server ', $DBServerName, '. Exiting....'
    Write-Log 2, $_
    exit(1)
}

# Verify existence of the Log Table, or create it if needed
Write-Log 5, "Verifying the Log table..."
$SqlCmd = New-Object Pervasive.Data.SqlClient.PsqlCommand
$SqlCmd.Connection = $SqlConnection
try
{
    $SqlCmd.CommandText = "SELECT TOP 1 JobId FROM Log"
    Write-Log 5, "SQL Statement is ", $SqlCmd.CommandText
    $reader = $sqlCmd.ExecuteReader();
    $reader.Close()
}
catch
{
    Write-Log 2, 'Log table not found. Creating....'
    try
    {
        $SqlCmd.CommandText = "
CREATE TABLE Log (
ID 				IDENTITY,
JobID 			INTEGER NOT NULL,
JobName 		VARCHAR(32) NOT NULL,
StartTime		TIMESTAMP,
EndTime			TIMESTAMP,
Status			INTEGER,
CommandLine		LONGVARCHAR
);
CREATE INDEX IDTime on Log(JobID, StartTime);
CREATE INDEX NameTime on Log(JobName, StartTime);
CREATE INDEX StatusName on Log(Status, JobName);
"
        Write-Log 5, "SQL Statement is ", $SqlCmd.CommandText
        $SqlCmd.ExecuteNonQuery() | Out-Null
    }
    catch
    {
        Write-Log 2, 'Log table could not be created, exiting....'
        Write-Log 2, $_
    }
    try
    {
        $SqlCmd.CommandText = "
CREATE PROCEDURE CleanLog (in :numdays INTEGER = 90) WITH DEFAULT HANDLER
AS BEGIN
   DECLARE :thisid INTEGER;
   DECLARE cur1 CURSOR FOR SELECT ID FROM Log WHERE StartTime < CURDATE() - :numdays FOR UPDATE;
   OPEN cur1;
   FETCH NEXT FROM cur1 into :thisid;
   WHILE (SQLSTATE = '00000') DO
      DELETE WHERE CURRENT OF cur1;
      FETCH NEXT FROM cur1 into :thisid;
   END WHILE;
   CLOSE cur1;
END;
"
        Write-Log 5, "SQL Statement is ", $SqlCmd.CommandText
        $SqlCmd.ExecuteNonQuery() | Out-Null
    }
    catch
    {
        Write-Log 2, 'Procedure CleanLog could not be created, exiting....'
        Write-Log 2, $_
    }
}

# Verify existence of the Jobs Table, or create it if needed
Write-Log 5, "Verifying the Jobs table..."
$SqlCmd = New-Object Pervasive.Data.SqlClient.PsqlCommand
$SqlCmd.Connection = $SqlConnection
try
{
    $SqlCmd.CommandText = "SELECT TOP 1 ID FROM Jobs"
    Write-Log 5, "SQL Statement is ", $SqlCmd.CommandText
    $reader = $sqlCmd.ExecuteReader();
    $reader.Close()
}
catch
{
    # Write-Log 2, $_
    Write-Log 2, 'Jobs table not found. Creating....'
    try
    {

        $SqlCmd.CommandText = "
CREATE TABLE Jobs (
ID 				IDENTITY,
Name 			VARCHAR(32) NOT NULL,
Priority		INTEGER DEFAULT 0,
LastRunStatus		INTEGER,
LastRunStartTime	TIMESTAMP,
LastRunEndTime		TIMESTAMP,
NextStartTime		TIMESTAMP,
RunInterval     INTEGER,
ScheduleValidFrom	TIME,
ScheduleValidUntil	TIME,
LogLevel		INTEGER,
LogFilePath		VARCHAR(128),
ConfigFilePath	VARCHAR(128),
Override1		VARCHAR(50),
Override2		VARCHAR(50),
Override3		VARCHAR(50),
Override4		VARCHAR(50),
Override5		VARCHAR(250),
SyncStartTimeReference	TIMESTAMP,
SyncEndTimeReference TIMESTAMP
);
CREATE UNIQUE INDEX NameKey ON Jobs(Name);
CREATE INDEX PriorityName ON Jobs(Priority, Name);
CREATE INDEX PriorityTime ON Jobs(Priority, NextStartTime);
CREATE INDEX TimePriority ON Jobs(NextStartTime, Priority);
"
        Write-Log 5, "SQL Statement is ", $SqlCmd.CommandText
        $SqlCmd.ExecuteNonQuery() | Out-Null
    }
    catch
    {
        Write-Log 2, 'Jobs table could not be created, exiting....'
        Write-Log 2, $_
    }
    try
    {
        $SqlCmd.CommandText = "
CREATE PROCEDURE CloneJob (in :oldname VARCHAR(32), in :newname VARCHAR(32)) WITH DEFAULT HANDLER
AS BEGIN
   INSERT INTO Jobs(Name, RunInterval, ScheduleValidFrom, ScheduleValidUntil, LogLevel, LogFilePath, ConfigFilePath, Override1, Override2, Override3, Override4, Override5, SyncStartTimeReference, SyncEndTimeReference)
   SELECT :newname, RunInterval, ScheduleValidFrom, ScheduleValidUntil, LogLevel, LogFilePath, ConfigFilePath, Override1, Override2, Override3, Override4, Override5, SyncStartTimeReference, SyncEndTimeReference
   FROM Jobs WHERE Name = :oldname;   
END;
"
        Write-Log 5, "SQL Statement is ", $SqlCmd.CommandText
        $SqlCmd.ExecuteNonQuery() | Out-Null
    }
    catch
    {
        Write-Log 2, 'Procedure CloneJob could not be created, exiting....'
        Write-Log 2, $_
    }
}
Write-Log 4, "All database tables validated, clear to proceed."

# Open a second connection to the GSSyncDB Database so that we can update data as needed
Write-Log 4, "Connecting to Database ", $DBName, " on ", $DBServerName
$SqlUpdateConnection = New-Object Pervasive.Data.SqlClient.PsqlConnection
$SqlUpdateConnection.ConnectionString = 'ServerName = ' + $DBServerName + '; DatabaseName = ' + $DBName + ';'
try
{
    Write-Log 5, "Connection String is ", $SqlUpdateConnection.ConnectionString
    $SqlUpdateConnection.Open();
    $SqlUpdateCmd = New-Object Pervasive.Data.SqlClient.PsqlCommand
    $SqlUpdateCmd.Connection = $SqlConnection
}
catch
{
    Write-Log 2, 'Unable to open secondary connection to Scheduler database ', $DBName, '. Exiting....'
    Write-Log 2, $_
    exit(1)
}

$KeepRunning = $True
while($KeepRunning)
{
    $LoopStartTime = Get-Date
    Write-Log 1, "Scanning at ", $LoopStartTime

    $SqlCmd.CommandText = "SELECT * FROM Jobs WHERE Priority > 0"
    Write-Log 5, "SQL Statement is ", $SqlCmd.CommandText
    $JobReader = $sqlCmd.ExecuteReader();
    while($JobReader.Read()) 
    {
        Write-Log 4, "Examining Job ", $JobReader["Name"], "(", $JobReader["ID"], ")"

        # Assume all jobs will run, and then filter them out as needed
        $RunThisJob = $True

        # Check the allowable times for this job to run and skip it if outside that range
        Write-Log 5, "-Scheduled from ", $JobReader["ScheduleValidFrom"], " to ", $JobReader["ScheduleValidUntil"]
        if ($JobReader["ScheduleValidFrom"] -is [DBNull] -or $JobReader["ScheduleValidUntil"] -is [DBNull])
        {
            Write-Log 5, "(No schedule, Job will run)"
        }
        else
        {
            Write-Log 5, "-Loop Time  : ", $LoopStartTime
            Write-Log 5, "-Valid From : ", $JobReader["ScheduleValidFrom"]
            Write-Log 5, "-Valid Until: ", $JobReader["ScheduleValidUntil"]
            if($JobReader["ScheduleValidFrom"] -ge $JobReader["ScheduleValidUntil"])
            {
                Write-Log 3, "Warning: Job ScheduleValidFrom >= ScheduleValidUntil -- Job will not run."
            }
            if($JobReader["ScheduleValidFrom"] -le $LoopStartTime -and $LoopStartTime -le $JobReader["ScheduleValidUntil"])
            {
                Write-Log 5, "(Job inside scheduled times)"
            }
            else
            {
                Write-Log 5, "(Job out of scheduled times)"
                $RunThisJob = $False
            }
        }

        # Check to see if this job is due to fire yet and skip if not...
        Write-Log 5, "-NextStartTime is ", $JobReader["NextStartTime"]
        if ($JobReader["NextStartTime"] -is [DBNull])
        {
            Write-Log 5, "-No next time, Job will run for the first time"
        }
        else
        {
            if ($JobReader["NextStartTime"] -le $LoopStartTime)
            {
                Write-Log 5, "-Job will run"
            }
            else
            {
                Write-Log 5, "-Job not due yet"
                $RunThisJob = $False
            }
        }

        if($RunThisJob)
        {
            $JobStartedAt = Get-Date
            Write-Log 1, "Starting Job ", $JobReader["Name"], "(", $JobReader["ID"], ") at ", $JobStartedAt

            $SqlUpdateCmd.CommandText = "UPDATE Jobs SET LastRunEndTime = NULL, LastRunStatus = NULL, LastRunStartTime = " + 
                    (Format-Timestamp $JobStartedAt) + " WHERE ID = " + $JobReader["ID"]
            Write-Log 5, "SQL Statement is ", $SqlUpdateCmd.CommandText
            $SqlUpdateCmd.ExecuteNonQuery() | Out-Null

            # Build the proper command line for this job
            $JobArguments = ""
            if(!($JobReader["ConfigFilePath"] -is [DBNull]))
            {
                $JobArguments = " /CF" + $JobReader["ConfigFilePath"]
            }
            if(!($JobReader["LogLevel"] -is [DBNull]))
            {
                $JobArguments += " /LL" + $JobReader["LogLevel"]
            }
            if(!($JobReader["LogFilePath"] -is [DBNull]))
            {
                $JobArguments += " /LF" + $JobReader["LogFilePath"]
            }

            # This Logic is tricky...see documentation!
            $StartTimeReference = ""
            $EndTimeReference = ""
            $UpdateSyncTimes = $False
            if($JobReader["SyncStartTimeReference"] -is [DBNull])
            {
                if($JobReader["SyncEndTimeReference"] -is [DBNull])
                {
                    # Do Nothing, because we are not leveraging times
                    $UpdateSyncTimes = $False
                    Write-Log 5, "Both Times NULL -- No updates to make"
                }
                else
                {
                    # New run with all records to the specified end time
                    $StartTimeReference = ""
                    $EndTimeReference = Format-StartStopTimestamp($JobReader["SyncEndTimeReference"])
                    $UpdateSyncTimes = $True
                    $NextSyncStart = $JobReader["SyncEndTimeReference"]
                    $NextSyncEnd = $LoopStartTime
                    Write-Log 5, "End Time Only -- Changing times to ", $NextSyncStart, " & ", $NextSyncEnd 
                }
            }
            else
            {
                if($JobReader["SyncEndTimeReference"] -is [DBNull])
                {
                    # New run with all records from the specified start time to the LoopTime
                    $StartTimeReference = Format-StartStopTimestamp($JobReader["SyncStartTimeReference"])
                    $EndTimeReference = Format-StartStopTimestamp($LoopStartTime)
                    $UpdateSyncTimes = $True
                    $NextSyncStart = $LoopStartTime
                    $NextSyncEnd = $LoopStartTime
                    Write-Log 5, "Start Time Only -- Changing times to ", $NextSyncStart, " & ", $NextSyncEnd 
                }
                else
                {
                    if($JobReader["SyncEndTimeReference"] -eq $JobReader["SyncStartTimeReference"]) 
                    {
                        # If times are the same, a normal delta job is running, update to new looptime
                        $StartTimeReference = Format-StartStopTimestamp($JobReader["SyncStartTimeReference"])
                        $EndTimeReference = Format-StartStopTimestamp($LoopStartTime)
                        $UpdateSyncTimes = $True
                        $NextSyncStart = $LoopStartTime
                        $NextSyncEnd = $LoopStartTime
                        Write-Log 5, "Both Times Equal -- Changing times to ", $NextSyncStart, " & ", $NextSyncEnd 
                    }
                    else
                    {
                        # User wanted a run between these specific times
                        $StartTimeReference = Format-StartStopTimestamp($JobReader["SyncStartTimeReference"])
                        $EndTimeReference = Format-StartStopTimestamp($JobReader["SyncEndTimeReference"])
                        if(!($JobReader["RunInterval"] -is [DBNull]))
                        {
                            # If user has a run interval, this is a lagging automation, so update accordingly
                            $UpdateSyncTimes = $True
                            $NextSyncStart = $JobReader["SyncEndTimeReference"]
                            $NextSyncEnd = $LoopStartTime
                            Write-Log 5, "RunInterval Set -- Changing times to ", $NextSyncStart, " & ", $NextSyncEnd 
                        }
                        else
                        {
                            # User manually ran this one time -- do not update times when done!
                            $UpdateSyncTimes = $False
                            Write-Log 5, "Run-Once Job -- No time changes"
                        }

                    }

                }
            }
            if($StartTimeReference -ne "")
            {
                $JobArguments += " /ST" + $StartTimeReference
            }
            if($EndTimeReference -ne "")
            {
                $JobArguments += " /ET" + $EndTimeReference
            }

            if(!($JobReader["Override1"] -is [DBNull]))
            {
                $JobArguments += " " + $JobReader["Override1"]
            }
            if(!($JobReader["Override2"] -is [DBNull]))
            {
                $JobArguments += " " + $JobReader["Override2"]
            }
            if(!($JobReader["Override3"] -is [DBNull]))
            {
                $JobArguments += " " + $JobReader["Override3"]
            }
            if(!($JobReader["Override4"] -is [DBNull]))
            {
                $JobArguments += " " + $JobReader["Override4"]
            }
            if(!($JobReader["Override5"] -is [DBNull]))
            {
                $JobArguments += " " + $JobReader["Override5"]
            }
                
            # Run the job here!
            Write-Log 4, "Starting Job: ", $PathToGSSync, " ", $JobArguments
            $CalledProcess = Start-Process -Wait -PassThru -FilePath $PathToGSSync -ArgumentList $JobArguments
            $JobStatus = $CalledProcess.ExitCode
            Write-Log 4, "Status Code Returned: ", $JobStatus
         
            # Job is done, determine elapsed time
            $JobCompletedAt = Get-Date
            Write-Log 4, "Completed Job ", $JobReader["Name"], "(", $JobReader["ID"], ") at ", $JobCompletedAt
            $ElapsedTime = $JobCompletedAt - $JobStartedAt
            Write-Log 4, "Elapsed Time: ", $ElapsedTime

            # Create a new Log Record for this run
            $SqlUpdateCmd.CommandText = "INSERT INTO Log(JobID, JobName, StartTime, EndTime, Status, CommandLine) VALUES ( " + 
                    $JobReader["ID"] + ", '" + $JobReader["Name"] + "', " + 
                    (Format-Timestamp $JobStartedAt) + ", " +
                    (Format-Timestamp $JobCompletedAt) + ", " +
                    $JobStatus + ", '" + $JobArguments + "')"
            Write-Log 5, "Log SQL Statement is ", $SqlUpdateCmd.CommandText
            try
            {
                $SqlUpdateCmd.ExecuteNonQuery() | Out-Null
            }
            catch
            {
                Write-Log 2, "Log Log Failed: ", $SqlUpdateCmd.CommandText
                Write-Log 2, $_
            }

            # Now, figure out what needs to change inside the Job record for the next run (starting with the easy ones)....
            $NewJobValues = "LastRunEndTime = " + (Format-Timestamp $JobCompletedAt)
            $NewJobValues += ", LastRunStatus = " + $JobStatus

            # Next, check the Job Interval and set the new time, or disable the job if it was a run-once request.
            Write-Log 5, "Job Interval: ", $JobReader["RunInterval"]
            if ($JobReader["RunInterval"] -is [DBNull])
            {
                Write-Log 3, "No Job Interval set for ", $JobReader["Name"], "(", $JobReader["ID"], ") -- Disabling Job!"
                $NextStartTime = $JobCompletedAt
                $NewJobValues += ", Priority = 0"
            }
            else
            {
                $NextStartTime = $LoopStartTime.AddMinutes($JobReader["RunInterval"])
            }
            $NewJobValues += ", NextStartTime = " + (Format-Timestamp $NextStartTime) 

            # Update the Sync Times if needed
            if($UpdateSyncTimes)
            {
                $NewJobValues += ", SyncStartTimeReference = " + (Format-Timestamp $NextSyncStart)
                $NewJobValues += ", SyncEndTimeReference = " + (Format-Timestamp $NextSyncEnd)
            }

            # Time to set the new Job record values
            $SqlUpdateCmd.CommandText = "UPDATE Jobs SET " + $NewJobValues + " WHERE ID = " + $JobReader["ID"]
            Write-Log 5, "SQL Statement is ", $SqlUpdateCmd.CommandText
            try
            {
                $SqlUpdateCmd.ExecuteNonQuery() | Out-Null
            }
            catch
            {
                Write-Log 2, "Job Update Statement Failed: ", $SqlUpdateCmd.CommandText
                Write-Log 2, $_
            }
        }
        else
        {
            Write-Log 4, "Skipping Job ", $JobReader["Name"], "(", $JobReader["ID"], ")"
        }

    }
    # Clean up from this loop and sleep for a bit...
    $JobReader.Close()
    [system.gc]::Collect();

    # Now sleep for a short time to let the server breathe between runs
    Write-Log 3, "Sleeping for ", $SleepSeconds, " seconds..."
    $SleepRemaining = $SleepSeconds
    while($SleepRemaining -gt 0)
    {
        Start-Sleep -Seconds 1
        $SleepRemaining -= 1
        try 
        {
            if ([console]::KeyAvailable)
            {
                $x = [System.Console]::ReadKey()
                switch ($x.key)
                {
                    Escape { $SleepRemaining = 0; $KeepRunning = $False }
                    Enter { $SleepRemaining = 0 }
                    D1 { $LogLevel = 1; Write-Host "Log Level now 1" }
                    D2 { $LogLevel = 2; Write-Host "Log Level now 2" }
                    D3 { $LogLevel = 3; Write-Host "Log Level now 3" }
                    D4 { $LogLevel = 4; Write-Host "Log Level now 4" }
                    D5 { $LogLevel = 5; Write-Host "Log Level now 5" }
                    #Default { "Key " + $x.key + "Pressed" }
                }
            } 
        }
        catch
        { }
    }
   
} # Looping forever (or at least until the user hits Escape)    
Write-Log 1
Write-Log 1, "Terminating GSSync Scheduler..."
$SqlConnection.Close()
$SqlUpdateConnection.Close()
[Pervasive.Data.SqlClient.PsqlConnection]::ClearAllPools();
exit
