//------------------------------------------------------------------------------
// <copyright file="SES_ConnectionHelper.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------------------




namespace SES.DB{
    using System;
    using System.Web;
    using System.Globalization;
    using System.Collections;
    using System.Collections.Specialized;
    using System.Data;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using System.IO;
    using System.Threading;
    using System.Configuration;
    using System.Web.Util;
    using System.Security.Permissions;
    using System.Web.Hosting;
    using System.Security.Principal;
    using System.Security;
    using System.Diagnostics;
    using System.Web.Configuration;


    public static class ManagementHelper {
        //private const string s_connPrefix = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
        private const string s_connPrefix = "user id=sa;password=!love123;initial catalog=WEB_Security;data source=";
        static private Hashtable _ConnectionsHash = Hashtable.Synchronized(new Hashtable(StringComparer.InvariantCultureIgnoreCase));

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        //private static void BuildConnection(string dbFileName) {
        //    /////////////////////////////////////////////
        //    // Step 0: Check if connection already exists
        //    if (_ConnectionsHash[dbFileName] != null)
        //        return;
        //    /////////////////////////////////////////////
        //    // Step 1: Check if it is a valid connection string
        //    bool isConnString = false;
        //    SqlConnection conn = null;

        //    if (dbFileName.IndexOf(';') >= 0 && dbFileName.IndexOf('=') >= 0) { // Is probably a connection string
        //        try {
        //            conn = new SqlConnection(dbFileName);
        //            try {
        //                conn.Open();
        //                isConnString = true;
        //            }
        //            finally {
        //                conn.Close();
        //            }
        //        }
        //        catch {
        //            isConnString = false;
        //        }
        //    }

        //    if (isConnString) {
        //        _ConnectionsHash.Add(dbFileName, new SES_ConnectionHolder(conn));
        //        return;
        //    }

        //    ////////////////////////////////////////////////////////////////////
        //    // Step 2: Check is it's a full path: use as-is, if it is a full path
        //    if (Path.IsPathRooted(dbFileName)) {
        //        EnsureValidMdbFile(dbFileName);
        //        _ConnectionsHash.Add(dbFileName, new SES_ConnectionHolder(new SqlConnection(s_connPrefix + dbFileName)));
        //        return;
        //    }

        //    ////////////////////////////////////////////////////////////
        //    // Step 3: Ensure that it doesn't try to walk up a directory
        //    if (dbFileName.Contains("..")) {
        //        throw new Exception("File name can not contain dot dot(..): " + dbFileName);
        //    }

        //    ////////////////////////////////////////////////////////////
        //    // Step 4: Get the full path for this (relative) filename
        //    string filename = GetFullPathNameFromDBFileName(dbFileName);

        //    ////////////////////////////////////////////////////////////
        //    // Step 5: Create and add connection
        //    EnsureValidMdbFile(filename);
        //    _ConnectionsHash.Add(dbFileName, new SES_ConnectionHolder(new SqlConnection(s_connPrefix + filename)));
        //}
        private static void BuildConnection(string dbDataSource) {
            /////////////////////////////////////////////
            bool isConnString = false;
            SqlConnection conn = null;

            if (dbDataSource.IndexOf(';') >= 0 && dbDataSource.IndexOf('=') >= 0) { // Is probably a connection string
                try {
                    conn = new SqlConnection(dbDataSource);
                    try {
                        conn.Open();
                        isConnString = true;
                    }
                    catch (Exception ex) {
                        Debug.Print(ex.Message);
                    }
                    finally {
                        conn.Close();
                    }
                }
                catch {
                    isConnString = false;
                }
            }

            if (isConnString) {
                try {
                    _ConnectionsHash.Add(dbDataSource, new ManagementHolder(conn));
                }
                catch (Exception ex) {
                    Debug.Print(ex.Message);
                }
                return;
            }

            _ConnectionsHash.Add(dbDataSource, new ManagementHolder(new SqlConnection(dbDataSource)));//s_connPrefix + dbDataSource)));
        }

        public static int GetApplicationID(SqlConnection connection, string applicationName) {
            return GetApplicationID(connection, applicationName, false);
        }
        public static int GetApplicationID(SqlConnection connection, string applicationName, bool createIfNeeded) {
            SqlCommand lookupCommand = new SqlCommand(
                "SELECT ApplicationId FROM Applications WHERE ApplicationName = @AppName", connection);
            lookupCommand.Parameters.Add(new SqlParameter("@AppName", applicationName));

            object lookupResult = lookupCommand.ExecuteScalar();
            if ((lookupResult != null) && (lookupResult is int)) {
                return (int)lookupResult;
            }

            if (createIfNeeded) {
                SqlCommand createCommand = new SqlCommand("INSERT INTO Applications (ApplicationName) VALUES (@AppName)",
                    connection);
                createCommand.Parameters.Add(new SqlParameter("@AppName", applicationName));

                if (createCommand.ExecuteNonQuery() != 0) {
                    lookupResult = lookupCommand.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is int)) {
                        return (int)lookupResult;
                    }
                }
            }
            return 0;
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        //public static SES_ConnectionHolder GetConnection(string dbFileName, bool revertImpersonation) {
        public static ManagementHolder GetConnection(string dbDataSource, bool revertImpersonation) {
            dbDataSource = dbDataSource.Trim();

            /////////////////////////////////////////////////
            // Lock the connections table, and see if it already exists
            lock (_ConnectionsHash) {
                ManagementHolder holder = (ManagementHolder)_ConnectionsHash[dbDataSource];
                if (holder != null) {//&& !File.Exists(holder.Connection.DataSource)) {
                    _ConnectionsHash.Remove(dbDataSource);
                    holder = null;
                }
                if (holder == null) {
                    BuildConnection(dbDataSource);
                    holder = (ManagementHolder)_ConnectionsHash[dbDataSource];
                }
                if (holder == null) {
                    return null;
                }
                holder.Open(null);
                return holder;
            }
        }

        public static int GetPathID(SqlConnection connection, int applicationID, string path) {
            return GetPathID(connection, applicationID, path, false);
        }

        public static int GetPathID(SqlConnection connection, int applicationID, string path, bool createIfNeeded) {
            SqlCommand lookupCommand = new SqlCommand("SELECT PathId FROM Paths WHERE ApplicationId = @AppId AND Path = @Path",
                connection);
            lookupCommand.Parameters.Add(new SqlParameter("@AppId", applicationID));
            lookupCommand.Parameters.Add(new SqlParameter("@Path", path));

            object lookupResult = lookupCommand.ExecuteScalar();
            if ((lookupResult != null) && (lookupResult is int)) {
                return (int)lookupResult;
            }

            if (createIfNeeded) {
                SqlCommand createCommand = new SqlCommand("INSERT INTO Paths (ApplicationId, Path) VALUES (@AppID, @Path)",
                    connection);
                createCommand.Parameters.Add(new SqlParameter("@AppID", applicationID));
                createCommand.Parameters.Add(new SqlParameter("@Path", path));

                if (createCommand.ExecuteNonQuery() != 0) {
                    lookupResult = lookupCommand.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is int)) {
                        return (int)lookupResult;
                    }
                }
            }

            return 0;
        }

        public static int GetUserID(SqlConnection connection, int applicationID, string userName) {
            return GetUserID(connection, applicationID, userName, false, false, DateTime.Now);
        }
        public static int GetUserID(SqlConnection connection, int applicationID, string userName, bool createIfNeeded) {
            return GetUserID(connection, applicationID, userName, createIfNeeded, false, DateTime.Now);
        }
        public static int GetUserID(SqlConnection connection, int applicationID, string userName, bool createIfNeeded, bool newUserIsAnonymous) {
            return GetUserID(connection, applicationID, userName, createIfNeeded, newUserIsAnonymous, DateTime.Now);
        }
        public static int GetUserID(SqlConnection connection, int applicationID, string userName, bool createIfNeeded, bool newUserIsAnonymous, DateTime lastActivityDate) {
            if (applicationID == 0 || userName == null || userName.Length < 1) // Application doesn't exist or user doesn't exist
                return 0;

            if (connection == null)
                return 0; // Wrong params!

            SqlCommand lookupCommand = new SqlCommand(
                @"SELECT UserId FROM Users WHERE ApplicationId = @AppId AND UserName = @UserName",
                                                            connection);
            lookupCommand.Parameters.Add(new SqlParameter("@AppId", applicationID));
            lookupCommand.Parameters.Add(new SqlParameter("@UserName", userName));

            object lookupResult = lookupCommand.ExecuteScalar();
            if ((lookupResult != null) && (lookupResult is int)) {
                return (int)lookupResult;
            }

            if (createIfNeeded) {
                SqlCommand createCommand = new SqlCommand(@"INSERT INTO Users " +
                                                                @"(ApplicationId, UserName, IsAnonymous, LastActivityDate) " +
                                                                @"VALUES (@AppID, @UserName, @IsAnonymous, @LastActivityDate)",
                                                                connection);
                createCommand.Parameters.Add(new SqlParameter("@AppID", applicationID));
                createCommand.Parameters.Add(new SqlParameter("@UserName", userName));
                createCommand.Parameters.Add(new SqlParameter("@IsAnonymous", newUserIsAnonymous));
                createCommand.Parameters.Add(new SqlParameter("@LastActivityDate", new DateTime(lastActivityDate.Year, lastActivityDate.Month, lastActivityDate.Day, lastActivityDate.Hour, lastActivityDate.Minute, lastActivityDate.Second)));

                if (createCommand.ExecuteNonQuery() != 0) {
                    lookupResult = lookupCommand.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is int)) {
                        return (int)lookupResult;
                    }
                }
            }

            return 0;
        }

        ///////////////////////////////////////////////////////////////////////////////
        ///////////////////////////////////////////////////////////////////////////////
        //private static string GetFullPathNameFromDBFileName(string relativeFileName) {
        //    relativeFileName = relativeFileName.Replace('/', '\\'); // replace / with \
        //    if (relativeFileName.StartsWith("~\\"))
        //        relativeFileName = relativeFileName.Substring(2);
        //    else if (relativeFileName.StartsWith("\\"))
        //        relativeFileName = relativeFileName.Substring(1);
        //    return Path.Combine(HttpRuntime.AppDomainAppPath, relativeFileName);
        //}

        ///////////////////////////////////////////////////////////////////////////////
        ///////////////////////////////////////////////////////////////////////////////
        //private static void EnsureValidMdbFile(string fileName) {
        //    SqlConnection conn = null;
        //    try {
        //        conn = new SqlConnection(s_connPrefix + fileName);
        //        conn.Open();
        //    }
        //    catch {
        //        throw new Exception("AccessFile is not valid: " + fileName);
        //    }
        //    finally {
        //        if (conn != null)
        //            conn.Close();
        //    }
        //}

        /////////////////////////////////////////////////////////////////////////////
        // 已知 connectionName 取得 connectionnString value
        // 例: 已知 profile 內的 connectionName 為 ConnString_WEBSecurity
        //      利用 ConnString_WEBSecurity 取得真正的 connectionstring value
        /////////////////////////////////////////////////////////////////////////////
        public static string GetConnectionStringFromConnectionName(string connectionName, bool appLevel) {
            ConnectionStringSettings connObj = ConfigurationManager.ConnectionStrings[connectionName];
            if (connObj != null) {
                return connObj.ConnectionString;
            }

            return null;
        }

        ///////////////////////////////////////////////////////////////////////////////
        ///////////////////////////////////////////////////////////////////////////////
        public static Exception GetBetterException(Exception e, ManagementHolder holder) {
            try {
                if (!(e is SqlException) || holder.Connection == null ||
                    holder.Connection.DataSource == null || holder.Connection.DataSource.Length < 1) {
                    return e;
                }
                //if (!File.Exists(holder.Connection.DataSource)) {
                //    return new FileNotFoundException(String.Empty, holder.Connection.DataSource, e);
                //}
            }
            finally {
                if (holder.Connection != null)
                    holder.Connection.Close();
            }

            //FileStream s = null;
            //Exception eWrite = null;
            //try {
            //    s = File.OpenWrite(holder.Connection.DataSource);
            //}
            //catch (Exception except) {
            //    eWrite = except;
            //}
            //finally {
            //    if (s != null)
            //        s.Close();
            //}
            //if (eWrite != null && (eWrite is UnauthorizedAccessException)) {
            //    HttpContext context = HttpContext.Current;
            //    if (context != null) {
            //        context.Response.Clear();
            //        context.Response.StatusCode = 500;
            //        context.Response.Write("Cannot write to DB File");
            //        context.Response.End();
            //    }
            //    return new Exception("AccessFile is not writtable", eWrite);
            //}
            return e;
        }

        public static void CheckConnectionString(string fileName) {
            if (fileName.IndexOf(';') >= 0 && fileName.IndexOf('=') >= 0) // Is probably a connection string
                return;
            //if (Path.IsPathRooted(fileName)) { // Full path
            //    if (!File.Exists(fileName))
            //        throw new Exception("ASP.NET_Access_Providers2 File not found: " + fileName);
            //    return;
            //}
            //char c = fileName[0];
            //if (c == '/' || c == '\\') {
            //    throw new Exception("ASP.NET_Access_Providers2 File can not start with this char: " + c);
            //}
            //if (fileName.Contains("..")) {
            //    throw new Exception("File name can not contain '..': " + fileName);
            //}
        }

        public static DateTime RoundToSeconds_DT(DateTime dt) {
            return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
        }
        public static string RoundToSeconds_S(DateTime dt) {
            return (new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second)).ToString("yyyy/MM/dd HH:mm:ss");
        }

    }

    /// //////////////////////////////////////////////////////////////////////////////
    public sealed class ManagementHolder {
        public SqlConnection Connection;
        public DateTime CreateDate;
        private bool _Opened;

        //////////////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////////////
        public ManagementHolder(SqlConnection connection) {
            Connection = connection;
            CreateDate = DateTime.Now;
        }

        //////////////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////////////
        public void Open(HttpContext context) {
            //////////////////////////////////////////
            // Step 1: Get Exclusive lock
            Monitor.Enter(this); //取得指定物件的獨佔鎖定。
            if (_Opened)
                return; // Already opened

            //////////////////////////////////////////
            // Step 3: Open connection
            try {
                Connection.Open();
            }
            catch {
                // remove exclusive lock
                Monitor.Exit(this);
                throw; // re-throw the exception
            }

            _Opened = true; // Open worked!
        }

        //////////////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////////////
        public void Close() {
            if (!_Opened) // Not open!
                return;

            // Close connection
            Connection.Close();

            _Opened = false;

            // Remove exclusive access
            Monitor.Exit(this);
        }
    }
}