using Sonex.Data.Database;

namespace Sonex.Data.Records;

public sealed class ProductWebInfoRecord
{
    public string Artnr { get; set; } = string.Empty;
    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 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 bool BK { get => Notification.StartsWith("Thuisbezorgd voor", StringComparison.OrdinalIgnoreCase); }
    public bool DPD { get => !BK; }
    public DateTime? WebInfoUpdatedAt { get; set; }

    public static Task<DB.SingleResult<ProductWebInfoRecord>> Get(
        string artnr,
        CancellationToken ct = default)
    {
        return DB.QuerySingleAsync<ProductWebInfoRecord>(
            """
            SELECT
                artnr,
                url,
                title,
                keywords,
                notification,
                promo_text,
                stock_message,
                category,
                root_category,
                last_category,
                description,
                material,
                color,
                series,
                minimum_order_quantity,
                online_only,
                can_order,
                is_trending,
                is_nieuw,
                is_acties,
                web_info_updated_at,
                bk,
                dpd
            FROM sonex.products
            WHERE artnr = @artnr
            LIMIT 1;
            """,
            new
            {
                artnr = Normalize(artnr) ?? string.Empty
            },
            ct: ct);
    }

    public async Task<DB.SingleResult<bool>> Update(CancellationToken ct = default)
    {
        return await DB.QuerySingleAsync<bool>(
            """
            UPDATE sonex.products
            SET
                url = @Url,
                title = @Title,
                category = @Category,
                root_category = @RootCategory,
                last_category = @LastCategory,
                keywords = @Keywords,
                description = @Description,
                material = @Material,
                color = @Color,
                series = @Series,
                minimum_order_quantity = @MinimumOrderQuantity,
                online_only = @OnlineOnly,
                can_order = @CanOrder,
                is_trending = @IsTrending,
                is_nieuw = @IsNieuw,
                is_acties = @IsActies,
                notification = @Notification,
                promo_text = @PromoText,
                bk = @BK,
                dpd = @DPD,
                stock_message = @StockMessage,
                web_info_updated_at = now()
            WHERE artnr = @Artnr
            RETURNING TRUE;
            """,
            BuildParameters(),
            ct: ct).ConfigureAwait(false);
    }

    public static async Task<DB.SingleResult<int>> ClearOlderThan(
        DateTime updatedOlderThan,
        CancellationToken ct = default)
    {
        return await DB.QuerySingleAsync<int>(
            """
            WITH updated AS (
                UPDATE sonex.products
                SET
                    url = '',
                    title = '',
                    category = '',
                    root_category = '',
                    last_category = '',
                    keywords = '',
                    description = '',
                    material = '',
                    color = '',
                    series = '',
                    minimum_order_quantity = NULL,
                    online_only = FALSE,
                    can_order = FALSE,
                    is_trending = NULL,
                    is_nieuw = NULL,
                    is_acties = NULL,
                    notification = '',
                    promo_text = '',
                    stock_message = '',
                    dpd = NULL,
                    bk = NULL,
                    web_info_updated_at = NULL
                WHERE web_info_updated_at < @UpdatedOlderThan
                RETURNING 1
            )
            SELECT COUNT(*)::int
            FROM updated;
            """,
            new
            {
                UpdatedOlderThan = updatedOlderThan
            },
            ct: ct).ConfigureAwait(false);
    }

    public async Task<DB.SingleResult<bool>> Upsert(CancellationToken ct = default)
    {
        return await DB.QuerySingleAsync<bool>(
            """
            INSERT INTO sonex.products
            (
                artnr,
                url,
                title,
                category,
                root_category,
                last_category,
                keywords,
                description,
                material,
                color,
                series,
                minimum_order_quantity,
                online_only,
                can_order,
                is_trending,
                is_nieuw,
                is_acties,
                notification,
                promo_text,
                bk,
                dpd,
                stock_message,
                web_info_updated_at
            )
            VALUES
            (
                @Artnr,
                @Url,
                @Title,
                @Category,
                @RootCategory,
                @LastCategory,
                @Keywords,
                @Description,
                @Material,
                @Color,
                @Series,
                @MinimumOrderQuantity,
                @OnlineOnly,
                @CanOrder,
                @IsTrending,
                @IsNieuw,
                @IsActies,
                @Notification,
                @PromoText,
                @BK,
                @DPD,
                @StockMessage,
                now()
            )
            ON CONFLICT (artnr) DO UPDATE
            SET
                url = EXCLUDED.url,
                title = EXCLUDED.title,
                category = EXCLUDED.category,
                root_category = EXCLUDED.root_category,
                last_category = EXCLUDED.last_category,
                keywords = EXCLUDED.keywords,
                description = EXCLUDED.description,
                material = EXCLUDED.material,
                color = EXCLUDED.color,
                series = EXCLUDED.series,
                minimum_order_quantity = EXCLUDED.minimum_order_quantity,
                online_only = EXCLUDED.online_only,
                can_order = EXCLUDED.can_order,
                is_trending = EXCLUDED.is_trending,
                is_nieuw = EXCLUDED.is_nieuw,
                is_acties = EXCLUDED.is_acties,
                notification = EXCLUDED.notification,
                promo_text = EXCLUDED.promo_text,
                bk = EXCLUDED.bk,
                dpd = EXCLUDED.dpd,
                stock_message = EXCLUDED.stock_message,
                web_info_updated_at = now()
            RETURNING TRUE;
            """,
            BuildParameters(),
            ct: ct).ConfigureAwait(false);
    }

    public static Task<DB.Result<StaleWebInfoCandidateRecord>> GetStaleCandidates(
        DateTime updatedOlderThan,
        CancellationToken ct = default)
    {
        return DB.QueryListAsync<StaleWebInfoCandidateRecord>(
            """
            SELECT
                artnr,
                url,
                web_info_updated_at
            FROM sonex.products
            WHERE web_info_updated_at < @UpdatedOlderThan
              AND COALESCE(url, '') <> ''
            ORDER BY web_info_updated_at ASC, artnr ASC;
            """,
            new
            {
                UpdatedOlderThan = updatedOlderThan
            },
            ct: ct);
    }

    public static Task<DB.Result<UpdatedWebInfoCandidateRecord>> GetUpdatedCandidates(
        DateTime updatedAtOrAfter,
        CancellationToken ct = default)
    {
        return DB.QueryListAsync<UpdatedWebInfoCandidateRecord>(
            """
            SELECT
                artnr,
                url,
                web_info_updated_at
            FROM sonex.products
            WHERE web_info_updated_at >= @UpdatedAtOrAfter
              AND COALESCE(url, '') <> ''
            ORDER BY web_info_updated_at ASC, artnr ASC;
            """,
            new
            {
                UpdatedAtOrAfter = updatedAtOrAfter
            },
            ct: ct);
    }

    public static async Task<DB.SingleResult<int>> UpdateCategoryCollectionFlags(
        IReadOnlyCollection<string> trendingArticleNumbers,
        IReadOnlyCollection<string> nieuwArticleNumbers,
        IReadOnlyCollection<string> actiesArticleNumbers,
        CancellationToken ct = default)
    {
        ArgumentNullException.ThrowIfNull(trendingArticleNumbers);
        ArgumentNullException.ThrowIfNull(nieuwArticleNumbers);
        ArgumentNullException.ThrowIfNull(actiesArticleNumbers);

        string[] trending = NormalizeDistinctArticleNumbers(trendingArticleNumbers);
        string[] nieuw = NormalizeDistinctArticleNumbers(nieuwArticleNumbers);
        string[] acties = NormalizeDistinctArticleNumbers(actiesArticleNumbers);

        return await DB.QuerySingleAsync<int>(
            """
            WITH updated AS (
                UPDATE sonex.products
                SET
                    is_trending = CASE
                        WHEN COALESCE(url, '') = '' THEN NULL
                        ELSE artnr = ANY(@Trending)
                    END,
                    is_nieuw = CASE
                        WHEN COALESCE(url, '') = '' THEN NULL
                        ELSE artnr = ANY(@Nieuw)
                    END,
                    is_acties = CASE
                        WHEN COALESCE(url, '') = '' THEN NULL
                        ELSE artnr = ANY(@Acties)
                    END
                RETURNING 1
            )
            SELECT COUNT(*)::int
            FROM updated;
            """,
            new
            {
                Trending = trending,
                Nieuw = nieuw,
                Acties = acties
            },
            ct: ct).ConfigureAwait(false);
    }

    private object BuildParameters()
    {
        return new
        {
            Artnr = Normalize(Artnr) ?? string.Empty,
            Url = Normalize(Url) ?? string.Empty,
            Title = Normalize(Title) ?? string.Empty,
            Category = Normalize(Category) ?? string.Empty,
            RootCategory = Normalize(RootCategory) ?? string.Empty,
            LastCategory = Normalize(LastCategory) ?? string.Empty,
            Keywords = Normalize(Keywords) ?? string.Empty,
            Description = Normalize(Description) ?? string.Empty,
            Material = Normalize(Material) ?? string.Empty,
            Color = Normalize(Color) ?? string.Empty,
            Series = Normalize(Series) ?? string.Empty,
            MinimumOrderQuantity,
            OnlineOnly,
            CanOrder,
            IsTrending,
            IsNieuw,
            IsActies,
            Notification = Normalize(Notification) ?? string.Empty,
            PromoText = Normalize(PromoText) ?? string.Empty,
            StockMessage = Normalize(StockMessage) ?? string.Empty,
            BK = BK,
            DPD = DPD
        };
    }

    private static string[] NormalizeDistinctArticleNumbers(IReadOnlyCollection<string> articleNumbers)
    {
        return articleNumbers
            .Where(static value => !string.IsNullOrWhiteSpace(value))
            .Select(static value => value.Trim())
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToArray();
    }

    private static string? Normalize(string? value)
    {
        return string.IsNullOrWhiteSpace(value)
            ? null
            : value.Trim();
    }

    public sealed class StaleWebInfoCandidateRecord
    {
        public string Artnr { get; set; } = string.Empty;
        public string Url { get; set; } = string.Empty;
        public DateTime? WebInfoUpdatedAt { get; set; }
    }

    public sealed class UpdatedWebInfoCandidateRecord
    {
        public string Artnr { get; set; } = string.Empty;
        public string Url { get; set; } = string.Empty;
        public DateTime? WebInfoUpdatedAt { get; set; }
    }
}