Imports System.IO
Imports System.IO.Compression
Imports MySql.Data
Imports MySql.Data.MySqlClient

Module Module1
    Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpAppName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Integer, ByVal lpFileName As String) As Integer

    Private Function ReadINI(ByVal sSection As String, ByVal sKey As String, ByVal sIniFileName As String)
        Dim nLength As Integer
        Dim sTemp As String
        Dim lsTemp As Integer
        sTemp = Space(255)
        lsTemp = Len(sTemp)
        nLength = GetPrivateProfileString(sSection, sKey, "", sTemp, lsTemp, sIniFileName)
        Return Left(sTemp, nLength)
    End Function

    Sub Main()
        Console.Title = "Psychostats GeoIP Updater"
        Console.BackgroundColor = ConsoleColor.Black
        Console.ForegroundColor = ConsoleColor.Red

        Dim logWriter As New StreamWriter("log.txt", True)

        If Not File.Exists(My.Application.Info.DirectoryPath & "\config.ini") Then
            Try
                Using sw As New StreamWriter("config.ini")
                    sw.WriteLine("[MySql]")
                    sw.WriteLine("# Хост должен быть IP адресом, DNS имена не распознаются.")
                    sw.WriteLine("dbhost = 127.0.0.1")
                    sw.WriteLine("dbport = 3306")
                    sw.WriteLine("dbname = psychostats")
                    sw.WriteLine("dbuser = user")
                    sw.WriteLine("dbpass = password")
                    sw.WriteLine("dbtblprefix = ps_")
                End Using
            Catch ex As Exception
                Console.WriteLine(ex.Message)
                logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
                logWriter.Close()
                Threading.Thread.Sleep(5000)
                Exit Sub
            End Try
        End If

        Dim iniPath As String = My.Application.Info.DirectoryPath & "\config.ini"

        'Удаление старых файлов
        If File.Exists(My.Application.Info.DirectoryPath & "\GeoIPCountryCSV.zip") Then
            Try
                My.Computer.FileSystem.DeleteFile(My.Application.Info.DirectoryPath & "\GeoIPCountryCSV.zip")
            Catch ex As Exception
                Console.WriteLine(ex.Message)
                logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
                logWriter.Close()
                Threading.Thread.Sleep(5000)
                Exit Sub
            End Try
        End If

        If File.Exists(My.Application.Info.DirectoryPath & "\GeoIPCountryWhois.csv") Then
            Try
                My.Computer.FileSystem.DeleteFile(My.Application.Info.DirectoryPath & "\GeoIPCountryWhois.csv")
            Catch ex As Exception
                Console.WriteLine(ex.Message)
                logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
                logWriter.Close()
                Threading.Thread.Sleep(5000)
                Exit Sub
            End Try
        End If

        'Загрузка базы IP адресов
        Console.WriteLine("Загрузка базы IP адресов...")
        Try
            My.Computer.Network.DownloadFile("http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip", My.Application.Info.DirectoryPath & "\GeoIPCountryCSV.zip")
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
            logWriter.Close()
            Threading.Thread.Sleep(5000)
            Exit Sub
        End Try

        'Распаковка базы IP адресов
        Console.WriteLine("Распаковка базы IP адресов...")
        Dim sh As New Shell32.Shell()
        Dim output As Shell32.Folder = sh.NameSpace(My.Application.Info.DirectoryPath)
        Dim input As Shell32.Folder = sh.NameSpace(My.Application.Info.DirectoryPath & "\GeoIPCountryCSV.zip")
        output.CopyHere(input.Items, 16)

        'Загрузка базы в массив
        Dim GeoIPDB As New List(Of String)
        For Each record In File.ReadAllLines("GeoIPCountryWhois.csv")
            GeoIPDB.Add(record.Replace("""", ""))
        Next

        'Составление списка стран и удаление дубликатов
        Dim geoip_cc As New List(Of String)
        For Each line In GeoIPDB
            Dim sline() As String = line.Split(",")
            geoip_cc.Add(sline(4) & "," & sline(5))
        Next
        Dim geoip_cc_distinc = geoip_cc.Distinct()

        'Чтение настроек Mysql из ini файла
        Console.WriteLine("Чтение настроек Mysql из ini файла...")
        Dim dbhost As String = ReadINI("MySQL", "dbhost", iniPath)
        Dim dbport As Integer = ReadINI("MySQL", "dbport", iniPath)
        Dim dbname As String = ReadINI("MySQL", "dbname", iniPath)
        Dim dbuser As String = ReadINI("MySQL", "dbuser", iniPath)
        Dim dbpass As String = ReadINI("MySQL", "dbpass", iniPath)
        Dim dbtblprefix As String = ReadINI("MySQL", "dbtblprefix", iniPath)

        Dim conn As New MySqlConnection("Server=" & dbhost & "; port=" & dbport & "; database=" & dbname & "; User id=" & dbuser & "; password=" & dbpass)
        Dim cmd As New MySqlCommand
        cmd.Connection = conn

        'Соединение с БД
        Try
            conn.Open()
        Catch ex As Exception
            Console.WriteLine("Ошибка подключения к БД: " & ex.Message)
            Console.WriteLine("Выход через 5 секунд...")
            logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
            logWriter.Close()
            clean_files()
            Threading.Thread.Sleep(5000)
            Exit Sub
        End Try

        'Очистка старых записей из БД
        Console.WriteLine("Очистка старых записей в БД...")
        Try
            cmd.CommandText = "TRUNCATE " & dbtblprefix & "geoip_ip"
            cmd.ExecuteNonQuery()

            cmd.CommandText = "TRUNCATE " & dbtblprefix & "geoip_cc"
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
            logWriter.Close()
            clean_files()
            Threading.Thread.Sleep(5000)
            Exit Sub
        End Try

        'Загрузка новых записей в БД
        Console.WriteLine("Загрузка новых записей в БД...")
        Try
            For Each line In GeoIPDB
                Dim Query() As String = line.Split(",")
                cmd.CommandText = "INSERT INTO `" & dbtblprefix & "geoip_ip` (`cc`, `start`, `end`) VALUES ('" & Query(4) & "', " & Query(2) & ", " & Query(3) & ");"
                cmd.ExecuteNonQuery()
            Next

            For Each line In geoip_cc_distinc
                Dim Query() As String = line.Split(",")
                cmd.CommandText = "INSERT INTO `" & dbtblprefix & "geoip_cc` (`cc`, `cn`) VALUES (""" & Query(0) & """, """ & Query(1) & """);"
                cmd.ExecuteNonQuery()
            Next

        Catch ex As Exception
            Console.WriteLine(ex.Message)
            logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
            logWriter.Close()
            clean_files()
            Threading.Thread.Sleep(5000)
            Exit Sub
        End Try

        'Закрытие соединения с БД
        Console.WriteLine("Закрытие соединения с БД...")
        Try
            conn.Close()
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: " & ex.Message)
            logWriter.Close()
            Threading.Thread.Sleep(5000)
            Exit Sub
        End Try

        'Удаление временных файлов
        Console.WriteLine("Удаление временных файлов...")
        clean_files()
        Console.WriteLine("База IP адресов успешно обновлена.")
        logWriter.WriteLine("[" & DateString & "]-[" & TimeOfDay & "]: База IP адресов успешно обновлена.")
        logWriter.Close()
        Threading.Thread.Sleep(5000)
    End Sub

    Private Sub clean_files()
        My.Computer.FileSystem.DeleteFile(My.Application.Info.DirectoryPath & "\GeoIPCountryWhois.csv")
        My.Computer.FileSystem.DeleteFile(My.Application.Info.DirectoryPath & "\GeoIPCountryCSV.zip")
    End Sub

End Module