using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using DbDoc.BusinessObjects;

using System.Configuration;

using Microsoft.SqlServer.Server;

using Microsoft.SqlServer.Management.Smo;



using System.Configuration.Provider;

using Microsoft.SqlServer.Management.Common;

using DbDoc.Provider.Source;

using DbDoc.Provider.Persistence;



namespace DbDoc.Provider.Source.Sql

{

    public class SqlSourceProvider : SourceProvider

    {

        private string targetConnectionString;

        private List<string> includeDatabases = new List<string>();

        private List<string> excludeDatabases = new List<string>();



        #region Properties

        public override string ConnectionInfo

        {

            get { return targetConnectionString; }

        }



        public override string StorageName

        {

            get

            {

                if (string.IsNullOrWhiteSpace(ConnectionInfo))

                    return string.Empty;

                string[] settings = ConnectionInfo.Split(new char[] { ';' });



                var server = (from s in settings

                              where s.Split(new char[] { '=' })[0].ToLower() == "server"

                              select s.Split(new char[] { '=' })[1]).FirstOrDefault();

                return server;

            }

        }



        public override IEnumerable<string> ExcludedDatabasesList

        {

            get { return excludeDatabases; }

        }

        #endregion





        public override void Initialize()

        {

            Setting connectionString = PersistenceProviderManager.Provider.GetByKey(Constants.Settings.SOURCE_CONNECTIONSTRING);

            if (connectionString == null)

                throw new ConfigurationException("The source database connection string has not been defined");

            targetConnectionString = connectionString.RawValue;

            Setting dbs = PersistenceProviderManager.Provider.GetByKey(Constants.Settings.EXCLUDED_DATABASES);

            if (dbs != null)

            {

                excludeDatabases.AddRange(dbs.RawValue.Split(new char[] { ',' }));

            }

        }



        public override IList<string> GetServerDatabases()

        {

            using (SqlConnection con = new SqlConnection(targetConnectionString))

            {

                ServerConnection cn = new ServerConnection(con);



                Server server = new Server(cn);

                IList<string> databases = new List<string>();

                foreach (Microsoft.SqlServer.Management.Smo.Database db in server.Databases)

                    if (!excludeDatabases.Contains(db.Name))

                        databases.Add(db.Name);



                return databases;

            }

        }



        public override IList<string> GetServerTables(string database)

        {

            using (SqlConnection con = new SqlConnection(targetConnectionString))

            {

                ServerConnection cn = new ServerConnection(con);



                Server server = new Server(cn);

                IList<string> tables = new List<string>();

                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];



                if (db != null && !excludeDatabases.Contains(db.Name))

                    foreach (Microsoft.SqlServer.Management.Smo.Table tbl in db.Tables)

                        tables.Add(tbl.Name);



                return tables;

            }

        }



        public override IList<string> GetServerViews(string database)

        {

            using (SqlConnection con = new SqlConnection(targetConnectionString))

            {

                ServerConnection cn = new ServerConnection(con);



                Server server = new Server(cn);

                IList<string> views = new List<string>();

                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];

                if (db != null)

                    foreach (Microsoft.SqlServer.Management.Smo.View vw in db.Views)

                        views.Add(vw.Name);



                return views;

            }

        }



        public override IList<string> GetServerStoredProcs(string database)

        {

            using (SqlConnection con = new SqlConnection(targetConnectionString))

            {

                ServerConnection cn = new ServerConnection(con);



                Server server = new Server(cn);

                IList<string> procs = new List<string>();

                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];

                if (db != null)

                    foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure proc in db.StoredProcedures)

                        procs.Add(proc.Name);



                return procs;

            }

        }



        public override IList<string> GetServerStoredProcs(string database, bool includeSystemObjects)

        {

            using (SqlConnection con = new SqlConnection(targetConnectionString))

            {

                ServerConnection cn = new ServerConnection(con);



                Server server = new Server(cn);

                IList<string> procs = new List<string>();

                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];

                if (db != null)

                    foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure proc in db.StoredProcedures)

                        if (proc.IsSystemObject == includeSystemObjects)

                            procs.Add(proc.Name);



                return procs;

            }

        }



        public override IList<string> GetServerColumns(string database, string table)

        {

            using (SqlConnection con = new SqlConnection(targetConnectionString))

            {

                ServerConnection cn = new ServerConnection(con);



                Server server = new Server(cn);

                IList<string> columns = new List<string>();

                if (server.Databases[database] != null)

                {

                    Microsoft.SqlServer.Management.Smo.Table tbl = server.Databases[database].Tables[table];

                    if (null != tbl)

                        foreach (Microsoft.SqlServer.Management.Smo.Column column in tbl.Columns)

                            columns.Add(column.Name);

                }

                return columns;

            }

        }



        public override IList<string> GetServerFunctions(string database)

        {

            using (SqlConnection con = new SqlConnection(targetConnectionString))

            {

                ServerConnection cn = new ServerConnection(con);



                Server server = new Server(cn);

                IList<string> functions = new List<string>();

                Microsoft.SqlServer.Management.Smo.Database db = server.Databases[database];

                foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction func in db.UserDefinedFunctions)

                    functions.Add(func.Name);



                return functions;

            }

        }



        #region Private Methods

        private static void PopulateDbObject(SqlDataReader reader, DbDoc.BusinessObjects.DbObject obj)

        {

            obj.Id = reader.GetInt32(reader.GetOrdinal("Id"));

            obj.Deleted = reader.GetBoolean(reader.GetOrdinal("Deleted"));

            obj.Name = reader.GetString(reader.GetOrdinal("Name"));

            if (!reader.IsDBNull(reader.GetOrdinal("CommentId")))

                obj.Comment.Id = reader.GetInt32(reader.GetOrdinal("CommentId"));

            if (!reader.IsDBNull(reader.GetOrdinal("Message")))

                obj.Comment.Text = reader.GetString(reader.GetOrdinal("Message"));

            if (!reader.IsDBNull(reader.GetOrdinal("IsSystemObject")))

                obj.Comment.Text = reader.GetString(reader.GetOrdinal("IsSystemObject"));

            if (!reader.IsDBNull(reader.GetOrdinal("SystemInfo")))

                obj.Comment.Text = reader.GetString(reader.GetOrdinal("SystemInfo"));

        }



        #endregion

      

    }

}