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

namespace WK5.Core.OpenCart
{
    public class Product
    {
        private string _model = String.Empty;
        private string _sku = String.Empty;
        private string _upc = String.Empty;
        private string _ean = String.Empty;
        private string _jan = String.Empty;
        private string _isbn = String.Empty;
        private string _mpn = String.Empty;
        private string _location = String.Empty;
        private string? _image;

        #region Properties
        [CompareField("product_id")]
        public int ProductId { get; set; }
        [CompareField("quantity")]
        public int Quantity { get; set; }
        [CompareField("stock_status_id")]
        public int StockStatusId { get; set; }
        [CompareField("manufacturer_id")]
        public int ManufacturerId { get; set; }
        public int Points { get; set; }
        [CompareField("tax_class_id")]
        public int TaxClassId { get; set; }
        [CompareField("weight_class_id")]
        public int WeightClassId { get; set; }
        [CompareField("length_class_id")]
        public int LengthClassId { get; set; }
        public int Minimum { get; set; } = 1;
        [CompareField("sort_order")]
        public int SortOrder { get; set; }
        public int Viewed { get; set; }

        [CompareField("date_added")]
        public DateTime DateAdded { get; set; }
        [CompareField("date_modified")]
        public DateTime DateModified { get; set; }
        [CompareField("date_available")]
        public DateTime DateAvailable { get; set; }


        public string Model { get => _model; set => _model = value ?? String.Empty; }
        public string SKU { get => _sku; set => _sku = value ?? String.Empty; }
        public string UPC { get => _upc; set => _upc = value ?? String.Empty; }
        public string EAN { get => _ean; set => _ean = value ?? String.Empty; }
        public string JAN { get => _jan; set => _jan = value ?? String.Empty; }
        public string ISBN { get => _isbn; set => _isbn = value ?? String.Empty; }
        public string MPN { get => _mpn; set => _mpn = value ?? String.Empty; }
        public string Location { get => _location; set => _location = value ?? String.Empty; }
        public string? Image { get => _image; set => _image = value ?? String.Empty; }


        public decimal Price { get; set; } = 0.0m;
        public decimal Weight { get; set; } = 0.0m;
        public decimal Length { get; set; } = 0.0m;
        public decimal Width { get; set; } = 0.0m;
        public decimal Height { get; set; } = 0.0m;

        public bool Shipping { get; set; } = true;
        public bool Subtract { get; set; } = true;
        public bool Status { get; set; } = false;


        #endregion       

        internal string GetUpdateProperties(MySqlController2? mySqlController = null)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("quantity = @quantity,");
            sb.AppendLine("stock_status_id = @stock_status_id,");
            sb.AppendLine("manufacturer_id = @manufacturer_id,");
            sb.AppendLine("points = @points,");
            sb.AppendLine("tax_class_id = @tax_class_id,");
            sb.AppendLine("weight_class_id = @weight_class_id,");
            sb.AppendLine("length_class_id = @length_class_id,");
            sb.AppendLine("minimum = @minimum,");
            sb.AppendLine("sort_order = @sort_order,");
            sb.AppendLine("date_added = @date_added,");
            sb.AppendLine("date_modified = @date_modified,");
            sb.AppendLine("date_available = @date_available,");
            sb.AppendLine("model = @model,");
            sb.AppendLine("sku = @sku,");
            sb.AppendLine("upc = @upc,");
            sb.AppendLine("ean = @ean,");
            sb.AppendLine("jan = @jan,");
            sb.AppendLine("isbn = @isbn,");
            sb.AppendLine("mpn = @mpn,");
            sb.AppendLine("location = @location,");
            sb.AppendLine("image = @image,");
            sb.AppendLine("price = @price,");
            sb.AppendLine("weight = @weight,");
            sb.AppendLine("length = @length,");
            sb.AppendLine("width = @width,");
            sb.AppendLine("height = @height,");
            sb.AppendLine("shipping = @shipping,");
            sb.AppendLine("subtract = @subtract,");
            sb.AppendLine("status = @status,");


            if (mySqlController is not null)
            {
                mySqlController.AddParameter("@product_id", ProductId);
                mySqlController.AddParameter("@quantity", Quantity);
                mySqlController.AddParameter("@stock_status_id", StockStatusId);
                mySqlController.AddParameter("@manufacturer_id", ManufacturerId);
                mySqlController.AddParameter("@points", Points);
                mySqlController.AddParameter("@tax_class_id", TaxClassId);
                mySqlController.AddParameter("@weight_class_id", WeightClassId);
                mySqlController.AddParameter("@length_class_id", LengthClassId);
                mySqlController.AddParameter("@minimum", Minimum);
                mySqlController.AddParameter("@sort_order", SortOrder);
                mySqlController.AddParameter("@viewed", Viewed);
                mySqlController.AddParameter("@date_added", DateAdded);
                mySqlController.AddParameter("@date_modified", DateModified);
                mySqlController.AddParameter("@date_available", DateAvailable);
                mySqlController.AddParameter("@model", Model);
                mySqlController.AddParameter("@sku", SKU);
                mySqlController.AddParameter("@upc", UPC);
                mySqlController.AddParameter("@ean", EAN);
                mySqlController.AddParameter("@jan", JAN);
                mySqlController.AddParameter("@isbn", ISBN);
                mySqlController.AddParameter("@mpn", MPN);
                mySqlController.AddParameter("@location", Location);
                mySqlController.AddParameter("@image", Image);
                mySqlController.AddParameter("@price", Price);
                mySqlController.AddParameter("@weight", Weight);
                mySqlController.AddParameter("@length", Length);
                mySqlController.AddParameter("@width", Width);
                mySqlController.AddParameter("@height", Height);
                mySqlController.AddParameter("@shipping", Shipping);
                mySqlController.AddParameter("@subtract", Subtract);
                mySqlController.AddParameter("@status", Status);
            }

            return sb.ToString();
        }

        internal static async Task<T?> GetProductAsync<T>(T product, int product_id, string connectionString) where T : Product
        {
            using MySqlController2 mySqlController = new MySqlController2(connectionString);

            mySqlController.AddParameter("@product_id", product_id);
            DataRow? row = await mySqlController.SelectRowAsync("SELECT * FROM `product` WHERE product_id = @product_id");
            return row == null ? null : ObjectErweiterung.DataRowZuObjekt<T>(product, row);
        }

        internal static async Task<T?> GetProductAsync<T>(T product, string model, string connectionString) where T : Product
        {
            using MySqlController2 mySqlController = new MySqlController2(connectionString);

            mySqlController.AddParameter("@model", model.ToUpper());
            DataRow? row = await mySqlController.SelectRowAsync("SELECT * FROM `product` WHERE UPPER(model) = @model");
            return row == null ? null : ObjectErweiterung.DataRowZuObjekt<T>(product, row);
        }

        internal async IAsyncEnumerable<T> GetZubehörAsync<T>(MySqlController2 mySqlController) where T : Product
        {
            mySqlController.AddParameter("@product_id", ProductId);
            DataTable data = await mySqlController.SelectDataAsync("SELECT DISTINCT p.* FROM product_related pr LEFT JOIN product p ON pr.related_id = p.product_id WHERE pr.product_id = @product_id");

            foreach (DataRow row in data.Rows)
            {
                yield return ObjectErweiterung.DataRowZuObjekt(ObjectErweiterung.InstanceFromType<T>(), row);
            }
        }

        internal async Task DeleteZubehörAsync(int related_id, MySqlController2 mySqlController)
        {
            mySqlController.AddParameter("@product_id", ProductId);
            mySqlController.AddParameter("@related_id", related_id);
            await mySqlController.QueryAsync("DELETE FROM `product_related` WHERE product_id = @product_id and related_id = @related_id");
        }

        internal async Task AddZubehörAsync(int related_id, MySqlController2 mySqlController)
        {
            mySqlController.AddParameter("@product_id", ProductId);
            mySqlController.AddParameter("@related_id", related_id);
            await mySqlController.QueryAsync("INSERT INTO product_related (product_id, related_id) VALUES (@product_id, @related_id)");
        }

    }
}
