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

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

        public DateTime FindFirstDateForInvoice(List<IProduct> products)
        {
            string query = "SELECT  MIN(CAST(CAST([Year] AS varchar) + '-' + CAST([Month] AS varchar) + '-01' AS datetime)) "
                           + "FROM SubscriptionDetail PD INNER JOIN Subscription P ON P.Id=PD.SubscriptionId "
                           + "WHERE PD.InvoiceId IS NULL AND P.Id IN ({0}) ";
            query = string.Format(query, ProductsIdToCommaSeparatedList(products));
            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                object date = _command.ExecuteScalar();
                if (date != null && date != DBNull.Value)
                    return Convert.ToDateTime(date);
            }
            finally
            {
                CleanUp();
            }

            return DateTime.MinValue;
        }

        public List<IProduct> FindProductsToInvoice(List<IProduct> products, InvoicingType invoicingType, DateTime date)
        {
            string query = "SELECT PD.Id, P.Id, PD.Price, PD.PricePLN, P.Vat, PD.InvoiceId, [Year], [Month] FROM SubscriptionDetail PD "
                           + "INNER JOIN Subscription P ON P.Id=PD.SubscriptionId "
                           + "WHERE PD.InvoiceId IS NULL ";

            if (invoicingType == InvoicingType.OneMonth)
            {
                query += "AND PD.[Year] = YEAR(@date) AND PD.[Month]=MONTH(@date) ";
            }
            if (invoicingType == InvoicingType.ThreeMonths)
            {
                query +=
                    "AND ((PD.[Year] = YEAR(@date) AND PD.[Month]=MONTH(@date)) OR (PD.[Year] = YEAR(DATEADD(MONTH, 1, @date)) AND PD.[Month]=MONTH(DATEADD(MONTH, 1, @date))) OR PD.[Year] = YEAR(DATEADD(MONTH, 2, @date)) AND PD.[Month]=MONTH(DATEADD(MONTH, 2, @date))) ";
            }
            query += "AND P.Id IN ({0})";

            query = string.Format(query, ProductsIdToCommaSeparatedList(products));

            Debug.WriteLine(query);

            List<IProductDetail> productDetails = new List<IProductDetail>();

            try
            {
                _connection.Open();
                _command = new SqlCommand(query, _connection);
                if (invoicingType == InvoicingType.OneMonth || invoicingType == InvoicingType.ThreeMonths)
                    _command.Parameters.AddWithValue("@date", date);
                _reader = _command.ExecuteReader();
                if (_reader != null)
                {
                    while (_reader.Read())
                    {
                        IProductDetail productDetail = new ProductDetail();
                        productDetail.Id = _reader.GetInt32(0);
                        productDetail.ProductId = _reader.GetInt32(1);
                        productDetail.Price = _reader.GetDecimal(2);
                        productDetail.PricePln = _reader.GetDecimal(3);
                        productDetail.Vat = _reader.GetDecimal(4);
                        productDetail.Year = _reader.GetInt32(6);
                        productDetail.Month = _reader.GetInt32(7);

                        productDetails.Add(productDetail);
                    }
                }
            }
            finally
            {
                CleanUp();
            }

            List<IProduct> invoiceProducts = new List<IProduct>();
            foreach (IProduct product in products)
            {
                List<IProductDetail> invoiceProductDetails = productDetails.FindAll(delegate(IProductDetail pd)
                                                                                  { return pd.ProductId == product.Id; });
                foreach (IProductDetail productDetail in invoiceProductDetails)
                {
                    product.AddProductDetail(productDetail);  
                }
                invoiceProducts.Add(product);
            }

            return invoiceProducts;
        }

        private string ProductsIdToCommaSeparatedList(List<IProduct> products)
        {
            StringBuilder sb = new StringBuilder();
            foreach (IProduct product in products)
            {
                sb.Append(product.Id + ",");
            }

            return sb.ToString().Remove(sb.ToString().Length - 1);
        }

        public void UpdateInvoiceId(List<IProduct> products, int invoiceId)
        {
            string querySub = "UPDATE Subscription SET InvoiceId=@invoiceId WHERE Id IN ({0}) ";
            string querySubDet = "UPDATE SubscriptionDetail SET InvoiceId=@invoiceId WHERE SubscriptionId IN ({0}) ";

            try
            {
                string subscriptionIdList = "-1";
                foreach (IProduct pr in products)
                {
                    if (pr.ProductType == ProductType.Subscription)
                        subscriptionIdList += ", " + pr.Id;
                }

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

                _command.Parameters.AddWithValue("@invoiceId", invoiceId);

                _command.CommandText = string.Format(querySub, subscriptionIdList);
                _command.ExecuteNonQuery();

                _command.CommandText = string.Format(querySubDet, subscriptionIdList);
                _command.ExecuteNonQuery();
            }
            finally
            {
                CleanUp();
            }
        }

        public void ClearInvoiceId(List<IProduct> products)
        {
            string querySub = "UPDATE Subscription SET InvoiceId=NULL WHERE Id IN ({0}) ";
            string querySubDet = "UPDATE SubscriptionDetail SET InvoiceId=NULL WHERE SubscriptionId IN ({0}) ";

            try
            {
                string subscriptionIdList = "-1";
                foreach (IProduct pr in products)
                {
                    if (pr.ProductType == ProductType.Subscription)
                    {
                        pr.HasInvoice = false;
                        ((Subscription)pr).InvoiceId = 0;
                        subscriptionIdList += ", " + pr.Id;
                    }
                }

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

                _command.CommandText = string.Format(querySub, subscriptionIdList);
                _command.ExecuteNonQuery();

                _command.CommandText = string.Format(querySubDet, subscriptionIdList);
                _command.ExecuteNonQuery();
            }
            finally
            {
                CleanUp();
            }
        }
    }
}