//	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 Database
	{
		public static void ShrinkAllLogs(string connectionString)
		{
			Spludlow.Data.IDAL dal = Spludlow.Data.DAL.Create(connectionString);
			dal.ChangeDatabase("");

			foreach (string databaseName in dal.DatabaseList())
			{
				dal.ChangeDatabase(databaseName);
				dal.Operation("shrink log");

				Spludlow.Log.Report("shrank log: " + databaseName);
			}
		}

		public static void Restore(string connectionString, string databaseName, string restoreFilename, string changeOwner)
		{
			Spludlow.Data.IDAL dal = Spludlow.Data.DAL.Create(connectionString);

			dal.CommandTimeout(60);

			ReplaceDatabase(dal, databaseName, restoreFilename);

			if (changeOwner != null)
				dal.ExecuteNonQuery(@"sp_changedbowner '" + changeOwner + "'");     //	Put into operation !!!!! only for SQL?

			dal.Operation("shrink data");
			dal.Operation("shrink log");
		}

		public static void ReplaceDatabase(Spludlow.Data.IDAL dal, string databaseName)
		{
			ReplaceDatabase(dal, databaseName, new DataSet());
		}

		public static void ReplaceDatabase(Spludlow.Data.IDAL dal, string databaseName, string restoreFilename)
		{
			ReplaceDatabase(dal, databaseName);

			dal.Restore(restoreFilename);
		}

		public static void ReplaceDatabase(Spludlow.Data.IDAL dal, string databaseName, DataSet schema)
		{
			dal.ChangeDatabase("");

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

			if (schema.Tables.Count == 0)
				dal.DatabaseCreate(databaseName);
			else
				dal.DatabaseCreate(databaseName, schema);

			dal.ChangeDatabase(databaseName);
		}

		public static void RowCountReport(Spludlow.Data.IDAL dal, string subject)
		{
			DataTable table = Spludlow.Data.TextTable.ReadText(new string[]
			{
				"TableName	Count",
				"String		Int32",
			});

			foreach (string tableName in dal.TableList())
			{
				int count = (int)dal.ExecuteScalar("SELECT COUNT(1) FROM " + tableName);
				table.Rows.Add(tableName, count);
			}

			DataView view = new DataView(table);
			view.Sort = "Count DESC";

			Spludlow.Log.Report(subject, new object[] { view });
		}

		public static void AutoAddForeignKeys2(DataSet schema)
		{
			//	Make catalogue of primary keys	(column name : list table names)

			Dictionary<string, List<string>> primaryKeyColumnTables = new Dictionary<string, List<string>>();

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

				foreach (Spludlow.SysTables.SchemaKeysRow row in keysTable.Rows)
				{
					if (row.KeyType != "P")
						continue;

					if (primaryKeyColumnTables.ContainsKey(row.ColumnName) == false)
						primaryKeyColumnTables.Add(row.ColumnName, new List<string>());

					string tableName = keysTable.TableName.Substring(0, keysTable.TableName.Length - 5);

					primaryKeyColumnTables[row.ColumnName].Add(tableName);
				}
			}

			//	Look for column names

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

				string tableName = columnsTable.TableName.Substring(0, columnsTable.TableName.Length - 8);

				DataTable keysTable = schema.Tables[tableName + "_Keys"];

				foreach (Spludlow.SysTables.SchemaColumnsRow row in columnsTable.Rows)
				{
					if (row.PrimaryKey == true)
						continue;

					if (primaryKeyColumnTables.ContainsKey(row.ColumnName) == false)
						continue;

					foreach (string parentTableName in primaryKeyColumnTables[row.ColumnName])
					{
						//	Skip if already have
						DataRow[] existingForeignKeys = keysTable.Select("KeyType = 'F' AND ParentTable = '" + parentTableName + "'");
						if (existingForeignKeys.Length > 0)
							continue;


						//	Add the key

						string keyName = "FK_" + tableName + "_" + parentTableName;

						foreach (Spludlow.SysTables.SchemaKeysRow primaryKeyRow in schema.Tables[parentTableName + "_Keys"].Select("KeyType = 'P'", "Ordinal"))
						{
							if (columnsTable.Rows.Find(primaryKeyRow.ColumnName) == null)
								throw new ApplicationException("AutoAddForeignKeys; Foriegn Table doen not conatin column from parent table primary key: " + primaryKeyRow.ColumnName);

							keysTable.Rows.Add(keyName, "F", primaryKeyRow.Ordinal, primaryKeyRow.Descending, primaryKeyRow.ColumnName, parentTableName, primaryKeyRow.ColumnName);
						}


					}
				}
			}


		}

		//	Should be in schema class !!!!!
		public static void AutoAddForeignKeys(DataSet schema)	//	Look for columns with same name as primary keys on other tables
		{
			Dictionary<string, string> primaryKeyColumnTables = new Dictionary<string, string>();   //	Primary Key column names must be unique

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

				foreach (Spludlow.SysTables.SchemaKeysRow row in keysTable.Rows)
				{
					if (row.KeyType != "P")
						continue;

					if (primaryKeyColumnTables.ContainsKey(row.ColumnName) == true)
						throw new ApplicationException("AutoAddForeignKeys; Schema conatins multiple primary keys containing same column Names: " + row.ColumnName);

					string tableName = keysTable.TableName.Substring(0, keysTable.TableName.Length - 5);

					primaryKeyColumnTables.Add(row.ColumnName, tableName);
				}
			}

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

				string tableName = columnsTable.TableName.Substring(0, columnsTable.TableName.Length - 8);

				DataTable keysTable = schema.Tables[tableName + "_Keys"];   //	Spludlow.SysTables.SchemaKeysDataTable

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

				foreach (Spludlow.SysTables.SchemaColumnsRow row in columnsTable.Rows)
				{
					if (row.PrimaryKey == true)
						continue;

					if (primaryKeyColumnTables.ContainsKey(row.ColumnName) == false)
						continue;

					string parentTableName = primaryKeyColumnTables[row.ColumnName];

					if (parentTables.Contains(parentTableName) == false)
					{
						DataRow[] existingForeignKeys = keysTable.Select("KeyType = 'F' AND ParentTable = '" + parentTableName + "'");

						if (existingForeignKeys.Length == 0)
							parentTables.Add(parentTableName);
					}
				}

				foreach (string parentTableName in parentTables)
				{
					string keyName = "FK_" + tableName + "_" + parentTableName;

					foreach (Spludlow.SysTables.SchemaKeysRow primaryKeyRow in schema.Tables[parentTableName + "_Keys"].Select("KeyType = 'P'", "Ordinal"))
					{
						if (columnsTable.Rows.Find(primaryKeyRow.ColumnName) == null)
							throw new ApplicationException("AutoAddForeignKeys; Foriegn Table doen not conatin column from parent table primary key: " + primaryKeyRow.ColumnName);

						keysTable.Rows.Add(keyName, "F", primaryKeyRow.Ordinal, primaryKeyRow.Descending, primaryKeyRow.ColumnName, parentTableName, primaryKeyRow.ColumnName);
					}
				}
			}
		}

		public static void ImportDatabaseUsingTemp(
			string schemaDirectory,
			string dataDirectory,
			string connectionString, string databaseName, string databaseBackupDirectory,
			Encoding encoding)
		{
			string tempDatabaseName = databaseName + "Temp";

			ImportDatabaseUsingTemp(schemaDirectory, dataDirectory, connectionString, tempDatabaseName, databaseBackupDirectory, connectionString, databaseName, databaseBackupDirectory, encoding);
		}


		public static void ImportDatabaseUsingTemp(
			string schemaDirectory,
			string dataDirectory,
			string tempConnectionString, string tempDatabaseName, string tempDatabaseBackupDirectory,
			string liveConnectionString, string liveDatabaseName, string liveDatabaseBackupDirectory,
			Encoding encoding)
		{
			Spludlow.SpeedProfile profile = new SpeedProfile("ImportDatabase");

			//	Import to temp database

			Spludlow.Data.Schemas.ImportDatabase(schemaDirectory, dataDirectory, tempConnectionString, tempDatabaseName, tempDatabaseBackupDirectory, encoding);

			profile.Submit("ImportDatabase");

			Spludlow.Data.IDAL tempDatabase = Spludlow.Data.DAL.Create(tempConnectionString);
			Spludlow.Data.IDAL liveDatabase = Spludlow.Data.DAL.Create(liveConnectionString);

			string backupFilename = null;
			string restoreFilename = null;

			try
			{
				//	Backup temp database
				backupFilename = tempDatabaseBackupDirectory + @"\" + tempDatabaseName + "-" + Spludlow.Text.TimeStamp() + ".bak";
				tempDatabase.ChangeDatabase(tempDatabaseName);
				tempDatabase.Backup(backupFilename);

				profile.Submit("Backup Temp");

				//	Copy backup to live temp directory
				restoreFilename = liveDatabaseBackupDirectory + @"\" + liveDatabaseName + "-" + Spludlow.Text.TimeStamp() + ".bak";
				File.Copy(backupFilename, restoreFilename);

				profile.Submit("Copy Backup File");

				//	Delete Live Database and re-create empty
				liveDatabase.ChangeDatabase("");
				if (liveDatabase.DatabaseExists(liveDatabaseName) == true)
					liveDatabase.DatabaseDelete(liveDatabaseName);
				liveDatabase.DatabaseCreate(liveDatabaseName);
				liveDatabase.ChangeDatabase(liveDatabaseName);

				profile.Submit("Re-Create Live");

				//	Restore live database
				liveDatabase.Restore(restoreFilename);

				profile.Submit("Restore Live");

				//	Delete temp datatabse
				tempDatabase.ChangeDatabase("");
				tempDatabase.DatabaseDelete(tempDatabaseName);

				profile.Submit("Delete Temp");

				profile.LogFinish("ImportDatabase");

				Spludlow.Log.Finish("ImportDatabase; Finished");

			}
			finally
			{
				if (backupFilename != null && File.Exists(backupFilename) == true)
					File.Delete(backupFilename);

				if (restoreFilename != null && File.Exists(restoreFilename) == true)
				{
					// Can be kept open for a bit!!!
					Spludlow.Io.Files.DeletePersistently(restoreFilename);
				}
			}
		}

		public static void ImportDatabaseUsingTemp(DataSet schema, DataSet dataSet, string connectionString, string databaseName, string databaseBackupDirectory)
		{
			string tempDatabaseName = databaseName + "Temp";
			
			Spludlow.Data.Schemas.ImportDatabase(schema, dataSet, connectionString, tempDatabaseName, databaseBackupDirectory);

			CopyDatabaseWithBackup(connectionString, tempDatabaseName, databaseBackupDirectory, connectionString, databaseName, databaseBackupDirectory);

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


		public static void CopyDatabaseWithBackup(
			string sourceConnectionString, string sourceDatabaseName, string sourceDatabaseBackupDirectory,
			string targetConnectionString, string targetDatabaseName, string targetDatabaseBackupDirectory)
		{
			Spludlow.SpeedProfile profile = new SpeedProfile("CopyDatabaseUsingTemp");

			Spludlow.Data.IDAL sourceDatabase = Spludlow.Data.DAL.Create(sourceConnectionString);
			Spludlow.Data.IDAL targetDatabase = Spludlow.Data.DAL.Create(targetConnectionString);

			string backupFilename = null;
			string restoreFilename = null;

			try
			{
				//	Backup source database
				backupFilename = sourceDatabaseBackupDirectory + @"\" + sourceDatabaseName + "-" + Spludlow.Text.TimeStamp() + ".bak";
				sourceDatabase.ChangeDatabase(sourceDatabaseName);
				sourceDatabase.Backup(backupFilename);

				profile.Submit("Backup Source");

				//	Copy backup to target temp directory
				restoreFilename = targetDatabaseBackupDirectory + @"\" + targetDatabaseName + "-" + Spludlow.Text.TimeStamp() + ".bak";
				File.Copy(backupFilename, restoreFilename);

				profile.Submit("Copy Backup File");

				//	Delete target Database and re-create empty
				targetDatabase.ChangeDatabase("");
				if (targetDatabase.DatabaseExists(targetDatabaseName) == true)
					targetDatabase.DatabaseDelete(targetDatabaseName);
				targetDatabase.DatabaseCreate(targetDatabaseName);
				targetDatabase.ChangeDatabase(targetDatabaseName);

				profile.Submit("Re-Create Empty Target");

				//	Restore live database
				targetDatabase.Restore(restoreFilename);

				profile.Submit("Restore To Taget");

				profile.LogFinish("CopyDatabaseUsingTemp");

				Spludlow.Log.Finish("CopyDatabaseUsingTemp; Finished");

			}
			finally
			{
				if (backupFilename != null && File.Exists(backupFilename) == true)
					File.Delete(backupFilename);

				if (restoreFilename != null && File.Exists(restoreFilename) == true)
				{
					// Can be kept open for a bit!!!
					Spludlow.Io.Files.DeletePersistently(restoreFilename);
				}
			}
		}

		public static void Backup(string host, string connectionString, string backupFilename)
		{
			Spludlow.Call.Now(host, "Spludlow", "Spludlow.Data.Database", "Backup", new string[] { connectionString, backupFilename });
		}
		public static void Backup(string connectionString, string backupFilename)
		{
			Spludlow.Data.IDAL sourceDatabase = Spludlow.Data.DAL.Create(connectionString);
			sourceDatabase.Backup(backupFilename);
		}

		/// <summary>
		/// Copy database using backup from remote network
		/// 
		/// Run on target host
		/// source connection string should include source database
		/// </summary>
		public static void CopyDatabaseWithBackupRemote(
			string sourceHost, string sourceConnectionString, string sourceDatabaseBackupDirectory,
			string targetConnectionString, string targetDatabaseName, string targetDatabaseBackupDirectory, string changeOwner)
		{

			string name = targetDatabaseName + "-" + Spludlow.Text.TimeStamp() + ".bak";

			string sourceBackupFilename = sourceDatabaseBackupDirectory + @"\" + name;
			string sourceBackupFilenameArchive = sourceBackupFilename + ".7z";

			string restoreFilename = targetDatabaseBackupDirectory + @"\" + name;
			string restoreFilenameArchive = restoreFilename + ".7z";

			try
			{
				Backup(sourceHost, sourceConnectionString, sourceBackupFilename);

				Spludlow.Call.Now(sourceHost, "Spludlow", "Spludlow.Archive", "Create", new object[] { sourceBackupFilenameArchive, sourceBackupFilename });

				Spludlow.Call.Download(sourceHost, sourceBackupFilenameArchive, restoreFilenameArchive);

				Spludlow.Archive.Extract(restoreFilenameArchive, Path.GetDirectoryName(restoreFilenameArchive));

				if (File.Exists(restoreFilename) == false)
					throw new ApplicationException("CopyDatabaseWithBackupRemote; did not extract backup file from archive" + restoreFilename);
				
				Spludlow.Data.IDAL targetDatabase = Spludlow.Data.DAL.Create(targetConnectionString);

				//	Delete target Database and re-create empty
				targetDatabase.ChangeDatabase("");
				if (targetDatabase.DatabaseExists(targetDatabaseName) == true)
					targetDatabase.DatabaseDelete(targetDatabaseName);
				targetDatabase.DatabaseCreate(targetDatabaseName);
				targetDatabase.ChangeDatabase(targetDatabaseName);

				//	Restore database
				targetDatabase.Restore(restoreFilename);

				if (changeOwner != null)
					targetDatabase.ExecuteNonQuery(@"sp_changedbowner '" + changeOwner + "'");     //	Put into operation !!!!! only for SQL?

				targetDatabase.Operation("shrink log");

			}
			finally
			{
				if (Spludlow.RemoteIO.FileExists(sourceHost, sourceBackupFilename) == true)
					Spludlow.RemoteIO.FileDelete(sourceHost, sourceBackupFilename);

				if (Spludlow.RemoteIO.FileExists(sourceHost, sourceBackupFilenameArchive) == true)
					Spludlow.RemoteIO.FileDelete(sourceHost, sourceBackupFilenameArchive);

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

				if (File.Exists(restoreFilename) == true)
				{
					// Can be kept open for a bit!!!
					Spludlow.Io.Files.DeletePersistently(restoreFilename);
				}
			}




		}

		public static void CreateFromDirectory(string schemaDirectory, string dataDirectory, string connectionString, string databaseName)
		{
			DataSet schema = Spludlow.Data.Schemas.ReadDirectory(schemaDirectory);

			Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create(connectionString);
			database.ChangeDatabase("");
			if (database.DatabaseExists(databaseName) == true)
				database.DatabaseDelete(databaseName);

			database.DatabaseCreate(databaseName, schema);

			database.ChangeDatabase(databaseName);

			if (dataDirectory == null)
				return;

			foreach (string tableName in Spludlow.Data.Schemas.TableNamesInOrder(schema))
			{
				string filename = dataDirectory + @"\" + tableName + ".txt";
				if (File.Exists(filename) == false)
					continue;

				DataTable table = Spludlow.Data.TextTable.ReadFile(filename);

				database.BulkInsert(table, tableName, null);
			}
		}

        public static void CopyDatabaseServerWithBulk(string sourceConnectionString, string targetConnectionString)
        {
            Spludlow.Data.IDAL sourceDatabase = Spludlow.Data.DAL.Create(sourceConnectionString);

            string[] databaseNames = sourceDatabase.DatabaseList();

            foreach (string databaseName in databaseNames)
            {
                CopyDatabaseWithBulk(sourceConnectionString, databaseName, targetConnectionString, databaseName);

            }
        }

        public static void CopyDatabaseWithBulk(string sourceConnectionString, string sourceDatabaseName, string targetConnectionString, string targetDatabaseName)
        {
            Spludlow.Data.IDAL sourceDatabase = Spludlow.Data.DAL.Create(sourceConnectionString);

            sourceDatabase.ChangeDatabase(sourceDatabaseName);

            DataSet schema = sourceDatabase.Schema();

            Spludlow.Data.IDAL targetDatabase = Spludlow.Data.DAL.Create(targetConnectionString);

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

            targetDatabase.DatabaseCreate(targetDatabaseName, schema);

            targetDatabase.ChangeDatabase(targetDatabaseName);

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

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

                targetDatabase.BulkInsert(table, tableName, null);

                Spludlow.Log.Info("Table Copied: " + tableName);
            }

            targetDatabase.Operation("shrink log");

            Spludlow.Log.Report("Database Copied: " + targetDatabaseName);

        }
	}
}