ores.refdata.portfolio_right

-- Whether an account is live. Security definer and narrow: the services that
-- check rights may not read the accounts table, and this answers one yes or
-- no about one account id.
create or replace function ores_refdata_account_is_live_fn(
    p_account_id uuid
) returns boolean as $$
begin
    return exists (
        select 1 from ores_iam_accounts_tbl
        where id = p_account_id
          and valid_to = ores_utility_infinity_timestamp_fn()
    );
end;
$$ language plpgsql stable security definer set search_path = public, pg_temp;

-- Whether an account holds a right at a portfolio node, directly or through
-- an ancestor. The function runs with the caller's rights, so row-level
-- security confines it to the session's tenant whatever tenant is passed.
-- A closed account holds nothing. Portfolio inserts refuse cycles; the depth
-- bound only keeps a corrupt tree from looping.
create or replace function ores_refdata_account_holds_portfolio_right_fn(
    p_tenant_id uuid,
    p_account_id uuid,
    p_portfolio_id uuid,
    p_right_code text
) returns boolean as $$
begin
    return exists (
    with recursive ancestry as (
        select id, parent_portfolio_id, 0 as depth
        from ores_refdata_portfolios_tbl
        where tenant_id = p_tenant_id
          and id = p_portfolio_id
          and valid_to = ores_utility_infinity_timestamp_fn()
        union all
        select p.id, p.parent_portfolio_id, a.depth + 1
        from ores_refdata_portfolios_tbl p
        join ancestry a on p.id = a.parent_portfolio_id
        where p.tenant_id = p_tenant_id
          and p.valid_to = ores_utility_infinity_timestamp_fn()
          and a.depth < 64
    )
        select 1
        from ores_refdata_portfolio_rights_tbl r
        join ancestry a on r.portfolio_id = a.id
        where r.tenant_id = p_tenant_id
          and r.account_id = p_account_id
          and r.right_code = p_right_code
          and r.valid_to = ores_utility_infinity_timestamp_fn()
    ) and ores_refdata_account_is_live_fn(p_account_id);
end;
$$ language plpgsql stable set search_path = public, pg_temp;