//	Spludlow Software
//	Copyright © Samuel P. Ludlow 2020 All Rights Reserved
//	Distributed under the terms of the GNU General Public License version 3
//	Distributed WITHOUT ANY WARRANTY; without implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
//	https://www.spludlow.co.uk/LICENCE.TXT
//	The Spludlow logo is a registered trademark of Samuel P. Ludlow and may not be used without permission
//	v1.14

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

using System.IO;
using System.Data;

namespace Spludlow.Data
{
	public class Schemas
	{
		private static DataTable _DataTypes = null;

		static Schemas()
		{
			try
			{
				string filename = "DataTypes.txt";
				
				if (Spludlow.Config.StandAlone == false)
					filename = Spludlow.Config.ProgramData + @"\Data\DataTypes.txt";

				if (File.Exists(filename) == false)
					throw new ApplicationException("Schemas; DataTypes file not found: " + filename);

				_DataTypes = Spludlow.Data.TextTable.ReadFile(filename, false);
			}
			catch (Exception ee)
			{
				Spludlow.Log.Error("Schemas; Initializing", ee);
				throw ee;
			}
		}

		public static DataTable DataTypesTable
		{
			get
			{
				return _DataTypes;
			}
		}

		public static SysTables.SchemaColumnsDataTable NewSchemaColumnsTable(string tableName)
		{
			SysTables.SchemaColumnsDataTable table = new SysTables.SchemaColumnsDataTable();
			table.TableName = tableName + "_Columns";
			return table;
		}


		public static SysTables.SchemaKeysDataTable NewSchemaKeysTable(string tableName)
		{
			SysTables.SchemaKeysDataTable table = new SysTables.SchemaKeysDataTable();
			table.TableName = tableName + "_Keys";
			return table;
		}

		public static DataRow GetColumn(DataSet schema, string tableName, string columnName)
		{
			string columnsTableName = tableName + "_Columns";

			if (schema.Tables.Contains(columnsTableName) == false)
				throw new ApplicationException("Schemas; Table not in schema: " + tableName);

			DataTable table = schema.Tables[columnsTableName];

			DataRow row = table.Rows.Find(columnName);

			if (row == null)
				throw new ApplicationException("Schemas; Column not in schema: " + tableName + "." + columnName);

			return row;
		}

		public static string[] PrimaryKeyColumns(DataSet schema, string tableName)
		{
			return PrimaryKeyColumns(schema.Tables[tableName + "_Columns"]);
		}
		public static string[] PrimaryKeyColumns(DataTable schemaColumnsTable)
		{
			List<string> keyColumns = new List<string>();

			foreach (DataRow row in schemaColumnsTable.Rows)
			{
				if ((bool)row["PrimaryKey"] == true)
					keyColumns.Add((string)row["ColumnName"]);
			}

			return keyColumns.ToArray();
		}

		public static string[] TableNames(DataSet schema)
		{
			string findSuffix = "_Columns";

			List<string> names = new List<string>();
			foreach (DataTable table in schema.Tables)
			{
				if (table.TableName.EndsWith(findSuffix) == true)
					names.Add(table.TableName.Substring(0, table.TableName.Length - findSuffix.Length));
			}

			return names.ToArray();
		}
		public static string[] TableNamesInOrder(DataSet schema)
		{
			string[] tableNames = TableNames(schema);

			return OrderTablesOnAncestors(tableNames, schema);
		}

		public static string[] OrderTablesOnAncestors(string[] tableNames, DataSet schema)		//	Sort a list of tables so the parents of foriegn keys come first, parents listed before chilren, use when adding tables
		{
			DataTable table = Spludlow.Data.TextTable.ReadText(new string[]
			{
				"TableName	ParentCount",
				"String		Int32",
			});

			foreach (string tableName in tableNames)
				table.Rows.Add(tableName, CountParents(tableName, schema, 0, tableName, new List<string>()));

			DataView view = new DataView(table);
			view.Sort = "ParentCount, TableName";

			List<string> result = new List<string>();

			foreach (DataRowView rowView in view)
				result.Add((string)rowView.Row["TableName"]);

			//Spludlow.Log.Report("OrderTablesOnParentage", new object[] { view });

			return result.ToArray();
		}

		private static int CountParents(string tableName, DataSet schema, int currentCount, string startTable, List<string> doneList)
		{
			if (doneList.Contains(tableName) == true)
				return currentCount;
			doneList.Add(tableName);

			if (currentCount > 16)
				throw new ApplicationException("CountParents; Circular relationship detected. tableName: " + tableName + ", startTable: " + startTable);

			string keysTableName = tableName + "_Keys";

			if (schema.Tables.Contains(keysTableName) == false)
				return currentCount;

			DataTable relationsTable = schema.Tables[keysTableName];

			List<string> parentTables = new List<string>();

			foreach (DataRow row in relationsTable.Select("KeyType = 'F'"))
			{
				string parentTableName = (string)row["ParentTable"];

				if (parentTableName == tableName)
					continue;

				if (parentTables.Contains(parentTableName) == false)
					parentTables.Add(parentTableName);
			}

			if (parentTables.Count > 0)
			{
				int maxCount = 0;
				foreach (string parentTableName in parentTables)
				{
					int count = CountParents(parentTableName, schema, currentCount + 1, startTable, doneList);
					if (count > maxCount)
						maxCount = count;
				}

				if (maxCount > currentCount)
					currentCount = maxCount;
			}

			return currentCount;
		}

		private static string[] FixColumns = new string[] { "DataTypeId", "MaxLength", "Precision", "Scale" };
		public static void FixForiegnKeys(DataSet schema)
		{
			DataTable reportTable = Spludlow.Data.TextTable.ReadText(new string[]
			{
				"ChildTable	ChildColumn	ParentTable	ParentColumn",
				"String		String		String		String",
			});

			foreach (string relation in new string[] { "Child", "Parent" })
			{
				foreach (string fixColumn in FixColumns)
				{
					string columnName = relation + fixColumn;
					Type type = typeof(int);
					if (fixColumn == "DataTypeId")
						type = typeof(string);

					reportTable.Columns.Add(columnName, type);
				}
			}

			foreach (DataTable keysTable in schema.Tables)	//	Order by parents first ?????
			{
				if (keysTable.TableName.EndsWith("_Keys") == false)
					continue;

				string childTableName = keysTable.TableName.Substring(0, keysTable.TableName.Length - 5);
				DataTable childColumnsTable = schema.Tables[childTableName + "_Columns"];
				if (childColumnsTable == null)
					throw new ApplicationException("CheckForiegnKeys; Can not find child columns table: " + childTableName);

				foreach (DataRow keyRow in keysTable.Rows)
				{
					if ((string)keyRow["KeyType"] != "F")
						continue;

					string childColumnName = (string)keyRow["ColumnName"];

					DataRow childColumnRow = childColumnsTable.Rows.Find(childColumnName);
					if (childColumnRow == null)
						throw new ApplicationException("CheckForiegnKeys; Can not find child columns row: " + childTableName + "." + childColumnName);

					string parentTableName = (string)keyRow["ParentTable"];
					string parentColumnName = (string)keyRow["ParentColumn"];

					DataTable parentColumnsTable = schema.Tables[parentTableName + "_Columns"];
					if (parentColumnsTable == null)
						throw new ApplicationException("CheckForiegnKeys; Can not find parent columns table: " + parentTableName);

					DataRow parentColumnRow = parentColumnsTable.Rows.Find(parentColumnName);
					if (parentColumnRow == null)
						throw new ApplicationException("CheckForiegnKeys; Can not find child columns row: " + parentTableName + "." + parentColumnName);

					bool problem = false;

					foreach (string fixColumn in FixColumns)
					{
						if (fixColumn == "DataTypeId")
						{
							string childString = (string)childColumnRow[fixColumn];
							string parentString = (string)parentColumnRow[fixColumn];
							if (childString != parentString)
							{
								problem = true;
								break;
							}
						}
						else
						{
							int childInt = (int)childColumnRow[fixColumn];
							int parentInt = (int)parentColumnRow[fixColumn];
							if (childInt != parentInt)
							{
								problem = true;
								break;
							}
						}
					}

					if (problem == true)
					{
						DataRow reportRow = reportTable.Rows.Add(new object[] { childTableName, childColumnName, parentTableName, parentColumnName });   //	ok to  leave end columns ??

						foreach (string fixColumn in FixColumns)
						{
							foreach (string relation in new string[] { "Child", "Parent" })
							{
								DataRow sourceRow;
								if (relation == "Child")
									sourceRow = childColumnRow;
								else
									sourceRow = parentColumnRow;

								string columnName = relation + fixColumn;
								reportRow[columnName] = sourceRow[fixColumn];
							}

							childColumnRow[fixColumn] = parentColumnRow[fixColumn];
						}

					}
				}
			}

			if (reportTable.Rows.Count > 0)
				Spludlow.Log.Warning("FixForiegnKeys; Fixed columns: " + reportTable.Rows.Count, new object[] { reportTable });
		}

		public static void FixLongIndexNames(DataSet schema)
		{
			int sequence = 0;

			List<string> usedNames = new List<string>();

			foreach (DataTable table in schema.Tables)
			{
				if (table.TableName.EndsWith("_Keys") == false)
					continue;

				List<string> keyNames = new List<string>();

				foreach (SysTables.SchemaKeysRow keyRow in table.Rows)
				{
					if (keyNames.Contains(keyRow.KeyName) == false)
						keyNames.Add(keyRow.KeyName);
				}

				foreach (string originalKeyName in keyNames)
				{
					string keyName = originalKeyName;

					if (keyName.Length > 34)     //	Or less ?		was 64
						keyName = keyName.Substring(0, 30);

					string sufix = "";
					while (usedNames.Contains(keyName + sufix) == true)
					{
						++sequence;
						sufix = sequence.ToString("0000");
					}
					keyName = keyName + sufix;
					if (keyName != originalKeyName)
					{
						Spludlow.Log.Warning("Schemas FixLongIndexNames; Fixed Name: " + originalKeyName + " -> " + keyName);

						foreach (SysTables.SchemaKeysRow keyRow in table.Select("KeyName = '" + originalKeyName + "'"))
							keyRow.KeyName = keyName;
					}

					usedNames.Add(keyName);
				}
			}
		}

		public static void DataTypeMatchReport(IDAL dal)	//	Use for determining if data type mapping is working
		{
			DataSet schema = dal.Schema();

			DataTable reportTable = Spludlow.Data.TextTable.ReadText(new string[]
			{
				"TableName	ColumnName	SchemaDataTypeId	MaxLength	Precision	Scale	SchemaTypeCode	DataDataType	Problem",
				"String		String		String				Int32		Int32		Int32	String			String			Boolean",
			});

			//	ColumnName	Ordinal	DataTypeId	MaxLength	Precision	Scale	PrimaryKey	AllowDBNull	AutoIncrement	Longest"

			foreach (string tableName in dal.TableList())
			{
				DataTable columnsTable = schema.Tables[tableName + "_Columns"];

				DataTable dataTable = dal.Select("SELECT * FROM " + tableName);		//	Watch out for large row counts !!!!!!!!!!!!!

				foreach (DataRow columnRow in columnsTable.Rows)
				{
					string columnName = (string)columnRow["ColumnName"];
					string dataTypeId = (string)columnRow["DataTypeId"];
					int maxLength = (int)columnRow["MaxLength"];
					int precision = (int)columnRow["Precision"];
					int scale = (int)columnRow["Scale"];

					string schemaTypeCode = Spludlow.Data.Schemas.Map("DataTypeId", dataTypeId, "TypeCode");

					DataColumn dataColumn = dataTable.Columns[columnName];

					string dataTypeCode = dataColumn.DataType.Name;

					bool problem = (schemaTypeCode != dataTypeCode);

					reportTable.Rows.Add(new object[] { tableName, columnName, dataTypeId, maxLength, precision, scale, schemaTypeCode, dataTypeCode, problem });
				}
			}

			Spludlow.Log.Report("DataTypeMatchReport", new object[] { reportTable });
		}


		public static DataSet ReadDirectory(string directoryName)
		{
			DataSet source = Spludlow.Data.TextTable.ReadDirectory(directoryName);

			DataSet target = new DataSet();

			foreach (DataTable sourceTable in source.Tables)
			{
				int index = sourceTable.TableName.LastIndexOf("_");
				if (index == -1)
					throw new ApplicationException("Schemas.ReadDirectory; Can not find underscoree in tablename: " + sourceTable.TableName);
				string tableType = sourceTable.TableName.Substring(index);

				DataTable targetTable;

				switch (tableType)
				{
					case "_Columns":
						targetTable = new Spludlow.SysTables.SchemaColumnsDataTable();
						break;
					case "_Keys":
						targetTable = new Spludlow.SysTables.SchemaKeysDataTable();
						break;
					default:
						throw new ApplicationException("Schemas.ReadDirectory; bad table type, tablename: " + sourceTable.TableName);
				}

				targetTable.TableName = sourceTable.TableName;

				foreach (DataRow row in sourceTable.Rows)
					targetTable.ImportRow(row);

				target.Tables.Add(targetTable);
			}

			return target;
		}

		public static void WriteDirectory(string directoryName, DataSet schema)
		{
			Spludlow.Data.TextTable.WriteDirectory(directoryName, schema);
		}

		public static void DumpDirectory(string connectionString, string parentDirectory)
		{
			string schemaDirectory = parentDirectory + @"\Schema";
			string dataDirectory = parentDirectory + @"\Data";

			if (Directory.Exists(schemaDirectory) == true)
				Directory.Delete(schemaDirectory, true);
			Directory.CreateDirectory(schemaDirectory);

			if (Directory.Exists(dataDirectory) == true)
				Directory.Delete(dataDirectory, true);
			Directory.CreateDirectory(dataDirectory);

			DumpDirectory(connectionString, schemaDirectory, dataDirectory);
		}
		public static void DumpDirectory(string connectionString, string schemaDirectory, string dataDirectory)
		{
			Spludlow.Data.IDAL source = Spludlow.Data.DAL.Create(connectionString);

			if (source.CurrentDatabase() == null)
				throw new ApplicationException("DumpDirectory; No current database.");

			DataSet schema = source.Schema();

			Spludlow.Data.Schemas.WriteDirectory(schemaDirectory, schema);

			foreach (string tableName in source.TableList())
			{
				string commandText = "SELECT * FROM [" + tableName + "]";
				DataTable table = source.Select(commandText);

				foreach (DataColumn column in table.Columns)
				{
					if (column.DataType != typeof(string))
						continue;

					foreach (DataRow row in table.Rows)
					{
						if (row.IsNull(column) == true)
							continue;

						StringBuilder text = new StringBuilder((string)row[column]);
						text.Replace(Environment.NewLine, " ");
						text.Replace("\n", " ");
						text.Replace("\r", " ");
						text.Replace("\t", " ");

						row[column] = text.ToString();
					}
				}


				string filename = dataDirectory + @"\" + tableName + ".txt";
				Spludlow.Data.TextTable.Write(filename, table);						//	Encoding !!!!
			}
		}

		public static void RestoreDirectory(string connectionString, string databaseName, string parentDirectory)
		{
			string schemaDirectory = parentDirectory + @"\Schema";
			string dataDirectory = parentDirectory + @"\Data";

			RestoreDirectory(connectionString, databaseName, schemaDirectory, dataDirectory);
		}
		public static void RestoreDirectory(string connectionString, string databaseName, string schemaDirectory, string dataDirectory)
		{
			Spludlow.Data.IDAL target = Spludlow.Data.DAL.Create(connectionString);

			target.ChangeDatabase("");

			if (target.DatabaseExists(databaseName) == true)
				target.DatabaseDelete(databaseName);
			target.DatabaseCreate(databaseName);

			target.ChangeDatabase(databaseName);

			DataSet schema = Spludlow.Data.Schemas.ReadDirectory(schemaDirectory);

			foreach (string tableName in Spludlow.Data.Schemas.TableNamesInOrder(schema))
			{
				target.TableCreate(tableName, schema);
			}

			target.SchemaRefresh();

			foreach (string tableName in Spludlow.Data.Schemas.TableNamesInOrder(schema))
			{
				//target.TableCreate(tableName, schema);
				string filename = dataDirectory + @"\" + tableName + ".txt";
				DataTable table = Spludlow.Data.TextTable.ReadFile(filename);   //	Encoding !!!!

				target.BulkInsert(table, tableName, null);								//	temp dir for non SQL dbs !!!!

				//Spludlow.Data.Schemas.CopyDatabaseTableRows(table, target, tableName, schema, target.Schema(), 10000);
			}

			target.TableAddKeys(schema, "UIF");
	
		}

		public static void CloneDatabase(string sourceConnectionString, string targetConnectionString, string targetDatabaseName)
		{
			Spludlow.Data.IDAL source = Spludlow.Data.DAL.Create(sourceConnectionString);
			Spludlow.Data.IDAL target = Spludlow.Data.DAL.Create(targetConnectionString);

			CloneDatabase(source, target, targetDatabaseName);
		}

		public static void CloneDatabase(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, string targetDatabaseName)
		{
			DataSet schema = source.Schema();

			string[] tableNames = source.TableList();

			CloneDatabase(source, target, schema, tableNames, targetDatabaseName);
		}

		public static void CloneDatabase(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, DataSet schema, string targetDatabaseName)
		{
			string[] tableNames = source.TableList();

			CloneDatabase(source, target, schema, tableNames, targetDatabaseName);
		}

		public static void CloneDatabase(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, DataSet schema, string[] tableNames, string targetDatabaseName)
		{
			target.ChangeDatabase("");

			if (target.DatabaseExists(targetDatabaseName) == true)
				target.DatabaseDelete(targetDatabaseName);

			target.DatabaseCreate(targetDatabaseName);

			CloneDatabaseTables(source, target, schema, tableNames, targetDatabaseName);

			target.TableAddKeys(schema, "UIF");
		}

		public static void CloneDatabaseTables(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, DataSet schema, string[] tableNames, string targetDatabaseName)
		{
			target.ChangeDatabase(targetDatabaseName);

			//FixForiegnKeys(schema);

			tableNames = OrderTablesOnAncestors(tableNames, schema);

			foreach (string tableName in tableNames)
			{
				target.TableCreate(tableName, schema);
			}
		}

		public static void CopyDatabase(string sourceConnectionString, string targetConnectionString, string targetDatabaseName, int reportFreq)
		{
			Spludlow.Data.IDAL source = Spludlow.Data.DAL.Create(sourceConnectionString);
			Spludlow.Data.IDAL target = Spludlow.Data.DAL.Create(targetConnectionString);
			Spludlow.Data.Schemas.CopyDatabase(source, target, targetDatabaseName, reportFreq);
		}
		public static void CopyDatabase(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, string targetDatabaseName, int reportFreq)
		{
			CopyDatabase(source, target, source.Schema(), targetDatabaseName, reportFreq);
		}
		public static void CopyDatabase(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, DataSet schema, string targetDatabaseName, int reportFreq)
		{
			CopyDatabase(source, target, schema, targetDatabaseName, source.TableList(), reportFreq);
		}

		public static void CopyDatabase(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, DataSet schema, string targetDatabaseName, string[] copyDataTables, int reportFreq)
		{
			string[] allTableNames = OrderTablesOnAncestors(source.TableList(), schema);

			target.ChangeDatabase("");

			if (target.DatabaseExists(targetDatabaseName) == true)
				target.DatabaseDelete(targetDatabaseName);

			target.DatabaseCreate(targetDatabaseName);

			CloneDatabaseTables(source, target, schema, allTableNames, targetDatabaseName);

			target.SchemaRefresh();
			
			CopyDatabaseRows(source, target, schema, targetDatabaseName, copyDataTables, reportFreq);

			target.CommandTimeout(240);		//	Can take ages on MySQL !!!

			target.TableAddKeys(schema, "UIF");		//	Move only F down here

			target.SchemaRefresh();

		}


		public static void CopyDatabaseRows(Spludlow.Data.IDAL source, Spludlow.Data.IDAL target, DataSet sourceSchema, string targetDatabaseName, string[] copyDataTables, int reportFreq)
		{
			DataSet targetSchema = target.Schema();

			copyDataTables = OrderTablesOnAncestors(copyDataTables, targetSchema);

			Spludlow.Log.Info("CopyDatabaseRows, About to copy " + copyDataTables.Length + " tables.", new object[] { copyDataTables });

			foreach (string tableName in copyDataTables)
			{
				string commandText = "SELECT * FROM [" + tableName + "]";
				DataTable sourceTable = source.Select(commandText);

				CopyDatabaseTableRows(sourceTable, target, tableName, sourceSchema, targetSchema, reportFreq);
			}

			target.SchemaRefresh();
		}

		public static void CopyDatabaseTableRows(DataTable sourceTable, Spludlow.Data.IDAL target, string tableName, DataSet sourceSchema, DataSet targetSchema, int reportFreq)	//	Target Schema must be actual databse target for insert identity bidge to work
		{
			bool testRun = false;
			if (reportFreq < 0)
			{
				reportFreq = -reportFreq;
				testRun = true;
			}

			string identityInsertOperation = null;

			foreach (SysTables.SchemaColumnsRow sourceColumnRow in sourceSchema.Tables[tableName + "_Columns"].Rows)
			{
				SysTables.SchemaColumnsRow targetColumnRow = (SysTables.SchemaColumnsRow)targetSchema.Tables[tableName + "_Columns"].Rows.Find(sourceColumnRow.ColumnName);

				//	Wipe out strange datatypes
				if (sourceColumnRow.DataTypeId.Contains(":") == true && targetColumnRow.DataTypeId.Contains(":") == false)
				{
					foreach (DataRow row in sourceTable.Rows)
						row[sourceColumnRow.ColumnName] = DBNull.Value;
				}

				//	Allow identity insert & bodge target schema so inserter includes AutoIncrement
				if (targetColumnRow.AutoIncrement == true)
				{
					identityInsertOperation = "identity insert " + tableName;

					targetColumnRow.AutoIncrement = false;		//	!!! not in play ???
				}
			}

			DAL.CommandInfo insertInfo = target.MakeInserter(tableName);

			int count = 0;
			foreach (DataRow row in sourceTable.Rows)
			{
				if (identityInsertOperation == null)
				{
					target.Insert(insertInfo, row);
				}
				else
				{
					target.Begin();
					try
					{
						target.Operation(identityInsertOperation);
						target.Insert(insertInfo, row);
						target.Commit();
					}
					catch
					{
						target.Rollback();
						throw;
					}
				}

				++count;
				if (reportFreq != 0 && (count % reportFreq) == 0)
				{
					Spludlow.Log.Info("Schema CopyDatabaseRows; Insert Progress " + tableName + ", " + count + "/" + sourceTable.Rows.Count);
					if (testRun == true)
						break;
				}
			}

			Spludlow.Log.Info("CopyDatabaseRows, Copied table: " + tableName + ", row count: " + count);
		}

		public static void DisableAutoIncrement(DataSet schema, string tableName)
		{
			foreach (SysTables.SchemaColumnsRow columnRow in schema.Tables[tableName + "_Columns"].Rows)
			{
				columnRow.AutoIncrement = false;
			}
		}

		public static void ImportTable(DataTable sourceTable, Spludlow.Data.IDAL target, string tableName, int reportFreq)
		{
			sourceTable.TableName = tableName;

			DataSet schema = Spludlow.Data.ADO.Schema(sourceTable);

			ImportTable(sourceTable, target, tableName, schema, reportFreq);
		}

		public static void ImportTable(DataTable sourceTable, Spludlow.Data.IDAL target, string tableName, DataSet schema, int reportFreq)
		{
			if (target.TableExists(tableName) == true)
				target.TableDelete(tableName);

			target.TableCreate(tableName, schema);

			CopyDatabaseTableRows(sourceTable, target, tableName, schema, schema, reportFreq);
		}

		public static void ImportTable(string[] textTableFilenames, Spludlow.Data.IDAL target, string tableName, DataSet schema, int reportFreq)
		{
			if (target.TableExists(tableName) == true)
				target.TableDelete(tableName);

			target.TableCreate(tableName, schema);

			foreach (string filename in textTableFilenames)
			{
				DataTable table = Spludlow.Data.TextTable.ReadFile(filename);
				table.TableName = tableName;
				CopyDatabaseTableRows(table, target, tableName, schema, schema, reportFreq);
			}
		}

		public static void ImportDatabase(string schemaDirectory, string textTablesDirectory, string connectionString, string databaseName, string tempDirectory)
		{
			ImportDatabase(schemaDirectory, textTablesDirectory, connectionString, databaseName, tempDirectory, Encoding.Default);
		}
		public static void ImportDatabase(string schemaDirectory, string textTablesDirectory, string connectionString, string databaseName, string tempDirectory, Encoding encoding)
		{
			DateTime startTime = DateTime.Now;

			DataSet schema = Spludlow.Data.Schemas.ReadDirectory(schemaDirectory);

			string[] tableNames = Spludlow.Data.Schemas.TableNamesInOrder(schema);

			Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(connectionString);

			database.ChangeDatabase("");

			if (database.DatabaseExists(databaseName) == true)
				database.DatabaseDelete(databaseName);

			database.DatabaseCreate(databaseName);

			database.ChangeDatabase(databaseName);

			foreach (string tableName in tableNames)
				database.TableCreate(tableName, schema);

			database.TableAddKeys(schema, "UI");

			foreach (string tableName in tableNames)
			{
				string dataFilename = textTablesDirectory + @"\" + tableName + ".txt";

				if (File.Exists(dataFilename) == false)
				{
					Spludlow.Log.Warning("ImportDatabase; Text Table not found: " + dataFilename);
					continue;
				}

				DataTable table = Spludlow.Data.TextTable.ReadFile(dataFilename, encoding);

				database.BulkInsert(table, tableName, tempDirectory);

				Spludlow.Log.Info("ImportDatabase; Done table: " + tableName + ", rows: " + table.Rows.Count);
			}

			database.CommandTimeout(240);

			database.TableAddKeys(schema, "F");

			database.Operation("shrink log");

			Spludlow.Log.Finish("ImportDatabase; Finished, took: " + Spludlow.Text.TimeTook(startTime), new object[] { schema });
		}

		public static void ImportDatabase(DataSet schema, DataSet dataSet, string connectionString, string databaseName, string tempDirectory)
		{
			Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(connectionString);

			ImportDatabase(schema, dataSet, database, databaseName, tempDirectory);
		}

		public static void ImportDatabase(DataSet schema, DataSet dataSet, Spludlow.Data.IDAL database, string databaseName, string tempDirectory)
		{
			DateTime startTime = DateTime.Now;
			
			string[] tableNames = Spludlow.Data.Schemas.TableNamesInOrder(schema);

			database.ChangeDatabase("");

			if (database.DatabaseExists(databaseName) == true)
				database.DatabaseDelete(databaseName);

			database.DatabaseCreate(databaseName);

			database.ChangeDatabase(databaseName);

			foreach (string tableName in tableNames)
				database.TableCreate(tableName, schema);

			database.TableAddKeys(schema, "UI");

			foreach (DataTable table in dataSet.Tables)
				database.BulkInsert(table, table.TableName, tempDirectory);

			database.CommandTimeout(240);

			database.TableAddKeys(schema, "F");

			database.Operation("shrink log");

			Spludlow.Log.Finish("ImportDatabase; Finished, took: " + Spludlow.Text.TimeTook(startTime), new object[] { schema });
		}

		public static void CopyWithBackup(IDAL source, IDAL target, string targetDatabaseName, string tempDirectory)
		{
			CopyWithBackup(source, target, targetDatabaseName, tempDirectory, tempDirectory);
		}
		public static void CopyWithBackup(IDAL source, IDAL target, string targetDatabaseName, string sourceTempDirectory, string targetTempDirectory)
		{
			if (source.CurrentDatabase() == null)
				throw new ApplicationException("CopyWithBackup; Must have a current source database.");

			if (source.DataSource().ToLower() == target.DataSource().ToLower() && source.CurrentDatabase().ToLower() == targetDatabaseName.ToLower())
				throw new ApplicationException("CopyWithBackup; Copying to same place, datasource:" + source.DataSource() + ", databaseName:" + targetDatabaseName);

			target.ChangeDatabase("");
			if (target.DatabaseExists(targetDatabaseName) == true)
				target.DatabaseDelete(targetDatabaseName);
			target.DatabaseCreate(targetDatabaseName);

			target.ChangeDatabase(targetDatabaseName);

			string backupFilename = sourceTempDirectory + @"\" + source.DataSource() + "-" + source.CurrentDatabase() + ".bak";
			string restoreFilename = targetTempDirectory + @"\" + target.DataSource() + "-" + targetDatabaseName + ".bak";

			if (File.Exists(backupFilename) == true)
				File.Delete(backupFilename);

			if (File.Exists(restoreFilename) == true)
				File.Delete(restoreFilename);

			source.Backup(backupFilename);

			System.Threading.Thread.Sleep(8000);	//	Backup seems to return before it finishes !!!

			File.Copy(backupFilename, restoreFilename);

			target.Restore(restoreFilename);

			File.Delete(backupFilename);
			File.Delete(restoreFilename);

			target.SchemaRefresh();
		}

		public static void BackupAllDatabases(IDAL dal, string tempDirectory)
		{
			dal.ChangeDatabase("");

			foreach (string database in dal.DatabaseList())
			{
				string filename = tempDirectory + @"\" + database + ".bak";
				if (File.Exists(filename) == true)
					File.Delete(filename);

				dal.ChangeDatabase(database);
				dal.Backup(filename);
			}
		}

		public static void ShrinkAllDatabases(string connectionString)
		{
			Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(connectionString);
			ShrinkAllDatabases(database);
		}
		public static void ShrinkAllDatabases(IDAL dal)
		{
			dal.ChangeDatabase("");

			foreach (string database in dal.DatabaseList())
			{
				dal.ChangeDatabase(database);
				dal.Operation("shrink data");
				dal.Operation("shrink log");
			}
		}

		public static void ClearAllRows(IDAL dal)
		{
			string[] tableNames = OrderTablesOnAncestors(dal.TableList(), dal.Schema());

			for (int index = tableNames.Length - 1; index >= 0; --index)
				dal.TableClear(tableNames[index]);
		}

		public static bool TableHasForeignReferences(DataSet schema, string tableName)
		{
			DataRow[] primaryKeyRows = schema.Tables[tableName + "_Keys"].Select("KeyType = 'P'");

			if (primaryKeyRows.Length == 0)
				return false;

			string columnName = (string)primaryKeyRows[0]["ColumnName"];

			foreach (DataTable keysTable in schema.Tables)
			{
				if (keysTable.TableName.EndsWith("_Keys") == false)
					continue;

				DataRow[] foreignKeyRows = keysTable.Select("KeyType = 'F' AND ParentTable = '" + tableName + "' AND ParentColumn = '" + columnName + "'");

				if (foreignKeyRows.Length > 0)
					return true;
			}

			return false;
		}

		public static void TidySchemaTable(DataTable table)
		{
			foreach (DataRow row in table.Rows)
			{
				string dataTypeId = (string)row["DataTypeId"];

				int maxLength = 0;
				int precision = 0;
				int scale = 0;

				if (dataTypeId == "Decimal")
				{
					precision = (int)row["Precision"];
					scale = (int)row["Scale"];
				}
				else
				{
					if (dataTypeId.EndsWith("Char") == true || dataTypeId.EndsWith("String") == true || dataTypeId.EndsWith("Binary") == true)
						maxLength = (int)row["MaxLength"];
				}

				row["MaxLength"] = maxLength;
				row["Precision"] = precision;
				row["Scale"] = scale;
			}
		}

		public static string NativeToCommon(string nativeType, string databaseType)
		{
			string commonType = Map(databaseType, nativeType, "DataTypeId");

			if (commonType == null)
			{
				commonType = databaseType + ":" + nativeType;
				Spludlow.Log.Warning("Schemas, NativeToCommon; can't find find: " + databaseType + ", " + commonType);
			}

			return commonType;
		}

		public static string CommonToNative(string commonType, string databaseType)
		{
			int index = commonType.IndexOf(":");
			if (index != -1)
			{
				string sourceDatabaseType = commonType.Substring(0, index);
				string unknownType = commonType.Substring(index + 1);

				if (databaseType == sourceDatabaseType)
					return unknownType;

				return Map("DataTypeId", "Boolean", databaseType);		//			default unknown type !!!!!!!!!!!!!!!!!!!!!
			}

			string nativeType = Map("DataTypeId", commonType, databaseType);

			if (nativeType == null)
				throw new ApplicationException("Schemas, CommonToNative; can't find find: " + databaseType + ", " + commonType);

			return nativeType;
		}

		private static string Map(string filterColumn, string filterValue, string resultColumn)
		{
			DataView view = new DataView(_DataTypes);
			view.RowFilter = filterColumn + " = '" + filterValue + "'";

			if (view.Count == 0)
				return null;
				
			return (string)view[0][resultColumn];
		}

		public static DataSet SchemaNativeDataTypeReport(DataSet nativeSchema, string[] tableNames)	//	??? old
		{
			string[] columnNames = new string[] { "DATA_TYPE", "CHARACTER_MAXIMUM_LENGTH", "NUMERIC_PRECISION", "NUMERIC_SCALE" };


			DataTable countTable = nativeSchema.Tables["Columns"].Clone();

			foreach (string tableName in tableNames)
			{
				foreach (DataRow columnRow in nativeSchema.Tables["Columns"].Select("TABLE_NAME = '" + tableName + "'", "ORDINAL_POSITION"))
				{
					countTable.ImportRow(columnRow);

					DataRow countRow = countTable.Rows[countTable.Rows.Count - 1];	//	Fix DBnulls
					foreach (string columnName in columnNames)
					{
						if (countRow.IsNull(columnName) == false)
							continue;

						if (columnName == "DATA_TYPE")
							countRow[columnName] = "";
						else
							countRow[columnName] = 0;
					}
				}
			}

			string countColumnName = "OccuranceCount";

			DataTable resultTable = Spludlow.Data.ADO.GroupCount(countTable, columnNames, countColumnName);

			StringBuilder sort = new StringBuilder();
			foreach (string columnName in columnNames)
			{
				if (sort.Length > 0)
					sort.Append(", ");
				sort.Append(columnName);
			}
			
			DataView view = new DataView(resultTable);
			view.Sort = sort.ToString();

			return Spludlow.Data.ADO.WireDataSet(view);
		}

		public static void SchemaReport(Spludlow.Data.IDAL dal, string subject, bool includeSchemasSchema)
		{
			DataSet dataSet;

			dataSet = dal.SchemaReader();
			Spludlow.Log.Report(subject + " Schema Reader", new object[] { dataSet });
			if (includeSchemasSchema == true)
				Spludlow.Log.Report(subject + " Schema Reader (with schema)", new object[] { Spludlow.Data.ADO.AddSchema(dataSet) });

			dataSet = dal.SchemaConnection();
			Spludlow.Log.Report(subject + " Schema Connection", new object[] { dataSet });
			if (includeSchemasSchema == true)
				Spludlow.Log.Report(subject + " Schema Connection (with schema)", new object[] { Spludlow.Data.ADO.AddSchema(dataSet) });

			dataSet = dal.SchemaNative();
			Spludlow.Log.Report(subject + " Schema Native", new object[] { dataSet });
			if (includeSchemasSchema == true)
				Spludlow.Log.Report(subject + " Schema Native (with schema)", new object[] { Spludlow.Data.ADO.AddSchema(dataSet) });

			dataSet = dal.SchemaANSI();
			Spludlow.Log.Report(subject + " Schema ANSI", new object[] { dataSet });
			if (includeSchemasSchema == true)
				Spludlow.Log.Report(subject + " Schema ANSI (with schema)", new object[] { Spludlow.Data.ADO.AddSchema(dataSet) });

			Spludlow.Log.Report(subject + " Schema", new object[] { dal.Schema() });
		}

		public static void FixTableNamesWithSchema(DataSet schema)
		{
			foreach (string tableName in TableNames(schema))
			{
				DataTable columnsTable = schema.Tables[tableName + "_Columns"];
				DataTable keysTable = schema.Tables[tableName + "_Keys"];

				columnsTable.TableName = columnsTable.TableName.Replace(".", "_");
				keysTable.TableName = keysTable.TableName.Replace(".", "_");

				foreach (Spludlow.SysTables.SchemaKeysRow row in keysTable.Select("KeyType = 'F'"))
					row.ParentTable = row.ParentTable.Replace(".", "_");
			}
		}

		public static void FixKeyNames(DataSet schema)
		{
			Dictionary<string, string> prefixes = new Dictionary<string, string>();
			prefixes.Add("P", "PK_");
			prefixes.Add("U", "UX_");
			prefixes.Add("I", "IX_");
			prefixes.Add("X", "XI_");
			prefixes.Add("T", "TI_");
			prefixes.Add("F", "FK_");

			foreach (string tableName in TableNames(schema))
			{
				DataTable keysTable = schema.Tables[tableName + "_Keys"];

				List<string> keyNames = new List<string>();
				foreach (DataRow row in keysTable.Rows)
				{
					string keyName = (string)row["KeyName"];
					if (keyNames.Contains(keyName) == false)
						keyNames.Add(keyName);
				}

				for (int keyIndex = 0; keyIndex < keyNames.Count; ++keyIndex)
				{
					string existingKeyName = keyNames[keyIndex];

					DataRow[] rows = keysTable.Select("KeyName = '" + existingKeyName + "'");

					string keyType = (string)rows[0]["KeyType"];

					string newName = prefixes[keyType];
					newName += tableName;
					//if (keyType == "F")
					//	newName += "_" + (string)rows[0]["ParentTable"];

					if (keyType != "P")
						newName += "_" + keyIndex.ToString();

					foreach (DataRow row in rows)
						row["KeyName"] = newName;
				}
			}
		}




	}
}