//#define SQLLITE_CONNECTION_TEST
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Diagnostics;
using Enterprise.Logging;
using libxmltv;
using libxmltv.Interfaces;
using libxmltv.Core;
namespace GBPVRProgramDatabaseFixer
{
    public interface IOldNewProgram
    {
        SQLLITE.IPROGRAMME OldProgram { get; }
        SQLLITE.IPROGRAMME NewProgram { get; }
    }
    public interface ISQLLITE
    {
        List<SQLLITE.IPROGRAMME> Programs { get; }
        List<SQLLITE.IRECORDING_SCHEDULE> Recordings { get; }
        List<SQLLITE.ICHANNEL> Channels { get; }
        IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs);
        List<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> removed_programs);

        void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);
        void UpdateGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs);

    }
    public class SQLLITE : ISQLLITE
    {
        private class OldNewProgram : IOldNewProgram
        {
            public OldNewProgram() : this(new PROGRAMME(), new PROGRAMME()) { }
            public OldNewProgram(IPROGRAMME _old, IPROGRAMME _new) { OldProgram = _old; NewProgram = _new; }

            public IPROGRAMME OldProgram { get; private set; }
            public IPROGRAMME NewProgram { get; private set; }
        }
        public static ISQLLITE Create(string database, EventHandler<EventArgs> OnInstanceCreated)
        {
            return new SQLLITE(database, OnInstanceCreated);
        }

        #region DATABASE DEFINITIONS

        public interface ICHANNEL
        {
            Int64 oid { get; }
            String name { get; }
            String channelID { get; }
            Int64 channel_number { get; }
            String favourite_channel { get; }
            String display_name { get; }
        }
        private class CHANNEL : ICHANNEL
        {
            public CHANNEL()
            {
                BaseDatabaseDefinition<CHANNEL>.CreateDefault(this);
            }
            //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }

            public static void Create(ref CHANNEL instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<CHANNEL>.Create(ref instance, r, index);
            }
            #region ICHANNEL members
            public Int64 oid { get; set; }
            public String name { get; set; }
            public String channelID { get; set; }
            public Int64 channel_number { get; set; }
            public String favourite_channel { get; set; }
            public String display_name { get; set; }
            #endregion
        }
        public interface IRECORDING_SCHEDULE
        {
            Int64 oid { get; }
            Int64 programme_oid { get; }
            Int64 capture_source_oid { get; }
            Int16 status { get; }
            String filename { get; }
            Int64 recording_type { get; }
            Int64 recording_group { get; }
            DateTime manual_start_time { get; }
            DateTime manual_end_time { get; }
            Int64 manual_channel_oid { get; }
            Int64 quality_level { get; }
            Int64 pre_pad_minutes { get; }
            Int64 post_pad_minutes { get; }
            Int32 priority { get; }
            String conversion_profile { get; }
        }

        private static class BaseDatabaseDefinition<T>
        {
            public static void CreateDefault(T instance)
            {
                try
                {
                    Type t = typeof(T);
                    var props = t.GetProperties();
                    foreach (var prop in props)
                    {
                        Type prop_type = prop.PropertyType;
                        object field_value = null;
                        try
                        {
                            if (prop_type == typeof(string))
                            {
                                field_value = string.Empty;
                            }
                            else
                            {
                                field_value = Activator.CreateInstance(prop_type);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        prop.SetValue(instance, field_value, null);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            public static void Create(ref T instance, SQLiteDataReader r, int index)
            {
                string field_name = r.GetName(index);
                Type field_type = r.GetFieldType(index);
                object field_value = r.GetValue(index);
                //gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());

                Type t = typeof(T);
                var props = t.GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.ToLower() == field_name.ToLower())
                    {
                        if (prop.PropertyType == field_type)
                        {
                            Type db_type = field_value.GetType();
                            try
                            {
                                if (db_type == typeof(System.DBNull))
                                {
                                    prop.SetValue(instance, null, null);
                                }
                                else
                                {
                                    prop.SetValue(instance, field_value, null);
                                }

                            }
                            catch (Exception ex)
                            {
                                throw ex;
                            }
                        }
                        else
                        {
                            gLog.Verbose.Debug.WriteLine("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name);
                            throw new InvalidOperationException(string.Format("Found Property: {0} but there was a type mismatch. Property Type: '{1}' Expected: '{2}'", prop.Name, prop.PropertyType.Name, field_type.Name));
                        }
                    }
                }

            }
        }

        private class RECORDING_SCHEDULE : IRECORDING_SCHEDULE
        {
            public RECORDING_SCHEDULE()
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.CreateDefault(this);
            }
            //public RECORDING_SCHEDULE(SQLiteDataReader r, int index) { BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(this, r, index); }

            public static void Create(ref RECORDING_SCHEDULE instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<RECORDING_SCHEDULE>.Create(ref instance, r, index);
            }

            #region IRECORDING_SCHEDULE members
            public Int64 oid { get; set; }
            public Int64 programme_oid { get; set; }
            public Int64 capture_source_oid { get; set; }
            public Int16 status { get; set; }
            public String filename { get; set; }
            public Int64 recording_type { get; set; }
            public Int64 recording_group { get; set; }
            public DateTime manual_start_time { get; set; }
            public DateTime manual_end_time { get; set; }
            public Int64 manual_channel_oid { get; set; }
            public Int64 quality_level { get; set; }
            public Int64 pre_pad_minutes { get; set; }
            public Int64 post_pad_minutes { get; set; }
            public Int32 priority { get; set; }
            public String conversion_profile { get; set; }
            #endregion
        }

        public interface IPROGRAMME : IEquatable<IPROGRAMME>
        {
            Int64 oid { get; set; }
            String name { get; set; }
            String sub_title { get; set; }
            String description { get; set; }
            DateTime start_time { get; set; }
            DateTime end_time { get; set; }
            Int64 channel_oid { get; set; }
            String unique_identifier { get; set; }
            String rating { get; set; }

            string ToString();
            IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite);
        }
        private class PROGRAMME : IPROGRAMME
        {
            public PROGRAMME()
            {
                BaseDatabaseDefinition<PROGRAMME>.CreateDefault(this);
            }
            //public PROGRAMME(SQLiteDataReader r, int index) : base(r, index) { }
            public static void Create(ref PROGRAMME instance, SQLiteDataReader r, int index)
            {
                BaseDatabaseDefinition<PROGRAMME>.Create(ref instance, r, index);
            }
            #region IPROGRAMME members
            public Int64 oid { get; set; }
            public String name { get; set; }
            public String sub_title { get; set; }
            public String description { get; set; }
            public DateTime start_time { get; set; }
            public DateTime end_time { get; set; }
            public Int64 channel_oid { get; set; }
            public String unique_identifier { get; set; }
            public String rating { get; set; }
            public IProgramDefinition AsXMLTVProgramDefinition(ISQLLITE sqllite)
            {
                ProgramList.ProgramDefintion definition = new ProgramList.ProgramDefintion();

                var channel = sqllite.Channels.Find(s => s.oid == this.channel_oid);
                var channelname = channel.display_name;
                var split = channelname.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                definition.ChannelNumber = Convert.ToInt32(split.First());
                definition.ChannelName = string.IsNullOrEmpty(split.Last()) ? string.Empty : split.Last().ToString();
                definition.Description = this.description;
                definition.Start = this.start_time.ToDateTimeString();
                definition.Stop = this.end_time.ToDateTimeString();
                definition.SubTitle = this.sub_title;
                definition.Title = this.name;
                return definition;
            }
            #endregion

            public bool Equals(IPROGRAMME other)
            {
                return this.ToString() == other.ToString();
            }
            public override string ToString()
            {
                StringBuilder builder = new StringBuilder();

                builder.AppendFormat("oid: '{0}' ", oid);
                builder.AppendFormat("name: '{0}' ", name);
                builder.AppendFormat("sub_title: '{0}' ", sub_title);
                builder.AppendFormat("description: '{0}' ", description);
                builder.AppendFormat("start_time: '{0}' ", start_time.ToDateTimeString());
                builder.AppendFormat("end_time: '{0}' ", end_time.ToDateTimeString());
                builder.AppendFormat("channel_oid: '{0}' ", channel_oid);
                builder.AppendFormat("unique_identifier: '{0}' ", unique_identifier);
                builder.AppendFormat("rating: '{0}'", rating);

                return builder.ToString();
            }
            public override bool Equals(object obj)
            {
                if (obj == null) { throw new ArgumentNullException("obj", "Object to compare cannot be null"); }
                if (obj.GetType().IsAssignableFrom(typeof(IPROGRAMME)))
                {
                    return this.Equals((IPROGRAMME)obj);
                }
                return base.Equals(obj);
            }
            public override int GetHashCode()
            {
                return this.ToString().GetHashCode();
            }
        }
        #endregion
        private static class TABLES
        {
            public const string RECORDING_SCHEDULE = "RECORDING_SCHEDULE";
            public const string PROGRAMME = "PROGRAMME";
            public const string CHANNEL = "CHANNEL";
        }

        //public SQLLite() { }
        protected SQLLITE(string database, EventHandler<EventArgs> OnInstanceCreated)
        {
            this.OnInstanceCreated = OnInstanceCreated;
            //CreateConnection(database);
            this.Database = database;
            if (!CreateDatabaseBackup())
            {
                gLog.Error.WriteLine("Failed to backup database.");
                return;
            }
            ConnectionTest();
            ReadChannelData();
            ReadRecodringScheduleData();
            ReadProgrammeData();
            OnCreatedInstance(this, new EventArgs());
        }

        [NonSerialized]
        private EventHandler<EventArgs> _OnInstanceCreated;
        private EventHandler<EventArgs> OnInstanceCreated { get { return _OnInstanceCreated; } set { _OnInstanceCreated = value; } }

        private void OnCreatedInstance(object sender, EventArgs e)
        {
            if (OnInstanceCreated != null) { OnInstanceCreated.Invoke(sender, e); }
        }

        private string Database;
        #region ISQLLITE members
        public List<IPROGRAMME> Programs { get; private set; }
        public List<IRECORDING_SCHEDULE> Recordings { get; private set; }
        public List<ICHANNEL> Channels { get; private set; }

        public IDateTimeRange GetProgramsDateRange(List<SQLLITE.IPROGRAMME> programs)
        {
            var list = new List<IPROGRAMME>(programs.ToArray());
            DateTime first = new DateTime();
            DateTime last = new DateTime();
            first = list.OrderBy(s => s.start_time).ToList().First().start_time;
            last = list.OrderBy(s => s.start_time).ToList().Last().start_time;
            gLog.Verbose.Debug.WriteLine("\tFirst: {0} = ({1})", first.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), first.ToDateTimeString());
            gLog.Verbose.Debug.WriteLine("\tLast: {0} = ({1})", last.ToString("yyyy/MM/dd HH:mm:ss.fffffff"), last.ToDateTimeString());
            var range = DateTimeRange.Create(first, last);
            return range;
        }

        public List<SQLLITE.IPROGRAMME> FixGBPVRProgramsDatabase(List<SQLLITE.IPROGRAMME> gbpvr_programs, List<libxmltv.Interfaces.IProgramDefinition> xmltv_programs, out List<SQLLITE.IPROGRAMME> source_invalid)
        {
            source_invalid = new List<IPROGRAMME>();
            List<IPROGRAMME> source_valid = new List<IPROGRAMME>();
            gbpvr_programs = gbpvr_programs.OrderBy(s => s.start_time).ToList();
            xmltv_programs = xmltv_programs.OrderBy(s => DateTime.Parse(s.Start)).ToList();
            gbpvr_programs.TrimExcess();
            xmltv_programs.TrimExcess();

            double total = gbpvr_programs.Count;
            double index = 0;
            double progress = 0;
            foreach (var program in gbpvr_programs)
            {
                progress = 100.0 * (index / total);
                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));
                // find an entry in xmltv_programs by Channel Number & StartDate                
                var program_xmltv_entry = program.AsXMLTVProgramDefinition(this);
                var xmltv_entry = xmltv_programs.Find(s => s.ChannelNumber == program_xmltv_entry.ChannelNumber && s.Start == program_xmltv_entry.Start);
                if (xmltv_entry == null)
                {
                    // xmltv entry was not found
                    source_invalid.Add(program);
                }
                else
                {
                    // check if the xmltv entry has different data from the current program
                    if (!xmltv_entry.Equals(program_xmltv_entry))
                    {
                        // data is different
                        var updated_program = program;

                        if (program_xmltv_entry.Title != xmltv_entry.Title)
                        {
                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing title", updated_program.oid);
                            updated_program.name = xmltv_entry.Title;
                        }
                        if (program_xmltv_entry.SubTitle != xmltv_entry.SubTitle)
                        {
                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing sub_title", updated_program.oid);
                            updated_program.sub_title = xmltv_entry.SubTitle;
                        }
                        if (program_xmltv_entry.Stop != xmltv_entry.Stop)
                        {
                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing end_time", updated_program.oid);
                            updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
                        }
                        if (program_xmltv_entry.Start != xmltv_entry.Start)
                        {
                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing start_time", updated_program.oid);
                            updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
                        }
                        if (program_xmltv_entry.Description != xmltv_entry.Description)
                        {
                            gLog.Verbose.Warn.WriteLine("    Program oid: '{0}' - replacing description", updated_program.oid);
                            updated_program.description = xmltv_entry.Description;
                        }

                        var updated_xmltv_entry = updated_program.AsXMLTVProgramDefinition(this);
                        if (!xmltv_entry.Equals(updated_xmltv_entry))
                        {
                            throw new Exception(string.Format("Program oid '{0}' was not properly updated.", updated_program.oid));
                        }
                        //source_valid.Add(new OldNewProgram(program, updated_program));
                        source_valid.Add(updated_program);
                    }
                    else
                    {
                        // data is the same
                        //source_valid.Add(new OldNewProgram(program, program));
                        source_valid.Add(program);
                    }
                }
                index++;
            }
            gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
            gLog.Info.WriteLine("Total Valid GB-PVR Programs: 0x{0:x8}", source_valid.Count);
            gLog.Info.WriteLine("Total Invalid GB-PVR Programs: 0x{0:x8}", source_invalid.Count);
            gLog.Info.WriteLine("Total GB-PVR Programs (Valid+Invalid): 0x{0:x8} == 0x{1:x8}", source_valid.Count + source_invalid.Count, gbpvr_programs.Count);
            #region old-code
            //List<OldNewProgram> source_update = new List<OldNewProgram>();
            ////if (range == null)
            ////{
            ////    gLog.Warn.WriteLine("The DateTimeRange passed in is null...returning the original program list");
            ////    return list;
            ////}
            ////gLog.Warn.WriteLine("FilterProgramsByDateRange has not been implemented");            
            //double total = gbpvr_programs.Count;
            //double index = 0;
            //double progress = 0;
            //foreach (var program in gbpvr_programs)
            //{  
            //    progress = 100.0 * (index / total);
            //    gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Filtering GBPVR Programs: {0:00}%", (int)progress)));                
            //    var channel_oid = program.channel_oid;
            //    var channel = this.Channels.Find(s => s.oid == channel_oid);
            //    var start_date = program.start_time;
            //    //var xmltv_entry_list = xmltv_programs.FindAll(s => s.ChannelNumber == channel.channel_number && s.Start == start_date.ToDateTimeString());
            //    var query = from c in xmltv_programs 
            //                where 
            //                c.ChannelNumber == channel.channel_number &&
            //                c.Start == start_date.ToDateTimeString()
            //                select c;
            //    IProgramDefinition xmltv_entry = null;
            //    if (query.Count()-1 > 0)
            //    {
            //        gLog.Verbose.Error.WriteLine("Found more than one entry: Matching channel='{0}' and start='{1}'", channel.channel_number, start_date.ToDateTimeString());
            //        gLog.Verbose.Error.WriteLine("    GB-PVR Program Data: oid='{0}' channel_oid='{1}' name='{2}' sub_title='{3}' description='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.description);
            //        if (Debugger.IsAttached)
            //        {                      
            //            gLog.Error.WriteLine("    Found: {0} matching entries", query.Count());
            //            int k_index = 0;
            //            foreach (var k in query)
            //            {
            //                gLog.Verbose.Error.WriteLine("    query[{0}]: channel='{1}' start='{2}' ('{3}') title='{4}' subtitle='{5}' description='{6}'", k_index, k.ChannelNumber, k.Start, DateTime.Parse(k.Start).ToString("yyyy/MM/dd HH:mm:ss.fffffff"), k.Title, k.SubTitle, k.Description);
            //                k_index++;
            //            }
            //            Debugger.Break();
            //        }
            //    }
            //    else
            //    {
            //        xmltv_entry = query.FirstOrDefault();
            //    }
            //    if (xmltv_entry == null)
            //    {
            //        //gLog.Verbose.Warn.WriteLine("Invalidating GB-PVR Program: oid='{0}' channel_oid='{1}' title='{2}' start='{3}'", program.oid, program.channel_oid, program.name, program.start_time.ToDateTimeString());
            //        source_invalid.Add(program);                   
            //    }
            //    else
            //    {
            //        //gLog.Verbose.Warn.WriteLine("Updating GB-PVR Program (if needed): oid='{0}' channel_oid='{1}' title='{2}' subtitle='{3}' start='{4}'", program.oid, program.channel_oid, program.name, program.sub_title, program.start_time.ToDateTimeString());
            //        var updated_program = program;
            //        bool needsupdate = false;
            //        if (xmltv_entry.Title != program.name)
            //        {
            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
            //            //gLog.Verbose.Warn.WriteLine("        Old Title: {0}", program.name);
            //            //gLog.Verbose.Warn.WriteLine("        New Title: {0}", xmltv_entry.Title);
            //            updated_program.name = xmltv_entry.Title;
            //            needsupdate = true;
            //        }
            //        if (xmltv_entry.SubTitle != program.sub_title)
            //        {
            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
            //            //gLog.Verbose.Warn.WriteLine("        Old SubTile: {0}", program.sub_title);
            //            //gLog.Verbose.Warn.WriteLine("        New SubTile: {0}", xmltv_entry.SubTitle);
            //            updated_program.sub_title = xmltv_entry.SubTitle;
            //            needsupdate = true;
            //        }
            //        if (xmltv_entry.Description != program.description)
            //        {
            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
            //            //gLog.Verbose.Warn.WriteLine("        Old Descption: {0}", program.description);
            //            //gLog.Verbose.Warn.WriteLine("        New Descption: {0}", xmltv_entry.Description);
            //            updated_program.description = xmltv_entry.Description;
            //            needsupdate = true;
            //        }
            //        if (DateTime.Parse(xmltv_entry.Start) != program.start_time)
            //        {
            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
            //            //gLog.Verbose.Warn.WriteLine("        Old StartTime: {0}", program.start_time.ToDateTimeString());
            //            //gLog.Verbose.Warn.WriteLine("        New StartTime: {0}", DateTime.Parse(xmltv_entry.Start).ToDateTimeString());
            //            updated_program.start_time = DateTime.Parse(xmltv_entry.Start);
            //            needsupdate = true;
            //        }
            //        if (DateTime.Parse(xmltv_entry.Stop) != program.end_time)
            //        {
            //            //gLog.Verbose.Warn.WriteLine("    Updating:");
            //            //gLog.Verbose.Warn.WriteLine("        Old EndTime: {0}", program.end_time.ToDateTimeString());
            //            //gLog.Verbose.Warn.WriteLine("        New EndTime: {0}", DateTime.Parse(xmltv_entry.Stop).ToDateTimeString());
            //            updated_program.end_time = DateTime.Parse(xmltv_entry.Stop);
            //            needsupdate = true;
            //        }                    
            //        if (needsupdate)
            //        {
            //            OldNewProgram p = new OldNewProgram();
            //            p.OldProgram = program;
            //            p.NewProgram = updated_program;
            //            source_update.Add(p);
            //        }
            //        source_valid.Add(updated_program);
            //    }
            //    index++;
            //}
            //source_valid = source_valid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
            //source_invalid = source_invalid.OrderBy(s => s.channel_oid).ThenBy(s => s.start_time).ToList();
            //if (source_invalid.Count > 0)
            //{
            //    double source_invalid_count = source_valid.Count;
            //    double source_invalid_index = 0;
            //    double source_invalid_progress = 0;
            //    foreach (var old_program in source_invalid)
            //    {
            //        source_invalid_progress = 100.0 * (source_invalid_index / source_invalid_count);
            //        gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_invalid_progress, string.Format("Removing old GBPVR Programs: {0:00}%", (int)source_invalid_progress)));    
            //        // remove database entry
            //        if (!RemoveProgramDatabaseEntry(old_program))
            //        {
            //            gLog.Error.WriteLine("Failed to remove program with oid: '{0}'", old_program.oid);
            //        }
            //        else
            //        {
            //            gLog.Verbose.Info.WriteLine("Removed program with oid: '{0}'", old_program.oid);
            //        }
            //        source_invalid_index++;
            //    }
            //}
            //else
            //{
            //    gLog.Info.WriteLine("No old GB-PVR Programs needed to be removed.");
            //}
            //if (source_update.Count > 0)
            //{
            //    double source_update_count = source_valid.Count;
            //    double source_update_index = 0;
            //    double source_update_progress = 0;
            //    foreach (var p in source_update)
            //    {
            //        source_update_progress = 100.0 * (source_update_index / source_update_count);
            //        gLog.ReportProgress(this, new ReportProgressEventArgs((int)source_update_progress, string.Format("Updating GBPVR Programs: {0:00}%", (int)source_update_progress)));    
            //        // remove database entry
            //        if (!UpdateProgramDatabaseEntry(p.OldProgram,p.NewProgram))
            //        {
            //            gLog.Error.WriteLine("Failed to update program with oid: '{0}'", p.OldProgram.oid);
            //        }
            //        else
            //        {
            //            gLog.Verbose.Info.WriteLine("Upated program with oid: '{0}'", p.OldProgram.oid);
            //        }
            //        source_update_index++;
            //    }
            //}
            //else
            //{
            //    gLog.Info.WriteLine("No GB-PVR Programs needed to be updated.");
            //}
            //gLog.Info.WriteLine("Total XMLTV Programs: 0x{0:x8}", xmltv_programs.Count);
            //gLog.Info.WriteLine("Updated: 0x{0:x8} GB-PVR Programs", source_valid.Count);
            //gLog.Info.WriteLine("Removed: 0x{0:x8} GB-PVR Programs", source_invalid.Count);
            //gLog.Info.WriteLine("Total GB-PVR Programs (Updated & Removed): 0x{0:x8}", source_valid.Count + source_invalid.Count);
            #endregion

            return source_valid;
        }
        #endregion


        private string CreateConnectionString()
        {
            string connection_string = string.Format("Data Source={0}", this.Database);
            return connection_string;
        }

        private SQLiteConnection CreateConnection() { SQLiteConnection connection = new SQLiteConnection(CreateConnectionString()); return connection; }

        [Conditional("SQLLITE_CONNECTION_TEST")]
        private void ConnectionTest()
        {
            try
            {
                using (SQLiteConnection con = CreateConnection())
                {
                    con.Open();
                    string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
                    gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                    using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                    {
                        using (SQLiteDataReader r = cmd.ExecuteReader())
                        {
                            if (!r.HasRows)
                            {
                                gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                            }
                            else
                            {
                                while (r.Read())
                                {
                                    for (int i = 0; i < r.FieldCount; i++)
                                    {
                                        string field_name = r.GetName(i);
                                        Type field_type = r.GetFieldType(i);
                                        object field_value = r.GetValue(i);
                                        gLog.Verbose.Debug.WriteLine("Name: '{0}' Type: '{1}' Value: '{2}'", field_name, field_type.Name, field_value == null ? "null" : field_value.ToString());
                                    }
                                    break;
                                }
                            }
                        }
                    }
                    con.Clone();
                }
                OnCreatedInstance(this, new EventArgs());
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }


        private bool CreateDatabaseBackup()
        {
            try
            {
                string backup_file = string.Format("{0}.{1}", this.Database, DateTime.Now.ToString("yyyyMMddHHmmss"));
                gLog.Info.WriteLine("Creating Database backup...");
                gLog.Info.WriteLine("\tSource: {0}", this.Database);
                gLog.Info.WriteLine("\tDestination: {0}", backup_file);

                System.IO.File.Copy(this.Database, backup_file);
                return true;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
                return false;
            }
        }
        private void ReadChannelData()
        {
            try
            {
                List<ICHANNEL> channels = new List<ICHANNEL>();
                using (SQLiteConnection con = CreateConnection())
                {
                    try
                    {
                        con.Open();
                        string command_text = string.Format("select * from {0};", TABLES.CHANNEL);
                        gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                        {
                            using (SQLiteDataReader r = cmd.ExecuteReader())
                            {
                                if (!r.HasRows)
                                {
                                    gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                                }
                                else
                                {
                                    while (r.Read())
                                    {
                                        CHANNEL channel = new CHANNEL();
                                        for (int i = 0; i < r.FieldCount; i++)
                                        {
                                            CHANNEL.Create(ref channel, r, i);
                                        }
                                        channels.Add(channel);
                                    }
                                }
                            }
                        }
                    }
                    catch (SQLiteException ex)
                    {
                        gLog.Error.WriteLine(ex.ToString());
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                this.Channels = channels;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
        private void ReadProgrammeData()
        {
            try
            {
                List<IPROGRAMME> programs = new List<IPROGRAMME>();
                using (SQLiteConnection con = CreateConnection())
                {
                    try
                    {
                        con.Open();
                        string command_text = string.Format("select * from {0};", TABLES.PROGRAMME);
                        gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                        {
                            using (SQLiteDataReader r = cmd.ExecuteReader())
                            {
                                if (!r.HasRows)
                                {
                                    gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                                }
                                else
                                {
                                    while (r.Read())
                                    {
                                        PROGRAMME program = new PROGRAMME();
                                        for (int i = 0; i < r.FieldCount; i++)
                                        {
                                            PROGRAMME.Create(ref program, r, i);
                                        }
                                        programs.Add(program);
                                    }
                                }
                            }
                        }
                    }
                    catch (SQLiteException ex)
                    {
                        gLog.Error.WriteLine(ex.ToString());
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                this.Programs = programs;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }
        private void ReadRecodringScheduleData()
        {
            try
            {
                List<IRECORDING_SCHEDULE> recordings = new List<IRECORDING_SCHEDULE>();
                using (SQLiteConnection con = CreateConnection())
                {
                    try
                    {
                        con.Open();
                        string command_text = string.Format("select * from {0};", TABLES.RECORDING_SCHEDULE);
                        gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                        {
                            using (SQLiteDataReader r = cmd.ExecuteReader())
                            {
                                if (!r.HasRows)
                                {
                                    gLog.Warn.WriteLine("Query: '{0}' returned no rows.", cmd.CommandText);
                                }
                                else
                                {
                                    while (r.Read())
                                    {
                                        RECORDING_SCHEDULE recording = new RECORDING_SCHEDULE();
                                        for (int i = 0; i < r.FieldCount; i++)
                                        {
                                            RECORDING_SCHEDULE.Create(ref recording, r, i);
                                        }
                                        recordings.Add(recording);
                                    }
                                }
                            }
                        }
                    }
                    catch (SQLiteException ex)
                    {
                        gLog.Error.WriteLine(ex.ToString());
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                this.Recordings = recordings;
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
            }
        }

        public void RemoveOldGBPVRPrograms(List<SQLLITE.IPROGRAMME> programs)
        {            
            gLog.Info.WriteLine("Removing all gbpvr programs.");
            //double total = programs.Count;
            //double index = 0;
            //double progress = 0;
            //foreach (var program in programs)
            //{
            //    progress = 100.0 * (index / total);
            //    gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Removing GBPVR Programs: {0:00}%", (int)progress)));
            //    if (!RemoveProgramDatabaseEntry(program))
            //    {
            //        gLog.Error.WriteLine("Failed to remove program with oid: {0}", program.oid);
            //    }
            //    index++;
            //}
            Stopwatch st = new Stopwatch();
            st.Start();
            if (!RemoveAllProgramDatabaseEntries())
            {
                gLog.Error.WriteLine("Failed to remove one or more program(s)");
            }
            st.Stop();
            gLog.Warn.WriteLine("    operation took: {0:0.00000} minutes", st.Elapsed.TotalMinutes);
        }
        public void UpdateGBPVRPrograms(List<IPROGRAMME> programs)
        {
            int ChunkSize = 500;
            var ChunkList = programs.Chunk<IPROGRAMME>(ChunkSize).ToList();

            int total = programs.Count;
            int index = 0;
            double progress = 0;
            Stopwatch st = new Stopwatch();
            st.Start();
            foreach (var p in ChunkList)
            {
                progress = 100.0 * (index / total);
                //gLog.Info.WriteLine("Inserting {0} gbpvr programs.", p.Count);   
                if (!InsertProgramEntryDatabase(p.ToList()))
                {
                    gLog.Error.WriteLine("Failed to insert one or more program(s)");
                }
                gLog.ReportProgress(this, new ReportProgressEventArgs((int)progress, string.Format("Inserting GBPVR Programs: {0:00}%", (int)progress)));
                index += p.Count();
            }
            st.Stop();
            gLog.Warn.WriteLine("    operation took: {0:0.00000} minutes", st.Elapsed.TotalMinutes);
        }

        private bool RemoveAllProgramDatabaseEntries()
        {
            bool result = false;
            try
            {
                using (SQLiteConnection con = CreateConnection())
                {
                    try
                    {
                        //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
                        con.Open();
                        string command_text = string.Format(@"DELETE FROM [{0}];", TABLES.PROGRAMME);
                        //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                        {
                            //cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
                            int rowsupdated = cmd.ExecuteNonQuery();
                            //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
                        }
                        result = true;
                    }
                    catch (SQLiteException ex)
                    {
                        gLog.Error.WriteLine(ex.ToString());
                        result = false;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
                result = false;
            }
            return result;
        }
        private bool RemoveProgramDatabaseEntry(IPROGRAMME old_program)
        {
            bool result = false;
            try
            {
                using (SQLiteConnection con = CreateConnection())
                {
                    try
                    {
                        //gLog.Verbose.Info.WriteLine("Removing old program with oid: '{0}'", old_program.oid);
                        con.Open();
                        string command_text = string.Format(@"DELETE FROM [{0}] WHERE [oid] = @oid;", TABLES.PROGRAMME);
                        //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                        {
                            cmd.Parameters.Add(new SQLiteParameter("oid", old_program.oid));
                            int rowsupdated = cmd.ExecuteNonQuery();
                            //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
                        }
                        result = true;
                    }
                    catch (SQLiteException ex)
                    {
                        gLog.Error.WriteLine(ex.ToString());
                        result = false;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
                result = false;
            }
            return result;
        }
        private bool InsertProgramEntryDatabase(List<IPROGRAMME> list)
        {
            bool result = false;
            try
            {
                using (SQLiteConnection con = CreateConnection())
                {
                    try
                    {
                        //gLog.Verbose.Info.WriteLine("Updating old program with oid: '{0}'", new_program.oid);
                        con.Open();
                        //string command_text = string.Format(@"UPDATE [{0}] SET [name]=@name,[sub_title]=@subtitle, WHERE [OID] = @oid", TABLES.PROGRAMME);
                        string command_text = string.Empty;
                        command_text = BuildGBPVRMultiInsertCommand(list);
                        //gLog.Verbose.Debug.WriteLine("Executing Command: '{0}'", command_text);
                        using (SQLiteCommand cmd = new SQLiteCommand(command_text, con))
                        {                          
                            int rowsupdated = cmd.ExecuteNonQuery();
                            //gLog.Verbose.Info.WriteLine("Updated '{0}' rows", rowsupdated);
                        }

                        result = true;
                    }
                    catch (SQLiteException ex)
                    {
                        gLog.Error.WriteLine(ex.ToString());
                        result = false;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                gLog.Error.WriteLine(ex.ToString());
                result = false;
            }
            return result;
        }

        private string BuildGBPVRMultiInsertCommand(List<IPROGRAMME> list)
        {
            StringBuilder builder = new StringBuilder();
            /*
                insert into table1 (field1,field2) values (value1,value2);
                insert into table1 (field1,field2) values (value1,value2);
                insert into table1 (field1,field2) values (value1,value2);
                insert into table1 (field1,field2) values (value1,value2)
             */
            builder.AppendLine("begin transaction;");
            foreach (var t in list)
            {
                builder.AppendLine(BuildGBPVRSingleInsertCommand(t));
            }
            builder.AppendLine("end transaction;");
            return builder.ToString();
        }

        private string BuildGBPVRSingleInsertCommand(IPROGRAMME program)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("insert into {0} (oid,name,sub_title,description,start_time,end_time,channel_oid,unique_identifier,rating) values (", TABLES.PROGRAMME);
            builder.AppendFormat("{0},",program.oid);
            builder.AppendFormat("\"{0}\",", program.name);
            builder.AppendFormat("\"{0}\",", program.sub_title);
            builder.AppendFormat("\"{0}\",", program.description);
            builder.AppendFormat("'{0}',", program.start_time);
            builder.AppendFormat("'{0}',", program.end_time);
            builder.AppendFormat("{0},", program.channel_oid);
            builder.AppendFormat("\"{0}\",", program.unique_identifier);
            builder.AppendFormat("\"{0}\");", program.rating);
            return builder.ToString();
        }
      
    }
}