using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tango.Core.Components
{
    public static class DataBaseDescriptionsHelper
    {
        public class DbDescription
        {
            public String TableName { get; set; }
            public String ColumnName { get; set; }
            public String TableDescription { get; set; }
            public String ColumnDescription { get; set; }
        }

        public class ForeignKeyDescription
        {
            public String TableName { get; set; }
            public String ColumnName { get; set; }
        }

        public static List<DbDescription> GetDescriptions(DbConnection connection)
        {
            List<DbDescription> dbDescriptions = new List<DbDescription>();

            var command = connection.CreateCommand();
            command.CommandText = @"SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder";

            DataTable table = new DataTable();
            table.Load(command.ExecuteReader());

            foreach (var row in table.Rows.OfType<DataRow>())
            {
                String table_name = row.ItemArray.GetValue(0).ToString().Replace("dbo.", "");
                String table_decription = row.ItemArray.GetValue(1).ToString();
                String column_name = row.ItemArray.GetValue(2).ToString();
                String column_description = row.ItemArray.GetValue(3).ToString();

                if (!String.IsNullOrWhiteSpace(table_decription) || !String.IsNullOrWhiteSpace(column_description))
                {
                    dbDescriptions.Add(new DbDescription()
                    {
                        TableName = table_name,
                        TableDescription = table_decription,
                        ColumnName = column_name,
                        ColumnDescription = column_description,
                    });
                }
            }

            return dbDescriptions;
        }

        public static List<ForeignKeyDescription> GetForeignKeys(DbConnection connection)
        {
            List<ForeignKeyDescription> keys = new List<ForeignKeyDescription>();

            var command = connection.CreateCommand();
            command.CommandText = "SELECT a.TABLE_NAME, a.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON a.CONSTRAINT_CATALOG = b.CONSTRAINT_CATALOG AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME";

            DataTable table = new DataTable();
            table.Load(command.ExecuteReader());

            foreach (var row in table.Rows.OfType<DataRow>())
            {
                String table_name = row.ItemArray.GetValue(0).ToString();
                String column_name = row.ItemArray.GetValue(1).ToString();

                keys.Add(new ForeignKeyDescription()
                {
                    TableName = table_name,
                    ColumnName = column_name
                });
            }

            return keys;
        }
    }
}