using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using skyscraper5.Dvb.Descriptors;
using skyscraper5.Dvb.Psi.Model;
using static skyscraper5.Dvb.Descriptors.ServiceListDescriptor;
using skyscraper8.Skyscraper.Scraper.Storage;

namespace skyscraper5.Data.MySql
{
    public partial class MySqlDataStorage : DataStorage
    {
        private void InsertSdtCaIdentifiers(MySqlTransaction transaction, ushort transportStreamId, ushort originalNetworkId, SdtService sdtService)
        {
            if (sdtService.CaIdentifiers == null)
                return;

            if (sdtService.CaIdentifiers.Length == 0)
                return;

            MySqlCommand mySqlCommand = transaction.Connection.CreateCommand();
            mySqlCommand.Transaction = transaction;
            mySqlCommand.CommandText = "INSERT INTO dvb_sdt_ca_identifiers" +
                                       "    (tsid, onid, service_id, ca_identifier) " +
                                       "VALUES" +
                                       "    (@tsid,@onid,@service_id,@ca_identifier)";
            mySqlCommand.Parameters.Add("@tsid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@onid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@service_id", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@ca_identifier", MySqlDbType.Int32);
            mySqlCommand.Parameters["@tsid"].Value = transportStreamId;
            mySqlCommand.Parameters["@onid"].Value = originalNetworkId;
            mySqlCommand.Parameters["@service_id"].Value = sdtService.ServiceId;
            foreach (ushort sdtServiceCaIdentifier in sdtService.CaIdentifiers)
            {
                mySqlCommand.Parameters["@ca_identifier"].Value = sdtServiceCaIdentifier;
                mySqlCommand.ExecuteNonQuery();
            }
        }

        private void InsertSdtCountryAvailability(MySqlTransaction transaction, ushort transportStreamId, ushort originalNetworkId, SdtService sdtService)
        {
            if (sdtService.CountryAvailability == null)
                return;

            if (sdtService.CountryAvailability.Count == 0)
                return;

            MySqlCommand mySqlCommand = transaction.Connection.CreateCommand();
            mySqlCommand.Transaction = transaction;
            mySqlCommand.CommandText = "INSERT INTO dvb_sdt_country_availability" +
                                       "    (tsid, onid, service_id, country, availability) " +
                                       "VALUES " +
                                       "    (@tsid,@onid,@service_id,@country,@availability)";
            mySqlCommand.Parameters.Add("@tsid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@onid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@service_id", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@country", MySqlDbType.VarChar);
            mySqlCommand.Parameters.Add("@availability", MySqlDbType.Bool);
            mySqlCommand.Parameters["@tsid"].Value = transportStreamId;
            mySqlCommand.Parameters["@onid"].Value = originalNetworkId;
            mySqlCommand.Parameters["@service_id"].Value = sdtService.ServiceId;
            foreach (KeyValuePair<string, bool> keyValuePair in sdtService.CountryAvailability)
            {
                mySqlCommand.Parameters["@country"].Value = keyValuePair.Key;
                mySqlCommand.Parameters["@availability"].Value = keyValuePair.Value;
                mySqlCommand.ExecuteNonQuery();
            }
        }

        private void InsertSdtMultilingualServiceName(MySqlConnection connection, ushort transportStreamId, ushort originalNetworkId, SdtService sdtService)
        {
            if (sdtService.MultilingualServiceName == null)
                return;

            if (sdtService.MultilingualServiceName.Count == 0)
                return;

            MySqlCommand mySqlCommand = connection.CreateCommand();
            mySqlCommand.CommandText =
                "INSERT INTO dvb_sdt_multilingual_service_name" +
                "    (tsid, onid, service_id, language_code, service_provider_name, service_name)" +
                "VALUES" +
                "    (@tsid, @onid, @service_id, @language_code, @service_provider_name, @service_name)";
            mySqlCommand.Parameters.Add("@tsid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@onid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@service_id", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@language_code", MySqlDbType.VarChar);
            mySqlCommand.Parameters.Add("@service_provider_name", MySqlDbType.VarChar);
            mySqlCommand.Parameters.Add("@service_name", MySqlDbType.VarChar);
            mySqlCommand.Parameters["@tsid"].Value = transportStreamId;
            mySqlCommand.Parameters["@onid"].Value = originalNetworkId;
            mySqlCommand.Parameters["@service_id"].Value = sdtService.ServiceId;

            foreach (MultilingualServiceNameDescriptor.MultilingualServiceName multilingualServiceName in sdtService
                         .MultilingualServiceName)
            {
                mySqlCommand.Parameters["@language_code"].Value = multilingualServiceName.Iso639LanguageCode;
                mySqlCommand.Parameters["@service_provider_name"].Value = multilingualServiceName.ServiceProviderName;
                mySqlCommand.Parameters["@service_name"].Value = multilingualServiceName.ServiceName;
                mySqlCommand.ExecuteNonQuery();
            }
        }

        private void InsertSdtNvodReferences(MySqlTransaction transaction, ushort transportStreamId, ushort originalNetworkId, SdtService sdtService)
        {
            if (sdtService.NvodReferences == null)
                return;

            if (sdtService.NvodReferences.Length == 0)
                return;

            MySqlCommand mySqlCommand = transaction.Connection.CreateCommand();
            mySqlCommand.Transaction = transaction;
            mySqlCommand.CommandText =
                "INSERT INTO dvb_sdt_nvod_references " +
                "    (tsid, onid, service_id, other_tsid, other_onid, other_service_id) " +
                "VALUES " +
                "    (@tsid, @onid, @service_id, @other_tsid, @other_onid, @other_service_id)";
            mySqlCommand.Parameters.Add("@tsid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@onid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@service_id", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@other_tsid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@other_onid", MySqlDbType.Int32);
            mySqlCommand.Parameters.Add("@other_service_id", MySqlDbType.Int32);
            mySqlCommand.Parameters["@tsid"].Value = transportStreamId;
            mySqlCommand.Parameters["@onid"].Value = originalNetworkId;
            mySqlCommand.Parameters["@service_id"].Value = sdtService.ServiceId;

            foreach (NvodReferenceDescriptor.NvodReference nvodReference in sdtService.NvodReferences)
            {
                mySqlCommand.Parameters["@other_tsid"].Value = nvodReference.TransportStreamId;
                mySqlCommand.Parameters["@other_onid"].Value = nvodReference.OriginalNetworkId;
                mySqlCommand.Parameters["@other_service_id"].Value = nvodReference.ServiceId;
                mySqlCommand.ExecuteNonQuery();
            }
        }

        private static SdtService GetSdt(ushort transportStreamId, ushort originalNetworkId, ushort oldServiceId, MySqlConnection connection)
        {
            MySqlCommand mySqlCommand = connection.CreateCommand();
            mySqlCommand.CommandText = "SELECT * FROM dvb_sdt WHERE tsid = @tsid AND onid = @onid AND service_id = @service_id";
            mySqlCommand.Parameters.Add("@tsid", DbType.Int32);
            mySqlCommand.Parameters.Add("@onid", DbType.Int32);
            mySqlCommand.Parameters.Add("@service_id", DbType.Int32);
            mySqlCommand.Parameters["@tsid"].Value = transportStreamId;
            mySqlCommand.Parameters["@onid"].Value = originalNetworkId;
            mySqlCommand.Parameters["@service_id"].Value = oldServiceId;
            MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
            SdtService result = null;
            if (mySqlDataReader.Read())
            {
                ushort tsid = mySqlDataReader.GetUInt16(0);
                ushort onid = mySqlDataReader.GetUInt16(1);
                ushort serviceId = mySqlDataReader.GetUInt16(2);
                bool eitSchedule = mySqlDataReader.GetBoolean(3);
                bool eitScheduleFollowing = mySqlDataReader.GetBoolean(4);
                RunningStatus runningStatus = (RunningStatus)mySqlDataReader.GetInt16(5);
                bool freeCaMode = mySqlDataReader.GetBoolean(6);
                result = new SdtService(serviceId, eitSchedule, eitScheduleFollowing, runningStatus, freeCaMode);
                if (!mySqlDataReader.IsDBNull(7))
                    result.ServiceName = mySqlDataReader.GetString(7);
                if (!mySqlDataReader.IsDBNull(8))
                    result.ServiceProviderName = mySqlDataReader.GetString(8);
                if (!mySqlDataReader.IsDBNull(9))
                    result.ServiceType = (ServiceDescriptor.ServiceTypeCoding)mySqlDataReader.GetInt32(9);
                if (!mySqlDataReader.IsDBNull(10))
                    result.PrivateDataSpecifier = mySqlDataReader.GetUInt32(10);
                if (!mySqlDataReader.IsDBNull(11))
                    result.DataBroadcastId = mySqlDataReader.GetUInt16(11);
                if (!mySqlDataReader.IsDBNull(12))
                    result.Selector = mySqlDataReader.GetByteArray(12);
                if (!mySqlDataReader.IsDBNull(13))
                    result.ReferenceServiceId = mySqlDataReader.GetUInt16(13);
                if (!mySqlDataReader.IsDBNull(14))
                    result.DefaultAuthority = mySqlDataReader.GetString(14);
                if (!mySqlDataReader.IsDBNull(15))
                    result.ControlRemoteAccessOverInternet = mySqlDataReader.GetInt32(15);
                if (!mySqlDataReader.IsDBNull(16))
                    result.DoNotApplyRevocation = mySqlDataReader.GetBoolean(16);
                if (!mySqlDataReader.IsDBNull(17))
                    result.DoNotScramble = mySqlDataReader.GetBoolean(17);
                if (!mySqlDataReader.IsDBNull(18))
                    result.OldOriginalNetworkId = mySqlDataReader.GetUInt16(18);
                if (!mySqlDataReader.IsDBNull(19))
                    result.OldServiceId = mySqlDataReader.GetUInt16(19);
                if (!mySqlDataReader.IsDBNull(20))
                    result.OldTransportStreamId = mySqlDataReader.GetUInt16(20);
                if (!mySqlDataReader.IsDBNull(21))
                    Guid.Parse(mySqlDataReader.GetString(21));
                DateTime dateAdded = mySqlDataReader.GetDateTime(22);
                long numUpdates = mySqlDataReader.GetInt64(23);
                if (!mySqlDataReader.IsDBNull(24))
                    mySqlDataReader.GetDateTime(24);    //DateUpdated
                if (!mySqlDataReader.IsDBNull(25))
                    result.ComponentTag = mySqlDataReader.GetByte(25);
                if (!mySqlDataReader.IsDBNull(26))
                    result.Iso639LanguageCode = mySqlDataReader.GetString(26);
                if (!mySqlDataReader.IsDBNull(27))
                    result.Text = mySqlDataReader.GetString(27);
            }

            mySqlDataReader.Close();
            return result;
        }

        private struct SdtCoordinate
        {
            public ushort TransportStreamId { get; }
            public ushort OriginalNetworkId { get; }
            public ushort SdtServiceServiceId { get; }

            public SdtCoordinate(ushort transportStreamId, ushort originalNetworkId, ushort sdtServiceServiceId)
            {
                TransportStreamId = transportStreamId;
                OriginalNetworkId = originalNetworkId;
                SdtServiceServiceId = sdtServiceServiceId;
            }

            public bool Equals(SdtCoordinate other)
            {
                return TransportStreamId == other.TransportStreamId && OriginalNetworkId == other.OriginalNetworkId && SdtServiceServiceId == other.SdtServiceServiceId;
            }

            public override bool Equals(object obj)
            {
                return obj is SdtCoordinate other && Equals(other);
            }

            public override int GetHashCode()
            {
                return HashCode.Combine(TransportStreamId, OriginalNetworkId, SdtServiceServiceId);
            }
        }

        private HashSet<SdtCoordinate> _sdtCoordinates;
        private HashSet<SdtCoordinate> _sdtUpdateCoordinates;

        private void InsertSdtComponents(MySqlTransaction transaction, ushort transportStreamId, ushort originalNetworkId, SdtService sdtService)
        {
            if (sdtService.Components == null)
                return;

            if (sdtService.Components.Count == 0)
                return;

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_sdt_components (tsid, nid, service_id, component_tag, stream_content_ext, stream_content, component_type, iso_639_language_code, text) " +
                "VALUES (@tsid, @nid, @service_id, @component_tag, @stream_content_ext, @stream_content, @component_type, @iso_639_language_code, @text)";
            command.Parameters.AddWithValue("@tsid", transportStreamId);
            command.Parameters.AddWithValue("@nid", originalNetworkId);
            command.Parameters.AddWithValue("@service_id", sdtService.ServiceId);
            command.Parameters.Add("@component_tag", MySqlDbType.Int16);
            command.Parameters.Add("@stream_content_ext", MySqlDbType.Int16);
            command.Parameters.Add("@stream_content", MySqlDbType.Int16);
            command.Parameters.Add("@component_type", MySqlDbType.Int16);
            command.Parameters.Add("@iso_639_language_code", MySqlDbType.VarChar);
            command.Parameters.Add("@text", MySqlDbType.Text);
            bool[] componentTags = new bool[256];
            foreach (ComponentDescriptor component in sdtService.Components)
            {
                if (componentTags[component.ComponentTag])
                    continue;

                componentTags[component.ComponentTag] = true;
                command.Parameters["@component_tag"].Value = component.ComponentTag;
                command.Parameters["@stream_content_ext"].Value = component.StreamContentExt;
                command.Parameters["@stream_content"].Value = component.StreamContent;
                command.Parameters["@component_type"].Value = component.ComponentType;
                command.Parameters["@iso_639_language_code"].Value = component.Iso639LanguageCode;
                command.Parameters["@text"].Value = component.Text;
                command.ExecuteNonQuery();
            }
        }


		public bool TestForSdtService(ushort transportStreamId, ushort originalNetworkId, SdtService sdtService)
		{
			if (_sdtCoordinates == null)
				_sdtCoordinates = new HashSet<SdtCoordinate>();

			SdtCoordinate coordinate = new SdtCoordinate(transportStreamId, originalNetworkId, sdtService.ServiceId);
			if (_sdtCoordinates.Contains(coordinate))
				return true;


			using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
			{
				bool read;
				connection.Open();
				using (MySqlCommand mySqlCommand = connection.CreateCommand())
				{
					mySqlCommand.CommandText = "SELECT dateadded FROM dvb_sdt WHERE tsid=@tsid AND onid=@onid AND service_id=@sid";
					mySqlCommand.Parameters.Add("@tsid", DbType.Int32);
					mySqlCommand.Parameters.Add("@onid", DbType.Int32);
					mySqlCommand.Parameters.Add("@sid", DbType.Int32);
					mySqlCommand.Parameters["@tsid"].Value = transportStreamId;
					mySqlCommand.Parameters["@onid"].Value = originalNetworkId;
					mySqlCommand.Parameters["@sid"].Value = sdtService.ServiceId;
					MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
					read = mySqlDataReader.Read();
					if (read)
						_sdtCoordinates.Add(coordinate);
					mySqlDataReader.Close();
				}
				connection.Close();
				return read;
			}
		}


		public bool UpdateSdtService(ushort transportStreamId, ushort originalNetworkId, SdtService newer)
		{
			if (_sdtUpdateCoordinates == null)
				_sdtUpdateCoordinates = new HashSet<SdtCoordinate>();

			SdtCoordinate sdtCoordinate = new SdtCoordinate(transportStreamId, originalNetworkId, newer.ServiceId);

			if (_sdtUpdateCoordinates.Contains(sdtCoordinate))
				return false;

			using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
			{
				connection.Open();
				SdtService older = GetSdt(transportStreamId, originalNetworkId, newer.ServiceId, connection);
				if (older == null)
				{
					connection.Close();
					return false;
				}

				if (!older.NeedsUpdate(newer))
				{
					connection.Close();
					return false;
				}

				EnqueueSpeedhack(SpeedhackType.UpdateSdt, transportStreamId, originalNetworkId, newer);
				_sdtUpdateCoordinates.Add(sdtCoordinate);
				connection.Close();
				return true;
			}
		}



		public void StoreSdtService(ushort transportStreamId, ushort originalNetworkId, SdtService sdtService)
		{
			SdtCoordinate coordinate = new SdtCoordinate(transportStreamId, originalNetworkId, sdtService.ServiceId);
			_sdtCoordinates.Add(coordinate);
			EnqueueSpeedhack(SpeedhackType.InsertSdt, transportStreamId, originalNetworkId, sdtService);
		}


	}
}
