//	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.Data;

namespace Spludlow.Data
{
	/// <summary>
	/// Fixes / Bodges on sample databse schemas to make them more portable
	/// </summary>
	public class DemoDatabases
	{
		/// <summary>
		/// Remove unique indexes on text columns
		/// </summary>
		public static void Fix_Access_Northwind(DataSet schema)
		{
			foreach (string tableName in new string[] { "Customers", "Employees", "Products", "Shippers", "Suppliers", "Suppliers" })
			{
				foreach (DataRow row in schema.Tables[tableName + "_Keys"].Select("KeyName LIKE '%_Attachments_%'"))
					row.Delete();
			}

			foreach (DataRow row in schema.Tables["Orders_Keys"].Select("KeyName LIKE 'Orders_VersionHistory_%'"))
				row.Delete();

			foreach (DataRow row in schema.Tables["Products_Keys"].Select("KeyName LIKE 'Products_Supplier IDs%'"))
				row.Delete();

			//	Long Index Names

			Spludlow.Data.Schemas.FixLongIndexNames(schema);
		}

		/// <summary>
		/// Uses XML Indexes, just going to remove them
		/// </summary>
		public static void Fix_DB2_SAMPLE(DataSet schema)
		{
			foreach (DataTable table in schema.Tables)
			{
				if (table.TableName.EndsWith("_Keys") == false)
					continue;

				foreach (DataRow row in table.Select("KeyName LIKE '%_XMLIDX'"))
					row.Delete();
			}
		}

		/// <summary>
		/// MySQL dont like multicolumn PKs where one column is AutoIncrement
		/// </summary>
		public static void Fix_MSQL_AdventureWorks(DataSet schema)
		{
			Spludlow.Data.Schemas.FixTableNamesWithSchema(schema);

			Spludlow.Data.Schemas.FixKeyNames(schema);

			schema.Tables["Purchasing_PurchaseOrderDetail_Columns"].Rows.Find("PurchaseOrderDetailID")["AutoIncrement"] = false;
			schema.Tables["Sales_SalesOrderDetail_Columns"].Rows.Find("SalesOrderDetailID")["AutoIncrement"] = false;
			schema.Tables["Person_EmailAddress_Columns"].Rows.Find("EmailAddressID")["AutoIncrement"] = false;

			DataRow[] rows;

			rows = schema.Tables["Production_ProductReview_Keys"].Select("KeyName = 'IX_Production_ProductReview_1'");

			foreach (DataRow row in rows)
				row.Delete();
		}

		/// <summary>
		/// No FKs actually defined but there are indexes with names starting with "FK_" so convert them
		/// </summary>
		public static void Fix_SQLite_MediaPortal(DataSet schema)
		{
			List<string> tableNames = new List<string>(Spludlow.Data.Schemas.TableNames(schema));

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

				foreach (Spludlow.SysTables.SchemaKeysRow keyRow in keysTable.Rows)
				{
					if (keyRow.KeyName.StartsWith("FK_") == false || keyRow.KeyType != "I")
						continue;

					int index = keyRow.KeyName.LastIndexOf("_");

					string parentTable = keyRow.KeyName.Substring(index + 1);

					if (tableNames.Contains(parentTable, StringComparer.OrdinalIgnoreCase) == false)
						parentTable = parentTable.TrimEnd(new char[] { 's', '1' });

					if (tableNames.Contains(parentTable, StringComparer.OrdinalIgnoreCase) == false)
						throw new ApplicationException("FixMediaPortal: Parent table not found: " + parentTable);

					DataRow[] parentPrimaryKeyRows = schema.Tables[parentTable + "_Keys"].Select("KeyType = 'P' AND Ordinal = " + keyRow.Ordinal);

					if (parentPrimaryKeyRows.Length != 1)
						throw new ApplicationException("FixMediaPortal: Parent table primary key not found, parentTable:" + parentTable + ", ordinal:" + keyRow.Ordinal);

					Spludlow.SysTables.SchemaKeysRow parentPrimaryKeyRow = (Spludlow.SysTables.SchemaKeysRow)parentPrimaryKeyRows[0];

					keyRow.KeyType = "F";
					keyRow.ParentTable = parentTable;
					keyRow.ParentColumn = parentPrimaryKeyRow.ColumnName;
				}
			}

			Spludlow.Data.Schemas.FixForiegnKeys(schema);
		}
	}
}