using KarleyLibrary.Erweiterungen;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace WK5.Core.OpenCart
{
    public class ProductSpecial
    {
        #region Properties
        public int product_special_id { get; set; }
        public int product_id { get; set; }
        public int customer_group_id { get; set; }
        public int priority { get; set; }
        public decimal price { get; set; }
        public DateTime date_start { get; set; }
        public DateTime date_end { get; set; }
        #endregion

        public static async Task<ProductSpecial?> GetProductSpecialAsync(int product_id, int customer_group_id, string connectionString)
        {
            using MySqlController2 _mySqlController = new MySqlController2(connectionString);
            _mySqlController.AddParameter("@PRODUCT_ID", product_id);
            _mySqlController.AddParameter("@CUSTOMER_GROUP_ID", customer_group_id);
            var row = await _mySqlController.SelectRowAsync("SELECT * FROM `product_special` WHERE product_id = @PRODUCT_ID AND customer_group_id = @CUSTOMER_GROUP_ID");
            return row == null ? null : ObjectErweiterung.DataRowZuObjekt(new ProductSpecial(), row);
        }

        public static async IAsyncEnumerable<ProductSpecial> GetProductSpecialsAsync(int product_id, string connectionString)
        {
            using MySqlController2 _mySqlController = new MySqlController2(connectionString);
            _mySqlController.AddParameter("@PRODUCT_ID", product_id);
            DataTable data = await _mySqlController.SelectDataAsync("SELECT * FROM `product_special` WHERE product_id = @PRODUCT_ID");
            foreach (DataRow row in data.Rows)
            {
                if (row is not null)
                {
                    yield return ObjectErweiterung.DataRowZuObjekt(new ProductSpecial(), row);
                }
            }
        }

        public static async Task UpdateProductSpecial(ProductSpecial special, string connectionString)
        {
            using MySqlController2 mySqlController = new MySqlController2(connectionString);

            string sql = @"UPDATE `product_special` SET 
product_id = @product_id,
customer_group_id = @customer_group_id,
priority = @priority,
price = @price,
date_start = @date_start,
date_end = @date_end
WHERE product_special_id = @product_special_id";

            mySqlController.AddParameter("@product_id", special.product_id);
            mySqlController.AddParameter("@customer_group_id", special.customer_group_id);
            mySqlController.AddParameter("@priority", special.priority);
            mySqlController.AddParameter("@price", special.price);
            mySqlController.AddParameter("@date_start", "0000-00-00");
            mySqlController.AddParameter("@date_end", (special.date_end == default(DateTime) ? "0000-00-00" : special.date_end));
            mySqlController.AddParameter("@product_special_id", special.product_special_id);

            await mySqlController.QueryAsync(sql);
        }

        public static async Task<ProductSpecial> CreateProductAsync(ProductSpecial special, string connectionString)
        {
            using MySqlController2 mySqlController = new MySqlController2(connectionString);

            string sql = @"INSERT INTO `product_special` 
(product_id, customer_group_id, priority, price, date_start, date_end)
VALUES
(@product_id, @customer_group_id, @priority, @price, @date_start, @date_end)";


            mySqlController.AddParameter("@product_id", special.product_id);
            mySqlController.AddParameter("@customer_group_id", special.customer_group_id);
            mySqlController.AddParameter("@priority", special.priority);
            mySqlController.AddParameter("@price", special.price);
            mySqlController.AddParameter("@date_start", "0000-00-00");
            mySqlController.AddParameter("@date_end", (special.date_end == default(DateTime) ? "0000-00-00" : special.date_end));


            await mySqlController.QueryAsync(sql);

            object tmpId = await mySqlController.FetchObjectAsync("SELECT LAST_INSERT_ID()");

            if (tmpId != null)
            {
                special.product_special_id = Convert.ToInt32(tmpId);
            }

            return special;


        }


        public static async Task DeleteProductSpecialAsync(int product_special_id, string connectionString)
        {
            string sql = @"DELETE FROM `product_special` WHERE product_special_id = @product_special_id";

            using MySqlController2 mySqlController = new MySqlController2(connectionString);

            mySqlController.AddParameter("@product_special_id", product_special_id);

            await mySqlController.QueryAsync(sql);
        }

        public static async Task DeleteProductSpecialsAsync(int product_id, string connectionString)
        {
            string sql = @"DELETE FROM `product_special` WHERE product_id = @product_id";

            using MySqlController2 mySqlController = new MySqlController2(connectionString);

            mySqlController.AddParameter("@product_id", product_id);

            await mySqlController.QueryAsync(sql);
        }
    }
}
