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

namespace sharpcomparer
{
    public class SqlUtils
    {
        public static DataTable Join(DataTable First, DataTable Second, DataColumn[] FJC, DataColumn[] SJC)
        {
            First.TableName += "LEFT";
            Second.TableName += "RIGHT";
            DataTable table = new DataTable("Join");
            using (DataSet ds = new DataSet())
            {
                ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() });
                DataColumn[] parentcolumns = new DataColumn[FJC.Length];
                for (int i = 0; i < parentcolumns.Length; i++)
                {
                    parentcolumns[i] = ds.Tables[0].Columns[FJC[i].ColumnName];
                }
                DataColumn[] childcolumns = new DataColumn[SJC.Length];
                for (int i = 0; i < childcolumns.Length; i++)
                {
                    childcolumns[i] = ds.Tables[1].Columns[SJC[i].ColumnName];
                }
                DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false);
                ds.Relations.Add(r);
                for (int i = 0; i < First.Columns.Count; i++)
                {
                    table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);
                }
                for (int i = 0; i < Second.Columns.Count; i++)
                {
                    if (!table.Columns.Contains(Second.Columns[i].ColumnName))
                        table.Columns.Add(Second.Columns[i].ColumnName, Second.Columns[i].DataType);
                    else
                        table.Columns.Add(Second.Columns[i].ColumnName + "_Second", Second.Columns[i].DataType);
                }

                table.BeginLoadData();
                foreach (DataRow firstrow in ds.Tables[0].Rows)
                {
                    DataRow[] childrows = firstrow.GetChildRows(r);
                    if (childrows != null && childrows.Length > 0)
                    {
                        object[] parentarray = firstrow.ItemArray;
                        foreach (DataRow secondrow in childrows)
                        {
                            object[] secondarray = secondrow.ItemArray;
                            object[] joinarray = new object[parentarray.Length + secondarray.Length];
                            Array.Copy(parentarray, 0, joinarray, 0, parentarray.Length);
                            Array.Copy(secondarray, 0, joinarray, parentarray.Length, secondarray.Length);
                            table.LoadDataRow(joinarray, true);
                        }
                    }
                }
                table.EndLoadData();
            }
            return table;
        }

        public static DataTable Join(DataTable First, DataTable Second, DataColumn FJC, DataColumn SJC)
        {
            return Join(First, Second, new DataColumn[] { FJC }, new DataColumn[] { SJC });
        }

        public static DataTable Join(DataTable First, DataTable Second, string FJC, string SJC)
        {
            return Join(First, Second, new DataColumn[] { First.Columns[FJC] }, new DataColumn[] { First.Columns[SJC] });
        }

        public static DataTable Intersect(DataTable First, DataTable Second)
        {
            DataColumn[] firstcolumns = new DataColumn[First.Columns.Count];
            for (int i = 0; i < firstcolumns.Length; i++)
            {
                firstcolumns[i] = First.Columns[i];
            }

            DataColumn[] secondcolumns = new DataColumn[Second.Columns.Count];
            for (int i = 0; i < secondcolumns.Length; i++)
            {
                secondcolumns[i] = Second.Columns[i];
            }
            DataTable table = Join(First, Second, firstcolumns, secondcolumns);
            table.TableName = "Intersect";

            return table;
        }

        private static bool RowEqual(object[] Values, object[] OtherValues)
        {
            if (Values == null)
                return false;

            for (int i = 0; i < Values.Length; i++)
            {
                if (!Values[i].Equals(OtherValues[i]))
                    return false;
            }
            return true;
        }

        public static DataTable Distinct(DataTable Table, DataColumn[] Columns)
        {
            DataTable table = new DataTable("Distinct");
            string sort = string.Empty;

            for (int i = 0; i < Columns.Length; i++)
            {
                table.Columns.Add(Columns[i].ColumnName, Columns[i].DataType);
                sort += Columns[i].ColumnName + ",";
            }

            DataRow[] sortedrows = Table.Select(string.Empty, sort.Substring(0, sort.Length - 1));
            object[] currentrow = null;
            object[] previousrow = null;

            table.BeginLoadData();
            foreach (DataRow row in sortedrows)
            {
                currentrow = new object[Columns.Length];

                for (int i = 0; i < Columns.Length; i++)
                {
                    currentrow[i] = row[Columns[i].ColumnName];
                }

                if (!RowEqual(previousrow, currentrow))
                    table.LoadDataRow(currentrow, true);

                previousrow = new object[Columns.Length];
                for (int i = 0; i < Columns.Length; i++)
                {
                    previousrow[i] = row[Columns[i].ColumnName];
                }
            }

            table.EndLoadData();
            return table;
        }

        public static DataTable Distinct(DataTable Table, DataColumn Column)
        {
            return Distinct(Table, new DataColumn[] { Column });
        }

        public static DataTable Distinct(DataTable Table, string Column)
        {
            return Distinct(Table, Table.Columns[Column]);
        }

        public static DataTable Distinct(DataTable Table, params string[] Columns)
        {
            DataColumn[] columns = new DataColumn[Columns.Length];

            for (int i = 0; i < Columns.Length; i++)
            {
                columns[i] = Table.Columns[Columns[i]];
            }

            return Distinct(Table, columns);
        }

        public static DataTable Distinct(DataTable Table)
        {
            DataColumn[] columns = new DataColumn[Table.Columns.Count];

            for (int i = 0; i < Table.Columns.Count; i++)
            {
                columns[i] = Table.Columns[i];
            }

            return Distinct(Table, columns);
        }

        public static DataTable Difference(DataTable First, DataTable Second)
        {
            First.TableName += "LEFT";
            Second.TableName += "RIGHT";
            DataTable table = new DataTable("Difference");
            using (DataSet ds = new DataSet())
            {
                ds.Tables.AddRange(new DataTable[] { First.Copy(), Second.Copy() });
                DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];
                for (int i = 0; i < firstcolumns.Length; i++)
                {
                    firstcolumns[i] = ds.Tables[0].Columns[i];
                }
                DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];
                for (int i = 0; i < secondcolumns.Length; i++)
                {
                    secondcolumns[i] = ds.Tables[1].Columns[i];
                }
                DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
                ds.Relations.Add(r);
                for (int i = 0; i < First.Columns.Count; i++)
                {
                    table.Columns.Add(First.Columns[i].ColumnName, First.Columns[i].DataType);
                }

                table.BeginLoadData();
                foreach (DataRow parentrow in ds.Tables[0].Rows)
                {
                    DataRow[] childrows = parentrow.GetChildRows(r);
                    if (childrows == null || childrows.Length == 0)
                        table.LoadDataRow(parentrow.ItemArray, true);
                }
                table.EndLoadData();
            }

            return table;
        }
    }
}