//	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.Email
{
	//	add indexes to DB address unique and EmailKey is used to match single (unique)

	public class BulkData
	{
		private static Spludlow.Data.IDAL _Database = Spludlow.Data.DAL.Create("@Spludlow");
		
		public static void SubmitToAddresses(string filename)
		{
			SubmitToAddresses(File.ReadAllLines(filename));
		}
		public static void SubmitToAddresses(string[] addresses)
		{
			//	Get list of good addresses to add

			HashSet<string> existingAddresses = ToAddressesHashSet();
			HashSet<string> addAddresses = new HashSet<string>();

			foreach (string rawAddress in addresses)
			{
				string address = rawAddress.Trim().ToLower();

				if (existingAddresses.Contains(address) == true)
					continue;

				if (addAddresses.Contains(address) == true)
					continue;

				if (Spludlow.Net.Smtp.ValidAddress(address) == false)
				{
					Spludlow.Log.Warning("Bulk Email Data Submit Address Format: " + address);
					continue;
				}

				if (Spludlow.Net.Smtp.ValidateAddressMX(address) == false)
				{
					Spludlow.Log.Warning("Bulk Email Data Submit Address MX Record: " + address);
					continue;
				}

				addAddresses.Add(address);
			}

			//	Prepare inserts

			DateTime addDate = DateTime.Now;

			Schema.ToEmailAddressesDataTable table = new Schema.ToEmailAddressesDataTable();
			foreach (string address in addAddresses)
			{
				Schema.ToEmailAddressesRow row = table.NewToEmailAddressesRow();

				row.Address = address;
				row.EmailKey = Guid.NewGuid().ToString();
				row.DateAdded = addDate;
				row.SendCount = 0;

				table.Rows.Add(row);
			}

			//	Database inserts in transaction

			Spludlow.Data.DAL.CommandInfo inserter = _Database.MakeInserter("ToEmailAddresses");

			_Database.Begin();

			try
			{
				foreach (DataRow row in table.Rows)
					_Database.Insert(inserter, row);

				_Database.Commit();
			}
			catch
			{
				_Database.Rollback();
				throw;
			}

			Spludlow.Log.Report("Bulk Data SubmitToAddresses; new:" + addAddresses.Count + ", submit:" + addresses.Length + ", existing: " + existingAddresses.Count);

		}

		public static HashSet<string> ToAddressesHashSet()
		{
			HashSet<string> addresses = new HashSet<string>();

			DataTable table = _Database.Select("SELECT Address FROM ToEmailAddresses");
			foreach (DataRow row in table.Rows)
				addresses.Add((string)row["Address"]);

			return addresses;
		}

		/// <summary>
		/// beforeDateLastSend should be the first DateLastSent of the current batch this will prevent re-sends if it dies half way through, you can use DateTime.MinValue the first time
		/// </summary>
		public static Schema.ToEmailAddressesDataTable Recipients(bool isMarketing, DateTime beforeDateLastSend)
		{
			string commandText = "SELECT * FROM ToEmailAddresses WHERE ((DateReturned IS NULL) AND (DateUnsubscribeEverything IS NULL) @DateUnsubscribeMarketing @DateLastSend)";

			Schema.ToEmailAddressesDataTable table = new Schema.ToEmailAddressesDataTable();

			commandText = commandText.Replace("@DateUnsubscribeMarketing", (isMarketing == true ?  "AND (DateUnsubscribeMarketing IS NULL)" : ""));

			if (beforeDateLastSend != DateTime.MinValue)
			{
				commandText = commandText.Replace("@DateLastSend", "AND (DateLastSend IS NULL OR DateLastSend < @DateLastSend)");
				_Database.Fill(table, commandText, beforeDateLastSend);
			}
			else
			{
				commandText = commandText.Replace("@DateLastSend", "");
				_Database.Fill(table, commandText);
			}
			
			return table;
		}

		public static void ConfirmSend(long toEmailAddressId, DateTime dateLastSend)
		{
			Spludlow.Data.DAL.CommandInfo updater = _Database.MakeUpdater("ToEmailAddresses", new string[] { "SendCount", "DateLastSend" });

			string commandText = "SELECT SendCount FROM ToEmailAddresses WHERE ToEmailAddressId = " + toEmailAddressId;

			_Database.Begin();

			try
			{
				int count = (int)_Database.ExecuteScalar(commandText) + 1;

				_Database.Update(updater, toEmailAddressId, count, dateLastSend);

				_Database.Commit();
			}
			catch
			{
				_Database.Rollback();
			}
		}

		/// <summary>
		/// Used by the Unsubscribe web page
		/// </summary>
		public static Schema.ToEmailAddressesDataTable GetRecord(string emailKey)
		{
			Schema.ToEmailAddressesDataTable table = new Schema.ToEmailAddressesDataTable();

			_Database.Fill(table, "SELECT * FROM ToEmailAddresses WHERE (EmailKey = @EmailKey)", emailKey);

			if (table.Rows.Count == 0)
				return null;

			if (table.Rows.Count > 1)
			{
				Spludlow.Log.Error("Bulk Email Data multiple matches for key: " + emailKey);
				return null;
			}
			return table;
		}

		public static void UnsubscribeMarketing(string emailKey)
		{
			Schema.ToEmailAddressesDataTable table = GetRecord(emailKey);
			if (table == null)
				return;

			Schema.ToEmailAddressesRow row = (Schema.ToEmailAddressesRow)table.Rows[0];

			if (row.IsDateUnsubscribeMarketingNull() == true)
				row.DateUnsubscribeMarketing = DateTime.Now;
			else
				row.SetDateUnsubscribeMarketingNull();

			_Database.Update("ToEmailAddresses", row);

		}


	}
}