// Chattr - Twitter client on .NET
//
// Copyright (c) 2007 Katsuhiko Ichinose <ichi@users.sourceforge.jp>
//
// Chattr is Free Software released under GNU General Public License.
//
// $Id: SQLite.cs 90 2007-12-30 16:21:29Z ichi $

using System;
using System.Collections.Generic;
using System.Text;

using System.Text.RegularExpressions;
using System.Data.SQLite;

using System.Diagnostics;

namespace Chattr
{
    static class SQLite
    {
        static public void CreateDB(string dir)
        {
            try
            {
                string source = Regex.Replace(Properties.Settings.Default.ConnectionStringChattr, @"data source\=\""", "data source=\"" + dir);
                Debug.WriteLine("SQLite.CreateDB(" + dir + "): " + source);
                using (SQLiteConnection cnn = new SQLiteConnection(source))
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    try
                    {
                        cnn.Open();
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("cnn.Open() Error\n" +
                            "in SQLite.CreateDB()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // TableStatusの作成
                        cmd.CommandText = "CREATE TABLE IF NOT EXISTS TableStatus(id bigint PRIMARY KEY, created_at datetime, text varchar, source varchar, source_url varchar, user_id bigint, user_name varchar, user_screen_name varchar, user_location varchar, user_profile_image varchar, flags int)";
                        cmd.ExecuteNonQuery(); // Create the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("CREATE TABLE IF NOT EXISTS TableStatus() Error\n" +
                            "in SQLite.CreateDB()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // TableProfileImageの作成
                        cmd.CommandText = "CREATE TABLE IF NOT EXISTS TableProfileImage(url varchar PRIMARY KEY, image varbinary, width int, height int)";
                        cmd.ExecuteNonQuery(); // Create the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("CREATE TABLE IF NOT EXISTS TableProfileImage() Error\n" +
                            "in SQLite.CreateDB()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // TableTinyUrlの作成
                        cmd.CommandText = "CREATE TABLE IF NOT EXISTS TableTinyUrl(tiny varchar PRIMARY KEY, url varchar)";
                        cmd.ExecuteNonQuery(); // Create the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("CREATE TABLE IF NOT EXISTS TableTinyUrl() Error\n" +
                            "in SQLite.CreateDB()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // TableIdの作成
                        cmd.CommandText = "CREATE TABLE IF NOT EXISTS TableId(id bigint PRIMARY KEY, replace bigint)";
                        cmd.ExecuteNonQuery(); // Create the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("CREATE TABLE IF NOT EXISTS TableId() Error\n" +
                            "in SQLite.CreateDB()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // IndexStatusの作成
                        cmd.CommandText = "CREATE INDEX IF NOT EXISTS IndexStatus ON TableStatus(created_at, id)";
                        cmd.ExecuteNonQuery(); // Create the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("CREATE INDEX IF NOT EXISTS IndexStatus ON TableStatus() Error\n" +
                            "in SQLite.CreateDB()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    cnn.Close();
                }
            }
            catch (SystemException ex)
            {
                Debug.WriteLine("SQLite.CreateDB() Error\n" +
                    ex.Message + "\n" + "Source: " + ex.Source);
            }
        }

        static public void DropTable(string dir)
        {
            try
            {
                string source = Regex.Replace(Properties.Settings.Default.ConnectionStringChattr, @"data source\=\""", "data source=\"" + dir);
                Debug.WriteLine("SQLite.DropTable(" + dir + "): " + source);
                using (SQLiteConnection cnn = new SQLiteConnection(source))
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    try
                    {
                        cnn.Open();
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("cnn.Open() Error\n" +
                            "in SQLite.DropTable()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // TableStatusの削除
                        cmd.CommandText = "DROP TABLE IF EXISTS TableStatus";
                        cmd.ExecuteNonQuery(); // Drop the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("DROP TABLE IF EXISTS TableStatus Error\n" +
                            "in SQLite.DropTable()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // TableProfileImageの削除
                        cmd.CommandText = "DROP TABLE IF EXISTS TableProfileImage";
                        cmd.ExecuteNonQuery(); // Drop the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("DROP TABLE IF EXISTS TableProfileImage Error\n" +
                            "in SQLite.DropTable()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // TableTinyUrlの削除
                        cmd.CommandText = "DROP TABLE IF EXISTS TableTinyUrl";
                        cmd.ExecuteNonQuery(); // Drop the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("DROP TABLE IF EXISTS TableTinyUrl Error\n" +
                            "in SQLite.DropTable()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    cnn.Close();
                }
            }
            catch (SystemException ex)
            {
                Debug.WriteLine("SQLite.DropTable() Error\n" +
                    ex.Message + "\n" + "Source: " + ex.Source);
            }
        }

        static public void Vacuum(string dir)
        {
            try
            {
                DateTime now = DateTime.Now;
                string source = Regex.Replace(Properties.Settings.Default.ConnectionStringChattr, @"data source\=\""", "data source=\"" + dir);
                Debug.WriteLine(now + " SQLite.Vacuum(" + dir + "): " + source);
                using (SQLiteConnection cnn = new SQLiteConnection(source))
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    try
                    {
                        cnn.Open();
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("cnn.Open() Error\n" +
                            "in SQLite.Vacuum()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // VACUUMの実行
                        cmd.CommandText = "VACUUM";
                        cmd.ExecuteNonQuery(); // Create the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("VACUUM Error\n" +
                            "in SQLite.Vacuum()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    cnn.Close();
                }
                DateTime fin = DateTime.Now;
                Debug.WriteLine(fin + " SQLite.Vacuum: " + (fin - now));
            }
            catch (SystemException ex)
            {
                Debug.WriteLine("SQLite.Vacuum() Error\n" +
                    ex.Message + "\n" + "Source: " + ex.Source);
            }
        }

        static public void DeleteOldStatuses(string dir, DateTime date)
        {
            try
            {
                DateTime now = DateTime.Now;
                string source = Regex.Replace(Properties.Settings.Default.ConnectionStringChattr, @"data source\=\""", "data source=\"" + dir);
                Debug.WriteLine(now + " SQLite.DeleteOldStatuses(" + dir + "): " + source);
                using (SQLiteConnection cnn = new SQLiteConnection(source))
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    try
                    {
                        cnn.Open();
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("cnn.Open() Error\n" +
                            "in SQLite.DeleteOldStatuses()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // 古いステータスを削除
                        cmd.CommandText = @"DELETE FROM TableStatus WHERE created_at < '" +
                            date.ToString("yyyy-MM-dd HH:mm:ss.ffffff") + @"'";
                        Debug.WriteLine(cmd.CommandText);
                        cmd.ExecuteNonQuery(); // Create the table, don't expect returned data
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine(cmd.CommandText + " Error\n" +
                            "in SQLite.DeleteOldStatuses()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    cnn.Close();
                }
                DateTime fin = DateTime.Now;
                Debug.WriteLine(fin + " SQLite.DeleteOldStatuses: " + (fin - now));
            }
            catch (SystemException ex)
            {
                Debug.WriteLine("SQLite.DeleteOldStatuses() Error\n" +
                    ex.Message + "\n" + "Source: " + ex.Source);
            }
        }

        static public SQLiteConnection ExecuteReader(string dir, string command, ref SQLiteDataReader reader)
        {
            try
            {
                string source = Regex.Replace(Properties.Settings.Default.ConnectionStringChattr, @"data source\=\""", "data source=\"" + dir);
                Debug.WriteLine("SQLite.ExecuteReader(" + command + "): " + source);
                SQLiteConnection cnn = new SQLiteConnection(source);
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    try
                    {
                        cnn.Open();
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("cnn.Open() Error\n" +
                            "in SQLite.ExecuteReader()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // コマンドの実行
                        cmd.CommandText = command;
                        reader = cmd.ExecuteReader();
                        return cnn;
                    }
                    catch (SystemException ex)
                    {
                        cnn.Close();
                        Debug.WriteLine("cmd.ExecuteReader() Error\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }
                }
            }
            catch (SystemException ex)
            {
                Debug.WriteLine("SQLite.ExecuteReader() Error\n" +
                    ex.Message + "\n" + "Source: " + ex.Source);
            }
            return (SQLiteConnection)null;
        }

        static public SQLiteConnection ExecuteScalar(string dir, string command, ref object scalar)
        {
            try
            {
                string source = Regex.Replace(Properties.Settings.Default.ConnectionStringChattr, @"data source\=\""", "data source=\"" + dir);
                Debug.WriteLine("SQLite.ExecuteScalar(" + command + "): " + source);
                SQLiteConnection cnn = new SQLiteConnection(source);
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    try
                    {
                        cnn.Open();
                    }
                    catch (SystemException ex)
                    {
                        Debug.WriteLine("cnn.Open() Error\n" +
                            "in SQLite.ExecuteScalar()\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }

                    try
                    {
                        // コマンドの実行
                        cmd.CommandText = command;
                        scalar = cmd.ExecuteScalar();
                        return cnn;
                    }
                    catch (SystemException ex)
                    {
                        cnn.Close();
                        Debug.WriteLine("cmd.ExecuteScalar() Error\n" +
                            ex.Message + "\n" + "Source: " + ex.Source);
                    }
                }
            }
            catch (SystemException ex)
            {
                Debug.WriteLine("SQLite.ExecuteScalar() Error\n" +
                    ex.Message + "\n" + "Source: " + ex.Source);
            }
            return (SQLiteConnection)null;
        }
    }
}
