using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;

namespace CoPackLibrary.DataAccessLayer
{
   public interface IDatabaseGateway
   {
      int ExecuteQuery(IQuery query);
      int ExecuteQuery(IModifyQuery query, DateTime updateDate);
      ReturnType ExecuteQuery<ReturnType>(IResultQuery<ReturnType> query);

      void StartTransaction();
      void Commit();
      void Rollback();
   }

   public class DatabaseGateway : IDatabaseGateway
   {
      public DatabaseGateway() : this("CPSDsn")
      {
      }

      public DatabaseGateway(string connectionStringName)
      {
         ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];

         _providerFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
         _connectionString = connectionStringSettings.ConnectionString;
         _connectionStringName = connectionStringName;
      }

      private readonly DbProviderFactory _providerFactory;
      private readonly string _connectionString;
      private readonly string _connectionStringName;
      private IDbConnection _transactionalConnection;
      private IDbTransaction _transaction;

      public int ExecuteQuery(IQuery query)
      {
         IDbCommand command = CreateUsing(query.Prepare);
         var result = command.ExecuteNonQuery();

         Dispose(command);

         return result;
      }

      public int ExecuteQuery(IModifyQuery query, DateTime updateDate)
      {
         int rowsAffected = ExecuteQuery(query);
         if (rowsAffected > 0)
            ExecuteQuery(query.GetHistoryInsertUsing(updateDate));

         return rowsAffected;
      }

      public T ExecuteQuery<T>(IResultQuery<T> query)
      {
         T result;
         IDbCommand command = CreateUsing(query.Prepare);

         using (IDataReader reader = command.ExecuteReader())
         {
            result = query.MapResult(reader);
         }

         Dispose(command);

         return result;
      }

      public void StartTransaction()
      {
         _transactionalConnection = CreateConnection();
         _transaction = _transactionalConnection.BeginTransaction();
      }

      public void Commit()
      {
         if (_transaction == null) return;

         _transaction.Commit();
         _transaction = null;
         _transactionalConnection.Dispose();
         _transactionalConnection = null;
      }

      public void Rollback()
      {
         if (_transaction == null) return;

         _transaction.Rollback();
         _transaction = null;
         _transactionalConnection.Dispose();
         _transactionalConnection = null;
      }

      private IDbConnection CreateConnection()
      {
         IDbConnection conn = _providerFactory.CreateConnection();
         conn.ConnectionString = _connectionString;
         conn.Open();

         return conn;
      }

      private IDbCommand CreateUsing(Action<IDbCommand> prepare)
      {
         IDbConnection conn = _transactionalConnection ?? CreateConnection();

         IDbCommand command = conn.CreateCommand();
         command.Transaction = _transaction;
         prepare(command);

         if (Debugger.IsAttached)
            Debug(command);

         return command;
      }

      private void Dispose(IDbCommand cmd)
      {
         if (_transactionalConnection == null)
            cmd.Connection.Dispose();

         cmd.Dispose();
      }

      private void Debug(IDbCommand cmd)
      {
         var sql = cmd.CommandText.Replace("\r\n", " ").Trim();
         while (sql.Length != (sql = sql.Replace("  ", " ")).Length)
         {
         }

         var parameters = cmd.Parameters.Cast<IDataParameter>().Select(x => "{0}: '{1}'".F(x.ParameterName, x.Value)).Join(", ");

         var debugMsg = "DatabaseGateway {0} : {1}  ({2})".F(_connectionStringName, sql, parameters);

         System.Diagnostics.Debug.WriteLine(debugMsg);
      }
   }

   public static class DatabaseGatewayExtensions
   {
      public static IEnumerable<IDataRecord> ReadAll(this IDataReader reader)
      {
         while (reader.Read()) yield return reader;
      }

      public static DataTable GetDataTable(this IDataReader reader)
      {
         var table = new DataTable();
         table.Load(reader);
         return table;
      }

      public static IEnumerable<DataRow> GetDataRows(this IDataReader reader)
      {
         var table = new DataTable();
         table.Load(reader);
         return table.Rows.Cast<DataRow>();
      }
   }
}