//	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;
using System.Text.RegularExpressions;

namespace Spludlow.Video
{
	/// <summary>
	///	Export 2 Media Portal SQLite tables as TextTables, with some basic processing
	///	tuningdetail -> C:\ProgramData\SpludlowV1\Data\DVB.txt
	///	program -> C:\ProgramData\SpludlowV1\Data\DVBProgrammes.txt
	///	
	///	Make sure Config set: "VideoRecorder.TunerHost"
	///	
	///	Grant permissions to directory
	///	C:\ProgramData\Team MediaPortal		SpludlowUser	Read (seems to work without doing anything)
	///	
	/// SQLite DAL must be installed on TunerHost
	/// 
	///	Was accessing SQLite through network share but export was intermitantly hanging service process (had to reboot host!).
	///	TESTING NOW: RemoteSelect() so query is performed locally, 09/11/2017...? ok at 17/11
	///	
	///	NOTE: Code expects MP 1:1 between chanels and tuningdetails rows
	///	
	/// ENCODING BUG: I think Media portal must be not encoding the strings as they come out wrong?
	/// </summary>
	public class MediaPortal
	{
		public static string LocalDatabasePath = @"C:\ProgramData\Team MediaPortal\MP2-Server\Database\MP2TVE_3.s3db";

		public static string ConnectionString = "[SQLite] " + LocalDatabasePath;

		public static void LogSchema()
		{
			Spludlow.Data.IDAL dal = Spludlow.Data.DAL.Create(ConnectionString);

			DataSet dataSet = dal.Schema();
			
			Spludlow.Log.Report("MediaPortal Schema", new object[] { dataSet });
		}

		public static void LogTableContents(bool includeProgram)
		{
			Spludlow.Data.IDAL dal = Spludlow.Data.DAL.Create(ConnectionString);

			DataSet dataSet = new DataSet();

			foreach (string tableName in dal.TableList())
			{
				if (includeProgram == false && tableName.ToLower() == "program")
					continue;

				DataTable table = dal.Select("SELECT * FROM " + tableName);
				table.TableName = tableName;

				dataSet.Tables.Add(table);
			}

			Spludlow.Log.Report("MediaPortal Table Contents includeProgram: " + includeProgram, new object[] { dataSet });
		}

		public static DataTable RemoteSelect(string commandText)    //	Call the SQLite DAL SelectDS() method on remote host
		{
			string tunerHost = Spludlow.Config.Get("VideoRecorder.TunerHost");

			DataSet dataSet = (DataSet)Spludlow.Call.Now(tunerHost, "Spludlow.Data.SQLite", "Spludlow.Data.DALSQLite", "SelectDS", new object[] { commandText }, new object[] { LocalDatabasePath }, CallFlags.LargeResult);
			return dataSet.Tables[0];
		}

		public static void Export()
		{
			Export(null);
		}
		public static void Export(string databaseDirectory)
		{
			string commandText;
			DataTable table;

			commandText = "SELECT idChannel, name, channelType, channelNumber, frequency, countryId, isRadio, isTv, networkId, transportId, serviceId, pmtPid, freeToAir, modulation, polarisation, symbolrate, bandwidth " +
							"FROM tuningdetail ORDER BY channelNumber, frequency, idTuning";

			if (databaseDirectory == null)
			{
				table = RemoteSelect(commandText);
			}
			else
			{
				Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create("[sqlite] " + databaseDirectory);
				table = database.Select(commandText);
			}
			ExportChannels(table, Spludlow.Config.ProgramData + @"\Data\DVB.txt");


			commandText = "SELECT idProgram, idChannel, startTime, endTime, title, description, genre FROM program";
			if (databaseDirectory == null)
			{
				table = RemoteSelect(commandText);
			}
			else
			{
				Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create("[sqlite] " + databaseDirectory);
				table = database.Select(commandText);
			}
			ExportPrograms(table, Spludlow.Config.ProgramData + @"\Data\DVBProgrammes.txt");
		}

		public static void ExportChannels(DataTable mediaPortalTable, string targetFilename)
		{
			DataTable table = Spludlow.Data.TextTable.ReadText(new string[]
			{
				"ChannelId	NetworkType	ChannelType	Number	Name	Frequency	Language	NetworkId	TransportId	ServiceId	PmtPid	FreeToAir	Modulation	Polarisation	SymbolRate	Bandwidth",
				"Int32*		String		String		Int32	String	Int32		String		Int32		Int32		Int32		Int32	Boolean		String		String			Int32		Int32",
			});

			foreach (DataRow mpRow in mediaPortalTable.Rows)
			{
				DataRow row = table.NewRow();

				string networkType;         //	Ter, Sat, Cab
				int mpChannelType = (int)mpRow["channelType"];
				switch (mpChannelType)
				{
					case 4:
						networkType = "T";
						break;
					default:
						throw new ApplicationException("Unknown MP channelType: " + mpChannelType);
				}

				string channelType = "T";   //	Tv, Radio, Data?
				if ((bool)mpRow["isRadio"] == true)
					channelType = "R";

				string language;            // en
				int mpCountryId = (int)mpRow["countryId"];
				switch (mpCountryId)
				{
					case 31:
						language = "en";
						break;
					default:
						throw new ApplicationException("Unknown MP CountryId: " + mpCountryId);
				}

				row["ChannelId"] = (int)mpRow["idChannel"];
				row["NetworkType"] = networkType;
				row["ChannelType"] = channelType;
				row["Number"] = mpRow["channelNumber"];
				row["Name"] = mpRow["name"];
				row["Frequency"] = mpRow["frequency"];
				row["Language"] = language;
				row["NetworkId"] = mpRow["networkId"];
				row["TransportId"] = mpRow["transportId"];
				row["ServiceId"] = mpRow["serviceId"];
				row["PmtPid"] = mpRow["pmtPid"];
				row["FreeToAir"] = mpRow["freeToAir"];
				row["Modulation"] = DBNull.Value;
				row["Polarisation"] = DBNull.Value;
				row["SymbolRate"] = DBNull.Value;
				row["Bandwidth"] = mpRow["bandwidth"];

				table.Rows.Add(row);
			}

			Spludlow.Data.TextTable.Write(targetFilename, table);

			Spludlow.Log.Report("Exported MediaPortal Channels to file: " + targetFilename, new object[] { table });
		}

		public static void ExportPrograms(DataTable mediaPortalProgramTable, string targetFilename)
		{
			DataTable table = Spludlow.Data.TextTable.ReadText(new string[]
			{
				"ProgramId	ChannelId	StartDate	EndDate		Duration	Title	Description	Genre",
				"Int64*		Int32		DateTime	DateTime	Int32		String	String		String",
			});

			Regex cleanWhite = new Regex(@"\s+");

			foreach (DataRow sourceRow in mediaPortalProgramTable.Rows)
			{
				DataRow row = table.NewRow();
				
				TimeSpan span = (DateTime)sourceRow["endTime"] - (DateTime)sourceRow["startTime"];

				row["ProgramId"] = sourceRow["idProgram"];
				row["ChannelId"] = sourceRow["idChannel"];
				row["StartDate"] = sourceRow["startTime"];
				row["EndDate"] = sourceRow["endTime"];
				row["Duration"] = span.TotalMinutes;
				row["Title"] = cleanWhite.Replace((string)sourceRow["title"], " ");
				row["Description"] = cleanWhite.Replace((string)sourceRow["description"], " ");
				row["Genre"] = cleanWhite.Replace((string)sourceRow["genre"], " ");

				table.Rows.Add(row);
			}

			Spludlow.Data.TextTable.Write(targetFilename, table);

			Spludlow.Log.Report("Exported MediaPortal Programmes to file: " + targetFilename, new object[] { table });
		}


	}
}