using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;

namespace sharpcomparer
{
    public class DatabaseRegistration
    {
        private string name;
        private string dataSource;
        private bool integratedSecurity;
        private string initialCatalog;
        private string userID;
        private string password;
        private List<Table> tables;
        private List<View> views;
        private List<Procedure> procedures;
        private List<User> users;
        private List<TableData> tablesData;
        private SqlConnection connection;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        public string DataSource
        {
            get { return dataSource; }
            set { dataSource = value; }
        }

        public bool IntegratedSecurity
        {
            get { return integratedSecurity; }
            set { integratedSecurity = value; }
        }

        public string InitialCatalog
        {
            get { return initialCatalog; }
            set { initialCatalog = value; }
        }
        
        public string UserID
        {
            get { return userID; }
            set { userID = value; }
        }

        public string Password
        {
            get { return password; }
            set { password = value; }
        }

        public string ConnectionString
        {
            get
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = DataSource;
                builder.IntegratedSecurity = IntegratedSecurity;
                builder.UserID = UserID;

                if (!string.IsNullOrEmpty(Password))
                {
                    builder.Password = Password;
                }

                if (!string.IsNullOrEmpty(InitialCatalog))
                {
                    builder.InitialCatalog = InitialCatalog;
                }

                return builder.ConnectionString;
            }
        }

        [Hide()]
        public List<Table> Tables
        {
            get
            {
                if (tables == null)
                {
                    tables = GetTables(false);
                }
                return tables;
            }
        }

        [Hide()]
        public List<View> Views
        {
            get
            {
                if (views == null)
                {
                    views = GetViews();
                }
                return views;
            }
        }

        [Hide()]
        public List<Procedure> Procedures
        {
            get
            {
                if (procedures == null)
                {
                    GetProcedures();
                }
                return procedures;
            }
        }

        [Hide()]
        public List<User> Users
        {
            get
            {
                if (users == null)
                {
                    GetUsers();
                }
                return users;
            }
        }

        [Hide()]
        public List<TableData> TablesData
        {
            get
            {
                if (tablesData == null)
                {
                    tablesData = GetTablesData();
                }
                return tablesData;
            }
        }

        [Hide()]
        public SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(ConnectionString);
                }
                return connection;
            }
        }

        public DatabaseRegistration(string name, string dataSource, bool integratedSecurity, string initialCatalog, string userID, string password)
        {
            this.name = name;
            this.dataSource = dataSource;
            this.integratedSecurity = integratedSecurity;
            this.initialCatalog = initialCatalog;
            this.userID = userID;
            this.password = password;
        }

        private List<Table> GetTables(bool view)
        {
            List<Table> list = new List<Table>();

            string[] restrictions = new string[] { InitialCatalog, null, null, view ? "VIEW" : "BASE TABLE" };

            Connection.Open();
            DataTable dt = Connection.GetSchema("Tables", restrictions);

            foreach (DataRow row in dt.Rows)
            {
                string tableCatalog = (string)row["TABLE_CATALOG"];
                string tableSchema = (string)row["TABLE_SCHEMA"];
                string tableName = (string)row["TABLE_NAME"];
                string tableType = (string)row["TABLE_TYPE"];

                Table table = new Table(
                    tableCatalog,
                    tableSchema,
                    tableName,
                    tableType
                );

                GetTableColumns(connection, table);
                if (!view)
                {
                    GetIndexes(connection, table);
                }

                list.Add(table);
            }

            Connection.Close();

            return list;
        }

        private List<View> GetViews()
        {
            List<View> list = new List<View>();

            string[] restrictions = new string[] { InitialCatalog };

            Connection.Open();
            DataTable dt = Connection.GetSchema("Views", restrictions);

            foreach (DataRow row in dt.Rows)
            {
                string tableCatalog = (string)row["TABLE_CATALOG"];
                string tableSchema = (string)row["TABLE_SCHEMA"];
                string tableName = (string)row["TABLE_NAME"];
                string checkOption = (string)row["CHECK_OPTION"];
                string isUpdatable = (string)row["IS_UPDATABLE"];

                View view = new View(
                    tableCatalog,
                    tableSchema,
                    tableName,
                    checkOption,
                    isUpdatable
                );

                GetTableColumns(connection, view);

                list.Add(view);
            }

            Connection.Close();

            return list;
        }

        private void GetProcedures()
        {
            string[] restrictions = new string[] { InitialCatalog };
            Connection.Open();
            DataTable dt = Connection.GetSchema("Procedures", restrictions);

            procedures = new List<Procedure>();

            foreach (DataRow row in dt.Rows)
            {
                string specificCatalog = (string)row["SPECIFIC_CATALOG"];
                string specificSchema = (string)row["SPECIFIC_SCHEMA"];
                string specificName = (string)row["SPECIFIC_NAME"];
                string routineCatalog = (string)row["ROUTINE_CATALOG"];
                string routineSchema = (string)row["ROUTINE_SCHEMA"];
                string routineName = (string)row["ROUTINE_NAME"];
                string routineType = (string)row["ROUTINE_TYPE"];
                DateTime created = (DateTime)row["CREATED"];
                DateTime lastAltered = (DateTime)row["LAST_ALTERED"];

                Procedure procedure = new Procedure(
                    specificCatalog,
                    specificSchema,
                    specificName,
                    routineCatalog,
                    routineSchema,
                    routineName,
                    routineType,
                    created,
                    lastAltered);

                procedures.Add(procedure);

                GetProcedureParamters(Connection, procedure);
            }

            Connection.Close();
        }

        private void GetUsers()
        {
            string[] restrictions = new string[] { InitialCatalog };
            Connection.Open();
            DataTable dt = Connection.GetSchema("Users");

            users = new List<User>();

            foreach (DataRow row in dt.Rows)
            {
                short uid = Convert.ToInt16(row["uid"]);
                string userName = (string)row["user_name"];
                DateTime createdate = (DateTime)row["createdate"];
                DateTime updatedate = (DateTime)row["updatedate"];

                User user = new User(
                    uid,
                    userName,
                    createdate,
                    updatedate);

                users.Add(user);
            }

            Connection.Close();
        }

        private void GetTableColumns(SqlConnection connection, Table table)
        {
            DataTable columns = connection.GetSchema("Columns", new string[] { InitialCatalog, null, table.TableName, null });

            foreach (DataRow row in columns.Rows)
            {
                string tableCatalog = (string)row["TABLE_CATALOG"];
                string tableSchema = (string)row["TABLE_SCHEMA"];
                string tableName = (string)row["TABLE_NAME"];
                string columnName = (string)row["COLUMN_NAME"];
                int ordinalPosition = Convert.ToInt32(row["ORDINAL_POSITION"]);
                string columnDefault = row["COLUMN_DEFAULT"] == DBNull.Value ? null : (string)row["COLUMN_DEFAULT"];
                bool isNullable = (string)row["IS_NULLABLE"] == "YES";
                string dataType = (string)row["DATA_TYPE"];
                int characterMaximumLength = row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value ? -1 : Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
                int characterOctetLength = row["CHARACTER_OCTET_LENGTH"] == DBNull.Value ? -1 : Convert.ToInt32(row["CHARACTER_OCTET_LENGTH"]);
                byte numericPrecision = row["NUMERIC_PRECISION"] == DBNull.Value ? (byte)0 : Convert.ToByte(row["NUMERIC_PRECISION"]);
                short numericPrecisionRadix = row["NUMERIC_PRECISION_RADIX"] == DBNull.Value ? (short)-1 : Convert.ToInt16(row["NUMERIC_PRECISION_RADIX"]);
                int numericScale = row["NUMERIC_SCALE"] == DBNull.Value ? -1 : Convert.ToInt32(row["NUMERIC_SCALE"]);
                short datetimePrecision = row["DATETIME_PRECISION"] == DBNull.Value ? (short)-1 : Convert.ToInt16(row["DATETIME_PRECISION"]);
                string characterSetCatalog = row["CHARACTER_SET_CATALOG"] == DBNull.Value ? null : (string)row["CHARACTER_SET_CATALOG"];
                string characterSetSchema = row["CHARACTER_SET_SCHEMA"] == DBNull.Value ? null : (string)row["CHARACTER_SET_SCHEMA"];
                string characterSetName = row["CHARACTER_SET_NAME"] == DBNull.Value ? null : (string)row["CHARACTER_SET_NAME"];
                string collationCatalog = row["COLLATION_CATALOG"] == DBNull.Value ? null : (string)row["COLLATION_CATALOG"];

                Column column = new Column(
                    tableCatalog,
                    tableSchema,
                    tableName,
                    columnName,
                    ordinalPosition,
                    columnDefault,
                    isNullable,
                    dataType,
                    characterMaximumLength,
                    characterOctetLength,
                    numericPrecision,
                    numericPrecisionRadix,
                    numericScale,
                    datetimePrecision,
                    characterSetCatalog,
                    characterSetSchema,
                    characterSetName,
                    collationCatalog);
                table.Columns.Add(column);
            }
        }

        private void GetViewColumns(SqlConnection connection, View view)
        {
            DataTable columns = connection.GetSchema("ViewColumns", new string[] { InitialCatalog, null, view.TableName });

            //DataTable columns = connection.GetSchema("ViewColumns", new string[] { Database, null, view.TableName, null });

            //foreach (DataRow row in columns.Rows)
            //{
            //    string viewCatalog = (string)row["VIEW_CATALOG"];
            //    string viewSchema = (string)row["VIEW_SCHEMA"];
            //    string viewName = (string)row["VIEW_NAME"];
            //    string tableCatalog = (string)row["TABLE_CATALOG"];
            //    string tableSchema = (string)row["TABLE_SCHEMA"];
            //    string tableName = (string)row["TABLE_NAME"];
            //    string columnName = (string)row["COLUMN_NAME"];

            //    ViewColumn column = new ViewColumn(
            //        viewCatalog,
            //        viewSchema,
            //        viewName,
            //        tableCatalog,
            //        tableSchema,
            //        tableName,
            //        columnName);
            //    view.Columns.Add(column);
            //}
        }

        private void GetProcedureParamters(SqlConnection connection, Procedure procedure)
        {
            DataTable parameters = connection.GetSchema("ProcedureParameters", new string[] { InitialCatalog, null, procedure.RoutineName, null });

            foreach (DataRow row in parameters.Rows)
            {
                string specificCatalog = (string)row["SPECIFIC_CATALOG"];
                string specificSchema = (string)row["SPECIFIC_SCHEMA"];
                string specificName = (string)row["SPECIFIC_NAME"];
                int ordinalPosition = Convert.ToInt32(row["ORDINAL_POSITION"]);
                string parameterMode = (string)row["PARAMETER_MODE"];
                string isResult = (string)row["IS_RESULT"];
                string asLocator = (string)row["AS_LOCATOR"];
                string parameterName = (string)row["PARAMETER_NAME"];
                string dataType = (string)row["DATA_TYPE"];
                int characterMaximumLength = row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value ? -1 : Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
                int characterOctetLength = row["CHARACTER_OCTET_LENGTH"] == DBNull.Value ? -1 : Convert.ToInt32(row["CHARACTER_OCTET_LENGTH"]);
                string collationCatalog = row["COLLATION_CATALOG"] == DBNull.Value ? null : (string)row["COLLATION_CATALOG"];
                string collationSchema = row["COLLATION_SCHEMA"] == DBNull.Value ? null : (string)row["COLLATION_SCHEMA"];
                string collationName = row["COLLATION_NAME"] == DBNull.Value ? null : (string)row["COLLATION_NAME"];
                string characterSetCatalog = row["CHARACTER_SET_CATALOG"] == DBNull.Value ? null : (string)row["CHARACTER_SET_CATALOG"];
                string characterSetSchema = row["CHARACTER_SET_SCHEMA"] == DBNull.Value ? null : (string)row["CHARACTER_SET_SCHEMA"];
                string characterSetName = row["CHARACTER_SET_NAME"] == DBNull.Value ? null : (string)row["CHARACTER_SET_NAME"];
                byte numericPrecision = row["NUMERIC_PRECISION"] == DBNull.Value ? (byte)0 : Convert.ToByte(row["NUMERIC_PRECISION"]);
                short numericPrecisionRadix = row["NUMERIC_PRECISION_RADIX"] == DBNull.Value ? (short)-1 : Convert.ToInt16(row["NUMERIC_PRECISION_RADIX"]);
                int numericScale = row["NUMERIC_SCALE"] == DBNull.Value ? -1 : Convert.ToInt32(row["NUMERIC_SCALE"]);
                short datetimePrecision = row["DATETIME_PRECISION"] == DBNull.Value ? (short)-1 : Convert.ToInt16(row["DATETIME_PRECISION"]);
                string intervalType = row["INTERVAL_TYPE"] == DBNull.Value ? null : (string)row["INTERVAL_TYPE"];
                string intervalPrecision = row["INTERVAL_PRECISION"] == DBNull.Value ? null : (string)row["INTERVAL_PRECISION"];

                ProcedureParameter parameter = new ProcedureParameter(
                    specificCatalog,
                    specificSchema,
                    specificName,
                    ordinalPosition,
                    parameterMode,
                    isResult,
                    asLocator,
                    parameterName,
                    dataType,
                    characterMaximumLength,
                    characterOctetLength,
                    collationCatalog,
                    collationSchema,
                    collationName,
                    characterSetCatalog,
                    characterSetSchema,
                    characterSetName,
                    numericPrecision,
                    numericPrecisionRadix,
                    numericScale,
                    datetimePrecision,
                    intervalType,
                    intervalPrecision);
                procedure.Parameters.Add(parameter);
            }
        }

        private void GetIndexes(SqlConnection connection, Table table)
        {
            DataTable indexes = connection.GetSchema("Indexes", new string[] { InitialCatalog, null, table.TableName });

            foreach (DataRow row in indexes.Rows)
            {
                string constraintCatalog = (string)row["CONSTRAINT_CATALOG"];
                string constraintSchema = (string)row["CONSTRAINT_SCHEMA"];
                string constraintName = (string)row["CONSTRAINT_NAME"];
                string tableCatalog = (string)row["TABLE_CATALOG"];
                string tableSchema = (string)row["TABLE_SCHEMA"];
                string tableName = (string)row["TABLE_NAME"];
                string indexName = (string)row["INDEX_NAME"];

                Index index = new Index(
                    constraintCatalog,
                    constraintSchema,
                    constraintName,
                    tableCatalog,
                    tableSchema,
                    tableName,
                    indexName);
                table.Indexes.Add(index);

                GetIndexColumns(connection, table, index);
            }
        }

        private void GetIndexColumns(SqlConnection connection, Table table, Index index)
        {
            DataTable indexColumns = connection.GetSchema("IndexColumns", new string[] { InitialCatalog, null, table.TableName, index.IndexName });

            foreach (DataRow row in indexColumns.Rows)
            {
                string constraintCatalog = (string)row["CONSTRAINT_CATALOG"];
                string constraintSchema = (string)row["CONSTRAINT_SCHEMA"];
                string constraintName = (string)row["CONSTRAINT_NAME"];
                string tableCatalog = (string)row["TABLE_CATALOG"];
                string tableSchema = (string)row["TABLE_SCHEMA"];
                string tableName = (string)row["TABLE_NAME"];
                string columnName = (string)row["COLUMN_NAME"];
                int ordinalPosition = Convert.ToInt32(row["ORDINAL_POSITION"]);
                byte keyType = Convert.ToByte(row["KEYTYPE"]);
                string indexName = (string)row["INDEX_NAME"];

                IndexColumn indexColumn = new IndexColumn(
                    constraintCatalog,
                    constraintSchema,
                    constraintName,
                    tableCatalog,
                    tableSchema,
                    tableName,
                    columnName,
                    ordinalPosition,
                    keyType,
                    indexName);
                index.IndexColumns.Add(indexColumn);
            }
        }

        public Table GetTable(string name)
        {
            Table result = Tables.Find(
                 delegate(Table table)
                 {
                     return table.TableName == name;
                 }
             );
            return result;
        }

        private List<TableData> GetTablesData()
        {
            List<TableData> tablesData = new List<TableData>();

            foreach (Table table in Tables)
            {
                int count = (int)ExecuteScalar("SELECT COUNT(*) FROM " + table.TableName);
                TableData data = new TableData(table.TableName, count);
                tablesData.Add(data);
            }
            return tablesData;
        }

        public object ExecuteScalar(string cmdText)
        {
            Connection.Open();
            SqlCommand command = new SqlCommand(cmdText, Connection);
            object ret = command.ExecuteScalar();
            Connection.Close();
            return ret;
        }

        public SqlDataReader ExecuteReader(string cmdText)
        {
            Connection.Open();
            SqlCommand command = new SqlCommand(cmdText, Connection);
            SqlDataReader reader = command.ExecuteReader();
            return reader;
        }

        public void CreateTable(Table table)
        {
            string fields = string.Empty;
            for (int i = 0; i < table.Columns.Count; i++)
            {
                Column column = table.Columns[i];
                string field = string.Format("{0} {1} {2}",
                    column.ColumnName,
                    column.Type,
                    column.IsNullable ? string.Empty : "not null");
                fields += field;
                if (i < table.Columns.Count - 1) fields += ",";
            }

            string cmdText = string.Format(
                "CREATE TABLE {0} ({1})",
                table.TableName,
                fields
                );

            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(cmdText, connection);
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}