using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;


namespace sample.histories.diff {


    /// <summary>
    ///
    /// </summary>
    public partial class HistoriyDiffForm : Form {
        /// <summary>
        ///
        /// </summary>
        public HistoriyDiffForm() {
            InitializeComponent();
        }


        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="ergs"></param>
        private void btnAppend_Click(object sender, EventArgs ergs) {
            int seq = 0;
            /*
             * URL のリストを作成する
             * ===========================================================================
             * tbPasteArea.Text の中身は、\n で区切られた URL の束です。
             * Opera の履歴を全て選択し、コピーしたものです。
             */
            string[] urls = tbPasteArea.Text.Split( new string[] { Environment.NewLine }, StringSplitOptions.None );

            using ( DbConnection connection = getConnection( "visit-history.db" ) ) {
                if ( connection.State != ConnectionState.Open )
                    connection.Open();

                DbCommand command = connection.CreateCommand();
                DbTransaction transaction = null;

                // histroies というテーブルがあるか調べます。
                command.CommandText = "select count(*) from sqlite_master where type ='table' and name = 'histories'";
                int exists = int.Parse( command.ExecuteScalar().ToString() );

                if ( exists == 0 ) {
                    // テーブルが存在しなければ create table 文を打ってテーブルを作成します。
                    command.CommandText = "create table histories ( seq int primary key, url text not null )";
                    command.ExecuteNonQuery();

                    transaction = connection.BeginTransaction();
                    try {
                        seq = 0;

                        foreach ( string url in urls ) {
                            command.CommandText = string.Format( "insert into histories (seq, url) values ({0}, '{1}')", seq, url );
                            command.ExecuteNonQuery();

                            ++seq;
                        }
                        transaction.Commit();
                    } catch ( Exception before_except ) {

                        try {
                            error( before_except );
                            transaction.Rollback();
                        } catch ( Exception after_except ) {
                            error( after_except );
                        }
                        tbPasteArea.Text = string.Empty;

                        return;

                    }
                } else {
                    command.CommandText = "create temporary table tmp_histories ( seq int primary key, url text not null )";
                    command.ExecuteNonQuery();

                    transaction = connection.BeginTransaction();
                    try {
                        seq = 0;

                        foreach ( string url in urls ) {
                            command.CommandText = string.Format( "insert into tmp_histories (seq, url) values ({0}, '{1}')", seq, url );
                            command.ExecuteNonQuery();

                            ++seq;
                        }
                        transaction.Commit();
                    } catch ( Exception before_except ) {

                        try {
                            error( before_except );
                            transaction.Rollback();
                        } catch ( Exception after_except ) {
                            error( after_except );
                        }
                        tbPasteArea.Text = string.Empty;

                        return;

                    }

                    // histories テーブルと、テンポラリテーブルの diff をとります。
                    // sqlite3 ではグルーピングのための括弧を使えません。
                    command.CommandText = string.Join( " ", new string[] {
                        "select url from histories",
                        "except",
                        "select url from tmp_histories",
                        "union all",
                        "select url from histories",
                        "except",
                        "select url from tmp_histories"
                        } );
                    // reader 起動中は insert 文を評価できないので、List に貯めておきます。
                    List<string> li = new List<string>();

                    using ( DbDataReader reader = command.ExecuteReader() ) {
                        if ( reader.HasRows ) {
                            while ( reader.Read() ) {
                                li.Add( reader["url"].ToString() );
                            }
                        }
                    }

                    transaction = connection.BeginTransaction();
                    try {
                        // 現在の histories の seq の最大値を求めます。
                        command.CommandText = "select max(seq) from histories";
                        seq = int.Parse( command.ExecuteScalar().ToString() ) + 1;
                        // 重複していない URL を histories に保存します。
                        foreach ( string url in li ) {
                            command.CommandText = string.Format( "insert into histories (seq, url) values ({0}, '{1}')",
                                                                 seq,
                                                                 url
                                                                 );
                            command.ExecuteNonQuery();

                            ++seq;
                        }
                    } catch ( Exception before_except ) {
                        try {
                            error( before_except );
                            transaction.Rollback();
                        } catch ( Exception after_except ) {
                            error( after_except );
                        }
                        tbPasteArea.Text = string.Empty;
                        return;
                    }
                }
                tbPasteArea.Text = string.Empty;
                MessageBox.Show( "処理が完了しました。", "通知" );
            }
        }


        /// <summary>
        ///
        /// </summary>
        /// <param name="filename"></param>
        /// <returns></returns>
        DbConnection getConnection(string filename) {
            return new SQLiteConnection( string.Format( "Data Source=./{0}", filename ) );
        }


        /// <summary>
        ///
        /// </summary>
        /// <param name="e"></param>
        void error(Exception e) {
            using ( StreamWriter writer = new StreamWriter( File.Open( "stderr.txt", FileMode.Append ) ) ) {
                writer.WriteLine( e.GetType().ToString() );
                writer.WriteLine( e.Source );
                writer.WriteLine( e.Message );
                writer.WriteLine( e.StackTrace );
                writer.WriteLine( e.TargetSite );
                writer.WriteLine( "----------------------------------------------------------------------------------------" );
            }
        }


    }


}