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

namespace Bowin.Common.Linq.Entity
{
    public class EntitySQLDocumentGenerator
    {
        public String ConnectionString { get; set; }
        public String InputFileName { get; set; }
        public String OutputFileName { get; set; }

        private SqlConnection _connection;


        public EntitySQLDocumentGenerator(String connectionString, String inputFileName, String outputFileName)
        {
            this.ConnectionString = connectionString;
            this.InputFileName = inputFileName;
            this.OutputFileName = outputFileName;

            this._connection = new SqlConnection(connectionString);
            this._connection.Open();
        }
        public void Dispose()
        {
            this._connection.Dispose();
        }

        private void CreateDocumentation()
        {

            XDocument doc = XDocument.Load(this.InputFileName);
            IEnumerable<XElement> entityTypeElements = doc.Descendants("{http://schemas.microsoft.com/ado/2008/09/edm}EntityType");

            int i = 0;
            foreach (XElement entityTypeElement in entityTypeElements)
            {
                String tableName = entityTypeElement.Attribute("Name").Value;
                IEnumerable<XElement> propertyElements = entityTypeElement.Descendants("{http://schemas.microsoft.com/ado/2008/09/edm}Property");

                this.AddNodeDocumentation(entityTypeElement, GetTableDocumentation(tableName));

                foreach (XElement propertyElement in propertyElements)
                {
                    String columnName = propertyElement.Attribute("Name").Value;
                    this.AddNodeDocumentation(propertyElement, GetColumnDocumentation(tableName, columnName));
                }
            }

            Console.WriteLine("Writing result to {0}", this.OutputFileName);
            if (File.Exists(this.OutputFileName))
                File.Delete(this.OutputFileName);
            doc.Save(this.OutputFileName);
        }
        private void AddNodeDocumentation(XElement element, String documentation)
        {
            if (String.IsNullOrEmpty(documentation))
                return;
            element.Descendants("{http://schemas.microsoft.com/ado/2008/09/edm}Documentation").Remove();

            element.AddFirst(new XElement("{http://schemas.microsoft.com/ado/2008/09/edm}Documentation", new XElement("{http://schemas.microsoft.com/ado/2008/09/edm}Summary", documentation)));
        }
        private String GetTableDocumentation(String tableName)
        {
            using (SqlCommand command = new SqlCommand(@" SELECT [value] 
                                                          FROM fn_listextendedproperty (
                                                                'MS_Description', 
                                                                'schema', 'dbo', 
                                                                'table',  @TableName, 
                                                                null, null)", this._connection))
            {

                command.Parameters.AddWithValue("TableName", tableName);

                return command.ExecuteScalar() as String;
            }
        }
        private String GetColumnDocumentation(String tableName, String columnName)
        {
            using (SqlCommand command = new SqlCommand(@"SELECT [value] 
                                                         FROM fn_listextendedproperty (
                                                                'MS_Description', 
                                                                'schema', 'dbo', 
                                                                'table', @TableName, 
                                                                'column', @columnName)", this._connection))
            {

                command.Parameters.AddWithValue("TableName", tableName);
                command.Parameters.AddWithValue("ColumnName", columnName);

                return command.ExecuteScalar() as String;
            }
        }
    }
}