ores.refdata.sandbox

-- The anchor is a node of the official tree, never a sandbox portfolio.
if exists (
    select 1 from ores_refdata_portfolios_tbl
    where tenant_id = NEW.tenant_id
      and id = NEW.anchor_portfolio_id
      and valid_to = ores_utility_infinity_timestamp_fn()
      and sandbox_id is not null
) then
    raise exception 'Invalid anchor_portfolio_id: %. A sandbox is anchored at an official portfolio.',
        NEW.anchor_portfolio_id
        using errcode = '23514';
end if;

-- Opening a sandbox, or handing it to a new owner, needs the right to
-- open sandboxes at the anchor. A version that keeps the owner, such as
-- archiving, does not ask again.
if not exists (
    select 1 from ores_refdata_sandboxes_tbl
    where tenant_id = NEW.tenant_id
      and id = NEW.id
      and owner_account_id = NEW.owner_account_id
      and valid_to = ores_utility_infinity_timestamp_fn()
) and not ores_refdata_account_holds_portfolio_right_fn(
    NEW.tenant_id, NEW.owner_account_id, NEW.anchor_portfolio_id, 'open_sandbox'
) then
    raise exception 'Account % may not open a sandbox at portfolio %: it holds no open_sandbox right there.',
        NEW.owner_account_id, NEW.anchor_portfolio_id
        using errcode = '42501';
end if;

-- Whether an account may see a sandbox: its owner always may; anyone with
-- read at the anchor may when it is shared; its members may when it is
-- shared with members. Runs with the caller's rights, so row-level security
-- confines it to the session's tenant. PL/pgSQL, so the members table, which
-- is created after this one, is resolved when the function runs.
create or replace function ores_refdata_account_sees_sandbox_fn(
    p_tenant_id uuid,
    p_account_id uuid,
    p_sandbox_id uuid
) returns boolean as $$
declare
    v_sandbox record;
begin
    select owner_account_id, anchor_portfolio_id, visibility into v_sandbox
    from ores_refdata_sandboxes_tbl
    where tenant_id = p_tenant_id
      and id = p_sandbox_id
      and valid_to = ores_utility_infinity_timestamp_fn();
    if not found then
        return false;
    end if;
    if v_sandbox.owner_account_id = p_account_id then
        return true;
    end if;
    if v_sandbox.visibility = 'shared' then
        return ores_refdata_account_holds_portfolio_right_fn(
            p_tenant_id, p_account_id, v_sandbox.anchor_portfolio_id, 'read');
    end if;
    if v_sandbox.visibility = 'members' then
        return exists (
            select 1 from ores_refdata_sandbox_members_tbl
            where tenant_id = p_tenant_id
              and sandbox_id = p_sandbox_id
              and account_id = p_account_id
              and valid_to = ores_utility_infinity_timestamp_fn()
        );
    end if;
    return false;
end;
$$ language plpgsql stable set search_path = public, pg_temp;

-- The account of the user the session acts for, from app.current_actor.
-- Security definer and narrow, for the same reason as the liveness check:
-- it answers one id, for the session's own actor.
create or replace function ores_refdata_actor_account_id_fn(
    p_tenant_id uuid
) returns uuid as $$
declare
    v_id uuid;
begin
    select id into v_id
    from ores_iam_accounts_tbl
    where username = ores_iam_current_actor_fn()
      and tenant_id in (p_tenant_id, ores_utility_system_tenant_id_fn())
      and valid_to = ores_utility_infinity_timestamp_fn()
    order by (tenant_id = p_tenant_id) desc
    limit 1;
    return v_id;
end;
$$ language plpgsql stable security definer set search_path = public, pg_temp;

-- Whether the session's actor may see a sandbox. A session with no actor,
-- such as a system process, sees none. The portfolio policy uses it to keep
-- a sandbox's portfolios out of every read and write by anyone else.
create or replace function ores_refdata_actor_sees_sandbox_fn(
    p_tenant_id uuid,
    p_sandbox_id uuid
) returns boolean as $$
declare
    v_account uuid := ores_refdata_actor_account_id_fn(p_tenant_id);
begin
    return v_account is not null
       and ores_refdata_account_sees_sandbox_fn(p_tenant_id, v_account, p_sandbox_id);
end;
$$ language plpgsql stable set search_path = public, pg_temp;