/**
 * CHANGELOG.md:
 *
 * - A function was added to retrieve the number of months within a `tstzrange`:
 *   `whole_months(tstzrange)`,
 */
create function whole_months(tstzrange)
    returns integer
    returns null on null input
    language sql
    immutable
    leakproof
    parallel safe
    return case
        when isempty($1) then 0
        when upper_inf($1) or lower_inf($1) then null
        else (
            select
                count(*)
            from
                generate_series(lower($1), upper($1), '1 month'::interval) as m
            where
                m + '1 month'::interval <= upper($1)
        )
    end;

comment on function whole_months(tstzrange) is
$md$Get the number of whole months that passed within the given `tstzrange` value.

This function takes the month length into account.

Inclusivity vs exclusivity of the `tstzrange` bounds are ignored by this
function.
$md$;

/**
 * CHANGELOG.md:
 *
 *   + including a procedure to test said function:
 *     `test__whole_months_from_tstzrange()`.
 */
create procedure test__whole_months_from_tstzrange()
    set pg_readme.include_this_routine_definition to true
    set plpgsql.check_asserts to true
    language plpgsql
    as $$
declare
    _tstzrange tstzrange;
    _expected_int int;
begin
    for _tstzrange, _expected_int in
        select
            tstzrange(lower_bound::timestamptz, upper_bound::timestamptz)
            ,expected_int
        from
            (
                values
                     ('2025-05-01', '2025-06-01', 1)
                    ,('2025-05-01', '2025-06-30', 1)
                    ,('2025-05-01', '2025-07-01', 2)
                    ,('2025-05-01', '2025-07-31', 2)
                    ,('2025-05-01', '2025-07-02', 2)
            ) as v (lower_bound, upper_bound, expected_int)
    loop
        assert whole_months(_tstzrange) = _expected_int, format(
            'whole_months(%L::tstzrange) = %s ≠ %s'
            ,_tstzrange, whole_months(_tstzrange), _expected_int
        );
    end loop;
end;
$$;


/**
 * CHANGELOG.md:
 *
 * - The readability and assertion failure output of the
 *   `test__whole_months_from_tstzrange()` procedure was improved.
 */
create or replace procedure test__whole_days_from_tstzrange()
    set pg_readme.include_this_routine_definition to true
    set plpgsql.check_asserts to true
    language plpgsql
    as $$
declare
    _tstzrange tstzrange;
    _2nd_arg float;
    _expected_int int;
begin
    -- First, we test the truncating without awareness of bound in/exclusivity.
    for _tstzrange, _expected_int in
        select
            tstzrange(lower_bound::timestamptz, upper_bound::timestamptz, bound_type)
            ,expected_int
        from (
            values
                ('2024-06-15 01:10', '2024-06-16 01:10', 1)
                ,('2021-12-01', '2022-01-01', 31)
                ,('2022-01-01', '2022-01-10', 9)
                ,('2022-01-01', '2022-01-01', 0)
                ,('2022-01-01', '2022-01-01 23:59:59.999999', 0)
        ) as without_bound_types (lower_bound, upper_bound, expected_int)
        cross join (
            values ('[]'), ('[)'), ('(]'), ('()')
        ) as bound_types (bound_type)
    loop
        assert whole_days(_tstzrange) = _expected_int, format(
            'whole_days(%L::tstzrange) = %s ≠ %s'
            ,_tztzrange, whole_days(_tstzrange) _expected_int
        );

        assert whole_days(_tstzrange, 0.0) = _expected_int, format(
            'whole_days(%L::tstzrange, %s) = %s ≠ %s'
            ,_tztzrange, 0.0, whole_days(_tstzrange, 0.0), _expected_int
        );
    end loop;

    -- Then we test treating the ranges as discrete ranges with a certain precision.
    assert whole_days('[2022-01-01, 2022-01-01 23:59:59.999999]'::tstzrange, 1.0) = 0;
    assert whole_days('[2022-01-01, 2022-01-02]'::tstzrange, 1.0) = 1;
    assert whole_days('[2022-01-01, 2022-01-02)'::tstzrange, 1.0) = 0;
    assert whole_days('[2022-01-01, 2022-01-02 00:00:00.000001]'::tstzrange, 0.000001) = 1;
    assert whole_days('[2022-01-01, 2022-01-02 00:00:00.000001)'::tstzrange, 0.000001) = 1;
    assert whole_days('[2022-01-01, 2022-01-02 00:00:00.000001)'::tstzrange, 0.000010) = 0;
    assert whole_days('[2022-01-01, 2022-01-02 00:00:00.000010)'::tstzrange, 0.000010) = 1;

    -- PostgreSQL integers do not support positive or negative infinity; `null` will have to do.
    assert whole_days('[,)'::tstzrange) is null;
    assert whole_days('[2024-06-15,)'::tstzrange) is null;
    assert whole_days('[,2024-06-15)'::tstzrange) is null;
end;
$$;