MySqlCommand

using System;
using System.Data;
using System.Text;
using Sinaa_Project;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace AmirSQL.MySQL
{
    public class MySqlCommand : IDisposable
    {
        //private MySqlCommandType _type;
        public MySqlCommandType Type { get; set; }
        private StringBuilder _command;
        private List<MySqlParameter> _parameters = new List<MySqlParameter>();
        public List<MySqlParameter> Parameters => _parameters;
        public string Command
        {
            get => _command.ToString();
            private set => _command = new StringBuilder(value);
        }

        private bool firstPart = true;
        private Dictionary<byte, string> insertFields;
        private Dictionary<byte, string> insertValues;
        private byte lastpair;

        public MySqlCommand(MySqlCommandType type)
        {
            Type = type;
            _command = new StringBuilder();

            switch (type)
            {
                case MySqlCommandType.SELECT:
                    _command = new StringBuilder("SELECT * FROM <R>");
                    break;
                case MySqlCommandType.UPDATE:
                    _command = new StringBuilder("UPDATE <R> SET ");
                    break;
                case MySqlCommandType.INSERT:
                    insertFields = new Dictionary<byte, string>();
                    insertValues = new Dictionary<byte, string>();
                    lastpair = 0;
                    _command = new StringBuilder("INSERT INTO <R> (<F>) VALUES (<V>)");
                    break;
                case MySqlCommandType.DELETE:
                    _command = new StringBuilder("DELETE FROM <R> WHERE <C> = <V>");
                    break;
                case MySqlCommandType.COUNT:
                    _command = new StringBuilder("SELECT count(<V>) FROM <R>");
                    break;
            }
        }
        private bool Comma()
        {
            if (firstPart)
            {
                firstPart = false;
                return false;
            }
            return _command[_command.Length - 1] != ',';
        }

        public MySqlCommand Select(string table)
        {
            _command = _command.Replace("<R>", $"`{table}`");
            return this;
        }

        public MySqlCommand Count(string table)
        {
            _command = _command.Replace("<R>", $"`{table}`");
            return this;
        }

        public MySqlCommand Delete(string table, string column, object value)
        {
            _command = _command.Replace("<R>", $"`{table}`")
                               .Replace("<C>", $"`{column}`")
                               .Replace("<V>", value is string str ? $"'{str.MySqlEscape()}'" : value.ToString());
            return this;
        }

        public MySqlCommand Update(string table)
        {
            _command = _command.Replace("<R>", $"`{table}`");
            return this;
        }

        //public MySqlCommand Set(string column, object value)
        //{
        //    if (Type == MySqlCommandType.UPDATE)
        //    {
        //        if (Comma())
        //            _command.Append($", `{column}` = {(value is byte[]? $"@{column}" : (value is string str ? $"'{str.MySqlEscape()}'" : value.ToString()))} ");
        //        else
        //            _command.Append($"`{column}` = {(value is byte[]? $"@{column}" : (value is string str ? $"'{str.MySqlEscape()}'" : value.ToString()))} ");

        //        if (value is byte[])
        //        {
        //            AddParameter($"@{column}", value);
        //        }
        //    }
        //    return this;
        //}
        //public MySqlCommand Set(string column, object value)
        //{
        //    if (Type == MySqlCommandType.UPDATE)
        //    {
        //        string parameterPlaceholder = $"@{column}";

        //        if (Comma())
        //        {
        //            _command.Append($", `{column}` = {parameterPlaceholder} ");
        //        }
        //        else
        //        {
        //            _command.Append($"`{column}` = {parameterPlaceholder} ");
        //        }

        //        AddParameter(parameterPlaceholder, value);
        //    }
        //    return this;
        //}
        public MySqlCommand Set(string column, object value)
        {
            if (Type == MySqlCommandType.UPDATE)
            {
                bool isByteArray = value is byte[];
                string parameterPlaceholder = $"@{column}";

                if (Comma())
                {
                    _command.Append($", `{column}` = {parameterPlaceholder} ");
                }
                else
                {
                    _command.Append($"`{column}` = {parameterPlaceholder} ");
                }

                // Add the parameter to the list
                AddParameter(parameterPlaceholder, value);


                Program.AddError($"Set column: {column}, value: {(isByteArray ? "byte[]" : value.ToString())}");
            }
            return this;
        }





        public MySqlCommand Insert(string table)
        {
            _command = _command.Replace("<R>", $"`{table}`");
            return this;
        }

        public MySqlCommand Insert(string field, object value)
        {
            insertFields.Add(lastpair, $"`{field}`");
            insertValues.Add(lastpair, value is byte[]? $"@{field}" : (value is string str ? $"'{str.MySqlEscape()}'" : value.ToString()));
            if (value is byte[])
            {
                AddParameter($"@{field}", value);
            }
            lastpair++;
            return this;
        }

        //public MySqlCommand AddParameter(string name, object value)
        //{
        //    _parameters.Add(new MySqlParameter(name, value));
        //    return this;
        //}
        public MySqlCommand AddParameter(string name, object value)
        {
            if (value == DBNull.Value)
            {
                _parameters.Add(new MySqlParameter(name, value));
            }
            else if (value is byte[])
            {
                _parameters.Add(new MySqlParameter(name, MySqlDbType.Blob) { Value = value });
            }
            else
            {
                _parameters.Add(new MySqlParameter(name, value));
            }
            return this;
        }


        public MySqlCommand Where(string column, object value, bool greater = false, bool like = false)
        {
            if (like)
            {
                if (value is string stringValue)
                {
                    _command.Append($"WHERE `{column}` LIKE '{stringValue.MySqlEscape()}'");
                }
                else
                {
                    _command.Append($"WHERE `{column}` LIKE {value}");
                }
            }
            else
            {
                if (value is string stringValue)
                {
                    _command.Append(greater ? $"WHERE `{column}` > '{stringValue.MySqlEscape()}'" : $"WHERE `{column}` = '{stringValue.MySqlEscape()}'");
                }
                else
                {
                    _command.Append(greater ? $"WHERE `{column}` > {value}" : $"WHERE `{column}` = {value}");
                }
            }
            return this;
        }

        public MySqlCommand And(string column, object value)
        {
            _command.Append($" AND `{column}` = {value}");
            return this;
        }

        public MySqlCommand Or(string column, object value)
        {
            _command.Append($" OR `{column}` = {value}");
            return this;
        }

        public MySqlCommand Order(string column)
        {
            _command.Append($" ORDER BY {column}");
            return this;
        }
        public int Execute()
        {
            using (var conn = DataHolder.MySqlConnection)
            {
                conn.Open();
                return Execute(conn);
            }
        }
        public int Execute(MySqlConnection conn)
        {

            if (Type == MySqlCommandType.INSERT)
            {
                var fields = string.Join(",", insertFields.Values);
                var values = string.Join(",", insertValues.Values);
                _command.Replace("<F>", fields).Replace("<V>", values);
            }
            Program.AddError("Executing Command: " + Command); // Log the command
            using (var cmd = new MySql.Data.MySqlClient.MySqlCommand(Command, conn))
            {
                foreach (var parameter in _parameters)
                {
                    Program.AddError($"Parameter: {parameter.ParameterName}, Value: {parameter.Value}"); // Log parameters
                    cmd.Parameters.Add(parameter);
                }
                return cmd.ExecuteNonQuery();
            }
        }

        public MySqlReader CreateReader()
        {
            return new MySqlReader(this);
        }

        public void Dispose()
        {
            insertValues?.Clear();
            insertFields?.Clear();
            _command = null;
        }
    }

    public enum MySqlCommandType
    {
        DELETE, INSERT, SELECT, UPDATE, COUNT
    }

    public class MySqlReader : IDisposable
    {
        private readonly DataSet _dataset;
        private DataRow _datarow;
        private int _row;
        private const string Table = "table";

        public MySqlReader(MySqlCommand command)
        {
            if (command.Type == MySqlCommandType.SELECT)
            {
                _dataset = new DataSet();
                _row = 0;
                using (var conn = DataHolder.MySqlConnection)
                {
                    conn.Open();
                    using (var adapter = new MySqlDataAdapter(command.Command, conn))
                        adapter.Fill(_dataset, Table);
                }
                command.Dispose();
            }
        }

        public void Dispose()
        {
            _dataset?.Dispose();
        }

        public bool Read()
        {
            if (_dataset == null || _dataset.Tables.Count == 0 || _dataset.Tables[Table].Rows.Count <= _row)
                return false;

            _datarow = _dataset.Tables[Table].Rows[_row];
            _row++;
            return true;
        }

        public int NumberOfRows => _dataset?.Tables.Count > 0 ? _dataset.Tables[Table].Rows.Count : 0;

        public T Read<T>(string columnName)
        {
            if (_datarow.IsNull(columnName)) return default;

            var value = _datarow[columnName];
            return (T)Convert.ChangeType(value, typeof(T));
        }
    }

    public static class MySqlExtensions
    {
        public static string MySqlEscape(this string usString)
        {
            if (usString == null) return null;
            return Regex.Replace(usString, @"[\r\n\x00\x1a\\'""]", @"\$0");
        }
    }

    public static class DataHolder
    {
        public static MySqlConnection MySqlConnection => new MySqlConnection(Program.connection("db_sql", ""));
    }
}