using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using skyscraper5.Dvb.SystemSoftwareUpdate.Model;
using skyscraper8.Skyscraper.Scraper.Storage;

namespace skyscraper5.Data.MySql
{
    public partial class MySqlDataStorage : DataStorage
    {
        private struct GroupCoordinate
        {
            public byte CommonActionType { get; }
            public byte CommonOuiHash { get; }
            public string CommonOui { get; }
            public byte CommonProcessingOrder { get; }

            public GroupCoordinate(byte commonActionType, byte commonOuiHash, string commonOui, byte commonProcessingOrder)
            {
                CommonActionType = commonActionType;
                CommonOuiHash = commonOuiHash;
                CommonOui = commonOui;
                CommonProcessingOrder = commonProcessingOrder;
            }

            public bool Equals(GroupCoordinate other)
            {
                return CommonActionType == other.CommonActionType && CommonOuiHash == other.CommonOuiHash && CommonOui == other.CommonOui && CommonProcessingOrder == other.CommonProcessingOrder;
            }

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

            public override int GetHashCode()
            {
                return HashCode.Combine(CommonActionType, CommonOuiHash, CommonOui, CommonProcessingOrder);
            }
        }

        private HashSet<GroupCoordinate> _untGroupCoordinates;
        private bool TestForUnt(MySqlTransaction transaction, UpdateNotificationGroup common)
        {
            GroupCoordinate groupCoordinate = new GroupCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder);
            if (_untGroupCoordinates.Contains(groupCoordinate))
                return true;

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_unt WHERE action_type = @action_type AND oui_hash = @oui_hash AND oui = @oui AND processing_order = @processing_order";
            command.Parameters.AddWithValue("@action_type", common.ActionType);
            command.Parameters.AddWithValue("@oui_hash", common.OuiHash);
            command.Parameters.AddWithValue("@oui", common.Oui);
            command.Parameters.AddWithValue("@processing_order", common.ProcessingOrder);
            MySqlDataReader mySqlDataReader = command.ExecuteReader();
            bool read = mySqlDataReader.Read();
            if (read)
                _untGroupCoordinates.Add(groupCoordinate);
            mySqlDataReader.Close();
            return read;
        }

        private void InsertUnt(MySqlTransaction transaction, UpdateNotificationGroup common)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_unt (action_type, oui_hash, oui, processing_order, data_broadcast_id, association_tag, private_data, update_flag, update_method, update_priority) " +
                "VALUES (@action_type, @oui_hash, @oui, @processing_order, @data_broadcast_id, @association_tag, @private_data, @update_flag, @update_method, @update_priority)";
            command.Parameters.AddWithValue("@action_type", common.ActionType);
            command.Parameters.AddWithValue("@oui_hash", common.OuiHash);
            command.Parameters.AddWithValue("@oui", common.Oui);
            command.Parameters.AddWithValue("@processing_order", common.ProcessingOrder);
            command.Parameters.AddWithValue("@data_broadcast_id", common.DataBroadcastId);
            command.Parameters.AddWithValue("@association_tag", common.AssociationTag);
            command.Parameters.AddWithValue("@private_data", common.PrivateData);
            command.Parameters.AddWithValue("@update_flag", common.UpdateFlag);
            command.Parameters.AddWithValue("@update_method", common.UpdateMethod);
            command.Parameters.AddWithValue("@update_priority", common.UpdatePriority);
            command.ExecuteNonQuery();

            GroupCoordinate groupCoordinate = new GroupCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder);
            _untGroupCoordinates.Add(groupCoordinate);
        }

        private struct CompatibilityCoordinate
        {
            public byte CommonActionType { get; }
            public byte CommonOuiHash { get; }
            public string CommonOui { get; }
            public byte CommonProcessingOrder { get; }
            public byte CompatibilityDescriptorType { get; }

            public CompatibilityCoordinate(byte commonActionType, byte commonOuiHash, string commonOui, byte commonProcessingOrder, byte compatibilityDescriptorType)
            {
                CommonActionType = commonActionType;
                CommonOuiHash = commonOuiHash;
                CommonOui = commonOui;
                CommonProcessingOrder = commonProcessingOrder;
                CompatibilityDescriptorType = compatibilityDescriptorType;
            }

            public bool Equals(CompatibilityCoordinate other)
            {
                return CommonActionType == other.CommonActionType && CommonOuiHash == other.CommonOuiHash && CommonOui == other.CommonOui && CommonProcessingOrder == other.CommonProcessingOrder && CompatibilityDescriptorType == other.CompatibilityDescriptorType;
            }

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

            public override int GetHashCode()
            {
                return HashCode.Combine(CommonActionType, CommonOuiHash, CommonOui, CommonProcessingOrder, CompatibilityDescriptorType);
            }
        }

        private HashSet<CompatibilityCoordinate> _compatibilityCoordinates;
        private bool TestForUntCompat(MySqlTransaction transaction, UpdateNotificationGroup common, Compatibility compatibility)
        {
            CompatibilityCoordinate compatibilityCoordinate = new CompatibilityCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder, compatibility.DescriptorType);
            if (_compatibilityCoordinates.Contains(compatibilityCoordinate))
                return true;

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_unt_compatibility WHERE " +
                "action_type = @action_type AND oui_hash = @oui_hash AND oui = @oui AND processing_order = @processing_order AND descriptor_type = @descriptor_type";
            command.Parameters.AddWithValue("@action_type", common.ActionType);
            command.Parameters.AddWithValue("@oui_hash", common.OuiHash);
            command.Parameters.AddWithValue("@oui", common.Oui);
            command.Parameters.AddWithValue("@processing_order", common.ProcessingOrder);
            command.Parameters.AddWithValue("@descriptor_type", compatibility.DescriptorType);
            MySqlDataReader dataReader = command.ExecuteReader();
            bool result = dataReader.Read();
            if (result)
                _compatibilityCoordinates.Add(compatibilityCoordinate);
            dataReader.Close();
            return result;
        }

        private void InsertUntCompat(MySqlTransaction transaction, UpdateNotificationGroup common, Compatibility compatibility)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_unt_compatibility (action_type, oui_hash, oui, processing_order, descriptor_type, private_data, specifier_type, specifier_data, model, version) " +
                "VALUES (@action_type, @oui_hash, @oui, @processing_order, @descriptor_type, @private_data, @specifier_type, @specifier_data, @model, @version)";
            command.Parameters.AddWithValue("@action_type", common.ActionType);
            command.Parameters.AddWithValue("@oui_hash", common.OuiHash);
            command.Parameters.AddWithValue("@oui", common.Oui);
            command.Parameters.AddWithValue("@processing_order", common.ProcessingOrder);
            command.Parameters.AddWithValue("@descriptor_type", compatibility.DescriptorType);
            command.Parameters.AddWithValue("@private_data", compatibility.PrivateData);
            command.Parameters.AddWithValue("@specifier_type", compatibility.SpecifierType);
            command.Parameters.AddWithValue("@specifier_data", compatibility.SpecifierData);
            command.Parameters.AddWithValue("@model", compatibility.Model);
            command.Parameters.AddWithValue("@version", compatibility.Version);
            command.ExecuteNonQuery();
            _compatibilityCoordinates.Add(new CompatibilityCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder, compatibility.DescriptorType));
        }

        private int GetUntPlatformSqlKey(Platform platform)
        {
            int result = 0;
            if (platform.UpdateFlag.HasValue)
                result |= 0x02;
            if (platform.DataBroadcastId.HasValue)
                result |= 0x04;
            if (platform.SuperCaSystemId.HasValue)
                result |= 0x20;
            if (platform.SerialData != null)
                result |= 0x80;
            if (platform.MacAddressMatches != null)
                result |= 0x40;
            if (platform.SubgroupTag != null)
                result |= 0x400;
            return result;
        }

        private struct GroupCompatabilityPlatformCoordinate
        {
            public byte CommonActionType { get; }
            public byte CommonOuiHash { get; }
            public string CommonOui { get; }
            public byte CommonProcessingOrder { get; }
            public byte CompatibilityDescriptorType { get; }
            public int PlatformId { get; }

            public GroupCompatabilityPlatformCoordinate(byte commonActionType, byte commonOuiHash, string commonOui, byte commonProcessingOrder, byte compatibilityDescriptorType, int platformId)
            {
                CommonActionType = commonActionType;
                CommonOuiHash = commonOuiHash;
                CommonOui = commonOui;
                CommonProcessingOrder = commonProcessingOrder;
                CompatibilityDescriptorType = compatibilityDescriptorType;
                PlatformId = platformId;
            }
        }

        private HashSet<GroupCompatabilityPlatformCoordinate> _platformCoordinates;

        private bool TestForUntCompatPlatform(MySqlTransaction transaction, UpdateNotificationGroup common, Compatibility compatibility, Platform platform)
        {
            GroupCompatabilityPlatformCoordinate platformCoordinate = new GroupCompatabilityPlatformCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder, compatibility.DescriptorType, GetUntPlatformSqlKey(platform));

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_unt_compatibility_platform WHERE action_type = @action_type AND oui_hash = @oui_hash AND oui = @oui AND processing_order = @processing_order " +
                "AND descriptor_type = @descriptor_type AND platform_type_bitmask = @platform_type_bitmask";
            command.Parameters.AddWithValue("@action_type", common.ActionType);
            command.Parameters.AddWithValue("@oui_hash", common.OuiHash);
            command.Parameters.AddWithValue("@oui", common.Oui);
            command.Parameters.AddWithValue("@processing_order", common.ProcessingOrder);
            command.Parameters.AddWithValue("@descriptor_type", compatibility.DescriptorType);
            command.Parameters.AddWithValue("@platform_type_bitmask", platformCoordinate.PlatformId);
            MySqlDataReader dataReader = command.ExecuteReader();
            bool result = dataReader.Read();
            if (result)
                _platformCoordinates.Add(platformCoordinate);
            dataReader.Close();
            return result;
        }

        private void InsertUntCompatPlatform(MySqlTransaction transaction, UpdateNotificationGroup common, Compatibility compatibility, Platform platform)
        {
            GroupCompatabilityPlatformCoordinate platformCoordinate = new GroupCompatabilityPlatformCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder, compatibility.DescriptorType, GetUntPlatformSqlKey(platform));

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_unt_compatibility_platform (action_type, oui_hash, oui, processing_order, descriptor_type, platform_type_bitmask, update_flag, update_method, update_priority, data_broadcast_id, association_tag, private_data, smart_card_number, super_ca_system_id, serial_data, mac_address_mask, subgroup_tag) " +
                "VALUES (@action_type, @oui_hash, @oui, @processing_order, @descriptor_type, @platform_type_bitmask, @update_flag, @update_method, @update_priority, @data_broadcast_id, " +
                " @association_tag, @private_data, @smart_card_number, @super_ca_system_id, @serial_data, @mac_address_mask, @subgroup_tag)";
            command.Parameters.AddWithValue("@action_type", common.ActionType);
            command.Parameters.AddWithValue("@oui_hash", common.OuiHash);
            command.Parameters.AddWithValue("@oui", common.Oui);
            command.Parameters.AddWithValue("@processing_order", common.ProcessingOrder);
            command.Parameters.AddWithValue("@descriptor_type", compatibility.DescriptorType);
            command.Parameters.AddWithValue("@platform_type_bitmask", platformCoordinate.PlatformId);
            command.Parameters.AddWithValue("@update_flag", platform.UpdateFlag);
            command.Parameters.AddWithValue("@update_method", platform.UpdateMethod);
            command.Parameters.AddWithValue("@update_priority", platform.UpdatePriority);
            command.Parameters.AddWithValue("@data_broadcast_id", platform.DataBroadcastId);
            command.Parameters.AddWithValue("@association_tag", platform.AssociationTag);
            command.Parameters.AddWithValue("@private_data", platform.PrivateData);
            command.Parameters.AddWithValue("@smart_card_number", platform.SmartCardNumber);
            command.Parameters.AddWithValue("@super_ca_system_id", platform.SuperCaSystemId);
            command.Parameters.AddWithValue("@serial_data", platform.SerialData);
            command.Parameters.AddWithValue("@mac_address_mask", platform.MacAddressMask);
            command.Parameters.AddWithValue("@subgroup_tag", platform.SubgroupTag);
            command.ExecuteNonQuery();

            InsertUntCompatPlatformMacAddressMatches(transaction, platformCoordinate, platform);
            _platformCoordinates.Add(platformCoordinate);
        }

        private void InsertUntCompatPlatformMacAddressMatches(MySqlTransaction transaction, GroupCompatabilityPlatformCoordinate platformCoordinate, Platform platform)
        {
            if (platform.MacAddressMatches == null)
                return;

            if (platform.MacAddressMatches.Length == 0)
                return;

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_unt_compatibility_platform_mac_address_matches (action_type, oui_hash, oui, processing_order, descriptor_type, platform_type_bitmask, mac_match) " +
                "VALUES (@action_type, @oui_hash, @oui, @processing_order, @descriptor_type, @platform_type_bitmask, @mac_match)  ";
            command.Parameters.AddWithValue("@action_type", platformCoordinate.CommonActionType);
            command.Parameters.AddWithValue("@oui_hash", platformCoordinate.CommonOuiHash);
            command.Parameters.AddWithValue("@oui", platformCoordinate.CommonOui);
            command.Parameters.AddWithValue("@processing_order", platformCoordinate.CommonProcessingOrder);
            command.Parameters.AddWithValue("@descriptor_type", platformCoordinate.CompatibilityDescriptorType);
            command.Parameters.AddWithValue("@platform_type_bitmask", platformCoordinate.PlatformId);
            command.Parameters.Add("@mac_match", MySqlDbType.VarChar);

            foreach (string match in platform.MacAddressMatches)
            {
                command.Parameters["@mac_match"].Value = match;
                command.ExecuteNonQuery();
            }
        }

		public bool TestForUpdateNotification(int hashCode, UpdateNotificationGroup common)
		{
			throw new NotImplementedException();
		}

		public void StoreUpdateNotification(int hashCode, UpdateNotificationGroup common, Compatibility compatibility, Platform platform)
		{
			if (_untGroupCoordinates == null)
				_untGroupCoordinates = new HashSet<GroupCoordinate>();
			if (_compatibilityCoordinates == null)
				_compatibilityCoordinates = new HashSet<CompatibilityCoordinate>();
			if (_platformCoordinates == null)
				_platformCoordinates = new HashSet<GroupCompatabilityPlatformCoordinate>();

			GroupCoordinate groupCoordinate = new GroupCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder);
			if (_untGroupCoordinates.Contains(groupCoordinate))
			{
				CompatibilityCoordinate compatibilityCoordinate = new CompatibilityCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder, compatibility.DescriptorType);
				if (_compatibilityCoordinates.Contains(compatibilityCoordinate))
				{
					GroupCompatabilityPlatformCoordinate platformCoordinate = new GroupCompatabilityPlatformCoordinate(common.ActionType, common.OuiHash, common.Oui, common.ProcessingOrder, compatibility.DescriptorType, GetUntPlatformSqlKey(platform));
					if (_platformCoordinates.Contains(platformCoordinate))
					{
						return;
					}
				}
			}

			using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
			{
				connection.Open();
				MySqlTransaction transaction = connection.BeginTransaction();

				if (!TestForUnt(transaction, common))
					InsertUnt(transaction, common);

				if (!TestForUntCompat(transaction, common, compatibility))
					InsertUntCompat(transaction, common, compatibility);

				if (!TestForUntCompatPlatform(transaction, common, compatibility, platform))
					InsertUntCompatPlatform(transaction, common, compatibility, platform);

				transaction.Commit();
			}
		}

	}
}
