using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using DBToolControls;
using System.IO;

namespace DBAdminService {
    /// <summary>
    /// Zusammenfassungsbeschreibung für MySqlService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // Um das Aufrufen dieses Webdiensts aus einem Skript mit ASP.NET-AJAX zuzulassen, heben Sie die Auskommentierung der folgenden Zeile auf. 
    // [System.Web.Script.Services.ScriptService]
    public class MySqlService : System.Web.Services.WebService {
        public static readonly string TransportEncryptionKey = @"b7困!#vS?難\n.어u";
        public List<String> queryResponse;

        public static string configurationFilePath = @"configuration.conf";



        private DBAdminServiceDBCreationReturnValue generateDbFiles(String host, String user, String pw, String db, String customerName)
        {
            DBAdminServiceDBCreationReturnValue result = new DBAdminServiceDBCreationReturnValue();
            try
            {
               
                FileStream fs = File.OpenRead(Server.MapPath(configurationFilePath));
                
                StreamReader sr = new StreamReader(fs);
                StreamWriter sw;
                String conf = sr.ReadToEnd(), tmp, id=null; String DBcnfTmp = null, BakCnf = null, targetFolder = null, targetFolder2 = null; int pos, epos;
                if (conf != "" && conf != null)
                {
                    pos = conf.IndexOf("<DBConfigTemplateFile>");
                    epos = conf.IndexOf("</DBConfigTemplateFile>");
                    if (pos != -1 && epos != -1)
                    {
                        DBcnfTmp = conf.Substring(pos + "<DBConfigTemplateFile>".Length, epos - (pos + "<DBConfigTemplateFile>".Length));
                        result.DBcnfTmp = DBcnfTmp;
                    }

                    pos = conf.IndexOf("<BackupConfigFile>");
                    epos = conf.IndexOf("</BackupConfigFile>");
                    if (pos != -1 && epos != -1)
                    {
                        BakCnf = conf.Substring(pos + "<BackupConfigFile>".Length, epos - (pos + "<BackupConfigFile>".Length));
                        result.BakCnf = BakCnf;
                    }

                    pos = conf.IndexOf("<BeWoPlanerMultitenancy>");
                    epos = conf.IndexOf("</BeWoPlanerMultitenancy>");
                    if (pos != -1 && epos != -1)
                    {
                        targetFolder = conf.Substring(pos + "<BeWoPlanerMultitenancy>".Length, epos - (pos + "<BeWoPlanerMultitenancy>".Length));
                        result.TargetFolder = targetFolder;
                    }

                    pos = conf.IndexOf("<BeWoPlanerBackupPath>");
                    epos = conf.IndexOf("</BeWoPlanerBackupPath>");
                    if (pos != -1 && epos != -1)
                    {
                        targetFolder2 = conf.Substring(pos + "<BeWoPlanerBackupPath>".Length, epos - (pos + "<BeWoPlanerBackupPath>".Length));
                        result.TargetFolder2 = targetFolder2;
                    }

                    // Create Dbcnf File 
                    try
                    {
                        fs = File.OpenRead(DBcnfTmp);
                        sr = new StreamReader(fs);
                        tmp = sr.ReadToEnd();
                        id = db;
                        pos = tmp.IndexOf("<Template_Password>");
                        tmp = tmp.Remove(tmp.IndexOf("<Template_Password>"), "<Template_Password>".Length);
                        tmp = tmp.Insert(pos, pw);

                        pos = tmp.IndexOf("<Template_USERID>");
                        tmp = tmp.Remove(tmp.IndexOf("<Template_USERID>"), "<Template_USERID>".Length);
                        tmp = tmp.Insert(pos, user);

                        pos = tmp.IndexOf("<Template_DATABASE>");
                        tmp = tmp.Remove(tmp.IndexOf("<Template_DATABASE>"), "<Template_DATABASE>".Length);
                        tmp = tmp.Insert(pos, db);

                        fs = File.Create(targetFolder+"\\"+db+".config");
                        sw = new StreamWriter(fs);
                        sw.WriteLine(tmp);
                        sw.Flush();
                        sw.Close();
                        fs.Close();
                        result.DBCnfFileCreated = true;
                    }
                    catch
                    {
                        result.BakFileCreated = false;
                    }

                    try
                    {
                        fs = File.OpenRead(BakCnf);
                        sr = new StreamReader(fs);
                        tmp = sr.ReadToEnd();
                        id = db;

                        pos = tmp.IndexOf("<Kundenname>");
                        tmp = tmp.Remove(tmp.IndexOf("<Kundenname>"), "<Kundenname>".Length);
                        tmp = tmp.Insert(pos, customerName);

                        pos = tmp.IndexOf("<Kundennummer>");
                        tmp = tmp.Remove(tmp.IndexOf("<Kundennummer>"), "<Kundennummer>".Length);
                        tmp = tmp.Insert(pos, db);

                        fs = File.Create(targetFolder2 + "\\" + db + ".db2mail");
                        sw = new StreamWriter(fs);
                        sw.WriteLine(tmp);
                        sw.Flush();
                        sw.Close();
                        fs.Close();
                        result.BakFileCreated = true;
                    }
                    catch
                    {
                        result.BakFileCreated = false;
                    }
                }

                result.Success = true;
                return result;
            }
            catch
            {
                result.Success = false;
                throw new Exception("Could not open configuration file");
                return result;
            }
        }
        private String findDatabaseName(String query)
        {
            String id = query.Substring(query.IndexOf("CREATE DATABASE `") + "CREATE DATABASE `".Length, 10);

            return id;
        }
        [WebMethod]
        public DBAdminServiceReturnValue createDatabase(string userName, string pw, string port, string query, string customerName)
        {
            DBAdminServiceReturnValue ret = new DBAdminServiceReturnValue();
            DBAdminServiceDBCreationReturnValue result = new DBAdminServiceDBCreationReturnValue();
            string connStr = String.Format("server=localhost;user id={0}; password={1}; port={2}; database=mysql; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(port));
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand com = new MySqlCommand(Decrypt(query), conn);

            try
            {
                               
                conn.Open();
                com.ExecuteNonQuery();
                ret.WasSuccessful = true;

            
               result =  this.generateDbFiles("localhost", Decrypt(userName), Decrypt(pw), this.findDatabaseName(Decrypt(query)), Decrypt(customerName));
               if (result.BakFileCreated == false)
                   throw new Exception("Exception BakFileCreated");
               if (result.DBCnfFileCreated == false)
                   throw new Exception("Exception DBCnfFileCreated");
               
            }
            catch (Exception e)
            {
                ret.WasSuccessful = false;
                if(e.Message.Equals("Exception BakFileCreated"))
                    ret.ErrorMessage = String.Format("Konnte db2mail-Datei \"{0}\" nicht in  \"{1}\" anlegen", result.BakCnf, result.TargetFolder2);
                else if(e.Message.Equals("Exception DBCnfFileCreated"))
                    ret.ErrorMessage = String.Format("Konnte Datenbankkonfigurationsdatei \"{0}\" nicht in  \"{1}\" anlegen", result.DBcnfTmp, result.TargetFolder);
                else
                ret.ErrorMessage = String.Format("Ausführen des SQL-Scriptes fehlgeschlagen. {0} ({1})", e.Message, e.GetType().ToString());
              
            }
            
            return ret;
        }

        [WebMethod]
        public string[] GetDatabaseList(string userName, string pw, string port)
        {
            string connStr = String.Format("server=localhost;user id={0}; password={1}; port={2}; database=mysql; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(port));
            List<string> dbList = new List<string>();

            using (MySqlConnection conn = new MySqlConnection(connStr)) {
                using (MySqlCommand cmd = new MySqlCommand("SHOW DATABASES", conn)) {
                    try {
                        conn.Open();
                        MySqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                            dbList.Add(reader.GetString(0));
                    } catch (Exception e) {
                        dbList.Add(String.Format("Auslesen der Datenbanken fehlgeschlagen. {0} ({1})", e.Message, e.GetType().ToString()));
                    }
                }
            }
            return dbList.ToArray();
        }


        [WebMethod]
        public DBAdminServiceReturnValue ExecuteUpdateOnDatabase(string userName, string pw, string port, string db, string query)
        {
            string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3}; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
            DBAdminServiceReturnValue result = new DBAdminServiceReturnValue();

            using (MySqlConnection conn = new MySqlConnection(connStr)) {
                using (MySqlCommand command = new MySqlCommand(Decrypt(query), conn)) {
                    MySqlDataReader sqlReadData;
                    try {
                        conn.Open();
                        //command.ExecuteNonQuery();
                       
                        sqlReadData = command.ExecuteReader();
                        this.queryResponse = new List<String>();
                    while(sqlReadData.Read())
                    {

                        string tmp = "";
                        for (int i = 0; i < sqlReadData.FieldCount; i++)
                        {
                            tmp += sqlReadData.GetName(i) + ":  " + sqlReadData.GetValue(i).ToString() + "\n" ;
                           
                        }
                        tmp += "-------\n";
                        this.queryResponse.Add(tmp);
                        result.QueryAnswer = queryResponse;
                    }
                        result.WasSuccessful = true;
                    } catch (Exception e) {
                        result.WasSuccessful = false;
                        result.ErrorMessage = Encrypt(String.Format("{0} ({1})", e.Message, e.GetType().ToString()));
                        result.QueryAnswer = null;
                    }
                }
            }
            return result;
        }
        [WebMethod]
        public DBAdminServiceReturnValue ExecuteHiddenUpdateOnDatabase(string userName, string pw, string db, string port, string query)
        {
            string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3};pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
            DBAdminServiceReturnValue result = new DBAdminServiceReturnValue();

            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                using (MySqlCommand command = new MySqlCommand(Decrypt(query), conn))
                {
                    MySqlDataReader sqlReadData;
                    try
                    {
                        conn.Open();
                        sqlReadData = command.ExecuteReader();
                        this.queryResponse = new List<String>();
                        while (sqlReadData.Read())
                        {

                            string tmp = "";
                            for (int i = 0; i < sqlReadData.FieldCount; i++)
                            {
                                tmp += sqlReadData.GetValue(i).ToString() + "\n";

                            }
                            
                            this.queryResponse.Add(tmp);
                            result.QueryAnswer = queryResponse;
                        }
                        result.WasSuccessful = true;
                    }
                    catch (Exception e)
                    {
                        result.WasSuccessful = false;
                        result.ErrorMessage = Encrypt(String.Format("{0} ({1})", e.Message, e.GetType().ToString()));
                    }
                }
            }
            return result;
        }

        public List<String> QueryResponse
        {
            get { return this.queryResponse;  }
            private set { this.queryResponse = value; }
        }

      
        [WebMethod]
        public DBAdminServiceReturnValue DatabaseIsReachable(string userName, string pw, string db, string port) {
            string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3}; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
            DBAdminServiceReturnValue result = new DBAdminServiceReturnValue();

            using (MySqlConnection conn = new MySqlConnection(connStr)) {
                try {
                    conn.Open();
                    result.WasSuccessful = true;
                } catch (Exception e) {
                    result.WasSuccessful = false;
                    result.ErrorMessage = Encrypt(e.Message + " (" + e.GetType().ToString() + ")");
                }
            }
            return result;
        }

        [WebMethod]
        public string[] GetTables(string userName, string pw, string db, string port)
        {
            string connStr = String.Format("server=localhost;user id={0}; password={1}; database={2}; port={3}; pooling=false", Decrypt(userName), Decrypt(pw), Decrypt(db), Decrypt(port));
            List<string> tableList = new List<string>();

            using (MySqlConnection conn = new MySqlConnection(connStr)) {
                using (MySqlCommand cmd = new MySqlCommand("SHOW TABLES", conn)) {
                    try {
                        conn.Open();
                        MySqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                            tableList.Add(Encrypt(reader.GetString(0)));
                    } catch (Exception e) {
                        tableList.Add(Encrypt(String.Format("Auslesen der Tabellen fehlgeschlagen. {0} ({1})", e.Message, e.GetType().ToString())));
                    }
                }
            }
            return tableList.ToArray();
        }

        [WebMethod]
        public string CreateNewTenantID() {
            // TODO implement
            // TODO implement create new Database
            System.Threading.Thread.Sleep(500);
            return "0000000000";
        }

        private static string Encrypt(string s) {
            return EncryptionHelper.EncryptString(TransportEncryptionKey, s);
        }

        private static string Decrypt(string s) {
            return EncryptionHelper.DecryptString(TransportEncryptionKey, s);
        }
    }

    public class DBAdminServiceReturnValue {
        bool wasSuccessful = true;
        string errorMessage = "";
        private List<String> queryAnswer = new List<String>();

        public List<String> QueryAnswer
        {
            get { return this.queryAnswer; }
            set { this.queryAnswer = value; }
        }
        public bool WasSuccessful {
            get { return wasSuccessful; }
            set { wasSuccessful = value; }
        }

        public string ErrorMessage {
            get { return errorMessage; }
            set { errorMessage = value; }
        }
    }

    public class DBAdminServiceDBCreationReturnValue
    {
        private bool success;
        public bool Success { get { return success; } set { success = value; } }
        public bool DBCnfFileCreated { get; set; }
        public bool BakFileCreated { get; set; }
        public String TargetFolder { get; set; }
        public String TargetFolder2 { get; set; }
        public String BakCnf { set; get; }
        public String DBcnfTmp { set; get; }
    }
}