using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Threading;
using System.IO;
using System.Data.SqlClient;
using edu.jhu.Cas.Jobs;
using edu.jhu.Cas.Servers;
using edu.jhu.Cas.Users;
using edu.jhu.Cas.Util;

using System.Configuration;

namespace edu.jhu.Cas.CJSetup {
    class CJSetup {
        string BA_CSTRING = ConfigurationSettings.AppSettings["BatchAdminCSTRING"];
        string ADMIN_LOGIN = ConfigurationSettings.AppSettings["adminLogin"];
        DataSet serversAndCats;
        public static void Main() {
            CJSetup cjs = new CJSetup();
            cjs.SetupLinks();
            cjs.SetupUsers();
        }
        const string CAT_USERS_QRY = @"execute sp_helpuser";
        const string LINKED_TO_QRY = @"exec sp_linkedservers";
        const string SERVERS_AND_CATS_QRY = @"
select * into #cats from (
    select usr, host, cat, [password] as pw 
    from servers
    where target not like '%MYDB%'
    union
    select mh.usr, mh.server as host, mydbname as cat, mh.pass as pw
    from users u, mydbhosts mh
    where mydbname is not null
    and u.mydbhost = mh.mydbhost
) cats
select distinct host from #cats --servers
select distinct usr from #cats --users
select * from #cats --catalogs
";
        
        string getAdminCString(string host, string cat) {
            string rst = ADMIN_LOGIN;
            rst = "Connection Timeout=5; " + rst;
            rst = "Initial Catalog = "+cat+"; "+rst;
            rst = "Data Source = "+host+"; "+rst;
            return rst;
        }
        public CJSetup() {
            serversAndCats = select(SERVERS_AND_CATS_QRY, BA_CSTRING);
        }
        public void SetupLinks() {
            Console.WriteLine("Checking links ".PadRight(79, '-'));
            foreach (DataRow dr in serversAndCats.Tables[0].Rows) {//all hosts
                string currentHost = dr["host"] as string;
                string currentHostCString = getAdminCString(currentHost, "master");
                try {
                    DataSet linkedTo = select(LINKED_TO_QRY, currentHostCString);//links of current host
                    foreach (DataRow dr2 in serversAndCats.Tables[0].Rows) {//all other hosts
                        string remoteServer = dr2["host"] as string;
                        //if (remoteServer == currentHost) continue;
                        Console.WriteLine(("[" + currentHost + "]").PadRight(50)+" -> "+("[" + remoteServer + "]").PadLeft(25));
                        try {
                            exec(getLinkQuery(remoteServer), currentHostCString);
                        } catch (Exception ex2) {
                            if (ex2.Message.IndexOf("already exists.") == -1)//non fatal
                                Console.WriteLine("ERROR: Failed to create link from [" + currentHost + "] to [" + remoteServer + "]\n" + ex2.Message);
                        }
                    }
                } catch (Exception ex) {
                    Console.WriteLine("ERROR: Could not connect to ["+currentHost+"]\n"+ex.Message);
                }
            }
        }
        public void SetupUsers() {
            Console.WriteLine("Checking users ".PadRight(79, '-'));
            foreach (DataRow dr in serversAndCats.Tables[2].Rows) {//all catalogs
                string localHost = dr["host"] as string;//credentials of this catalog
                string localCat = dr["cat"] as string;
                string localHostCString = getAdminCString(localHost, localCat);
                StringCollection sc = new StringCollection();
                try {
                    DataSet existing_users = select(CAT_USERS_QRY, localHostCString);//all logins of current catalog
                    foreach(DataRow dr2 in serversAndCats.Tables[2].Rows){
                        string remoteHost = dr2["host"] as string;//credentials of remote catalog
                        string remoteLogin = dr2["usr"] as string;
                        string remotePw = dr2["pw"] as string;
                        if (sc.Contains(localHost + localCat + remoteHost + remoteLogin)) continue;
                        sc.Add(localHost + localCat + remoteHost + remoteLogin);
                        Console.WriteLine(("Creating [" + localHost + "].["+localCat+"]").PadRight(50)+" -> "+(remoteHost+"."+remoteLogin).PadLeft(25));
                        try {//map this link to the remote login
                            exec(getLinkLoginQuery(remoteHost, remoteLogin,remotePw), localHostCString); //try to add the remote login as a local login
                        } catch (Exception ex) {//login already exists?
                            Console.WriteLine(ex.Message);
                        }
                    }
                } catch (Exception ex) {
                    Console.WriteLine("ERROR: Could not connect to [" + localHost + "]\n" + ex.Message);
                }
            }
        }
        bool colContainsString(DataTable hasCol, string colName, string s) {
            s = formatDBString(s);
            foreach (DataRow dr in hasCol.Rows) {
                string s2 = formatDBString(dr[colName] as string);
                if (s2.CompareTo(s) == 0) return true;
            }
            return false;
        }
        //void createLink(string 
        string formatDBString(string s) {
            if (s == null) return "";
            return s.Trim().ToUpper();
        }
        string getLinkQuery(string remoteServerName) {
            return "EXEC master.dbo.sp_addlinkedserver @server = N'" + remoteServerName + @"', @srvproduct=N'SQL Server'";                  
        }
        string getLinkLoginQuery(string remoteServerName, string remoteLogin, string remotePW) {
            return "EXEC sp_addlinkedsrvlogin '" + remoteServerName + "', 'false', NULL, '"+remoteLogin+"', '"+remotePW+"'";
        }
        private DataSet select(string qry, string cstring) {
            using (SqlConnection baCon = new SqlConnection(cstring)) {
                baCon.Open();
                using (SqlCommand com = new SqlCommand(qry, baCon)) {
                    SqlDataAdapter adap = new SqlDataAdapter(com);
                    DataSet ds = new DataSet();
                    adap.Fill(ds);
                    return ds;
                }
            }
        }
        private void exec(string qry, string cstring) {
            using (SqlConnection baCon = new SqlConnection(cstring)) {
                baCon.Open();
                using (SqlCommand com = new SqlCommand(qry, baCon)) {
                    com.ExecuteNonQuery();
                }
            }
        }
    }
}