//	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.Mame
{
	public class MameDatabase
	{
		//	Machine

		private static readonly string MachineRomQueryText =
			"SELECT machine.name AS machine_name, machine.sourcefile, machine.sampleof, machine.isbios, machine.isdevice, machine.ismechanical, machine.runnable, machine.description, machine.year, machine.manufacturer, machine.romof, machine.cloneof, " +
			"rom.name AS rom_name, rom.size, rom.crc, rom.sha1, rom.region, rom.offset, rom.status, rom.optional, rom.[merge], rom.bios " +
			"FROM machine INNER JOIN rom ON machine.machine_Id = rom.machine_Id " +
			"@WHERE " +
			"ORDER BY machine.name, rom.name";

		private static readonly string MachineDiskQueryText =
			"SELECT machine.name AS machine_name, machine.sourcefile, machine.sampleof, machine.isbios, machine.isdevice, machine.ismechanical, machine.runnable, machine.description, machine.year, machine.manufacturer, machine.romof, machine.cloneof, " +
			"disk.name AS disk_name, disk.[merge], disk.sha1, disk.region, disk.[index], disk.writable, disk.status, disk.optional " +
			"FROM machine INNER JOIN disk ON machine.machine_Id = disk.machine_Id " +
			"@WHERE " +
			"ORDER BY machine.name, disk.name";

		//	Software

		private static readonly string Software_SELECT_QueryText =
			"SELECT softwarelist.name AS softwarelist_name , softwarelist.description AS softwarelist_description, " +
			"software.name AS software_name, software.description AS software_description, software.year, software.publisher, software.supported, software.cloneof, " +
			"part.name AS part_name, part.interface, ";

		private static readonly string Software_FROM_QueryText =
			"FROM (((softwarelist INNER JOIN software ON softwarelist.softwarelist_Id = software.softwarelist_Id) " +
			"INNER JOIN part ON software.software_Id = part.software_Id) ";

		private static readonly string SoftwareRomQueryText =
			Software_SELECT_QueryText +
			"dataarea.name AS dataarea_name, dataarea.size AS dataarea_size, dataarea.width, dataarea.endianness, " +
			"rom.name AS rom_name, rom.size AS rom_size, rom.crc, rom.sha1, rom.offset, rom.loadflag, rom.status, rom.value " +
			Software_FROM_QueryText +
			"INNER JOIN dataarea ON part.part_Id = dataarea.part_Id) " +
			"INNER JOIN rom ON dataarea.dataarea_Id = rom.dataarea_Id " +
			"@WHERE " +
			"ORDER BY softwarelist.name, software.name, rom.name";

		private static readonly string SoftwareDiskQueryText =
			Software_SELECT_QueryText +
			"diskarea.name AS diskarea_name, " +
			"disk.name AS disk_name, disk.sha1, disk.writeable, disk.status " +
			Software_FROM_QueryText +
			"INNER JOIN diskarea ON part.part_Id = diskarea.part_Id) " +
			"INNER JOIN disk ON diskarea.diskarea_Id = disk.diskarea_Id " +
			"@WHERE " +
			"ORDER BY softwarelist.name, software.name, disk.name";

		private static MameConfiguration _Config = new MameConfiguration();

		public static readonly string DatabaseNameMachine = _Config.DatabaseDirectory + @"\MameMachine.sqlite";
		public static readonly string DatabaseNameSoftware = _Config.DatabaseDirectory + @"\MameSoftware.sqlite";


		public static Spludlow.Data.IDAL Server()
		{
			return Spludlow.Data.DAL.Create("[sqlite]");
		}

		public static Spludlow.Data.IDAL Machine()
		{
			return Spludlow.Data.DAL.Create("[sqlite] " + DatabaseNameMachine);
		}

		public static Spludlow.Data.IDAL Software()
		{
			return Spludlow.Data.DAL.Create("[sqlite] " + DatabaseNameSoftware);
		}

		public static void DumpAllToReport()
		{
			MachineRomDumpToReport();
			MachineDiskDumpToReport();
			SoftwareRomDumpToReport();
			SoftwareDiskDumpToReport();
		}
		public static void MachineRomDumpToReport()
		{
			MameReport.Report("Machine Rom Dump", MachineRomQuery(null));
		}
		public static void MachineDiskDumpToReport()
		{
			MameReport.Report("Machine Disk Dump", MachineDiskQuery(null));
		}
		public static void SoftwareRomDumpToReport()
		{
			MameReport.Report("Software Rom Dump", SoftwareRomQuery(null));
		}
		public static void SoftwareDiskDumpToReport()
		{
			MameReport.Report("Software Disk Dump", SoftwareDiskQuery(null));
		}


		public static DataTable MachineRomQuery(string whereText)
		{
			string commandText = MachineRomQueryText;
			
			string replace = whereText != null ? "WHERE (" + whereText + ")" : "";

			commandText = commandText.Replace("@WHERE", replace);
			
			return Machine().Select(commandText);
		}
		public static DataTable MachineDiskQuery(string whereText)
		{
			string commandText = MachineDiskQueryText;

			string replace = whereText != null ? "WHERE (" + whereText + ")" : "";

			commandText = commandText.Replace("@WHERE", replace);

			return Machine().Select(commandText);
		}
		public static DataTable SoftwareRomQuery(string whereText)
		{
			string commandText = SoftwareRomQueryText;

			string replace = whereText != null ? "WHERE (" + whereText + ")" : "";

			commandText = commandText.Replace("@WHERE", replace);

			return Software().Select(commandText);
		}
		public static DataTable SoftwareDiskQuery(string whereText)
		{
			string commandText = SoftwareDiskQueryText;

			string replace = whereText != null ? "WHERE (" + whereText + ")" : "";

			commandText = commandText.Replace("@WHERE", replace);

			return Software().Select(commandText);
		}

		public static HashSet<string> MachineRomHashes()
		{
			DataTable table = Machine().Select("SELECT sha1 FROM rom WHERE (sha1 IS NOT NULL)");
			return ExtractHashset(table);
		}
		public static HashSet<string> MachineDiskHashes()
		{
			DataTable table = Machine().Select("SELECT sha1 FROM disk WHERE (sha1 IS NOT NULL)");
			return ExtractHashset(table);
		}
		public static HashSet<string> SoftwareRomHashes()
		{
			DataTable table = Software().Select("SELECT sha1 FROM rom WHERE (sha1 IS NOT NULL)");
			return ExtractHashset(table);
		}

		public static HashSet<string> SoftwareDiskHashes()
		{
			DataTable table = Software().Select("SELECT sha1 FROM disk WHERE (sha1 IS NOT NULL)");
			return ExtractHashset(table);
		}

		public static HashSet<string> ExtractHashset(DataTable table)
		{
			HashSet<string> hashSet = new HashSet<string>();
			foreach (DataRow row in table.Rows)
				ExtractHashsetRow(hashSet, row);
			return hashSet;
		}

		public static HashSet<string> ExtractHashset(DataRow[] rows)
		{
			HashSet<string> hashSet = new HashSet<string>();
			foreach (DataRow row in rows)
				ExtractHashsetRow(hashSet, row);
			return hashSet;
		}

		private static void ExtractHashsetRow(HashSet<string> hashSet, DataRow row)
		{
			if (row.IsNull("sha1") == true)
				return;
			string sha1 = ((string)row["sha1"]).ToUpper();
			if (hashSet.Contains(sha1) == false)
				hashSet.Add(sha1);
		}

		public static Dictionary<string, List<string>> GetMachineToSoftwareListLookup()
		{
			string queryText = "SELECT machine.name AS machine_name, softwarelist.name AS softwarelist_name " +
				"FROM machine INNER JOIN softwarelist ON machine.machine_Id = softwarelist.machine_Id " +
				"ORDER BY machine.name, softwarelist.name";

			return GetLookup(queryText);
		}

		public static Dictionary<string, List<string>> GetSoftwareListToMachineLookup()
		{
			string queryText = "SELECT softwarelist.name AS softwarelist_name, machine.name AS machine_name " +
				"FROM softwarelist INNER JOIN machine ON softwarelist.machine_Id = machine.machine_Id " +
				"ORDER BY softwarelist.name, machine.name";

			return GetLookup(queryText);
		}

		private static Dictionary<string, List<string>> GetLookup(string queryText)
		{
			DataTable table = MameDatabase.Machine().Select(queryText);
			Dictionary<string, List<string>> result = new Dictionary<string, List<string>>();

			foreach (DataRow row in table.Rows)
			{
				string key = (string)row[0];
				string value = (string)row[1];

				if (result.ContainsKey(key) == false)
					result.Add(key, new List<string>());

				if (result[key].Contains(value) == false)
					result[key].Add(value);
			}
			return result;
		}

		public static List<string> ExtractSoftwareListNames(DataTable table)
		{
			List<string> list = new List<string>();

			foreach (DataRow row in table.Rows)
			{
				string name = (string)row["softwarelist_name"];
				if (list.Contains(name) == false)
					list.Add(name);
			}

			list.Sort();

			return list;
		}

		public static Dictionary<string, List<string>> SoftwareCloneofParentsLookup(string softwareListName)
		{
			string commentText = "SELECT software.name, software.cloneof " +
				"FROM softwarelist INNER JOIN software ON softwarelist.softwarelist_Id = software.softwarelist_Id " +
				"WHERE ((softwarelist.name = '@SoftwareListName') AND (software.cloneof IS NOT NULL)) " +
				"ORDER BY software.name, software.cloneof";

			DataTable table = MameDatabase.Software().Select(commentText.Replace("@SoftwareListName", softwareListName));
			table.PrimaryKey = new DataColumn[] { table.Columns["name"] };

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

			foreach (DataRow row in table.Rows)
				SoftwareCloneofParentsAdd(lookup, row, row);

			return lookup;
		}
		private static void SoftwareCloneofParentsAdd(Dictionary<string, List<string>> lookup, DataRow startRow, DataRow currentRow)
		{
			string name = (string)startRow["name"];
			string cloneof = (string)currentRow["cloneof"];

			if (lookup.ContainsKey(name) == false)
				lookup.Add(name, new List<string>());
			lookup[name].Add(cloneof);

			//	No grand parents !!!
			if (lookup[name].Count > 1)
				throw new ApplicationException(name);

			DataRow nextRow = startRow.Table.Rows.Find(cloneof);
			if (nextRow != null)
			{
				SoftwareCloneofParentsAdd(lookup, startRow, nextRow);
			}
		}

		//
		//	Machines with Roms
		//

		public static string[] MachinesWithRoms()
		{
			string commandText = "SELECT machine.name " +
				"FROM machine INNER JOIN rom ON machine.machine_Id = rom.machine_Id " +
				"WHERE (rom.sha1 IS NOT NULL) " +
				"GROUP BY machine.name " +
				"ORDER BY machine.name";

			DataTable table = Machine().Select(commandText);
			List<string> list = new List<string>();
			foreach (DataRow row in table.Rows)
				list.Add((string)row["name"]);
			return list.ToArray();
		}

		//
		//	Software Lists with Disk
		//
		public static string[] SoftwareListsWithDisks()
		{
			string commandText = "SELECT softwarelist.name " +
				"FROM (((softwarelist INNER JOIN software ON softwarelist.softwarelist_Id = software.softwarelist_Id) INNER JOIN part ON software.software_Id = part.software_Id) " +
				"INNER JOIN diskarea ON part.part_Id = diskarea.part_Id) INNER JOIN disk ON diskarea.diskarea_Id = disk.diskarea_Id " +
				"WHERE (disk.sha1 IS NOT NULL) GROUP BY softwarelist.name ORDER BY softwarelist.name";

			DataTable table = Software().Select(commandText);
			List<string> list = new List<string>();
			foreach (DataRow row in table.Rows)
				list.Add((string)row["name"]);
			return list.ToArray();
		}

		//
		//	Software Lists with ROMs
		//
		public static string[] SoftwareListsWithROMs()
		{
			string commandText = "SELECT softwarelist.name " +
				"FROM (((softwarelist INNER JOIN software ON softwarelist.softwarelist_Id = software.softwarelist_Id) INNER JOIN part ON software.software_Id = part.software_Id) " +
				"INNER JOIN dataarea ON part.part_Id = dataarea.part_Id) INNER JOIN rom ON dataarea.dataarea_Id = rom.dataarea_Id " +
				"WHERE (rom.sha1 IS NOT NULL) GROUP BY softwarelist.name ORDER BY softwarelist.name";

			DataTable table = Software().Select(commandText);
			List<string> list = new List<string>();
			foreach (DataRow row in table.Rows)
				list.Add((string)row["name"]);
			return list.ToArray();
		}
	}
}