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 insertSubscriptionDetailQuery = "INSERT INTO SubscriptionDetail(SubscriptionId, Price, PricePln, [Year], [Month]) "
                                                         + "VALUES(@subscriptionId, @price, @pricePln, @year, @month) "
                                                         + "SELECT SCOPE_IDENTITY();";

            try
            {
                _connection.Open();
                _transaction = _connection.BeginTransaction();
                _command = new SqlCommand(insertSubscriptionQuery, _connection);
                _command.Transaction = _transaction;

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

                    _command.Parameters.AddWithValue("@guid", subscription.Guid);
                    _command.Parameters.AddWithValue("@customerId", customer.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("@createdOn", subscription.CreatedOn);
                    _command.Parameters.AddWithValue("@createdBy", subscription.CreatedBy);
                    _command.Parameters.AddWithValue("@updatedOn", subscription.UpdatedOn);
                    _command.Parameters.AddWithValue("@updatedBy", subscription.UpdatedBy);
                    _command.Parameters.AddWithValue("@startDate", subscription.StartDate);

                    int subscriptionId = Convert.ToInt32(_command.ExecuteScalar());

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

                        SqlCommand sqlCommand = new SqlCommand(insertSubscriptionDetailQuery, _connection);
                        sqlCommand.Transaction = _transaction;
                        sqlCommand.Parameters.AddWithValue("@subscriptionId", subscriptionDetail.SubscriptionId);
                        sqlCommand.Parameters.AddWithValue("@price", subscriptionDetail.Price);
                        sqlCommand.Parameters.AddWithValue("@pricePln", subscriptionDetail.PricePln);
                        sqlCommand.Parameters.AddWithValue("@year", subscriptionDetail.Year);
                        sqlCommand.Parameters.AddWithValue("@month", subscriptionDetail.Month);

                        sqlCommand.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 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);

                        subscriptions.Add(subscription);
                    }
                }
            }
            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;

                _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);

                        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);
                        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);

                        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();
            }
        }

    }
}