ores.trading.structure_member

-- A leg fills a role its template allows, and no more legs than the
-- template states for that role. A package rests on no template, so it
-- constrains nothing and any role is allowed.
declare
    v_template text;
    v_max      integer;
    v_used     integer;
begin
    select template_code into v_template
    from ores_trading_structures_tbl
    where tenant_id = NEW.tenant_id and id = NEW.structure_id;

    if v_template is not null then
        select max_legs into v_max
        from ores_trading_structure_template_roles_tbl
        where tenant_id = ores_utility_system_tenant_id_fn()
          and template_code = v_template
          and role = NEW.role
          and valid_to = ores_utility_infinity_timestamp_fn();

        if not found then
            raise exception 'Invalid role: %. The template % does not allow this role.',
                NEW.role, v_template
                using errcode = '23514';
        end if;

        -- A maximum of zero means the role states no upper bound.
        if v_max > 0 then
            select count(*) into v_used
            from ores_trading_structure_members_tbl
            where tenant_id = NEW.tenant_id
              and structure_id = NEW.structure_id
              and role = NEW.role
              and valid_to = ores_utility_infinity_timestamp_fn();

            if v_used >= v_max then
                raise exception 'Invalid role: %. The template % allows % of that role and the structure holds %.',
                    NEW.role, v_template, v_max, v_used
                    using errcode = '23514';
            end if;
        end if;
    end if;
end;