using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Services;
using System.Web.Mail;
using edu.jhu.Cas.Util;
using edu.jhu.Cas.Servers;
using System.Collections.Generic;
namespace edu.jhu.Cas.Users {
    /// <summary>
    /// This encapsulates the user manipulation methods as a set of webservices. 
    /// THe separation of this from CasJobs was done mainly to reduce the size of the
    /// interface and as it seemed a logical separation. Mostly one wans the Jobs
    /// interface and not this which mainly concerns itself with user administration
    /// the one exception is Login i.e. getWebServivesId - but for a WebService user
    /// they probbaly never call this rather storing that number in a config file
    /// as is done in the CasJobsCL (Command line tool)..
    /// </summary>
    public class CasUsers : System.Web.Services.WebService {
        public long CreateAccount(long wsid, string userid, string name, string email,
            string password, string privileges, int emaillevel, short gvisible) {
            HasPrivilege(wsid, PRIVS.ADMIN);
            userid = userid.Trim();
            //if (!FilterFactory.IsValidSQLToken(userid)) 
                //throw new Exception("UserID may only contain letters and numbers, and must start with a letter.");
            long newwsid = userid.GetHashCode() + (email.GetHashCode() << 32);
            if (newwsid < 0) newwsid = newwsid * -1;
            JobUsers job = new JobUsers();
            
            
            job.SetValue(JobUsers.WEB_SERVICES_ID, newwsid);
            job.SetValue(JobUsers.USERID, userid);
            job.SetValue(JobUsers.NAME, name);
            job.SetValue(JobUsers.EMAIL, email);
            job.SetValue(JobUsers.PASSWORD, Encryptor.MD52Base64Encrypt(password));
            if (privileges != null)
                job.SetValue(JobUsers.PRIVILEGES, privileges);
            else
                job.SetValue(JobUsers.PRIVILEGES, DBNull.Value);
            job.SetValue(JobUsers.EMAIL_LEVEL, emaillevel);
            job.SetValue(JobUsers.CREATED_BY, wsid);
            job.SetValue(JobUsers.GVISIBLE, gvisible);
            try {
                SqlFactory.insert(job, job.CreateAccountStatement);
            } catch (SqlException se) {
                string upper = se.Message.ToUpper();
                if (upper.IndexOf("UNIQUE KEY") > -1 && upper.IndexOf("EMAIL") > -1)
                    throw new CasJobsException("Cannot create account; this email already exists.");
                if (upper.IndexOf("UNIQUE KEY") > -1 && upper.IndexOf("USERSID") > -1)
                    throw new CasJobsException("Cannot create account; this userid already exists.");
                throw;
            }
            return newwsid;
        }
        public void DeleteAccount(long wsid, long deleteme) {
            HasPrivilege(wsid, PRIVS.ADMIN);
            string delGTables = " delete from grouptables where webservicesid = " + deleteme;
            string delGMembers = " delete from groupmembers where webservicesid = " + deleteme;
            string delJobs = " delete from jobs where webservicesid = " + deleteme;
            User u = FindUser(wsid, " webservicesid = " + deleteme)[0];
            string kickAllUsersHack = @"
						alter database " + u.MyDBName + @" set read_only with rollback immediate
						
						alter database " + u.MyDBName + @" set read_write with rollback immediate
						
			";
            string dropMyDB = " drop database " + u.MyDBName;
            SqlFactory.insert(delGTables + delGMembers + delJobs);
            try {
                Server mydbS = GetMydbAdminServerObject(deleteme);
                SqlFactory.runQuery(kickAllUsersHack + dropMyDB, mydbS.ConnectionString);
            } catch { }//this can fail if mydb doesn't exist
            SqlFactory.insert("delete from users where webservicesid = " + deleteme);
        }
        /// <summary>
        /// not yet in full use the notion here is to replicate user informaiton 
        /// between cjobs instalations.
        /// </summary>
        /// <param name="service_wsid"></param>
        /// <param name="u"></param>
        /// <param name="deleteOld"></param>
        /// <returns></returns>
        public bool ReplicateAccount(long service_wsid, User u, bool deleteOld) {
            HasPrivilege(service_wsid, PRIVS.ADMIN);
            u.PWEncrypted = u.PWEncrypted == null ? null : u.PWEncrypted;
            u.Email = u.Email == null ? "" : u.Email;
            u.Name = u.Name == null ? "" : u.Name;
            if (u.WebServicesID == ID.SERVICE_ID)
                return false;//never update these accounts
            if (deleteOld) {
                string qry = "delete from users where webservicesid = " + u.WebServicesID
                    + " or userid = '" + u.UserID.Replace("'", "''") + "' or email = '" + u.Email.Replace("'", "''") + "'";
                SqlFactory.insert(qry);
            }
            JobUsers job = new JobUsers();

            job.SetValue(JobUsers.WEB_SERVICES_ID, u.WebServicesID);
            job.SetValue(JobUsers.USERID, u.UserID);
            job.SetValue(JobUsers.NAME, u.Name);
            job.SetValue(JobUsers.PASSWORD, u.PWEncrypted);
            job.SetValue(JobUsers.PRIVILEGES, u.Privileges);
            job.SetValue(JobUsers.EMAIL, u.Email);
            job.SetValue(JobUsers.EMAIL_LEVEL, u.EmailLevel);
            job.SetValue(JobUsers.CREATED_BY, u.WebServicesID);
            SqlFactory.insert(job, job.ReplicateAccountStatement);
            return true;
        }
        /// <summary>
        ///  even a logging in user is given the anonymous wsid for this action
        /// this is called by the WebServive below
        /// </summary>
        /// <param name="wsid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public long GetWebServiceId(long wsid, string userid) {
            HasPrivilege(wsid, PRIVS.ADMIN);
            SqlCommand c = new SqlCommand("select webservicesid from users where userid = @userid ");
            return long.Parse(
                SqlFactory.scalar("select webservicesid from users where userid = '" + userid + "'").ToString());
        }
        
        public long GetWebServiceId(string userid, string password) {
            if (userid == null || password == null)
                throw new Exception("Please set a userid and/or password.");

            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.USERID, userid);//intentional double-map
            job.SetValue(JobUsers.EMAIL, userid);

            string qry = "select webservicesid from users where userid = " + JobUsers.USERID + " and pwencrypted ";
            if (password != null && password.Length > 0)
                qry += "='" + Encryptor.MD52Base64Encrypt(password) + "'";
            else
                qry += " is null";
            Object o = SqlFactory.scalar(job, qry);
            if (o == null) return -1;
            return long.Parse(o.ToString());
        }
        public string ForgotPassword(long wsid, string id) {
            throw new Exception("This method has been deprecated.  Please send a mail if you'd like to reset your password.");
        }
        public bool UpdateAccount(long wsid, User user) {
            bool isAdmin = HasPrivilegeSafe(wsid, PRIVS.ADMIN);
            user.UserID = user.UserID.Trim();
            if (wsid != user.WebServicesID && !isAdmin)
                throw new CasJobsException(wsid + " does not have permission to modify " + user.WebServicesID);
            //if (!FilterFactory.IsValidSQLToken(user.UserID))
                //throw new Exception("UserID may only contain letters and numbers, and must start with a letter.");
            User[] us = FindUser(ID.SERVICE_ID, "webservicesid = " + user.WebServicesID);
            if (us.Length > 0) {
                JobUsers job = new JobUsers();
                job.SetValue(JobUsers.WEB_SERVICES_ID, user.WebServicesID);//for searching, not updating
                job.SetValue(JobUsers.USERID, user.UserID);
                job.SetValue(JobUsers.NAME, user.Name);
                job.SetValue(JobUsers.EMAIL, user.Email);
                // this gets called from more than one place - sometimes the password passed in
                // is the old one (especially from the admin page) in that case we o not want to
                // encrypt it again cause thats not nice - so best is to pass null
                // if you wan tthe password left as it was
                if (null != user.PWEncrypted && user.PWEncrypted != us[0].PWEncrypted) {
                    job.SetValue(JobUsers.PASSWORD, Encryptor.MD52Base64Encrypt(user.PWEncrypted));
                } else { // put in old pass
                    if (us[0].PWEncrypted == null || us[0].PWEncrypted.Length == 0) {
                        job.SetValue(JobUsers.PASSWORD, DBNull.Value);
                    } else {
                        job.SetValue(JobUsers.PASSWORD, us[0].PWEncrypted);
                    }
                }
                if (user.Privileges != null && user.Privileges.Trim() != "")
                    job.SetValue(JobUsers.PRIVILEGES, user.Privileges);
                else
                    job.SetValue(JobUsers.PRIVILEGES, DBNull.Value);
                //you cannot change web services id
                //you cannot change CreatedByl
                job.SetValue(JobUsers.EMAIL_LEVEL, user.EmailLevel);
                job.SetValue(JobUsers.GVISIBLE, user.GVisible ? 1 : 0);
                //you cannot change time created
                if (isAdmin) {
                    SqlFactory.insert(job, JobUsers.UpdateAccountAdminStatement);
                } else
                    SqlFactory.insert(job, JobUsers.UpdateAccountStatement);

                if (ConfigurationManager.AppSettings["OpenID"] == "true")
                {
                    SqlFactory.insert(job, "DELETE FROM openid WHERE wsid=" + JobUsers.WEB_SERVICES_ID);
                    foreach (string s in user.OpenID)
                    {
                        SqlFactory.insert(job, "INSERT openid(wsid,openid) VALUES(" + JobUsers.WEB_SERVICES_ID + ",'" + s + "')");
                    }
                }
                if (ConfigurationManager.AppSettings["Keystone"] == "true")
                {
                    SqlFactory.insert(job, "DELETE FROM Keystone WHERE wsid=" + JobUsers.WEB_SERVICES_ID);
                    if (user.KeystoneID != null)
                    {
                        SqlFactory.insert(job, "INSERT Keystone(wsid,keystoneid) VALUES(" + JobUsers.WEB_SERVICES_ID + ",'" + user.KeystoneID + "')");
                    }
                }

                return true;
            }
            return false;
        }
        public DataSet FindUserDataSet(long wsid, string predicate) {
            HasPrivilege(wsid, PRIVS.ADMIN);
            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.WEB_SERVICES_ID, wsid);
            string qry = JobUsers.FindUserStatement;
            if (predicate.Length > 1)
                qry += " where ";
            qry += predicate;
            DataSet dataSet = SqlFactory.select(job, qry);
            return dataSet;
        }

        public User[] FindUser(long wsid, string predicate) {
            DataSet ds = FindUserDataSet(wsid, predicate);
            User[] users = DataConverter.dataSet2User(ds);
            if (ConfigurationManager.AppSettings["OpenID"] == ("true"))
            {
                foreach (User user in users)
                {
                    user.OpenID = GetOpenID(wsid, user);
                }
            }
            if (ConfigurationManager.AppSettings["Keystone"] == ("true"))
            {
                foreach (User user in users)
                {
                    user.KeystoneID = GetKeystoneID(wsid, user);
                }
            }
            return users;
        }

        private string GetKeystoneID(long wsid, User user)
        {
            HasPrivilege(wsid, PRIVS.ADMIN);
            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.WEB_SERVICES_ID, wsid);

            DataSet dataSet = SqlFactory.select(job, "SELECT keystoneid FROM Keystone WHERE wsid='" + user.WebServicesID + "'");
            int n = dataSet.Tables[0].Rows.Count;
            string result = null;
            for (int i = 0; i < n; i++)
            {
                result = dataSet.Tables[0].Rows[i].ItemArray[0].ToString();
            }
            return result;
        }

        private string[] GetOpenID(long wsid, User user)
        {
            HasPrivilege(wsid, PRIVS.ADMIN);
            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.WEB_SERVICES_ID, wsid);
            
            DataSet dataSet = SqlFactory.select(job,"SELECT openid FROM openid WHERE wsid='"+user.WebServicesID+"'");
            int n = dataSet.Tables[0].Rows.Count;
            string[] result = new string[n];
            for (int i = 0; i < n; i++)
            {
                result[i] = dataSet.Tables[0].Rows[i].ItemArray[0].ToString();
            }
            return result;
        }
        public string GetUniqueUserId(string userid) {
            JobUsers job = new JobUsers();
            string tmp = userid;
            long x = 0;
            for (; ; ) {
                job.SetValue(JobUsers.USERID, tmp);
                if (SqlFactory.scalar(job, JobUsers.GetUniqueUserIdStatement) == null)
                    return tmp;
                tmp = userid + (x++).ToString();
            }
        }
        public void UploadDataSet(long wsid, string tableName, DataSet dataSet) {
            CreateMyDB(wsid);
            string tmpTableName = "happyTree";
            string startQry = "create table " + tmpTableName + "(x int primary key)";
            using (SqlConnection con = new SqlConnection(MyDBServerObject(wsid).ConnectionString)) {
                con.Open();
                SqlCommand command = new SqlCommand(startQry);
                command.Connection = con;
                try {
                    command.ExecuteNonQuery();
                } catch (Exception e) { Console.Out.WriteLine(e.StackTrace); }
                string extract = "select * from " + tmpTableName;
                SqlDataAdapter adapter = new SqlDataAdapter(extract, con);
                DataSet currentSet = new DataSet();
                adapter.Fill(currentSet);
                DataTable tmpTable = currentSet.Tables[0];
                foreach (DataRow newRow in dataSet.Tables[0].Rows)
                    tmpTable.ImportRow(newRow);
                adapter.Update(currentSet);

                string finishQry = "drop table " + tmpTableName;
                command.CommandText = finishQry;
                command.ExecuteNonQuery();
                con.Close();
            }
        }
        /// <summary>
        /// Throws a CasJobsException if the specified privilege does not exist on wsid account.
        /// </summary>
        /// <param name="wsid"></param>
        /// <param name="privilege"></param>
        public void HasPrivilege(long wsid, string privilege) {
            if (!HasPrivilegeSafe(wsid, privilege))
                throw new CasJobsException("User " + wsid + " does not have " + privilege + " permission");
        }
        /// <summary>
        /// Returns true if wsid has the specified privilege.
        /// </summary>
        /// <param name="wsid"></param>
        /// <param name="privilege"></param>
        public bool HasPrivilegeSafe(long wsid, string privilege) {
            privilege = privilege.Trim();
            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.WEB_SERVICES_ID, wsid);
            string qry = JobUsers.HasPrivilegeStatement.Replace(JobUsers.PRIVILEGES, privilege);
            if (SqlFactory.scalar(job, qry) == null)
                return false;
            else return true;
        }
        public bool HasMyDB(long wsid) {
            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.WEB_SERVICES_ID, wsid);
            return SqlFactory.scalar(job, JobUsers.HasMyDBStatement) != null;
        }
        /// <summary>
        /// finds the database server of the mydb of the given user
        /// </summary>
        /// <param name="wsid"></param>
        /// <returns></returns>
        public Server MyDBServerObject(long wsid) {
            if (!HasMyDB(wsid)) return null;
            /* query will proably return multiple objects without the 'top', but they should be identical */
            string qry = @"
select top 1 m.mydbhost as name, s.queue,s.target,m.usr,m.pass as password,m.server as host,u.mydbname as cat,s.execlimit,s.privs
from mydbhosts m, users u,servers s
where u.mydbhost = m.mydbhost
and s.target like '%mydb%'
and u.webservicesid= " + wsid;

            DataSet ds = SqlFactory.select(qry);
            return DataConverter.dataSet2Server(ds)[0];
        }
        /**
         * returns connectionString of a mydbname
         */
        public string TheirDBConnection(string mydbname) {
            User[] them = FindUser(ID.SERVICE_ID, " mydbname = '" + mydbname + "'");
            return MyDBServerObject(them[0].WebServicesID).ConnectionString;
        }
        //0 user 1 pass
        public string[] MyDBConnectionLogin(long adminID, long wsid) {
            HasPrivilege(adminID, PRIVS.ADMIN);
            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.WEB_SERVICES_ID, wsid);
            DataSet ds = SqlFactory.select(job, JobUsers.MyDBConnectionLoginStatement);
            string[] rst = new string[2];
            rst[0] = ds.Tables[0].Rows[0][0].ToString();
            rst[1] = ds.Tables[0].Rows[0][1].ToString();
            return rst;
        }
        public void CreateMyDB(long wsid) {
            if (HasMyDB(wsid)) return;
            JobUsers job = new JobUsers();
            job.SetValue(JobUsers.WEB_SERVICES_ID, wsid);
            User user = FindUser(ID.SERVICE_ID, "webservicesid = " + wsid)[0];
            string dbName = "MYDB_" + wsid;
            DataSet ds = SqlFactory.select(job, @"		
select top 1 a.mydbhost,path, admin_usr as usr, admin_pw as password, server as host, 'master' as cat
from mydbhosts a, (
	select max(cnt) as cnt, mydbhost from 
	((select u.mydbhost, count(u.mydbhost) as cnt
	from users u 
	where mydbhost is not null
	group by mydbhost)
	union(select distinct mydbhost,0 as cnt from mydbhosts)) a
	group by mydbhost
) b
where a.mydbhost = b.mydbhost and b.cnt < a.limit
			");
            Server s = DataConverter.dataSet2Server(ds)[0];
            if (ds.Tables[0].Rows.Count == 0)
                throw new Exception("No more MyDB's allowed");
            DataRow dr = ds.Tables[0].Rows[0];
            string cHost = dr["mydbhost"].ToString();
            string path = dr["path"].ToString();
            string createQuery = "create database " + dbName + " on (name = " + dbName +
                ", filename='" + path + dbName + "', size = " + user.MyDBMinSize + ", maxsize = " +
                user.MyDBMaxSize +
                @", filegrowth = 5)
alter database "+dbName+ @"
SET RECOVERY SIMPLE
";
            
            try {
                SqlFactory.runQuery(createQuery, s.ConnectionString);
            } catch (Exception e) {//mydb may already exist
                if (e.Message.IndexOf("already exists") == -1)
                    throw;
            }
            setDBAccess(wsid, (new FilterFactory()).SwitchMyDb(s.ConnectionString, dbName));
            SqlFactory.runQuery("update users set mydbname='" + dbName + "',mydbhost='" + cHost +
                "' where webservicesid = " + wsid, SqlFactory.JOBS_STRING);
        }
        private void setDBAccess(long wsid, string cString) {
            Server[] servers = (new CasServers()).FindDistinctOwnerServers(ID.SERVICE_ID);

            // 'errors' should only contain non-fatal exceptions.  these are stored temporaily for debug purposes
            string errors = "";

            foreach (Server server in servers)
                try {
                    errors += maybeAddDBUser(server.User, cString);
                } catch { continue; }
            DataSet ds = SqlFactory.select("select distinct usr from mydbhosts");
            foreach (DataRow dr in ds.Tables[0].Rows)
                try {
                    errors += maybeAddDBUser(dr[0].ToString(), cString);
                } catch { continue; }
        }
        private string maybeAddDBUser(string user, string cString) {
            string errors = "";
            string dboUser = SqlFactory.scalar("select SYSTEM_USER").ToString();
            errors += "(ADDED [" + dboUser + ", " + user + "])";

            //'fake' is not a real user, skip it
            if (user.ToLower().IndexOf("fake") > -1) return errors;

            //assuming the dbo has all appropriate permissions already
            if (user == dboUser) return errors;

            string addUser = "EXEC sp_adduser '" + user + "'";
            string main = "exec dbo.sp_grantdbaccess '" + user + "'";
            string permissions = "grant create table to " + user + "\n" +
                " grant create function to " + user + "\n" +
                " grant create procedure to " + user + "\n" +
                " grant create view to " + user + "\n";
            /* 
             * specifically trying to avoid this exception...
             * User, group, or role 'cjuser' already exists in the current database.
             */
            try {
                SqlFactory.runQuery(addUser, cString);
            } catch (SqlException se) {
                if (se.Message.IndexOf("already exists") > -1) {
                    errors += se.Message + "\n";
                } else throw;
            }
            try {
                SqlFactory.runQuery(main, cString);
            } catch (SqlException se) {
                if (se.Message.IndexOf("already exists") > -1) {
                    errors += se.Message + "\n";
                } else throw;
            }
            try {
                SqlFactory.runQuery(permissions, cString);
            } catch (SqlException se) {
                if (se.Message.IndexOf("already exists") > -1) {
                    errors += se.Message + "\n";
                } else throw;
            }
            return errors;
        }
        private Server GetMydbAdminServerObject(long wsid) {
            string qry = @"
select admin_usr as usr, admin_pw as password, server as host, 'master' as cat, cstringextra 
from mydbhosts m, users u 
where u.mydbhost = m.mydbhost
and u.webservicesid = " + wsid;
            return DataConverter.dataSet2Server(SqlFactory.select(qry))[0];
        }
        public void ChangeDBMaxSize(long serviceid, long targetid, int maxsize) {
            HasPrivilege(serviceid, PRIVS.ADMIN);
            string fileName = "MYDB_" + targetid.ToString();

            
            string qry = @"
alter database " + fileName + @"
MODIFY FILE 
	(NAME='" + fileName + @"',
	MAXSIZE=" + maxsize.ToString() + @")";

            bool dbExists = !(SqlFactory.scalar("SELECT DB_ID('" + fileName + "')").Equals(DBNull.Value));
            if (dbExists)
            {
                edu.jhu.Cas.Util.Server s = GetMydbAdminServerObject(targetid);
                SqlFactory.runQuery("DBCC SHRINKDATABASE(" + fileName + ")", s.ConnectionString);
                SqlFactory.runQuery(qry, s.ConnectionString);
            }

            SqlFactory.insert("update users set mydbmaxsize=" + maxsize + " where webservicesid = " + targetid);
        }
        public static string Revision = "$Revision: 147305 $";

        public void Sync() {
            string target = SYNC.WITH;
            int added = 0, changed = 0;
            bool didupdate = false;
            throw new Exception("Sync disabled");
            /*
            if(SYNC.WITH != null){
                local.far.CasUsers farCUsers = new local.far.CasUsers(target);
                User[] farUsers = farCUsers.FindUser(ID.SERVICE_ID,"lastmodified is not null and lastmodified > "+LastSync().ToOADate());
                foreach(User u in farUsers){
                    didupdate= UpdateAccount(u.WebServicesID,u);
                    changed+=didupdate?1:0;
                    if(!didupdate){
                        try{
                            Console.WriteLine("REPLICATING");
                            added+=ReplicateAccount(ID.SERVICE_ID,u,true)?1:0;
                        }catch{Console.WriteLine("Account creation failed for "+u.UserID);continue;}
                    }
                }
                SqlFactory.insert("insert into synclog(lastrun,recordsChanged,recordsAdded) values(getdate(),"+changed+","+added+")");
            }
             * */
        }
        public DateTime LastSync() {
            object o = SqlFactory.select("select max(lastrun) from synclog").Tables[0].Rows[0][0];
            return o is System.DBNull ? DateTime.MinValue : (DateTime)o;
        }
    }
}