using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Data.SqlClient;
using System.Data;

namespace Jhu.Graywulf.Schema.SqlServer
{
    class CasJobsDataset : SqlServerDataset 
    {
        public CasJobsDataset()
            : base()
        {
        }

        /// <summary>
        /// Copy constructor
        /// </summary>
        /// <param name="old"></param>
        public CasJobsDataset(DatasetBase old)
            : base(old)
        {
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="name"></param>
        /// <param name="connectionString"></param>
        public CasJobsDataset(string name, string connectionString)
            : base(name, connectionString)
        {
        }

        /// <summary>
        /// Copy constructor
        /// </summary>
        /// <param name="old"></param>
        public CasJobsDataset(SqlServerDataset old)
            : base(old)
        {
        }

        /// <summary>
        /// Loads column metadata for every column of a database object.
        /// </summary>
        /// <param name="databaseObject"></param>
        protected override void LoadAllColumnMetadata(DatabaseObject databaseObject)
        {
            try
            {
                var variables = ((IColumns)databaseObject).Columns;

                foreach (Variable v in variables.Values)
                {
                    v.Metadata = new VariableMetadata();
                }

                string objectName = databaseObject.ObjectName;
                string sql = "select * from dbo.fDocColumns(@objectName)";
                using (var cn = OpenConnection() as SqlConnection)
                {
                    using (var cmd = new SqlCommand(sql, cn))
                    {
                        cmd.Parameters.AddWithValue("@objectName", objectName);
                        using (var dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                string name = dr.GetString(1);
                                string unit = dr.GetString(4);
                                string ucd = dr.GetString(5);
                                string description = dr.GetString(6);

                                VariableMetadata meta = ((Variable)variables[name]).Metadata;
                                meta.Summary = description;
                                meta.Unit = unit;
                                meta.Content = ucd;
                            }
                        }
                    }
                }
            }
            catch (Exception) // didn't work
            {
                base.LoadAllColumnMetadata(databaseObject);
            }
        }

        protected override DatabaseObjectMetadata LoadDatabaseObjectMetadata(DatabaseObject databaseObject)
        {
            try
            {
                var sql = @"select name, description, text from DBObjects WHERE name=@objectName";

                using (var cn = OpenConnection() as SqlConnection)
                {
                    using (var cmd = new SqlCommand(sql, cn))
                    {
                        cmd.Parameters.Add("@objectName", SqlDbType.NVarChar).Value = databaseObject.ObjectName;

                        using (var dr = cmd.ExecuteReader())
                        {
                            var meta = new DatabaseObjectMetadata();

                            while (dr.Read())
                            {
                                meta.Summary = dr.GetString(1);
                                meta.Remarks = dr.GetString(2);
                            }

                            return meta;
                        }
                    }
                }
            }
            catch (Exception) // didn't work
            {
                return base.LoadDatabaseObjectMetadata(databaseObject);
            }
        }
    }
}
