### CONNECTING MySql.Data.dll LIBRARY
Add-Type -Path 'C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.19\Assemblies\v4.5.2\MySql.Data.dll'

### SET FOLDER TO WATCH + FILES TO WATCH + SUBFOLDERS YES/NO
$watcher = New-Object System.IO.FileSystemWatcher
$watcher.Path = "D:\source"
$watcher.Filter = "*.pdf"
$watcher.IncludeSubdirectories = $true
$watcher.EnableRaisingEvents = $true

### DEFINE ACTIONS AFTER AN EVENT IS DETECTED
$action_create = { $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString = 'Server=127.0.0.1; Port=3306; Database=test; Uid=root; Pwd=123123dan;' }
    $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
    $sql.Connection = $Connection
    $Connection.Open()
    $pattern = "[ \w-,]+(\.[\w]+)+"
    $name = [regex]::match($Event.SourceEventArgs.Name, $pattern)
    $path = [regex]::escape($Event.SourceEventArgs.FullPath)
    $changeType = $Event.SourceEventArgs.ChangeType
    $sql.CommandText = "INSERT INTO conclusion_files (filename, filepath) VALUES ('$name','$path')"
    $sql.ExecuteNonQuery()
    $sql.CommandText = "INSERT INTO conclusion_logs (new_filepath, action_name) VALUES ('$path',(SELECT ID FROM action_types WHERE action_name LIKE '$changeType'))"
    $sql.ExecuteNonQuery()
    Write-Host "$(Get-Date), $changeType, $old_path, $path"
    $Connection.Close()
}

$action_delete = { $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString = 'Server=127.0.0.1; Port=3306; Database=test; Uid=root; Pwd=123123dan;' }
    $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
    $sql.Connection = $Connection
    $Connection.Open()
    $path = [regex]::escape($Event.SourceEventArgs.FullPath)
    $changeType = $Event.SourceEventArgs.ChangeType
    $sql.CommandText = "DELETE FROM conclusion_files WHERE filepath = '$path'"
    $sql.ExecuteNonQuery()
    $sql.CommandText = "INSERT INTO conclusion_logs (old_filepath, action_name) VALUES ('$path',(SELECT ID FROM action_types WHERE action_name LIKE '$changeType'))"
    $sql.ExecuteNonQuery()
    Write-Host "$(Get-Date), $changeType, $old_path, $path"
    $Connection.Close()
}

$action_rename = { $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString = 'Server=127.0.0.1; Port=3306; Database=test; Uid=root; Pwd=123123dan;' }
    $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
    $sql.Connection = $Connection
    $Connection.Open()
    $pattern = "[ \w-,]+(\.[\w]+)+"
    $name = [regex]::match($Event.SourceEventArgs.Name, $pattern)
    $path = [regex]::escape($Event.SourceEventArgs.FullPath)
    $old_path = [regex]::escape($Event.SourceEventArgs.OldFullPath)
    $changeType = $Event.SourceEventArgs.ChangeType
    $sql.CommandText = "UPDATE conclusion_files SET filename = '$name', filepath = '$path'  WHERE filepath = '$old_path'"
    $sql.ExecuteNonQuery()
    $sql.CommandText = "INSERT INTO conclusion_logs (old_filepath, new_filepath, action_name) VALUES ('$old_path', '$path', (SELECT ID FROM action_types WHERE action_name LIKE '$changeType'))"
    $sql.ExecuteNonQuery()
    Write-Host "$(Get-Date), $changeType, $old_path, $path"
    $Connection.Close()
}

$action_change = { $Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString = 'Server=127.0.0.1; Port=3306; Database=test; Uid=root; Pwd=123123dan;' }
    $sql = New-Object MySql.Data.MySqlClient.MySqlCommand
    $sql.Connection = $Connection
    $Connection.Open()
    $path = [regex]::escape($Event.SourceEventArgs.FullPath)
    $changeType = $Event.SourceEventArgs.ChangeType
    $sql.CommandText = "UPDATE conclusion_files SET last_change = now() WHERE filepath = '$path'"
    $sql.ExecuteNonQuery()
    $sql.CommandText = "INSERT INTO conclusion_logs (old_filepath, action_name) VALUES ('$path', (SELECT ID FROM action_types WHERE action_name LIKE '$changeType'))"
    $sql.ExecuteNonQuery()
    Write-Host "$(Get-Date), $changeType, $old_path, $path"
    $Connection.Close()
}

### DECIDE WHICH EVENTS SHOULD BE WATCHED
Register-ObjectEvent $watcher "Created" -Action $action_create
Register-ObjectEvent $watcher "Deleted" -Action $action_delete
Register-ObjectEvent $watcher "Renamed" -Action $action_rename
Register-ObjectEvent $watcher "Changed" -Action $action_change
while ($true) { sleep 5 }