using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.IO;
using System.Net;
using Renci.SshNet;
using Renci.SshNet.Common;


namespace LogoApp
{
    class Databaze
    {
        private MySqlConnection pripojeni;
        private SshClient client;
        private string server;
        private string port;
        private string databaze;
        private string uzivatel;
        private string heslo;
        private string ftpserver;
        private string ftpuzivatel;
        private string ftpheslo;
        private string sshhost;
        private string sshport;
        private string sshuzivatel;
        private string sshheslo;

        public Databaze() // konstruktor tridy Databaze
        {
            #region Nacteni prihlasovacich udaju z .ini souboru
            try
            {
                string[] lines = File.ReadAllLines("nastaveni.ini"); // nacteni prihlasovacich udaju do databaze a na FTP server z .ini souboru

                server = lines[0].Substring(7);
                port = lines[1].Substring(5);
                databaze = lines[2].Substring(9);
                uzivatel = lines[3].Substring(9);
                if(lines[4].Substring(6) == "blank")
                {
                    heslo = "";
                }
                else { heslo = lines[4].Substring(6); }
                
                ftpserver = lines[6].Substring(10);
                ftpuzivatel = lines[7].Substring(9);
                if (lines[8].Substring(6) == "blank")
                {
                    ftpheslo = "";
                }
                else { ftpheslo = lines[8].Substring(6); }

                sshhost = lines[10].Substring(9);
                sshport = lines[11].Substring(9);
                sshuzivatel = lines[12].Substring(13);
                sshheslo = lines[13].Substring(10);
            }
            catch
            {
                MessageBox.Show("Neplatný soubor settings.ini!", "Chyba", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Environment.Exit(1);
            }
            #endregion

            string connectionString;
            connectionString = "SERVER=" + server + ";" + "PORT=" + port + ";" + "DATABASE=" + databaze + ";" + "UID=" + uzivatel + ";" + "PASSWORD=" + heslo + ";"; // vyplneni prihlasovacich udaju do databaze

            pripojeni = new MySqlConnection(connectionString);

            client = new SshClient(sshhost, Int32.Parse(sshport), sshuzivatel, sshheslo);
        }

        public bool Pripoj() // metoda pro pripojeni do databaze
        {
            try
            {
                pripojeni.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Server není dostupný");
                        break;

                    case 1045:
                        MessageBox.Show("Nesprávne jméno a/nebo heslo, zkontrolujte údaje v settings.ini");
                        break;
                }
                MessageBox.Show(ex.Message);
                return false;      
            }
        }

        public bool Odpoj() // metoda pro odpojeni od databaze
        {
            try
            {
                pripojeni.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public void Vloz(string uzivatel, string sada, string slovo, string spravnost, string nahravka) // metoda pro nahrani vysledku procvicovani do databaze
        {         
            string query = "INSERT INTO uzivatele (uzivatel, sada, slovo, spravnost, nahravka) VALUES(@uzivatel, @sada, @slovo, @spravnost, @nahravka)";

            MySqlCommand cmd = new MySqlCommand(query, pripojeni);

            cmd.Parameters.AddWithValue("@uzivatel", uzivatel);
            cmd.Parameters.AddWithValue("@sada", sada);
            cmd.Parameters.AddWithValue("@slovo", slovo);
            cmd.Parameters.AddWithValue("@spravnost", spravnost);
            cmd.Parameters.AddWithValue("@nahravka", nahravka);

            cmd.ExecuteNonQuery();
        }

        public bool PripojSSH() // metoda pro pripojeni k serveru pomoci SSH tunelu
        {     
            try
            {
                client.Connect();
                ForwardedPortLocal portSSH = new ForwardedPortLocal(IPAddress.Loopback.ToString(), 4545, IPAddress.Loopback.ToString(), 3306); // namapovani portu pocitace 4545 na port MySQL serveru 3306
                client.AddForwardedPort(portSSH);
                portSSH.Start();
                return true;
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool JePripojenoSSH() // metoda pro zjisteni, zda je jiz aplikace pripojena k serveru pomoci SSH tunelu
        {
            if (client.IsConnected == true)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void NahrajSoubor(string Soubor) // metoda pro nahrani nahravek vyslovnosti uzivatele na FTP server
        {
            FtpWebRequest req = (FtpWebRequest)FtpWebRequest.Create(ftpserver + "/" + Path.GetFileName(Soubor));
            req.Method = WebRequestMethods.Ftp.UploadFile;
            req.Credentials = new NetworkCredential(ftpuzivatel, ftpheslo);
            req.UsePassive = true;
            req.UseBinary = true;
            req.KeepAlive = false;

            FileStream stream = File.OpenRead(Soubor);

            byte[] buffer = new byte[stream.Length];
            stream.Read(buffer, 0, buffer.Length);
            stream.Close();

            Stream reqstream = req.GetRequestStream();
            reqstream.Write(buffer, 0, buffer.Length);
            reqstream.Close();
        }
    }
}
