using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using NpgsqlTypes;
using skyscraper5.Skyscraper.Scraper.Storage.Utilities;

namespace skyscraper5.Data.PostgreSql
{
	public partial class PostgresqlDataStore
	{
		private Dictionary<DatabaseKeyTdt, DateTime> _knownTdt;

		public bool UpdateTimeAndDate(int currentNetworkId, int currentTransportStreamId, DateTime utcTime)
		{
			if (_knownTdt == null)
				_knownTdt = new Dictionary<DatabaseKeyTdt, DateTime>();
			DatabaseKeyTdt key = new DatabaseKeyTdt(currentNetworkId, currentTransportStreamId);
			if (!_knownTdt.ContainsKey(key))
			{
				DateTime? inDb = SelectTdt(currentNetworkId, currentTransportStreamId);
				if (inDb.HasValue)
				{
					//already in db
					_knownTdt.Add(key, inDb.Value);
				}
				else
				{
					//not in db, need insert
					EnqueueTask(x => InsertTdt(x, currentNetworkId, currentTransportStreamId, utcTime));
					EnqueueTask(CommitTransaction);
					_knownTdt.Add(key, utcTime);
					return true;
				}
			}

			DateTime lastKnownTime = _knownTdt[key];
			if (lastKnownTime < utcTime)
			{
				EnqueueTask(x => UpdateTdt(x, currentNetworkId, currentTransportStreamId, utcTime));
				EnqueueTask(CommitTransaction);
				_knownTdt[key] = utcTime;
				return true;
			}
			return false;
		}

		private void UpdateTdt(NpgsqlConnection connection, int currentNetworkId, int currentTransportStreamId, DateTime utcTime)
		{
			NpgsqlCommand command = connection.CreateCommand();
			command.CommandText =
				"UPDATE dvb_tdt " +
				"SET utc_time = @utc, updated_counter = updated_counter + 1, updated_timestamp = CURRENT_TIMESTAMP " +
				"WHERE cnid = @cnid " +
				"AND ctsid = @ctsid";
			command.Parameters.AddWithValue("@cnid", NpgsqlDbType.Integer, currentNetworkId);
			command.Parameters.AddWithValue("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
			command.Parameters.AddWithValue("@utc", NpgsqlDbType.Timestamp, utcTime);
			command.ExecuteNonQuery();
		}

		private void InsertTdt(NpgsqlConnection conn, int currentNetworkId, int currentTransportStreamId, DateTime utcTime)
		{
			NpgsqlCommand command = conn.CreateCommand();
			command.CommandText = "INSERT INTO dvb_tdt (cnid, ctsid, utc_time) VALUES (@cnid, @ctsid, @utc)";
			command.Parameters.AddWithValue("@cnid", NpgsqlDbType.Integer, currentNetworkId);
			command.Parameters.AddWithValue("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
			command.Parameters.AddWithValue("@utc", NpgsqlDbType.Timestamp, utcTime);
			command.ExecuteNonQuery();
		}

		private DateTime? SelectTdt(int currentNetworkId, int currentTransportStreamId)
		{
			DateTime? result = null;
			using (NpgsqlConnection conn = new NpgsqlConnection(connectionStringBuilder.ToString()))
			{
				conn.Open();
				NpgsqlCommand command = conn.CreateCommand();
				command.CommandText = "SELECT utc_time FROM dvb_tdt WHERE cnid = @cnid AND ctsid = @ctsid";
				command.Parameters.AddWithValue("@cnid", NpgsqlDbType.Integer, currentNetworkId);
				command.Parameters.AddWithValue("@ctsid", NpgsqlDbType.Integer, currentTransportStreamId);
				NpgsqlDataReader dataReader = command.ExecuteReader();
				if (dataReader.Read())
				{
					result = dataReader.GetDateTime(0);
				}
				dataReader.Close();
				command.Dispose();
				conn.Close();
			}
			return result;
		}
		
	}
}