using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Text;
using System.Data;

using System.Linq;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;

using System.Diagnostics;
using Serilog;

namespace SQLite.BusinessLayer
{
    /// <summary>
    /// http://zetcode.com/csharp/sqlite/
    /// https://sqlite.org/cli.html
    /// 
    /// </summary>
    public sealed class SPSQLite
    {
        protected SQLiteConnection conn = null;
        protected string dbFilePath = string.Empty;
        protected string table_name = string.Empty;

        private static readonly Lazy<SPSQLite> lazy = new Lazy<SPSQLite>(() => new SPSQLite());

        public static SPSQLite Instance { get { return lazy.Value; } }

        public SPSQLite() 
        {
            dbFilePath = SPGlobal.DB_PATH;    
          	table_name = "TABLE_NAME";   
        }

        public int CreateTable()
        {
            using (var cmd = new SQLiteCommand(conn))
            {
                try
                {
                    cmd.CommandText =
                        $"CREATE TABLE IF NOT EXISTS {table_name}(window_id TEXT PRIMARY KEY, pos_x INT, pos_y INT, height INT, width INT)";

                    return cmd.ExecuteNonQuery();
                }
                catch { }
                return 0;
            }
        }

        public bool InsertOrUpdateRecord(string win_id, int x, int y, int h, int w, object obj)
        {
            if (!IsConnected())
                Reconnect();

            using (var cmd = new SQLiteCommand(conn))
            {
                try
                {
                    string commandTxt = string.Format($"REPLACE INTO {table_name}(window_id, pos_x, pos_y, height, width) " +
                        $"VALUES('{win_id}', {x}, {y}, {h}, {w})");

                    cmd.CommandText = commandTxt;
                    var num = cmd.ExecuteNonQuery();

                    if (num == 1)
                        return true;
                }
                catch
                {
                    Disconnect();
                }
                return false;
            }
        }

        public bool UpdateRecord(string win_id, int x, int y, int h, int w, object obj)
        {
            if (!IsConnected())
                Reconnect();

            using (var cmd = new SQLiteCommand(conn))
            {
                try
                {
                    string commandTxt = string.Format($"UPDATE {table_name} SET " +
                        $"pos_x = {x}, pos_y = {y}, height = {h}, width = {w}, " +
                        $"WHERE window_id = '{win_id}'");

                    cmd.CommandText = commandTxt;
                    var num = cmd.ExecuteNonQuery();

                    if (num == 1)
                        return true;
                }
                catch
                {
                    Disconnect();
                }
                return false;
            }
        }

        public void Initialize()
        {
            Connect();

            if (IsConnected())
            {
                CreateTable();
            }
        }

        public void Terminate()
        {
            Disconnect();
        }

        public bool DeleteRecord(string win_id)
        {
            return DeleteTableRecord(table_name, win_id);
        }

        // protected members
        public void Connect()
        {
            try
            {
                var cs = string.Format(@"URI=file:{0}", dbFilePath);
                conn = new SQLiteConnection(cs);
                conn.Open();
            }
            catch (Exception e)
            {
                Log.Error("SPSQLite.Connect Exception: {0}", e.StackTrace);
            }
        }

        public void Disconnect()
        {
            if (conn != null)
            {
                conn.Close();
                conn = null;
            }
        }

        public bool IsConnected()
        {
            return (conn != null ? true : false);
        }

        public bool Reconnect()
        {
            Disconnect();
            Connect();
            return IsConnected();
        }

        public void DropTable(string table_name)
        {
            using var cmd = new SQLiteCommand(conn);

            string commandTxt = string.Format($"DROP TABLE IF EXISTS {table_name}");
            cmd.CommandText = commandTxt;
            cmd.ExecuteNonQuery();
        }

        public bool DeleteTableRecord(string table_name, string win_id)
        {
            if (!IsConnected())
                Reconnect();

            using (var cmd = new SQLiteCommand(conn))
            {
                try
                {
                    string commandTxt = string.Format($"DELETE FROM {table_name} WHERE window_id = '{win_id}'");
                    cmd.CommandText = commandTxt;
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch
                {
                    Disconnect();
                }
                return false;
            }
        }

        // public members
        public DataTable SelectTableRecord(string win_id)
        {
            if (!IsConnected())
                Reconnect();

            using (var cmd = new SQLiteCommand(conn))
            {
                try
                {
                    string commandTxt = string.Format($"SELECT * FROM {table_name} WHERE window_id = '{win_id}'");
                    cmd.CommandText = commandTxt;
                    SQLiteDataReader dr = cmd.ExecuteReader();

                    var dt = new DataTable();
                    dt.Load(dr);

                    return dt;
                }
                catch { }
                return null;
            }            
        }

        
    }
}