using System.Data.SQLite;
using System.Dynamic;

namespace BananaStore
{
    public delegate void DbCallback(dynamic message);

    public abstract class DatabaseCommand
    {
        public string TableName { get; set; }
        public ExpandoObject Record { get; set; }
        public DbCallback Callback { get; set; }

        protected DatabaseCommand(string tableName,
            ExpandoObject record, DbCallback callback)
        {
            TableName = tableName;
            Record = record;
            Callback = callback;
        }

        public abstract void Execute(SQLiteConnection connection);
    }

    /// <summary>
    /// insert command
    /// </summary>

    public class InsertCommand : DatabaseCommand
    {
        public InsertCommand(string tableName,  ExpandoObject record,
            DbCallback callback)
            : base(tableName, record, callback) { }

        public override void Execute(SQLiteConnection connection)
        {
            var dict = Record as IDictionary<string, object>;
            if (dict != null)
            {
                string columns = string.Join(",", dict.Keys);
                string values = string.Join(",", dict.Values);
                string paramNames = string.Join(",", dict.Keys.Select(k => "@" + k));

                string sql = $"INSERT INTO {TableName}({columns}) VALUES({paramNames})";

                using(var cmd = new SQLiteCommand(sql, connection))
                {
                    foreach(var pair in dict)
                    {
                        cmd.Parameters.AddWithValue("@"+pair.Key, pair.Value);
                    }
                    try
                    {
                        cmd.ExecuteNonQuery();
                        Callback("Insert Success");

                    }catch(Exception ex)
                    {
                        Callback($"Exception: {ex.Message}");
                    }
                }


            }
        }
    }

    /// <summary>
    /// create command
    /// </summary>
    ///

    public class CreateCommand : DatabaseCommand
    {
        public CreateCommand(string tableName, ExpandoObject recordTemplate,
            DbCallback callback)
            : base(tableName, recordTemplate, callback) { }

        private string GetTypeForValue(object value)
        {
            if (value is int) return "INTEGER";
            else if (value is string) return "TEXT";
            else if (value is double || value is float) return "REAL";
            else if (value is byte[]) return "BLOB";
            else
                throw new
                    NotSupportedException($"Type {value.GetType().Name} not supported.");


        }

        public override void Execute(SQLiteConnection connection)
        {
            var dict = Record as IDictionary<string, object> ;
            if(dict != null)
            {
                var columnDefinitions = dict.Select(pair => {
                    string type = GetTypeForValue(pair.Value);
                    return $"{pair.Key} {type}";
                });

                string columns = string.Join(", ", columnDefinitions) ;
                string sql = $"CREATE TABLE IF NOT EXISTS " +
                    $" {TableName}(ID integer PRIMARY KEY AUTOINCREMENT, " +
                    $"{columns})";
            }

        }
    }


    internal class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello, World!");
        }
    }
}