CREATE OR ALTER FUNCTION pricing.guidance_logic (
    @target_price NUMERIC(20,5),
    @last_price   NUMERIC(20,5),
    @list_price   NUMERIC(20,5),
    @last_date    DATE
)
RETURNS @result TABLE (
    guidance_price NUMERIC(20,5),
    guidance_reason NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @price NUMERIC(20,5);
    DECLARE @reason NVARCHAR(MAX) = '';
    DECLARE @floored NUMERIC(20,5);
    DECLARE @capped  NUMERIC(20,5);
    DECLARE @use_last_price BIT = 0;

    -- Determine if last price is recent (within last 2 years)
    IF @last_price IS NOT NULL AND @last_date IS NOT NULL AND @last_date > DATEADD(YEAR, -2, CAST(GETDATE() AS DATE))
        SET @use_last_price = 1;

    IF @target_price IS NOT NULL AND @use_last_price = 1
    BEGIN
        SET @floored = dbo.GREATEST_NUMERIC205(@target_price, @last_price * 0.95);
        SET @capped  = dbo.LEAST_NUMERIC205(@floored, @last_price);
        SET @price   = dbo.LEAST_NUMERIC205(ISNULL(@list_price, 1e9), @capped);

        IF @price = @last_price
        BEGIN
            SET @reason = 'Cap at last price';
        END
        ELSE
        BEGIN
            SET @reason = 'Using target price';
            IF @target_price < @last_price * 0.95
                SET @reason += ', floored to 5% below last price';
            IF @target_price > @last_price
                SET @reason += ', capped to not exceed last price';
            IF @list_price IS NOT NULL AND @price = @list_price AND @target_price > @list_price
                SET @reason += ', capped to not exceed list price';
        END
    END
    ELSE IF @use_last_price = 1
    BEGIN
        SET @price = @last_price;
        SET @reason = 'Last price - no target';
    END
    ELSE IF @target_price IS NOT NULL
    BEGIN
        SET @price = @target_price;
        IF @last_price IS NOT NULL AND @last_date IS NOT NULL
        BEGIN
            SET @reason = CONCAT(
                'Last price ignored (too old: ', 
                CONVERT(NVARCHAR(10), @last_date, 120), 
                '), using target price'
            );
        END
        ELSE
        BEGIN
            SET @reason = 'Target price - no prior sale';
        END
    END
    ELSE
    BEGIN
        SET @price = NULL;
        IF @last_price IS NOT NULL AND @last_date IS NOT NULL
        BEGIN
            SET @reason = CONCAT(
                'Last price ignored (too old: ', 
                CONVERT(NVARCHAR(10), @last_date, 120), 
                '), no pricing available'
            );
        END
        ELSE
        BEGIN
            SET @reason = 'No pricing available';
        END
    END

    INSERT INTO @result VALUES (@price, @reason);
    RETURN;
END