// Copyright © 2017-2018 Atomic Software, LLC. All Rights Reserved.
// See LICENSE.md for full license information.

using Atom.Core.Collections;
using System;
using System.Data;
using System.Data.Odbc;
using System.Linq;

namespace Atom.Core.SQL
{
    public class StoredProcedure : IDisposable
    {
        private string ProcedureName;
        private OdbcCommand Command;
        private Dictionary<string, object> OutputParameters;

        public StoredProcedure(string procedureName, OdbcConnection connection)
        {
            ProcedureName = procedureName;
            Command = connection.CreateCommand();
            Command.CommandType = CommandType.StoredProcedure;
            OutputParameters = new Dictionary<string, object>();
        }

        public void AddParameter(string paramName, object value, int size = -1)
        {
            var Parameter = Command.Parameters.AddWithValue(paramName, value);

            if (size != -1)
            {
                Parameter.Size = size;
            }
        }

        public void AddOutput<T>(string paramName, int size = -1)
        {
            if (OutputParameters.ContainsKey(paramName))
            {
                return;
            }

            var Parameter = Command.Parameters.AddWithValue(paramName, default(T));
            Parameter.Direction = ParameterDirection.Output;

            if (typeof(T) == typeof(byte[]))
            {
                Parameter.DbType = DbType.Binary;
                Parameter.OdbcType = OdbcType.Binary;
            }

            if (size != -1)
            {
                Parameter.Size = size;
            }

            OutputParameters.Add(paramName, default(T));
        }

        private void InternalRun()
        {
            Command.CommandText = $"{{call {ProcedureName}";

            if (Command.Parameters.Count > 0)
            {
                Command.CommandText += "(";

                for (var i = 0; i < Command.Parameters.Count; i++)
                {
                    Command.CommandText += "?, ";
                }

                Command.CommandText = Command.CommandText.Substring(0, Command.CommandText.LastIndexOf(", ", StringComparison.Ordinal));
                Command.CommandText += ")";
            }

            Command.CommandText += "}";

            Command.ExecuteNonQuery();

            for (var i = 0; i < OutputParameters.Count; i++)
            {
                var output = OutputParameters.ElementAt(i);
                OutputParameters[output.Key] = Command.Parameters[output.Key].Value;
            }
        }

        public StoredProcedure Run()
        {
            InternalRun();
            return this;
        }

        public OdbcDataReader RunReader()
        {
            InternalRun();
            return Command.ExecuteReader();
        }

        public T GetOutput<T>(string paramName)
        {
            if (!OutputParameters.ContainsKey(paramName) || OutputParameters[paramName] is DBNull)
            {
                return default(T);
            }

            return (T) OutputParameters[paramName];
        }

        public void Dispose()
        {
            if (Command != null)
            {
                Command.Dispose();
                Command = null;
            }

            if (OutputParameters != null)
            {
                OutputParameters.Clear();
                OutputParameters = null;
            }

            ProcedureName = string.Empty;
        }

        ~StoredProcedure()
        {
            Dispose();
        }
    }
}
