using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Immutable;
using System.Data;
using System.Dynamic;
using System.Threading.Tasks;

namespace DirectSQL
{
    /// 
    /// Object to get result of SQL
    /// 
    /// This stands for cursor in RDB
    /// Type of DataReader
    /// Type of DbCommand
    /// Type of Transaction
    /// Type of Connection
    /// Type of DataParameter
    public class SqlResult:EnumerableObject,IDisposable
        where R : IDataReader 
        where CMD : IDbCommand 
        where T : IDbTransaction 
        where C : IDbConnection 
        where P : IDataParameter, new()
    {
        private R _reader;
        private CMD _command;

        private ImmutableArray _resultFields = emptyFields;
        private static readonly ImmutableArray emptyFields = new ImmutableArray();

        private ExpandoObject _resultValues;
        private (String name, Object value)[] _resultTuples;

        private readonly IEnumerable _innerEnumerator;

        /// 
        /// variable not to execute not needed initialization
        /// 
        private bool _allowInitialize;

        /// 
        /// Reader in ADO.NET
        /// 
        public IDataReader Reader
        {
            get
            {
                return _reader;
            }
        }

        /// 
        /// Command in ADO.NET
        /// 
        public IDbCommand Command
        {
            get
            {
                return _command;
            }
        }

        /// 
        /// Sql
        /// 
        /// CommandText of command
        public String Sql
        {
            get
            {
                return _command.CommandText;
            }
        }

        /// 
        /// Fields in result
        /// 
        public ImmutableArray ResultFields
        {
            get
            {
                InitResultFields();
                return _resultFields;
            }
        }

        /// 
        /// Result values as dynamic object
        /// 
        /// 
        /// Each column in result has a field in dynamic object.
        /// 
        public dynamic ResultValues
        {
            get
            {
                InitResultValues();
                return _resultValues;
            }
        }

        /// 
        /// Result object of type T
        /// 
        /// Type of result object
        /// convert from dynamic to T
        /// result object
        /// dynamic object is same as ResultValues
        public TP ResultObject(Func convert){
            return convert(ResultValues);
        }

        /// 
        /// Return enumerable of SqlResult
        /// 
        /// Type of object to be enumerated
        /// Convert from dynamic to T
        /// Object which enumerate result of SqlResult
        public IEnumerable AsEnumerable(Func convert)
        {
            return new Enumerable(this, convert);
        }

        /// 
        /// Return enumerable of SqlResult as dynamic
        /// 
        /// Object which enumerate result of SqlResult
        public IEnumerable AsEnumerable()
        {
            return new Enumerable(this, (obj => obj ));
        }        

        /// 
        /// Result values as an array of tuples
        /// 
        /// 
        /// Each tuple has name and value.
        /// Name is name of column and 
        /// value is value of column
        /// in result row
        /// 
        public (String name,Object value)[] ResultTuples
        {
            get
            {
                InitResultTuples();
                return _resultTuples;
            }
        }

        private SqlResult ( 
            String sql, 
            P[] parameters, 
            C connection, 
            IDbTransaction transaction)
        {
            _command = (CMD) connection.CreateCommand();

            _command.CommandText = sql;

            foreach(var param in parameters){
                _command.Parameters.Add(param);
            }

            if(transaction != 
                   DefaultTransaction.defaultTransaction) {
                _command.Transaction = (T)transaction;
            }

            _allowInitialize = true;

            _innerEnumerator = AsEnumerable();
        }

        internal SqlResult ( 
            String sql, 
            P[] parameters, 
            C connection, 
            T transaction) : this( sql, parameters, connection, (IDbTransaction) transaction)
        {
        }

        internal SqlResult ( 
            String sql, 
            P[] parameters, 
            C connection) : 
            this( 
                sql, 
                parameters, 
                connection, 
                DefaultTransaction.defaultTransaction)
        {
        }        

        /// 
        /// Move cursor to next
        /// 
        /// New row has values or not
        public bool Next()
        {
            _resultValues = null;
            _resultTuples = null;

            _allowInitialize = true;

            return _reader.Read();
        }

        internal void Init()
        {
            if (_allowInitialize)
            {
                if (_reader != null)
                    _reader.Close();

                _reader = (R) _command.ExecuteReader();
                _allowInitialize = false;

                _resultValues = null;
                _resultTuples = null;
                _resultFields = emptyFields;
            }
        }

        private void InitResultFields()
        {
            if (_resultFields != emptyFields)
                return; //Already initialized. No need to init again.

            List list = new List();
            for(int i = 0; i < _reader.FieldCount; i ++)
            {
                list.Add(_reader.GetName(i));
            }

            _resultFields = ImmutableArray.ToImmutableArray(list);
        }

        private void InitResultValues()
        {
            if (_resultValues != null)
                return;
            _resultValues = CreateResultValue(_reader, ResultFields);
        }

        private static ExpandoObject CreateResultValue(
            R reader, 
            ImmutableArray fields)
        {
            var values = new ExpandoObject();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                values.TryAdd(fields[i], reader.GetValue(i));
            }

            return values;
        }

        private void InitResultTuples()
        {
            if (_resultTuples != null)
                return;
            _resultTuples = CreateResultTuples(_reader, ResultFields);
        }

        private static (string, object)[] CreateResultTuples(
            R reader, 
            ImmutableArray resultFields)
        {
            var array = new (String, object)[resultFields.Length];
            for(int i = 0; i < resultFields.Length; i ++)
            {
                array[i] = (resultFields[i], reader.GetValue(i));
            }
            return array;
        }

        internal void Close()
        {
            if (_reader != null)
                _reader.Close();

            if ( _command != null )
                _command.Dispose();
        }

        /// 
        /// Dispose resources.
        /// 
        public void Dispose()
        {
            Close();
        }

        public static dynamic[] LoadSqlResult(
            String sql,
            P[] parameters,
            C connection, 
            T transaction)
        {
            var list = new List();

            Database.Query(
                sql,
                parameters,
                connection,
                transaction,
                (result) => {
                    while (result.Next())
                    {
                        list.Add(result.ResultValues);
                    }
                });

            return list.ToArray();
        }

        public static dynamic[] LoadSqlResult(
            String sql,
            (String name, object value)[] parameters,
            C connection, 
            T transaction)
        {
            return LoadSqlResult(
                sql,
                Database.ConvertToDbParameter(parameters),
                connection,
                transaction
            );
        }

        public static dynamic[] LoadSqlResult(
            String sql,
            C connection,
            T transaction)
        {
            return LoadSqlResult(
                sql, 
                new (String, object)[0], 
                connection, 
                transaction
            );
        }
        
        public static async Task LoadSqlResultAsync(
            String sql,
            C connection,
            T transaction)
        {
            return await LoadSqlResultAsync(
                sql, 
                new (String, object)[0], 
                connection, 
                transaction);
        }

        public static async Task LoadSqlResultAsync(
            String sql,
            (String name, object value)[] parameters,
            C connection,
            T transaction)
        {
            return await LoadSqlResultAsync(
                sql,
                Database.ConvertToDbParameter(parameters),
                connection,
                transaction
            );
        }

         public static async Task LoadSqlResultAsync(
            String sql,
            P[] parameters,
            C connection,
            T transaction)
        {
            Task task = Task.Run(() =>
            {
                var list = new List();

                Database.Query(
                    sql,
                    parameters,
                    connection,
                    transaction,
                    (result) =>
                    {
                        while (result.Next())
                        {
                            list.Add(result.ResultValues);
                        }
                    });

                return list.ToArray();
            });

            return await task;
        }

        public override IEnumerator GetEnumerator()
        {
            return _innerEnumerator.GetEnumerator();
        }

        private class Enumerable : IEnumerable 
        {
            private readonly SqlResult _sqlResult;
            private readonly Func _convert;

            internal Enumerable(
                SqlResult sqlResult,
                Func converter)
            {                
                _sqlResult = sqlResult;
                _convert = converter;
            }

            public IEnumerator GetEnumerator()
            {
                _sqlResult.Init();
                return new Enumerator(_sqlResult, _convert);
            }

            IEnumerator IEnumerable.GetEnumerator()
            {
                return GetEnumerator();
            }
        }

        private class Enumerator : IEnumerator, IEnumerator 
        {
            private SqlResult _sqlResult;
            private readonly Func _convert;

            internal Enumerator(
                SqlResult sqlResult, 
                Func converter)
            {
                _sqlResult = sqlResult;
                _convert = converter;
            }

            public TP Current => _sqlResult.ResultObject(_convert);

            object IEnumerator.Current => _sqlResult.ResultObject(_convert);

            public void Dispose()
            {
                _sqlResult = null;
            }

            public bool MoveNext()
            {
                return _sqlResult.Next();
            }

            public void Reset()
            {
                _sqlResult.Init();
            }
        }
    }
}