using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Baza_Reklam.Classes.Interfaces;
using Baza_Reklam.Classes.Model;
using Baza_Reklam.Classes.Model.Enums;

namespace Baza_Reklam.Classes.Repositories
{
    public class CustomerRepository : Repository<Customer>
    {
        public CustomerRepository(string connectionString)
            : base(connectionString)
        {
        }

        public override Customer Find(int id)
        {
            string query = "SELECT CustomerId, FirstName FROM Klienci WHERE CustomerId={0}";
            query = string.Format(query, id);

            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);

                _reader = _command.ExecuteReader();
                if (_reader != null && _reader.Read())
                    return new Customer(_reader.GetInt32(0), _reader.GetString(1));
            }
            finally
            {
                CleanUp();
            }

            return null;
        }

        public override void Save(Customer customer)
        {
            const string insertSubscriptionQuery = "INSERT INTO Subscription(Guid, CustomerId, SubscriptionTypeId, SubscriptionItems, BasePrice, TotalPrice, Discount, Vat, Currency, OrderId, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, StartDate) "
                                 + "VALUES(@guid, @customerId, @subscriptionTypeId, @subscriptionItems, @basePrice, @totalPrice, @discount, @vat, @currency, @orderId, @createdOn, @createdBy, @updatedOn, @updatedBy, @startDate) "
                                 + "SELECT SCOPE_IDENTITY();";

            const string updateSubscriptionQuery = "UPDATE Subscription SET SubscriptionTypeId=@subscriptionTypeId, SubscriptionItems=@subscriptionItems, "
                                 + "BasePrice=@basePrice, TotalPrice=@totalPrice, Discount=@discount, Vat=@vat, Currency=@currency, OrderId=@orderId, "
                                 + "UpdatedOn=@updatedOn, @updatedBy=UpdatedBy, StartDate=@startDate "
                                 + "WHERE Id=@id";

            const string insertSubscriptionDetailQuery = "INSERT INTO SubscriptionDetail(SubscriptionId, Price, PricePln, [Year], [Month]) "
                                                         + "VALUES(@subscriptionId, @price, @pricePln, @year, @month) "
                                                         + "SELECT SCOPE_IDENTITY();";

            const string deleteSubscriptionDetailQuery = "DELETE FROM SubscriptionDetail WHERE SubscriptionId=@subscriptionId";

            try
            {
                _connection.Open();
                _transaction = _connection.BeginTransaction();
                _command = _connection.CreateCommand();
                _command.Transaction = _transaction;

                int subscriptionId;

                foreach (Subscription subscription in customer.Subscriptions)
                {
                    _command.Parameters.Clear();

                    if (subscription.Id == 0)
                    {
                        _command.CommandText = insertSubscriptionQuery;
                        _command.Parameters.AddWithValue("@guid", subscription.Guid);
                        _command.Parameters.AddWithValue("@customerId", customer.Id);
                        _command.Parameters.AddWithValue("@createdOn", subscription.CreatedOn);
                        _command.Parameters.AddWithValue("@createdBy", subscription.CreatedBy);
                    }
                    else
                    {
                        _command.CommandText = updateSubscriptionQuery;
                        _command.Parameters.AddWithValue("@id", subscription.Id);
                    }

                    _command.Parameters.AddWithValue("@subscriptionTypeId", subscription.SubscriptionTypeId);
                    _command.Parameters.AddWithValue("@subscriptionItems", subscription.SubscriptionItems);
                    _command.Parameters.AddWithValue("@basePrice", subscription.BasePrice);
                    _command.Parameters.AddWithValue("@totalPrice", subscription.TotalPrice);
                    _command.Parameters.AddWithValue("@discount", subscription.Discount);
                    _command.Parameters.AddWithValue("@vat", subscription.Vat);
                    _command.Parameters.AddWithValue("@currency", subscription.Currency);
                    if (subscription.OrderId > 0)
                        _command.Parameters.AddWithValue("@orderId", subscription.OrderId);
                    else
                        _command.Parameters.AddWithValue("@orderId", DBNull.Value);

                    _command.Parameters.AddWithValue("@updatedBy", subscription.UpdatedBy);
                    _command.Parameters.AddWithValue("@updatedOn", subscription.UpdatedOn);
                    _command.Parameters.AddWithValue("@startDate", subscription.StartDate);

                    if (subscription.Id == 0)
                        subscriptionId = Convert.ToInt32(_command.ExecuteScalar());
                    else
                    {
                        _command.ExecuteNonQuery();
                        subscriptionId = subscription.Id;
                    }

                    if (subscription.Id > 0)
                    {
                        _command.Parameters.Clear();
                        _command.CommandText = deleteSubscriptionDetailQuery;
                        _command.Parameters.AddWithValue("@subscriptionId", subscription.Id);
                        _command.ExecuteNonQuery();
                    }

                    _command.CommandText = insertSubscriptionDetailQuery;

                    foreach (SubscriptionDetail subscriptionDetail in subscription.SubscriptionDetails)
                    {
                        subscriptionDetail.SubscriptionId = subscriptionId;

                        _command.Parameters.Clear();
                        _command.Parameters.AddWithValue("@subscriptionId", subscriptionDetail.SubscriptionId);
                        _command.Parameters.AddWithValue("@price", subscriptionDetail.Price);
                        _command.Parameters.AddWithValue("@pricePln", subscriptionDetail.PricePln);
                        _command.Parameters.AddWithValue("@year", subscriptionDetail.Year);
                        _command.Parameters.AddWithValue("@month", subscriptionDetail.Month);

                        _command.ExecuteScalar();
                    }
                }

                _transaction.Commit();
            }
            catch (Exception) 
            {
                _transaction.Rollback();
                throw;
            }
            finally
            {
                CleanUp();
            }
        }

        public List<Subscription> FindSubscriptions(int customerId)
        {
            string query = "SELECT Id, Guid, CustomerId, SubscriptionTypeId, SubscriptionItems, BasePrice, Discount, Vat, TotalPrice, Currency, OrderId, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, StartDate, InvoiceId FROM Subscription WHERE CustomerId={0} ORDER BY UpdatedOn DESC";
            query = string.Format(query, customerId);

            List<Subscription> subscriptions = new List<Subscription>();

            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);

                _reader = _command.ExecuteReader();
                if (_reader != null)
                {
                    while (_reader.Read())
                    {
/*
                        Subscription subscription = new Subscription(_reader.GetInt32(0));
                        subscription.Guid = _reader.GetGuid(1);
                        subscription.CustomerId = _reader.GetInt32(2);
                        subscription.SubscriptionTypeId = _reader.GetInt32(3);
                        subscription.SubscriptionItems = _reader.GetInt32(4);
                        subscription.BasePrice = _reader.GetDecimal(5);
                        subscription.Discount = _reader.GetDecimal(6);
                        subscription.Vat = _reader.GetDecimal(7);
                        subscription.TotalPrice = _reader.GetDecimal(8);
                        subscription.Currency = _reader.GetString(9);
                        if (_reader[10] != DBNull.Value)
                            subscription.OrderId = _reader.GetInt32(10);
                        subscription.CreatedOn = _reader.GetDateTime(11);
                        subscription.CreatedBy = _reader.GetInt32(12);
                        subscription.UpdatedOn = _reader.GetDateTime(13);
                        subscription.UpdatedBy = _reader.GetInt32(14);
                        subscription.StartDate = _reader.GetDateTime(15);
                        if (_reader[16] != DBNull.Value)
                            subscription.InvoiceId = _reader.GetInt32(16);
                        subscriptions.Add(subscription);
*/
                        subscriptions.Add(SetSubscription(_reader));
                    }
                }
            }
            finally
            {
                CleanUp();
            }

            return subscriptions;
        }

        public List<IProduct> FindProductsWithoutOrders(int customerId)
        {
            List<IProduct> products = new List<IProduct>();

            const string query = "sp_GetProductsWithoutOrder";

            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                _command.Parameters.AddWithValue("@customerId", customerId);
                _command.CommandType = CommandType.StoredProcedure;
                _command.CommandTimeout = 5 * 60;

                _reader = _command.ExecuteReader();
                if (_reader != null)
                {
                    while (_reader.Read())
                    {
                        Product product = new Product();
                        product.Id = _reader.GetInt32(0);
                        product.TitleId = _reader.GetInt32(1);
                        product.Type = _reader.GetString(2);
                        product.ProductType = (ProductType)_reader.GetInt32(3);
                        product.ShortName = _reader.GetString(4);
                        if (_reader[5] != DBNull.Value)
                            product.StartDate = _reader.GetDateTime(5);
                        product.Price = _reader.GetDecimal(6);
                        product.TotalPrice = _reader.GetDecimal(7);
                        product.Vat = _reader.GetDecimal(8);
                        product.Currency = _reader.GetString(9);
                        product.IsActivated = _reader.GetBoolean(10);
                        product.HasInvoice = _reader.GetBoolean(11);                        

                        if (_reader[12].GetType() == typeof(System.DBNull))
                            product.DetailCount = 0;
                        else
                            product.DetailCount = _reader.GetInt32(12);

                        product.Discount = _reader.GetDecimal(13);

                        products.Add(product);
                    }
                }
            }
            finally
            {
                CleanUp();
            }

            return products;
        }

        public List<IProduct> FindProductsWithOrders(int clientId)
        {
            List<IProduct> products = new List<IProduct>();

            const string query = "sp_GetProductsWithOrder";

            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                _command.Parameters.AddWithValue("@clientId", clientId);
                _command.CommandType = CommandType.StoredProcedure;

                _reader = _command.ExecuteReader();
                if (_reader != null)
                {
                    while (_reader.Read())
                    {
                        Product product = new Product();
                        product.Id = _reader.GetInt32(0);
                        product.TitleId = _reader.GetInt32(1);
                        product.Type = _reader.GetString(2);
                        product.ProductType = (ProductType) _reader.GetInt32(3);
                        product.ShortName = _reader.GetString(4);
                        //TODO: check datetime
                        if (!_reader.IsDBNull(5))
                            product.StartDate = _reader.GetDateTime(5);
                        product.Price = _reader.GetDecimal(6);
                        product.TotalPrice = _reader.GetDecimal(7);
                        product.Vat = _reader.GetDecimal(8);
                        product.Currency = _reader.GetString(9);
                        product.IsActivated = _reader.GetBoolean(10);
                        if (_reader[11] != DBNull.Value)
                            product.OrderId = _reader.GetInt32(11);
                        product.HasInvoice = _reader.GetBoolean(12);

                        if (_reader[13].GetType() == typeof(System.DBNull))
                            product.DetailCount = 0;
                        else
                            product.DetailCount = _reader.GetInt32(13);

                        product.Discount = _reader.GetDecimal(14);

                        products.Add(product);
                    }
                }
            }
            finally
            {
                CleanUp();
            }

            return products;
        }

        public void RemoveProductFromOrder(IProduct product)
        {
            const string query = "sp_RemoveProductFromOrder";
            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                _command.Parameters.AddWithValue("@productId", product.Id);
                _command.Parameters.AddWithValue("@productType", product.ProductType);
                _command.CommandType = CommandType.StoredProcedure;

                _command.ExecuteNonQuery();
            }
            finally
            {
                CleanUp();
            }
        }

        public void AddProductToOrder(IProduct product)
        {
            const string query = "sp_AddProductToOrder";
            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                _command.Parameters.AddWithValue("@productId", product.Id);
                _command.Parameters.AddWithValue("@productType", product.ProductType);
                _command.Parameters.AddWithValue("@orderId", product.OrderId);
                _command.CommandType = CommandType.StoredProcedure;

                _command.ExecuteNonQuery();
            }
            finally
            {
                CleanUp();
            }
        }

        public void RemoveSubscription(int subscriptionId)
        {
            const string queryDetails = "DELETE FROM SubscriptionDetail WHERE SubscriptionId=@subscriptionId";
            const string query = "DELETE FROM Subscription WHERE Id=@subscriptionId";

            try
            {
                _connection.Open();
                _command = _connection.CreateCommand();

                _command.CommandText = query;
                _command.Parameters.AddWithValue("@subscriptionId", subscriptionId);
                int n = _command.ExecuteNonQuery();

                _command.CommandText = queryDetails;
                n = _command.ExecuteNonQuery();
            }
            finally
            {
                CleanUp();
            }
        }

        private Subscription SetSubscription(SqlDataReader rdr)
        {
            Subscription subscription = new Subscription(rdr.GetInt32(0));
            subscription.Guid = rdr.GetGuid(1);
            subscription.CustomerId = rdr.GetInt32(2);
            subscription.SubscriptionTypeId = rdr.GetInt32(3);
            subscription.SubscriptionItems = rdr.GetInt32(4);
            subscription.BasePrice = rdr.GetDecimal(5);
            subscription.Discount = rdr.GetDecimal(6);
            subscription.Vat = rdr.GetDecimal(7);
            subscription.TotalPrice = rdr.GetDecimal(8);
            subscription.Currency = rdr.GetString(9);
            if (rdr[10] != DBNull.Value)
                subscription.OrderId = rdr.GetInt32(10);
            subscription.CreatedOn = rdr.GetDateTime(11);
            subscription.CreatedBy = rdr.GetInt32(12);
            subscription.UpdatedOn = rdr.GetDateTime(13);
            subscription.UpdatedBy = rdr.GetInt32(14);
            subscription.StartDate = rdr.GetDateTime(15);
            if (rdr[16] != DBNull.Value)
                subscription.InvoiceId = rdr.GetInt32(16);

            return subscription;
        }

        public Subscription GetSubscription(int subscrId)
        {
            string query = "SELECT Id, Guid, CustomerId, SubscriptionTypeId, SubscriptionItems, BasePrice, Discount, Vat, TotalPrice, Currency, OrderId, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, StartDate, InvoiceId FROM Subscription WHERE Id={0}";
            query = string.Format(query, subscrId);

            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);

                _reader = _command.ExecuteReader();
                if (_reader != null)
                    if (_reader.Read())
                        return SetSubscription(_reader);
            }
            finally
            {
                CleanUp();
            }

            return null;
        }
    }
}