using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySqlConnector;
using skyscraper5.Dvb.Descriptors;
using skyscraper5.Dvb.Descriptors.Extension;
using skyscraper5.Mpeg2.Descriptors;
using skyscraper5.Mpeg2.Psi.Model;
using System.IO;
using static skyscraper5.Dvb.Descriptors.ApplicationSignallingDescriptor;
using skyscraper8.Skyscraper.Scraper.Storage;

namespace skyscraper5.Data.MySql
{
    public partial class MySqlDataStorage : DataStorage
    {
        private bool TestForPmtStream(MySqlTransaction transaction, int onid, int tsid, int progno, int pid)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_pmt_streams WHERE onid = @onid AND tsid = @tsid AND program_number = @program_number AND elementary_pid = @elementary_pid";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@program_number", progno);
            command.Parameters.AddOrSet("@elementary_pid", pid);
            MySqlDataReader dataReader = command.ExecuteReader();
            bool result = dataReader.Read();
            dataReader.Close();
            return result;
        }

        private void InsertPmtStreams(MySqlConnection connection, ProgramMapping mapping, int onid, int tsid,
            MySqlTransaction mySqlTransaction)
        {
            if (mapping.Streams == null)
                return;

            if (mapping.Streams.Count == 0)
                return;


            MySqlCommand command = connection.CreateCommand();
            command.CommandText =
                "INSERT INTO dvb_pmt_streams (onid, tsid, program_number, stream_type, elementary_pid, component_tag, audio_type, iso_639_language_code, carousel_format_id, carousel_id, data_broadcast_id, data_broadcast_selector, supplementary_independant_stream, editorial_classification, supplementary_language_code, asvc, bsid, component_type, main_id, frame_rate, mpeg1_only_flag, chroma_format, constrained_parameter_flag, frame_rate_extension_flag, multiple_framerate_flag, profile_and_level_indicator, still_picture_flag, alignment_type, maximum_bitrate, audio_stream_id, free_format, layer, variable_rate_audio, constraint_set0_flag, avc_24hour_picture_flag, avc_compatible_flags, avc_still_present, constraint_set1_flag, constraint_set2_flag, constraint_set3_flag, constraint_set4_flag, constraint_set5_flag, frame_packing_sei_not_present, level_idc, profile_rdc, ca_pid, ca_system_id, ca_private_data, private_data_specifier, format_identifier, additional_identification_info, association_tag_selector, association_tag_transaction_id, association_tag_use, association_tag_private_data, association_tag_timeout, aac_type, aac_additional_info, aac_profile_and_level, saoc_de_flag, ancillary_data_identifier, mix_info_exists, substream1, substream2, substream3, copied_44bits, frame_only_constraint_flag, hdr_wcg_idc, hevc_24hr_picture_present_flag, hevc_still_present_flag, interlaced_source_flag, non_packed_constraint_flag, profile_compatibility_indication, profile_idc, profile_space, progressive_source_flag, sub_pic_hrd_params_not_present_flag, temporal_id_max, tier_flag, temporal_id_min, ac4_channel_mode, ac4_dialog_enhancement, ac4_dsi, leak_valid_flag, sb_leak_rate, sb_size, private_data_indicator, _90khz_flag, picture_and_timing_info_present_flag, hdr_management_valid_flag, k, n, num_units_in_tick, target_schedule_idx, target_schedule_idx_not_present_flag, adaption_field_data_identifier, fixed_frame_rate_flag, picture_to_display_conversion_flag, temporal_poc_flag, mpeg4_audio_profile_and_level, aac_channel_configuration, aac_profile, aac_additional_information, scrambling_mode, related_content_descriptor_present, num_t2mi_streams, t2mi_stream_id, pcr_iscr_common_clock_flag) " +
                "VALUES " +
                "(@onid, @tsid, @program_number, " +
                "@stream_type, @elementary_pid, @component_tag, @audio_type, @iso_639_language_code, @carousel_format_id, @carousel_id, @data_broadcast_id, @data_broadcast_selector, " +
                "@supplementary_independant_stream, @editorial_classification, @supplementary_language_code, @asvc, @bsid, @component_type, @main_id, @frame_rate, @mpeg1_only_flag, " +
                "@chroma_format, @constrained_parameter_flag, @frame_rate_extension_flag, @multiple_framerate_flag, @profile_and_level_indicator, @still_picture_flag, @alignment_type, " +
                "@maximum_bitrate, @audio_stream_id, @free_format, @layer, @variable_rate_audio, @constraint_set0_flag, @avc_24hour_picture_flag, @avc_compatible_flags, @avc_still_present, @constraint_set1_flag, @constraint_set2_flag, @constraint_set3_flag, @constraint_set4_flag, @constraint_set5_flag, @frame_packing_sei_not_present, @level_idc, @profile_rdc, @ca_pid, @ca_system_id, @ca_private_data, @private_data_specifier, @format_identifier, @additional_identification_info, @association_tag_selector, @association_tag_transaction_id, @association_tag_use, @association_tag_private_data, @association_tag_timeout, @aac_type, @aac_additional_info, @aac_profile_and_level, @saoc_de_flag, @ancillary_data_identifier, @mix_info_exists, @substream1, @substream2, @substream3, @copied_44bits, @frame_only_constraint_flag, @hdr_wcg_idc, @hevc_24hr_picture_present_flag, @hevc_still_present_flag, @interlaced_source_flag, @non_packed_constraint_flag, @profile_compatibility_indication, @profile_idc, @profile_space, @progressive_source_flag, @sub_pic_hrd_params_not_present_flag, @temporal_id_max, @tier_flag, @temporal_id_min, @ac4_channel_mode, @ac4_dialog_enhancement, @ac4_dsi, @leak_valid_flag, @sb_leak_rate, @sb_size, @private_data_indicator, @_90khz_flag, @picture_and_timing_info_present_flag, @hdr_management_valid_flag, @k, @n, @num_units_in_tick, @target_schedule_idx, @target_schedule_idx_not_present_flag, @adaption_field_data_identifier, @fixed_frame_rate_flag, @picture_to_display_conversion_flag, @temporal_poc_flag, @mpeg4_audio_profile_and_level, @aac_channel_configuration, @aac_profile, @aac_additional_information, @scrambling_mode, @related_content_descriptor_present, @num_t2mi_streams, @t2mi_stream_id, @pcr_iscr_common_clock_flag)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@program_number", mapping.ProgramNumber);
            foreach (ProgramMappingStream stream in mapping.Streams)
            {
                if (TestForPmtStream(mySqlTransaction, onid, tsid, mapping.ProgramNumber, stream.ElementaryPid))
                    continue;

                command.Parameters.AddOrSet("@stream_type",(int)stream.StreamType);
                command.Parameters.AddOrSet("@elementary_pid", stream.ElementaryPid);
                command.Parameters.AddOrSet("@component_tag", stream.ComponentTag);
                command.Parameters.AddOrSet("@audio_type", stream.AudioType);
                command.Parameters.AddOrSet("@iso_639_language_code", stream.Iso639LanguageCode);
                command.Parameters.AddOrSet("@carousel_format_id", stream.CarouselFormatId);
                command.Parameters.AddOrSet("@carousel_id", stream.CarouselId);
                command.Parameters.AddOrSet("@data_broadcast_id", stream.DataBroadcastId);
                command.Parameters.AddOrSet("@data_broadcast_selector", stream.DataBroadcastSelector);
                command.Parameters.AddOrSet("@supplementary_independant_stream", stream.SupplementaryIndependantStream);
                command.Parameters.AddOrSet("@editorial_classification", stream.EditorialClassification);
                command.Parameters.AddOrSet("@supplementary_language_code", stream.SupplementaryLanguageCode);
                command.Parameters.AddOrSet("@asvc", stream.Asvc);
                command.Parameters.AddOrSet("@bsid", stream.BSID);
                command.Parameters.AddOrSet("@component_type", stream.ComponentType);
                command.Parameters.AddOrSet("@main_id", stream.MainId);
                command.Parameters.AddOrSet("@frame_rate", stream.FrameRate);
                command.Parameters.AddOrSet("@mpeg1_only_flag", stream.Mpeg1OnlyFlag);
                command.Parameters.AddOrSet("@chroma_format", stream.ChromaFormat);
                command.Parameters.AddOrSet("@constrained_parameter_flag", stream.ConstrainedParameterFlag);
                command.Parameters.AddOrSet("@frame_rate_extension_flag", stream.FrameRateExtensionFlag);
                command.Parameters.AddOrSet("@multiple_framerate_flag", stream.MultipleFramerateFlag);
                command.Parameters.AddOrSet("@profile_and_level_indicator", stream.ProfileAndLevelIndication);
                command.Parameters.AddOrSet("@still_picture_flag", stream.StillPictureFlag);
                command.Parameters.AddOrSet("@alignment_type", stream.AlignmentType);
                command.Parameters.AddOrSet("@maximum_bitrate", stream.MaximumBitrate);
                command.Parameters.AddOrSet("@audio_stream_id", stream.AudioStreamId);
                command.Parameters.AddOrSet("@free_format", stream.FreeFormat);
                command.Parameters.AddOrSet("@layer", stream.Layer);
                command.Parameters.AddOrSet("@variable_rate_audio", stream.VariableRateAudio);
                command.Parameters.AddOrSet("@constraint_set0_flag", stream.ConstraintSet0Flag);
                command.Parameters.AddOrSet("@avc_24hour_picture_flag", stream.Avc24HourPictureFlag);
                //@avc_compatible_flags, @avc_still_present, @constraint_set1_flag, @constraint_set2_flag, @constraint_set3_flag, @constraint_set4_flag, @constraint_set5_flag,
                //@frame_packing_sei_not_present, @level_idc, @profile_rdc, @ca_pid, @ca_system_id, @ca_private_data, @private_data_specifier, @format_identifier, @additional_identification_info,
                //@association_tag_selector, @association_tag_transaction_id, @association_tag_use, @association_tag_private_data, @association_tag_timeout, @aac_type, @aac_additional_info,
                //@aac_profile_and_level, @saoc_de_flag, @ancillary_data_identifier, @mix_info_exists, @substream1, @substream2,
                command.Parameters.AddOrSet("@avc_compatible_flags", stream.AvcCompatibleFlags);
                command.Parameters.AddOrSet("@avc_still_present", stream.AvcStillPresent);
                command.Parameters.AddOrSet("@constraint_set1_flag", stream.ConstraintSet1Flag);
                command.Parameters.AddOrSet("@constraint_set2_flag", stream.ConstraintSet2Flag);
                command.Parameters.AddOrSet("@constraint_set3_flag", stream.ConstraintSet3Flag);
                command.Parameters.AddOrSet("@constraint_set4_flag", stream.ConstraintSet4Flag);
                command.Parameters.AddOrSet("@constraint_set5_flag", stream.ConstraintSet5Flag);
                command.Parameters.AddOrSet("@frame_packing_sei_not_present", stream.FramePackingSeiNotPresent);
                command.Parameters.AddOrSet("@level_idc", stream.LevelIdc);
                command.Parameters.AddOrSet("@profile_rdc", stream.ProfileIdc);
                command.Parameters.AddOrSet("@ca_pid", stream.CaPid);
                command.Parameters.AddOrSet("@ca_system_id", stream.CaSystemId);
                command.Parameters.AddOrSet("@ca_private_data", stream.CaPrivateData);
                command.Parameters.AddOrSet("@private_data_specifier", stream.PrivateDataSpecifier);
                command.Parameters.AddOrSet("@format_identifier", stream.FormatIdentifier);
                command.Parameters.AddOrSet("@additional_identification_info", stream.AdditionalIdentificationInfo);
                command.Parameters.AddOrSet("@association_tag_selector", stream.AssociationTagSelector);
                command.Parameters.AddOrSet("@association_tag_transaction_id", stream.AssociationTagTransactionId);
                command.Parameters.AddOrSet("@association_tag_use", stream.AssociationTagUse);
                command.Parameters.AddOrSet("@association_tag_private_data", stream.AssociationTagPrivateData);
                command.Parameters.AddOrSet("@association_tag_timeout", stream.AssociationTagTimeOut);
                command.Parameters.AddOrSet("@aac_type", stream.AacType);
                command.Parameters.AddOrSet("@aac_additional_info", stream.AacAdditionalInfo);
                command.Parameters.AddOrSet("@aac_profile_and_level", stream.AacProfileAndLevel);
                command.Parameters.AddOrSet("@saoc_de_flag", stream.SaocDeFlag);
                if (stream.AncillaryDataDescriptor != null)
                {
                    command.Parameters.AddOrSet("@ancillary_data_identifier", stream.AncillaryDataDescriptor.AncillaryDataIdentifier);
                }
                else
                {
                    command.Parameters.AddOrSet("@ancillary_data_identifier",(byte)0);
                    command.Parameters.AddOrSet("@ancillary_data_identifier",DBNull.Value);
                }
                command.Parameters.AddOrSet("@mix_info_exists", stream.MixInfoExists);
                command.Parameters.AddOrSet("@substream1", stream.Substream1);
                command.Parameters.AddOrSet("@substream2", stream.Substream2);
                //@substream3, @copied_44bits, @frame_only_constraint_flag, @hdr_wcg_idc, @hevc_24hr_picture_present_flag, @hevc_still_present_flag, @interlaced_source_flag,
                //@non_packed_constraint_flag, @profile_compatibility_indication, @profile_idc, @profile_space, @progressive_source_flag, @sub_pic_hrd_params_not_present_flag,
                //@temporal_id_max, @tier_flag, @temporal_id_min, @ac4_channel_mode, @ac4_dialog_enhancement, @ac4_dsi, @leak_valid_flag, @sb_leak_rate, @sb_size, @private_data_indicator,
                //@_90khz_flag, @picture_and_timing_info_present_flag, @hdr_management_valid_flag, @k, @n, @num_units_in_tick, @target_schedule_idx, @target_schedule_idx_not_present_flag,
                //@adaption_field_data_identifier, @fixed_frame_rate_flag, @picture_to_display_conversion_flag, @temporal_poc_flag, @mpeg4_audio_profile_and_level
                command.Parameters.AddOrSet("@substream3", stream.Substream3);
                command.Parameters.AddOrSet("@copied_44bits", stream.Copied44bits);
                command.Parameters.AddOrSet("@frame_only_constraint_flag", stream.FrameOnlyConstraintFlag);
                command.Parameters.AddOrSet("@hdr_wcg_idc", stream.HdrWcgIdc);
                command.Parameters.AddOrSet("@hevc_24hr_picture_present_flag", stream.Hevc24hrPicturePresentFlag);
                command.Parameters.AddOrSet("@hevc_still_present_flag", stream.HevcStillPresentFlag);
                command.Parameters.AddOrSet("@interlaced_source_flag", stream.InterlacedSourceFlag);
                command.Parameters.AddOrSet("@non_packed_constraint_flag", stream.NonPackedConstraintFlag);
                command.Parameters.AddOrSet("@profile_compatibility_indication", stream.ProfileCompatibilityIndication);
                command.Parameters.AddOrSet("@profile_idc", stream.ProfileIdc);
                command.Parameters.AddOrSet("@profile_space", stream.ProfileSpace);
                command.Parameters.AddOrSet("@progressive_source_flag", stream.ProgressiveSourceFlag);
                command.Parameters.AddOrSet("@sub_pic_hrd_params_not_present_flag", stream.SubPicHrdParamsNotPresentFlag);
                command.Parameters.AddOrSet("@temporal_id_max", stream.TemporalIdMax);
                command.Parameters.AddOrSet("@tier_flag", stream.TierFlag);
                command.Parameters.AddOrSet("@temporal_id_min", stream.TemporalIdMin);
                command.Parameters.AddOrSet("@ac4_channel_mode", stream.Ac4ChannelMode);
                command.Parameters.AddOrSet("@ac4_dialog_enhancement", stream.Ac4DialogEnhancement);
                command.Parameters.AddOrSet("@ac4_dsi", stream.Ac4Dsi);
                command.Parameters.AddOrSet("@leak_valid_flag", stream.LeakValidFlag);
                command.Parameters.AddOrSet("@sb_leak_rate", stream.SbLeakRate);
                command.Parameters.AddOrSet("@sb_size", stream.SbSize);
                command.Parameters.AddOrSet("@private_data_indicator", stream.PrivateDataIndicator);
                command.Parameters.AddOrSet("@_90khz_flag", stream._90khzFlag);
                command.Parameters.AddOrSet("@picture_and_timing_info_present_flag", stream.PictureAndTimingInfoPresentFlag);
                command.Parameters.AddOrSet("@hdr_management_valid_flag", stream.HdrManagementValidFlag);
                command.Parameters.AddOrSet("@k", stream.K);
                command.Parameters.AddOrSet("@n", stream.N);
                command.Parameters.AddOrSet("@num_units_in_tick", stream.NumUnitsInTick);
                command.Parameters.AddOrSet("@target_schedule_idx", stream.TargetScheduleIdx);
                command.Parameters.AddOrSet("@target_schedule_idx_not_present_flag", stream.TargetScheduleIdxNotPresentFlag);
                command.Parameters.AddOrSet("@adaption_field_data_identifier", stream.AdaptionFieldDataIdentifier);
                command.Parameters.AddOrSet("@fixed_frame_rate_flag", stream.FixedFrameRateFlag);
                command.Parameters.AddOrSet("@picture_to_display_conversion_flag", stream.PictureToDisplayConversionFlag);
                command.Parameters.AddOrSet("@temporal_poc_flag", stream.TemporalPocFlag);
                command.Parameters.AddOrSet("@mpeg4_audio_profile_and_level", stream.Mpeg4AudioProfileAndLevel);
                //@, @, @, @, @, @, @, @pcr_iscr_common_clock_flag)";
                command.Parameters.AddOrSet("@aac_channel_configuration", stream.AacChannelConfiguration);
                command.Parameters.AddOrSet("@aac_profile", stream.AacProfile);
                command.Parameters.AddOrSet("@aac_additional_information", stream.AacAdditionalInformation);
                command.Parameters.AddOrSet("@scrambling_mode", stream.ScramblingMode);
                command.Parameters.AddOrSet("@related_content_descriptor_present", stream.RelatedContentDescriptorPresent);
                command.Parameters.AddOrSet("@num_t2mi_streams", stream.NumT2MiStreams);
                command.Parameters.AddOrSet("@t2mi_stream_id", stream.T2MiStreamId);
                command.Parameters.AddOrSet("@pcr_iscr_common_clock_flag", stream.PcrIscrCommonClockFlag);
                command.Transaction = mySqlTransaction;
                SetNulls(command);
                command.ExecuteNonQuery();

                InsertPmtStreamsApplications(onid, tsid, stream, connection, mySqlTransaction);
                InsertPmtStreamsAudioPreselection(onid, tsid, stream, mySqlTransaction);
                InsertPmtStreamsFlexmux(onid, tsid, stream, connection);
                InsertPmtStreamsIp(onid, tsid, stream, connection);
                InsertPmtStreamsSubtitle(onid, tsid, stream, connection, mySqlTransaction);
                InsertPmtStreamsTeletext(onid, tsid, stream, connection, false, mySqlTransaction);
                InsertPmtStreamsTeletext(onid, tsid, stream, connection, true, mySqlTransaction);
                InsertPmtStreamsVbi(onid, tsid, stream, mySqlTransaction);
            }
        }

        private void InsertPmtStreamsVbi(int onid, int tsid, ProgramMappingStream stream, MySqlTransaction transaction)
        {
            if (stream.VbiData == null)
                return;
            if (stream.VbiData.Count == 0)
                return;

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_pmt_streams_vbi (onid, tsid, elementary_pid, field_parity, line_offset, data_service_id) " +
                "VALUES (@onid, @tsid, @elementary_pid, @field_parity, @line_offset, @data_service_id)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", stream.ElementaryPid);
            command.Parameters.Add("@field_parity", MySqlDbType.Bool);
            command.Parameters.Add("@line_offset", MySqlDbType.Int32);
            command.Parameters.Add("@data_service_id", MySqlDbType.Int16);
            foreach (VbiDataDescriptor.DataService dataService in stream.VbiData)
            {
                command.Parameters["@data_service_id"].Value = dataService.DataServiceId;
                for (int i = 0; i < dataService.FieldParity.Length; i++)
                {
                    if (!TestPmtStreamVbi(transaction, onid, tsid, stream.ElementaryPid, dataService.FieldParity[i], dataService.LineOffset[i]))
                    {
                        command.Parameters["@field_parity"].Value = dataService.FieldParity[i];
                        command.Parameters["@line_offset"].Value = dataService.LineOffset[i];
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        private bool TestPmtStreamVbi(MySqlTransaction transaction, int onid, int tsid, int streamElementaryPid, bool fieldParity, int lineOffset)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_pmt_streams_vbi WHERE onid = @onid AND tsid = @tsid AND elementary_pid = @elementary_pid AND field_parity = @field_parity AND line_offset = @line_offset";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", streamElementaryPid);
            command.Parameters.AddWithValue("@field_parity", fieldParity);
            command.Parameters.AddWithValue("@line_offset", lineOffset);
            MySqlDataReader dataReader = command.ExecuteReader();
            bool result = dataReader.Read();
            dataReader.Close();
            return result;
        }

        private void InsertPmtStreamsTeletext(int onid, int tsid, ProgramMappingStream stream, MySqlConnection connection, bool vbi, MySqlTransaction transaction)
        {
            TeletextDescriptor.TeletextDescriptorTeletext[] teletexts = vbi ? stream.VbiTeletexts : stream.Teletexts;
            if (teletexts == null)
                return;

            if (teletexts.Length == 0)
                return;
            
            MySqlCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_pmt_streams_teletext (onid, tsid, elementary_pid, ordinal, iso_639_language_code, teletext_type, teletext_magazine_number, teletext_page_number, vbi) " +
                "VALUES " +
                "(@onid, @tsid, @elementary_pid, @ordinal, @iso_639_language_code, @teletext_type, @teletext_magazine_number, @teletext_page_number, @vbi)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", stream.ElementaryPid);
            command.Parameters.AddWithValue("@vbi", vbi);
            command.Parameters.Add("@ordinal", MySqlDbType.Int32);
            command.Parameters.Add("@iso_639_language_code", MySqlDbType.VarChar);
            command.Parameters.Add("@teletext_type", MySqlDbType.Int32);
            command.Parameters.Add("@teletext_magazine_number", MySqlDbType.Int32);
            command.Parameters.Add("@teletext_page_number", MySqlDbType.Int32);
            for (int i = 0; i < teletexts.Length; i++)
            {
                if (TestForPmtTeletext(onid, tsid, stream.ElementaryPid, i, transaction))
                {
                    continue;
                }
                else
                {
                    TeletextDescriptor.TeletextDescriptorTeletext teletext = teletexts[i];
                    command.Parameters["@ordinal"].Value = i;
                    command.Parameters["@iso_639_language_code"].Value = teletext.Iso639LanguageCode;
                    command.Parameters["@teletext_type"].Value = (int)teletext.TeletextType;
                    command.Parameters["@teletext_magazine_number"].Value = teletext.TeletextMagazineNumber;
                    command.Parameters["@teletext_page_number"].Value = teletext.TeletextPageNumber;
                    command.ExecuteNonQuery();
                }
            }
        }

        private bool TestForPmtTeletext(int onid, int tsid, int streamElementaryPid, int i, MySqlTransaction transaction)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_pmt_streams_teletext WHERE onid = @onid AND tsid = @tsid AND elementary_pid = @elementary_pid AND ordinal = @ordinal";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", streamElementaryPid);
            command.Parameters.AddWithValue("@ordinal", i);
            MySqlDataReader dataReader = command.ExecuteReader();
            bool result = dataReader.Read();
            dataReader.Close();
            return result;
        }

        private void InsertPmtStreamsSubtitle(int onid, int tsid, ProgramMappingStream stream, MySqlConnection connection, MySqlTransaction mySqlTransaction)
        {
            if (stream.Subtitlings == null)
                return;

            if (stream.Subtitlings.Length == 0)
                return;

            MySqlCommand command = connection.CreateCommand();
            command.Transaction = mySqlTransaction;
            command.CommandText = "INSERT INTO dvb_pmt_streams_subtitle (onid, tsid, elementary_pid, ordinal, iso_639_language_type, subtitling_type, composition_page_id, ancillary_page_id) " +
                                  "VALUES " +
                                  "(@onid, @tsid, @elementary_pid, @ordinal, @iso_639_language_type, @subtitling_type, @composition_page_id, @ancillary_page_id)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", stream.ElementaryPid);
            for (int i = 0; i < stream.Subtitlings.Length; i++)
            {
                if (TestForPmtStreamSubtitle(onid, tsid, stream.ElementaryPid, i, mySqlTransaction))
                    continue;
                command.Parameters.AddOrSet("@ordinal", i);
                command.Parameters.AddOrSet("@iso_639_language_type", stream.Subtitlings[i].Iso639LanguageCode);
                command.Parameters.AddOrSet("@subtitling_type", stream.Subtitlings[i].SubtitlingType);
                command.Parameters.AddOrSet("@composition_page_id", stream.Subtitlings[i].CompositionPageId);
                command.Parameters.AddOrSet("@ancillary_page_id", stream.Subtitlings[i].AncillaryPageId);
                command.ExecuteNonQuery();
            }
        }

        private bool TestForPmtStreamSubtitle(int onid, int tsid, int pid, int ordinal, MySqlTransaction transaction)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_pmt_streams_subtitle WHERE onid = @onid AND tsid = @tsid AND elementary_pid = @elementary_pid AND ordinal = @ordinal";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", pid);
            command.Parameters.AddOrSet("@ordinal", ordinal);
            MySqlDataReader dataReader = command.ExecuteReader();
            bool result = dataReader.Read();
            dataReader.Close();
            return result;
        }

        private void InsertPmtStreamsIp(int onid, int tsid, ProgramMappingStream stream, MySqlConnection connection)
        {
            if (stream.IpMacNotificationInfo == null)
                return;

            if (stream.IpMacNotificationInfo.Length == 0)
                return;

            MySqlCommand command = connection.CreateCommand();
            command.CommandText =
                "INSERT INTO dvb_pmt_streams_ip (onid, tsid, elementary_pid, platform_id, action_type, int_versioning_flag, int_version) " +
                "VALUES " +
                "(@onid, @tsid, @elementary_pid, @platform_id, @action_type, @int_versioning_flag, @int_version)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", stream.ElementaryPid);
            foreach (DataBroadcastIdDescriptor.IpMacPlatform platform in stream.IpMacNotificationInfo)
            {
                command.Parameters.AddWithValue("@platform_id", platform.PlatformId);
                command.Parameters.AddWithValue("@action_type", platform.ActionType);
                command.Parameters.AddWithValue("@int_versioning_flag", platform.IntVersioningFlag);
                command.Parameters.AddWithValue("@int_version", platform.IntVersion);
                command.ExecuteNonQuery();
            }
        }

        private void InsertPmtStreamsFlexmux(int onid, int tsid, ProgramMappingStream stream, MySqlConnection connection)
        {
            if (stream.FlexMuxChannels == null)
                return;

            if (stream.FlexMuxChannels.Length == 0)
                return;

            MySqlCommand command = connection.CreateCommand();
            command.CommandText =
                "INSERT INTO dvb_pmt_streams_flexmux (nid, tsid, elementary_pid, es_id, flexmux_channel) VALUES (@nid,@tsid,@elementary_pid,@es_id,@flexmux_channel)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", stream.ElementaryPid);
            command.Parameters.Add("@es_id", MySqlDbType.Int32);
            command.Parameters.Add("@flexmux_channel", MySqlDbType.Int16);
            foreach (FmcDescriptor.Fmc flexMuxChannel in stream.FlexMuxChannels)
            {
                command.Parameters["@es_id"].Value = flexMuxChannel.EsId;
                command.Parameters["@flexmux_channel"].Value = flexMuxChannel.FlexMuxChannel;
                command.ExecuteNonQuery();
            }
        }

        private void InsertPmtStreamsAudioPreselection(int onid, int tsid, ProgramMappingStream stream, MySqlTransaction transaction)
        {
            if (stream.AudioPreselection == null)
                return;

            if (stream.AudioPreselection.Length == 0)
                return;

            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_pmt_streams_audio_preselection (onid, tsid, elementary_pid, preselection_id, audio_rendering_indication, audio_description, spoken_subtitles, dialogue_enhancement, interactivity_enabled, iso_639_language_code, message_id, component_tags, future_extension) " +
                "VALUES " +
                "(@onid, @tsid, @elementary_pid, @preselection_id, @audio_rendering_indication, @audio_description, @spoken_subtitles, @dialogue_enhancement, @interactivity_enabled, " +
                "@iso_639_language_code, @message_id, @component_tags, @future_extension)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", stream.ElementaryPid);
            foreach (AudioPreselectionDescriptor.AudioPreselection audioPreselection in stream.AudioPreselection)
            {
                command.Parameters.AddOrSet("@preselection_id", audioPreselection.PreselectionId);
                command.Parameters.AddOrSet("@audio_rendering_indication", audioPreselection.AudioRenderingIndication);
                command.Parameters.AddOrSet("@audio_description", audioPreselection.AudioDescription);
                command.Parameters.AddOrSet("@spoken_subtitles", audioPreselection.SpokenSubtitles);
                command.Parameters.AddOrSet("@dialogue_enhancement", audioPreselection.DialogueEnhancement);
                command.Parameters.AddOrSet("@interactivity_enabled", audioPreselection.InteractivityEnabled);
                command.Parameters.AddOrSet("@iso_639_language_code", audioPreselection.Iso639LangaugeCode);
                command.Parameters.AddOrSet("@message_id", audioPreselection.MessageId);
                command.Parameters.AddOrSet("@component_tags", audioPreselection.ComponentTags);
                command.Parameters.AddOrSet("@future_extension", audioPreselection.FutureExtension);
                command.ExecuteNonQuery();
            }
        }

        private void InsertPmtStreamsApplications(int onid, int tsid, ProgramMappingStream stream, MySqlConnection connection, MySqlTransaction transaction)
        {
            if (stream.Applications == null)
                return;
            if (stream.Applications.Count == 0)
                return;

            MySqlCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "INSERT INTO dvb_pmt_streams_applications (onid, tsid, elementary_pid, application_type, ait_version_number) " +
                "VALUES (@onid,@tsid,@elementary_pid,@application_type,@ait_version_number)";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", stream.ElementaryPid);
            foreach (ApplicationSignallingDescriptor.ApplicationSignal applicationSignal in stream.Applications)
            {
                if (TestForPmtStreamApplication(onid, tsid, stream.ElementaryPid, transaction, applicationSignal))
                    continue;
                command.Parameters.AddOrSet("@application_type", applicationSignal.ApplicationType);
                command.Parameters.AddOrSet("@ait_version_number", applicationSignal.AitVersionNumber);
                command.ExecuteNonQuery();
            }
        }

        private bool TestForPmtStreamApplication(int onid, int tsid, int pid, MySqlTransaction transaction, ApplicationSignallingDescriptor.ApplicationSignal signal)
        {
            MySqlCommand command = transaction.Connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText =
                "SELECT dateadded FROM dvb_pmt_streams_applications WHERE onid = @onid AND tsid = @tsid AND elementary_pid = @elementary_pid " +
                "AND application_type = @application_type AND ait_version_number = @ait_version_number";
            command.Parameters.AddWithValue("@onid", onid);
            command.Parameters.AddWithValue("@tsid", tsid);
            command.Parameters.AddWithValue("@elementary_pid", pid);
            command.Parameters.AddOrSet("@application_type", signal.ApplicationType);
            command.Parameters.AddOrSet("@ait_version_number", signal.AitVersionNumber);
            MySqlDataReader dataReader = command.ExecuteReader();
            bool result = dataReader.Read();
            dataReader.Close();
            return result;
        }

        private struct PmtCoordinate
        {
            public int nid, tsid;
            public ushort programNumber;

            public PmtCoordinate(int nid, int tsid, ushort programNumber)
            {
                this.nid = nid;
                this.tsid = tsid;
                this.programNumber = programNumber;
            }

            public bool Equals(PmtCoordinate other)
            {
                return nid == other.nid && tsid == other.tsid && programNumber == other.programNumber;
            }

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

            public override int GetHashCode()
            {
                return HashCode.Combine(nid, tsid, programNumber);
            }
        }

        private HashSet<PmtCoordinate> knownPmts;

		public bool TestForPmtEvent(int currentNetworkId, int currentTransportStreamId, ProgramMapping mapping)
		{
			if (knownPmts == null)
				knownPmts = new HashSet<PmtCoordinate>();

			PmtCoordinate coordinate = new PmtCoordinate(currentNetworkId, currentTransportStreamId, mapping.ProgramNumber);
			if (knownPmts.Contains(coordinate))
				return true;

			using (MySqlConnection connection = new MySqlConnection(_mcsb.ToString()))
			{
				connection.Open();
				MySqlCommand mySqlCommand = connection.CreateCommand();
				mySqlCommand.CommandText =
					"SELECT dateadded FROM dvb_pmt WHERE nid = @nid AND tsid = @tsid AND program_number = @program_number";
				mySqlCommand.Parameters.Add("@nid", MySqlDbType.Int32);
				mySqlCommand.Parameters.Add("@tsid", MySqlDbType.Int32);
				mySqlCommand.Parameters.Add("@program_number", MySqlDbType.Int32);
				mySqlCommand.Parameters["@nid"].Value = currentNetworkId;
				mySqlCommand.Parameters["@tsid"].Value = currentTransportStreamId;
				mySqlCommand.Parameters["@program_number"].Value = mapping.ProgramNumber;
				MySqlDataReader dataReader = mySqlCommand.ExecuteReader();
				bool result = dataReader.Read();
				dataReader.Close();
				connection.Close();

				if (result)
				{
					knownPmts.Add(coordinate);
				}

				return result;
			}
		}

		public bool StorePmtEvent(int currentNetworkId, int currentTransportStreamId, ProgramMapping mapping)
		{
			if (knownPmts == null)
				knownPmts = new HashSet<PmtCoordinate>();
			knownPmts.Add(new PmtCoordinate(currentNetworkId, currentTransportStreamId, mapping.ProgramNumber));

			EnqueueSpeedhack(SpeedhackType.InsertPmt, currentNetworkId, currentTransportStreamId, mapping);
			return true;
		}

	}
}
