Import-Module MySqlCmdlets #thanks to Jaroslaw Wiechecki http://mysqlcmdlet.wiechecki.it/doku.php
$log_file = "PATH\TO\LOG.html" #html log
$local_path = "PATH\TO\WEBSERVER\ROOT"

#server connect
$userName = 'root' 
$password = ConvertTo-SecureString 'root' -AsPlainText -Force
Connect-MySqlServer -Server localhost -UserName $userName -Password $password | Out-Null

#query
$result = Invoke-MysqlQuery -Query "SELECT dp.ID id, dp.project_name pn, dp.type pt FROM dashboard.projects dp WHERE dp.processed = 0"

#array id
$key = 0

function checkUpdate {
    param($id)
    $result = Invoke-MySqlQuery -Query "SELECT dp.processed pr FROM dashboard.projects dp WHERE dp.ID = $id"
    if ($result.pr -eq "1") {
        return $true
    }
    else {
        return $false
    }
}

#traversing results
foreach ($name in $result.pn) {
    $type = $result.pt[$key] #type of project
    $id = $result.id[$key] #row ID in table dashboard.projects

    if ($type -eq "p") {
        $type_html = "Personal"
    }
    elseif ($type -eq "b") {
        $type_html = "ByteBite"
    }

    #log header
    $content = "<tr><td class='head' colspan=2><span class='head_title'>Project Name: </span><span class='head_name'>$name</span><br><span class='head_subtitle'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + " - " + $type_html + "</td></tr>"

    if ($type -eq "p") {
        
        $path = "PATH\TO\LOCAL_REPO\$name.git"
        if (-not (Test-Path $path)) { #test for directory
            if (New-Item $path -Type Directory) { #creating directory
                
                $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>created new project directory in <strong>$path\</strong></td></tr>"

                #entering the directory
                Invoke-Expression -command "cd $path"
        
                #initialising git repository
                $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>initializing new git repository in <strong>$path\</strong></td></tr>"
                $git_bare = Invoke-Expression -Command "git init --bare"

                #waiting 2s to complete
                Start-Sleep -s 2

                if ($git_bare -match "Initialized empty Git repository in") { #if initialization succeeded
                    $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>new git repository <strong>$path\</strong> initialized</td></tr>"
            
                    #traversing to webserver directory
                    Invoke-Expression -Command "cd $local_path"

                    #cloning to new directory
                    $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>cloning git repository to <strong>$local_path\$name\</strong></td></tr>"
                    $git_clone = git clone $path 2>&1 #output redirecting to stderr and to variable, I cannot get to pass multiline result only to variable, redirecting to stderr seems to work result is in variable

                    #waiting 2s to complete
                    Start-Sleep -s 2

                    if (($git_clone -match "Cloning into") -and ($git_clone -match "done.")) { #if cloning succeded
                        $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>git repository cloned successfully to <strong>$local_path\$name\</strong></td></tr>"

                        #updating table
                        $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>updating table dashboard.projects for project <strong>$name</strong></td></tr>"
                        Invoke-MysqlQuery -Query "UPDATE dashboard.projects dp SET dp.processed = 1 WHERE dp.ID = $id"
                        if (checkUpdate $id) { #update query doesn't provide any result if successfully executed, I've created function to check it
                            #success
                            $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>table dashboard.projects for project <strong>$name</strong> updated successfully</td></tr>"
                        }
                        else {
                            #failure
                            $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>unable to update table dashboard.projects for project <strong>$name</strong></td></tr>"
                        }
                    }
                    else {
                        $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>Unable to clone git repository to <strong>$local_path\$name\</strong></td></tr>"
                    }
                }
                else {
                    $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>Unable to initialize new git repository in <strong>$path\</strong></td></tr>"
                }
            }
            else {
                $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>Unable to create new project directory in <strong>$path\</strong></td></tr>"
            }
        }
        else {
            $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>Project directory <strong>$path\</strong> already exists</td></tr>"
        }
    }
    elseif ($type -eq "b") {

        Import-Module SSHSessions #Thanks to Joakim Borger Svendsen https://www.powershelladmin.com/wiki/SSH_from_PowerShell_using_the_SSH.NET_library

        $repo_path = "C:\PATH\TO\REPOS\$name.git" #path to repository 
        $git_server_IP = "0.0.0.0" #IP addres of git repo server
        $git_create_user = "peter" #user for connecting to git server via ssh and creating repository (needs to have password)
        $git_clone_user = "auto_ssh" #user for cloning repository (doesn't need to have password)
        $git_create_pwd = "password"

        #connecting to git server
        New-SshSession -ComputerName $git_server_IP -Username $git_create_user -Password $git_create_pwd

        #creating new repository
        $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>initializing new git repository in <strong>git-server:$repo_path\</strong></td></tr>"
        $result = Invoke-SShCommand -ComputerName $git_server_IP -Command "git init --bare $repo_path"

        #waiting 2s to complete
        Start-Sleep -s 2

        if ($result -match "Initialized empty Git repository in") {
            #initialising git repository
            $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>new git repository <strong>git-server:$repo_path\</strong> initialized</td></tr>"

            #traversing to webserver directory
            Invoke-Expression -Command "cd $local_path"

            #cloning to new directory
            $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>cloning git repository to <strong>$local_path\$name\</strong></td></tr>"
            $git_clone = git clone $git_clone_user@0.0.0.0:_repos/$name.git 2>&1 #output redirecting to stderr and to variable, IP needs to be hardcoded, I can't get PS to use variable

            #waiting 2s to complete
            Start-Sleep -s 2

            if (($git_clone -match "Cloning into") -and ($git_clone -match "warning: You appear to have cloned an empty repository.")) { #if cloning succeded
                $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>git repository cloned successfully to <strong>$local_path\$name\</strong></td></tr>"

                #updating table
                $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>updating table dashboard.projects for project <strong>$name</strong></td></tr>"
                Invoke-MysqlQuery -Query "UPDATE dashboard.projects dp SET dp.processed = 1 WHERE dp.ID = $id"
                if (checkUpdate $id) { #update query doesn't provide any result if successfully executed, I've created function to check it
                    #success
                    $content += "<tr class='success'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>table dashboard.projects for project <strong>$name</strong> updated successfully</td></tr>"
                }
                else {
                    #failure
                    $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>unable to update table dashboard.projects for project <strong>$name</strong></td></tr>"
                }
            }
            else {
                $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>Unable to clone git repository to <strong>$local_path\$name\</strong></td></tr>"
            }
        }
        else {
            $content += "<tr class='failure'><td class='time_column'>" + (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") + "</td><td class='info_column'>Unable to initialize new git repository in <strong>git-server:$path\</strong></td></tr>"
        }

        Remove-SshSession -RemoveAll
    }
    $content + (Get-Content $log_file) | Set-Content $log_file #write all logs to log file ordered from newest to oldest project

    $key++
}
Disconnect-MySqlServer | Out-Null