using Dapper;
using Sonex.Data.Database;
using System.Text;

namespace Sonex.Data.Records;

public sealed class ProductRecord
{
    public string Artnr { get; set; } = string.Empty;
    public string? ArticleName { get; set; }
    public string? SalesClass { get; set; }
    public DateTime? ArticleDimensionsChecked { get; set; }
    public DateTime? LastReceived { get; set; }
    public string? BestBeforeDateUsed { get; set; }
    public string? BestBeforeDateMix { get; set; }

    public bool Packsize { get; set; }
    public bool ShipUnit { get; set; }
    public bool? Dpd { get; set; }
    public bool? Bk { get; set; }
    public decimal? PriceNl { get; set; }
    public decimal? PriceDe { get; set; }
    public decimal? PriceHc { get; set; }

    public string? PackagingSu { get; set; }
    public int? SuQuantity { get; set; }
    public decimal? SuWeightGross { get; set; }
    public decimal? SuWeightNet { get; set; }
    public decimal? SuLength { get; set; }
    public decimal? SuHeight { get; set; }
    public decimal? SuWidth { get; set; }
    public decimal? SuVolume { get; set; }
    public int? SuStackLimit { get; set; }
    public decimal? SuNestVolume { get; set; }
    public int? SuNestMax { get; set; }
    public string? SuStackability { get; set; }
    public string? SuFragile { get; set; }
    public string? SuKeepUpright { get; set; }

    public string? PackagingPu { get; set; }
    public int? PuQuantity { get; set; }
    public decimal? PuWeightGross { get; set; }
    public decimal? PuWeightNet { get; set; }
    public decimal? PuLength { get; set; }
    public decimal? PuHeight { get; set; }
    public decimal? PuWidth { get; set; }
    public decimal? PuVolume { get; set; }
    public int? PuStackLimit { get; set; }
    public decimal? PuNestVolume { get; set; }
    public int? PuNestMax { get; set; }
    public string? PuStackability { get; set; }
    public string? PuFragile { get; set; }
    public string? PuKeepUpright { get; set; }

    public string? PackagingHn { get; set; }
    public int? HnQuantity { get; set; }
    public decimal? HnWeightGross { get; set; }
    public decimal? HnWeightNet { get; set; }
    public decimal? HnLength { get; set; }
    public decimal? HnHeight { get; set; }
    public decimal? HnWidth { get; set; }
    public decimal? HnVolume { get; set; }
    public int? HnStackLimit { get; set; }
    public decimal? HnNestVolume { get; set; }
    public int? HnNestMax { get; set; }
    public string? HnStackability { get; set; }
    public string? HnFragile { get; set; }
    public string? HnKeepUpright { get; set; }

    public string? SuEans { get; set; }
    public string? PuEans { get; set; }
    public string? HnEans { get; set; }
    public string? ArticleParametersText { get; set; }
    public string? DcGroup { get; set; }
    public string? WebCluster { get; set; }
    public string? WebSalesClass { get; set; }
    public double? WebSalesShare { get; set; }

    public bool Archival { get; set; }
    public DateTime? LastEventDate { get; set; }

    public string Url { get; set; } = string.Empty;
    public string Title { get; set; } = string.Empty;
    public string Keywords { get; set; } = string.Empty;
    public string Notification { get; set; } = string.Empty;
    public string PromoText { get; set; } = string.Empty;
    public string StockMessage { get; set; } = string.Empty;
    public string Category { get; set; } = string.Empty;
    public string RootCategory { get; set; } = string.Empty;
    public string LastCategory { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public string Material { get; set; } = string.Empty;
    public string Color { get; set; } = string.Empty;
    public string Series { get; set; } = string.Empty;
    public string WebsiteStatus { get; set; } = string.Empty;
    public int? MinimumOrderQuantity { get; set; }
    public bool OnlineOnly { get; set; }
    public bool CanOrder { get; set; }
    public bool? IsTrending { get; set; }
    public bool? IsNieuw { get; set; }
    public bool? IsActies { get; set; }
    public DateTime? WebInfoUpdatedAt { get; set; }

    public static Task<DB.SingleResult<ProductRecord>> Get(string artnr)
    {
        return DB.QuerySingleAsync<ProductRecord>(
            """
            SELECT *
            FROM sonex.products
            WHERE artnr = @artnr
            LIMIT 1;
            """,
            new { artnr });
    }

    public static Task<DB.SingleResult<ProductRecord>> GetBasicInformation(
        string artnr,
        CancellationToken ct = default)
    {
        return DB.QuerySingleAsync<ProductRecord>(
            """
            SELECT
                artnr,
                article_name,
                su_eans,
                category,
                article_parameters_text,
                stock_message,
                notification,
                promo_text,
                archival,
                best_before_date_used,
                best_before_date_mix,
                packsize,
                ship_unit,
                bk,
                dpd,
                sales_class,
                dc_group,
                web_cluster,
                web_sales_class,
                web_sales_share,
                material,
                color,
                series,
                minimum_order_quantity,
                root_category,
                last_category,
                online_only,
                can_order,
                is_trending,
                is_nieuw,
                is_acties,
                description,
                price_nl,
                price_de,
                price_hc
            FROM sonex.products
            WHERE artnr = @artnr
            LIMIT 1;
            """,
            new { artnr },
            ct: ct);
    }

    public static Task<DB.SingleResult<ProductRecord>> GetDimensionsInformation(
        string artnr,
        CancellationToken ct = default)
    {
        return DB.QuerySingleAsync<ProductRecord>(
            """
            SELECT
                artnr,
                article_name,
                article_dimensions_checked,
                packaging_su,
                su_quantity,
                su_weight_gross,
                su_weight_net,
                su_length,
                su_height,
                su_width,
                su_volume,
                su_stack_limit,
                su_nest_volume,
                su_nest_max,
                su_stackability,
                su_fragile,
                su_keep_upright,
                packaging_pu,
                pu_quantity,
                pu_weight_gross,
                pu_weight_net,
                pu_length,
                pu_height,
                pu_width,
                pu_volume,
                pu_stack_limit,
                pu_nest_volume,
                pu_nest_max,
                pu_stackability,
                pu_fragile,
                pu_keep_upright,
                packaging_hn,
                hn_quantity,
                hn_weight_gross,
                hn_weight_net,
                hn_length,
                hn_height,
                hn_width,
                hn_volume,
                hn_stack_limit,
                hn_nest_volume,
                hn_nest_max,
                hn_stackability,
                hn_fragile,
                hn_keep_upright
            FROM sonex.products
            WHERE artnr = @artnr
            LIMIT 1;
            """,
            new { artnr },
            ct: ct);
    }

    public static Task<DB.Result<ProductRecord>> GetAll(int limit, CancellationToken ct = default)
    {
        return GetAll(limit, null, null, ct);
    }

    public static Task<DB.Result<ProductRecord>> GetAll(
        int limit,
        string? whereSql,
        IReadOnlyDictionary<string, object?>? whereParameters,
        CancellationToken ct = default)
    {
        var sql = new StringBuilder();
        sql.AppendLine(
            """
            SELECT *
            FROM sonex.products
            """);

        if (!string.IsNullOrWhiteSpace(whereSql))
        {
            sql.AppendLine();
            sql.Append("WHERE ");
            sql.AppendLine(whereSql.Trim());
        }

        sql.AppendLine(
            """
            ORDER BY artnr ASC
            LIMIT @limit;
            """);

        var parameters = new DynamicParameters();
        parameters.Add("limit", limit);
        if (whereParameters != null)
        {
            foreach (var item in whereParameters)
            {
                parameters.Add(item.Key, item.Value);
            }
        }

        return DB.QueryListAsync<ProductRecord>(
            sql.ToString(),
            parameters,
            ct: ct);
    }
}